File Exchange

image thumbnail

xlswrite1

version 1.0.0.0 (3.24 KB) by Matt Swartz
increases speed of xlswrite function when used in loops or multiple times.

34 Downloads

Updated 21 Mar 2006

No License

This code increases the speed of the xlswrite function when used in loops or multiple times. The problem with the original function is that it opens and closes the Excel server every time the function is used. To increase the speed I have just edited the original function by removing the server open and close function from the xlswrite function and moved them outside of the function. To use this first run the following code which opens the activex server and checks to see if the file already exists (creates if it doesnt):

Excel = actxserver ('Excel.Application');
File='C:\YourFileFolder\FileName';
if ~exist(File,'file')
ExcelWorkbook = Excel.workbooks.Add;
ExcelWorkbook.SaveAs(File,1);
ExcelWorkbook.Close(false);
end
invoke(Excel.Workbooks,'Open',File);

Then run the new xlswrite1 function as many times as needed or in a loop (for example xlswrite1(File,data,location). Then run the following code to close the activex server:

invoke(Excel.ActiveWorkbook,'Save');
Excel.Quit
Excel.delete
clear Excel

This works exactly like the original xlswrite function, only many many times faster.

Comments and Ratings (103)

Hariprasad

I'm unable to use this function as it is in my Linux machine.
I get the error :-
%%%%%
Error using actxserver (line 90)
Server creation failed. Invalid ProgID 'Excel.Application'.
%%%
I understand active x server/ Excel are not present in linux that is why I get this error.
I changed to xlwrite which works in linux but is too slow for my job. I need to write 9 excel files and 12 sheets within each file with about 100000 rows. I tried xlswrite1 on my Windows machine and its sooper fast but I need to run my function on Linux machine.
Any workaround please.

Nick Oatley

I've updated the script in line with R2018b, and moved the reference to Excel to being persistent within the function to make it even easier to use. Please see

https://uk.mathworks.com/matlabcentral/fileexchange/69745-xlswriteex

Nick.

Austin Bond

USE THE FOLLOWING SCRIPT TO AVOID THE "Object returned error code: 0x800A03EC" ERROR ON LINE 142 OR 143:

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

function xlswrite1(file,data,sheet,range)
Excel=evalin('caller','Excel'); %Modification to allow xlswrite1.m to be a subfunction
% Excel=evalin('base','Excel');
% Set default values.
Sheet1 = 1;

if nargin < 3
sheet = Sheet1;
range = '';
elseif nargin < 4
range = '';
end

if nargout > 0
success = true;
message = struct('message',{''},'identifier',{''});
end

% Handle input.
try
% handle requested Excel workbook filename.
if ~isempty(file)
if ~ischar(file)
error('MATLAB:xlswrite:InputClass','Filename must be a string');
end
% check for wildcards in filename
if any(findstr('*', file))
error('MATLAB:xlswrite:FileName', 'Filename must not contain *');
end
[Directory,file,ext]=fileparts(file);
if isempty(ext) % add default Excel extension;
ext = '.xls';
end
file = abspath(fullfile(Directory,[file ext]));
[a1 a2 a3] = fileattrib(file);
if a1 && ~(a2.UserWrite == 1)
error('MATLAB:xlswrite:FileReadOnly', 'File can not be read only.');
end
else % get workbook filename.
error('MATLAB:xlswrite:EmptyFileName','Filename is empty.');
end

% Check for empty input data
if isempty(data)
error('MATLAB:xlswrite:EmptyInput','Input array is empty.');
end

% Check for N-D array input data
if ndims(data)>2
error('MATLAB:xlswrite:InputDimension',...
'Dimension of input array cannot be higher than two.');
end

% Check class of input data
if ~(iscell(data) || isnumeric(data) || ischar(data)) && ~islogical(data)
error('MATLAB:xlswrite:InputClass',...
'Input data must be a numeric, cell, or logical array.');
end


% convert input to cell array of data.
if iscell(data)
A=data;
else
A=num2cell(data);
end

if nargin > 2
% Verify class of sheet parameter.
if ~(ischar(sheet) || (isnumeric(sheet) && sheet > 0))
error('MATLAB:xlswrite:InputClass',...
'Sheet argument must a string or a whole number greater than 0.');
end
if isempty(sheet)
sheet = Sheet1;
end
% parse REGION into sheet and range.
% Parse sheet and range strings.
if ischar(sheet) && ~isempty(strfind(sheet,':'))
range = sheet; % only range was specified.
sheet = Sheet1;% Use default sheet.
elseif ~ischar(range)
error('MATLAB:xlswrite:InputClass',...
'Range argument must a string of Excel A1 notation.');
end
end

catch
if ~isempty(nargchk(2,4,nargin))
error('MATLAB:xlswrite:InputArguments',nargchk(2,4,nargin));
elseif nargout == 0
rethrow(lasterror); % Display last error.
else
success = false;
message = lasterror; % Return last error.
end
return;
end
%------------------------------------------------------------------------------
try
% Construct range string
if isempty(strfind(range,':'))
% Range was partly specified or not at all. Calculate range.
[m,n] = size(A);
range = calcrange(range,m,n);
end
catch
if nargout == 0
rethrow(lasterror); % Display last error.
else
success = false;
message = lasterror; % Return last error.
end
return;
end

%------------------------------------------------------------------------------
try
% if ~exist(file,'file')
% % Create new workbook.
%
% %This is in place because in the presence of a Google Desktop
% %Search installation, calling Add, and then SaveAs after adding data,
% %to create a new Excel file, will leave an Excel process hanging.
% %This workaround prevents it from happening, by creating a blank file,
% %and saving it. It can then be opened with Open.
% ExcelWorkbook = Excel.workbooks.Add;
% ExcelWorkbook.SaveAs(file,1);
% ExcelWorkbook.Close(false);
% end

%Open file
%ExcelWorkbook = Excel.workbooks.Open(file);

try % select region.
% Activate indicated worksheet.
message = activate_sheet(Excel,sheet);

% Select range in worksheet.
Select(Range(Excel,sprintf('%s',range)));

catch % Throw data range error.
error('MATLAB:xlswrite:SelectDataRange',lasterr);
end

% Export data to selected region.
set(Excel.selection,'Value',A);
%ExcelWorkbook.Save
%ExcelWorkbook.Close(false) % Close Excel workbook.
%Excel.Quit;
catch
try
%ExcelWorkbook.Close(false); % Close Excel workbook.
end
%Excel.Quit;
%delete(Excel); % Terminate Excel server.
if nargout == 0
rethrow(lasterror); % Display last error.
else
success = false;
message = lasterror; % Return last error.
end
end
end

%--------------------------------------------------------------------------
function message = activate_sheet(Excel,Sheet)
% Activate specified worksheet in workbook.

% Initialise worksheet object
WorkSheets = Excel.sheets;
message = struct('message',{''},'identifier',{''});

% Get name of specified worksheet from workbook
try
TargetSheet = get(WorkSheets,'item',Sheet);
catch
% Worksheet does not exist. Add worksheet.
TargetSheet = addsheet(WorkSheets,Sheet);
warning('MATLAB:xlswrite:AddSheet','Added specified worksheet.');
if nargout > 0
[message.message,message.identifier] = lastwarn;
end
end

% activate worksheet
Activate(TargetSheet);
end

%------------------------------------------------------------------------------
function newsheet = addsheet(WorkSheets,Sheet)
% Add new worksheet, Sheet into worsheet collection, WorkSheets.

if isnumeric(Sheet)
% iteratively add worksheet by index until number of sheets == Sheet.
while WorkSheets.Count < Sheet
% find last sheet in worksheet collection
lastsheet = WorkSheets.Item(WorkSheets.Count);
newsheet = WorkSheets.Add([],lastsheet);
end
else
% add worksheet by name.
% find last sheet in worksheet collection
lastsheet = WorkSheets.Item(WorkSheets.Count);
newsheet = WorkSheets.Add([],lastsheet);
end
% If Sheet is a string, rename new sheet to this string.
if ischar(Sheet)
set(newsheet,'Name',Sheet);
end
end

%------------------------------------------------------------------------------
function [absolutepath]=abspath(partialpath)

% parse partial path into path parts
[pathname filename ext] = fileparts(partialpath);
% no path qualification is present in partial path; assume parent is pwd, except
% when path string starts with '~' or is identical to '~'.
if isempty(pathname) && isempty(strmatch('~',partialpath))
Directory = pwd;
elseif isempty(regexp(partialpath,'(.:|\\\\)')) && ...
isempty(strmatch('/',partialpath)) && ...
isempty(strmatch('~',partialpath));
% path did not start with any of drive name, UNC path or '~'.
Directory = [pwd,filesep,pathname];
else
% path content present in partial path; assume relative to current directory,
% or absolute.
Directory = pathname;
end

% construct absulute filename
absolutepath = fullfile(Directory,[filename,ext]);
end

%------------------------------------------------------------------------------
function range = calcrange(range,m,n)
% Calculate full target range, in Excel A1 notation, to include array of size
% m x n

range = upper(range);
cols = isletter(range);
rows = ~cols;
% Construct first row.
if ~any(rows)
firstrow = 1; % Default row.
else
firstrow = str2double(range(rows)); % from range input.
end
% Construct first column.
if ~any(cols)
firstcol = 'A'; % Default column.
else
firstcol = range(cols); % from range input.
end
try
lastrow = num2str(firstrow+m-1); % Construct last row as a string.
firstrow = num2str(firstrow); % Convert first row to string image.
% lastcol = dec2base27(base27dec(firstcol)+n-1); % DELETE THIS LINE OF CODE!!! THIS IS WHERE THE ERROR IS

% THIS IS THE SOLUTION TO THE PROBLEM
% - Basically just use MathWorks canned functions from the xlswrite.m function, specifically calcrange.m
% columnNumber.m - xlswrite1.m will need access to these (whether in the main body of the script, same directory, or added to the path)
% columnLetter.m - xlswrite1.m will need access to these (whether in the main body of the script, same directory, or added to the path)

%ANSWER IS HERE!!! RIGHT BELOW!!!
lastcol = columnLetter(columnNumber(firstcol)+n-1); % Construct last column. ADD THIS LINE OF CODE

range = [firstcol firstrow ':' lastcol lastrow]; % Final range string.
catch
error('MATLAB:xlswrite:CalculateRange',...
'Data range must be between A1 and IV65536.');
end
end

%------------------------------------------------------------------------------
% function s = dec2base27(d)
%
% % DEC2BASE27(D) returns the representation of D as a string in base 27,
% % expressed as 'A'..'Z', 'AA','AB'...'AZ', until 'IV'. Note, there is no zero
% % digit, so strictly we have hybrid base26, base27 number system. D must be a
% % negative integer bigger than 0 and smaller than 2^52, which is the maximum
% % number of columns in an Excel worksheet.
% %
% % Examples
% % dec2base(1) returns 'A'
% % dec2base(26) returns 'Z'
% % dec2base(27) returns 'AA'
% %-----------------------------------------------------------------------------
% b = 26;
% symbols = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
%
% d = d(:);
% if d ~= floor(d) | any(d < 0) | any(d > 1/eps)
% error('MATLAB:xlswrite:Dec2BaseInput',...
% 'D must be an integer, 0 <= D <= 2^52.');
% end
%
% % find the number of columns in new base
% n = max(1,round(log2(max(d)+1)/log2(b)));
% while any(b.^n <= d)
% n = n + 1;
% end
%
% % set b^0 column
% s(:,n) = rem(d,b);
% while n > 1 && any(d)
% if s(:,n) == 0
% s(:,n) = b;
% end
% if d > b
% % after the carry-over to the b^(n+1) column
% if s(:,n) == b
% % for the b^n digit at b, set b^(n+1) digit to b
% s(:,n-1) = floor(d/b)-1;
% else
% % set the b^(n+1) digit to the new value after the last carry-over.
% s(:,n-1) = rem(floor(d/b),b);
% end
% else
% s(:,n-1) = []; % remove b^(n+1) digit.
% end
% n = n - 1;
% end
% s = symbols(s);
% end
%
% %------------------------------------------------------------------------------
% function d = base27dec(s)
% % BASE27DEC(S) returns the decimal of string S which represents a number in
% % base 27, expressed as 'A'..'Z', 'AA','AB'...'AZ', until 'IV'. Note, there is
% % no zero so strictly we have hybrid base26, base27 number system.
% %
% % Examples
% % base27dec('A') returns 1
% % base27dec('Z') returns 26
% % base27dec('IV') returns 256
% %-----------------------------------------------------------------------------
%
% d = 0;
% b = 26;
% n = numel(s);
% for i = n:-1:1
% d = d+(s(i)-'A'+1)*(b.^(n-i));
% end
% end

%-------------------------------------------------------------------------------
function s = columnLetter(d)
% COLUMNLETTER(D) returns the representation of D as a spreadsheet column letter,
% expressed as 'A'..'Z', 'AA','AB'...'AZ', and so on.
%
% Examples:
% dec2base(1) returns 'A'
% dec2base(26) returns 'Z'
% dec2base(27) returns 'AA'
%
% See also matlab.io.spreadsheet.internal.columnNumber

% Copyright 2014-2016 The MathWorks, Inc.

persistent powersOf26
if isempty(powersOf26)
powersOf26 = 26.^(1:11);
end

digits = 1;
begin = 0;
current_sum = 26;
% This calculates the number of "letter-digits" in the output
while d > current_sum
digits = digits + 1;
begin = current_sum;
current_sum = begin + powersOf26(digits);
end

idx = zeros(1,digits);
pos = d - begin;

% Find the leftmost "letter-digits"
for i = digits:-1:3
remainder = rem(pos-1, powersOf26(i-1)) + 1;
idx(i) = (pos - remainder)/powersOf26(i-1) + 1;
pos = remainder;
end

% Find the right most "letter-digits"
if digits >= 2
idx(1) = rem(pos-1, 26) + 1;
idx(2) = (pos - idx(1))/26 + 1;
else
idx(1) = pos;
end

% Write out the reverse digits using char
s = char('A'-1+idx(end:-1:1));
end

%-------------------------------------------------------------------------------
function d = columnNumber(s)
% COLUMNNUMBER(S) returns the column number of S which is a spreadsheeet
% column letter 'A'..'Z', 'AA','AB'...'AZ', and so on.
%
% Examples:
% base27dec('A') returns 1
% base27dec('Z') returns 26
% base27dec('IV') returns 256
%
% See also matlab.io.spreadsheet.internal.columnLetter

% Copyright 2014-2016 The MathWorks, Inc.
s = upper(s);
if length(s) == 1
d = s(1) -'A' + 1;
else
cumulative = sum(26.^(1:numel(s)-1));
indexes_fliped = 1 + s - 'A';
indexes = fliplr(indexes_fliped);
indexes_in_cells = mat2cell(indexes, 1, ones(1,numel(indexes))); %#ok<MMTC>
d = cumulative + sub2ind(repmat(26, 1,numel(s)), indexes_in_cells{:});
end
end

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

If you wish to avoid having the error('MATLAB:xlswrite:SelectDataRange',lasterr);

You can use the following modification of the xlswrite1 function:

function xlswrite1(file,data,sheet,range)
Excel = evalin('caller','Excel');
% Set default values.
Sheet1 = 1;

if nargin < 3
sheet = Sheet1;
range = '';
elseif nargin < 4
range = '';
end

if nargout > 0
success = true;
message = struct('message',{''},'identifier',{''});
end

% Handle input.
try
% handle requested Excel workbook filename.
if ~isempty(file)
if ~ischar(file)
error('MATLAB:xlswrite:InputClass','Filename must be a string');
end
% check for wildcards in filename
if any(findstr('*', file))
error('MATLAB:xlswrite:FileName', 'Filename must not contain *');
end
[Directory,file,ext]=fileparts(file);
if isempty(ext) % add default Excel extension;
ext = '.xls';
end
file = abspath(fullfile(Directory,[file ext]));
[a1 a2 a3] = fileattrib(file);
if a1 && ~(a2.UserWrite == 1)
error('MATLAB:xlswrite:FileReadOnly', 'File can not be read only.');
end
else % get workbook filename.
error('MATLAB:xlswrite:EmptyFileName','Filename is empty.');
end

% Check for empty input data
if isempty(data)
error('MATLAB:xlswrite:EmptyInput','Input array is empty.');
end

% Check for N-D array input data
if ndims(data)>2
error('MATLAB:xlswrite:InputDimension',...
'Dimension of input array cannot be higher than two.');
end

% Check class of input data
if ~(iscell(data) || isnumeric(data) || ischar(data)) && ~islogical(data)
error('MATLAB:xlswrite:InputClass',...
'Input data must be a numeric, cell, or logical array.');
end


% convert input to cell array of data.
if iscell(data)
A=data;
else
A=num2cell(data);
end

if nargin > 2
% Verify class of sheet parameter.
if ~(ischar(sheet) || (isnumeric(sheet) && sheet > 0))
error('MATLAB:xlswrite:InputClass',...
'Sheet argument must a string or a whole number greater than 0.');
end
if isempty(sheet)
sheet = Sheet1;
end
% parse REGION into sheet and range.
% Parse sheet and range strings.
if ischar(sheet) && ~isempty(strfind(sheet,':'))
range = sheet; % only range was specified.
sheet = Sheet1;% Use default sheet.
elseif ~ischar(range)
error('MATLAB:xlswrite:InputClass',...
'Range argument must a string of Excel A1 notation.');
end
end

catch
if ~isempty(nargchk(2,4,nargin))
error('MATLAB:xlswrite:InputArguments',nargchk(2,4,nargin));
elseif nargout == 0
rethrow(lasterror); % Display last error.
else
success = false;
message = lasterror; % Return last error.
end
return;
end
%------------------------------------------------------------------------------
try
% Construct range string
if isempty(strfind(range,':'))
% Range was partly specified or not at all. Calculate range.
[m,n] = size(A);
range = calcrange(range,m,n);
end
catch
if nargout == 0
rethrow(lasterror); % Display last error.
else
success = false;
message = lasterror; % Return last error.
end
return;
end

%------------------------------------------------------------------------------
try
if ~exist(file,'file')
% Create new workbook.

%This is in place because in the presence of a Google Desktop
%Search installation, calling Add, and then SaveAs after adding data,
%to create a new Excel file, will leave an Excel process hanging.
%This workaround prevents it from happening, by creating a blank file,
%and saving it. It can then be opened with Open.
ExcelWorkbook = Excel.workbooks.Add;
ExcelWorkbook.SaveAs(file,1);
ExcelWorkbook.Close(false);
end

%Open file
%ExcelWorkbook = Excel.workbooks.Open(file);

try % select region.
% Activate indicated worksheet.
message = activate_sheet(Excel,sheet);

% Select range in worksheet.
Select(Range(Excel,sprintf('%s',range)));
catch % Throw data range error.
error('MATLAB:xlswrite:SelectDataRange',lasterr);
end

% Export data to selected region.
set(Excel.selection,'Value',A);
%ExcelWorkbook.Save
%ExcelWorkbook.Close(false) % Close Excel workbook.
%Excel.Quit;
catch
try
%ExcelWorkbook.Close(false); % Close Excel workbook.
end
%Excel.Quit;
%delete(Excel); % Terminate Excel server.
if nargout == 0
rethrow(lasterror); % Display last error.
else
success = false;
message = lasterror; % Return last error.
end
end
end
%--------------------------------------------------------------------------
function message = activate_sheet(Excel,Sheet)
% Activate specified worksheet in workbook.

% Initialise worksheet object
WorkSheets = Excel.sheets;
message = struct('message',{''},'identifier',{''});

% Get name of specified worksheet from workbook
try
TargetSheet = get(WorkSheets,'item',Sheet);
catch
% Worksheet does not exist. Add worksheet.
TargetSheet = addsheet(WorkSheets,Sheet);
warning('MATLAB:xlswrite:AddSheet','Added specified worksheet.');
if nargout > 0
[message.message,message.identifier] = lastwarn;
end
end

% activate worksheet
Activate(TargetSheet);
end
%------------------------------------------------------------------------------
function newsheet = addsheet(WorkSheets,Sheet)
% Add new worksheet, Sheet into worsheet collection, WorkSheets.

if isnumeric(Sheet)
% iteratively add worksheet by index until number of sheets == Sheet.
while WorkSheets.Count < Sheet
% find last sheet in worksheet collection
lastsheet = WorkSheets.Item(WorkSheets.Count);
newsheet = WorkSheets.Add([],lastsheet);
end
else
% add worksheet by name.
% find last sheet in worksheet collection
lastsheet = WorkSheets.Item(WorkSheets.Count);
newsheet = WorkSheets.Add([],lastsheet);
end
% If Sheet is a string, rename new sheet to this string.
if ischar(Sheet)
set(newsheet,'Name',Sheet);
end
end

%------------------------------------------------------------------------------
function [absolutepath]=abspath(partialpath)

% parse partial path into path parts
[pathname filename ext] = fileparts(partialpath);
% no path qualification is present in partial path; assume parent is pwd, except
% when path string starts with '~' or is identical to '~'.
if isempty(pathname) && isempty(strmatch('~',partialpath))
Directory = pwd;
elseif isempty(regexp(partialpath,'(.:|\\\\)')) && ...
isempty(strmatch('/',partialpath)) && ...
isempty(strmatch('~',partialpath));
% path did not start with any of drive name, UNC path or '~'.
Directory = [pwd,filesep,pathname];
else
% path content present in partial path; assume relative to current directory,
% or absolute.
Directory = pathname;
end

% construct absulute filename
absolutepath = fullfile(Directory,[filename,ext]);
%------------------------------------------------------------------------------
end
function range = calcrange(range,m,n)
% Calculate full target range, in Excel A1 notation, to include array of size
% m x n

range = upper(range);
cols = isletter(range);
rows = ~cols;
% Construct first row.
if ~any(rows)
firstrow = 1; % Default row.
else
firstrow = str2double(range(rows)); % from range input.
end
% Construct first column.
if ~any(cols)
firstcol = 'A'; % Default column.
else
firstcol = range(cols); % from range input.
end
try
lastrow = num2str(firstrow+m-1); % Construct last row as a string.
firstrow = num2str(firstrow); % Convert first row to string image.
lastcol = dec2base27(base27dec(firstcol)+n-1); % Construct last column.

range = [firstcol firstrow ':' lastcol lastrow]; % Final range string.
catch
error('MATLAB:xlswrite:CalculateRange',...
'Data range must be between A1 and IV65536.');
end
end
%------------------------------------------------------------------------------
function s = dec2base27(d)
d = d(:);
if d ~= floor(d) || any(d(:) < 0) || any(d(:) > 1/eps)
error(message('MATLAB:xlswrite:Dec2BaseInput'));
end
[num_digits, begin] = calculate_range(d);
s = index_to_string(d, begin, num_digits);
end

%----------------------------------------------------------------------
function string = index_to_string(index, first_in_range, digits)

letters = 'A':'Z';
working_index = index - first_in_range;
outputs = cell(1,digits);
[outputs{1:digits}] = ind2sub(repmat(26,1,digits), working_index);
string = fliplr(letters([outputs{:}]));
end
%----------------------------------------------------------------------
function [digits, first_in_range] = calculate_range(num_to_convert)

digits = 1;
first_in_range = 0;
current_sum = 26;
while num_to_convert > current_sum
digits = digits + 1;
first_in_range = current_sum;
current_sum = first_in_range + 26.^digits;
end
end

%------------------------------------------------------------------------------
function d = base27dec(s)
if length(s) == 1
d = s(1) -'A' + 1;
else
cumulative = 0;
for i = 1:numel(s)-1
cumulative = cumulative + 26.^i;
end
indexes_fliped = 1 + s - 'A';
indexes = fliplr(indexes_fliped);
indexes_in_cells = mat2cell(indexes, 1, ones(1,numel(indexes))); %#ok<MMTC>
d = cumulative + sub2ind(repmat(26, 1,numel(s)), indexes_in_cells{:});
end
%-------------------------------------------------------------------------------
end

Thank you very much for your contributions. Finally I succeeded in using this function. This is very very powerful function!

Austin Bond

Note, the prior comment (from Mathieu on 6 Sep 2016) is correct. The statement

Excel=evalin('base','Excel');

will need to be replaced with the statement

Excel=evalin('caller','Excel');

to allow xlswrite1.m to be a subfunction

Austin Bond

Ok, I have modified the script (slightly) to facilitate a simple copy+paste test run.

This script should work flawlessly with the UNMODIFIED xlswrite1.m function provided on this thread. The modifications made were to: (1) prompt the user to specify the .xlsx (or similar format) file, and (2) use the current directory to define the FULL PATH that the 'File' variable requires.

The 'cd' (current directory) function has been added in order to make the script work under any directory. You will need to make sure xlswrite1.m is visible to MatLab before for running the script. Thus, you'll either need to ad the xlswrite1.m to the current directory that you are running within, OR you can put the xlswrite1.m in a directory of your choice. You MAY need to update the MatLab Path AFTER pasting the xlswrite1.m function in the directory of your choice. If you choose this route you will probably need to run the command "rehash toolboxcache" or rehash command to make MatLab update the path. If MatLab has problems finding the function in your directory-of-choice, then you may want to run the "which -all xlswrite1" command, which will indicate which folder MatLab thinks the xlswrite1.m file resides.

%--------------------BEGINNING OF SCRIPT-----------------------------------
%% Comments
% This script will write an 8×8 matrix of random values (line 37) to
% cell 'C8' of the specified Excel spreadsheet, which is prompted for
% user input (line 13)

%% Opening Statements
fclose all; close all; clear; clc; tic;
disp('Code starting @'), disp(datetime)

%% Specifying Input Spreadsheet Filename

Spreadsheet_Name = input('insert your file name here '); %for example: 'testFinal4.xlsx';

%% Chandani's Code (from MathWorks Technical Support Team – 04/18/2018)

%Opening Excel appliction
Excel = actxserver ('Excel.Application');

%Specifying Excel file of interest (with the FULL path)
File = [cd,'\',Spreadsheet_Name];

%Checking if Excel file exist, and creating it if not.
%note this is done within xlswrite1.m function, see lines 118--132 within xlswrite1.m
%but the subsequent 'invoke' command/function (line 24) will not work in the event that the
%Excel file does not exist a priori.
if ~exist(File,'file')
ExcelWorkbook = Excel.workbooks.Add;
ExcelWorkbook.SaveAs(File);
ExcelWorkbook.Close(false);
end

%Opening Excel file
invoke(Excel.Workbooks,'Open',File);

%Creating an output matrix of random numbers
output_mat = rand(8,8);

%Calling xlswrite
xlswrite1(File,output_mat,'test sheet', 'C8');

%Closing Excel file
invoke(Excel.ActiveWorkbook,'Save');
Excel.Quit
Excel.delete
clear Excel

%% Termination Statements

disp(['Code completed after ', num2str(toc), ' seconds'])
%-----------------------END OF SCRIPT--------------------------------------

Austin Bond

Austin Bond

To All:

For those who don't want to waste half their day deciphering/debugging the supplied script (with the "File,1" typo) – try my example (featured below) first.
-- note: you'll have to update the file name/path variable near ~Line 11 ---> File='C:\YourFileFolder\FileName')

%-------------------------------------------------------BEGINNING OF SCRIPT-------------------------------------------------------------------------
%% Opening Statements
fclose all; close all; clear; clc; tic;
disp('Code starting @'), disp(datetime)

%% Chandani's Code (from MathWorks Technical Support Team – 04/18/2018)

%Opening Excel appliction
Excel = actxserver ('Excel.Application');

%Specifying Excel file of interest (with the FULL path)
File='C:\YourFileFolder\FileName';

%Checking if Excel file exist, and creating it if not.
%note this is done within xlswrite1.m function, see lines 118--132 within xlswrite1.m
%but the subsequent 'invoke' command/function (line 24) will not work in the event that the
%Excel file does not exist a priori.
if ~exist(File,'file')
ExcelWorkbook = Excel.workbooks.Add;
ExcelWorkbook.SaveAs(File);
ExcelWorkbook.Close(false);
end

%Opening Excel file
invoke(Excel.Workbooks,'Open',File);

%Creating an output matrix of random numbers
output_mat = rand(8,8);

%Calling xlswrite
xlswrite1(File,output_mat,'test sheet', 'C8');

%Closing Excel file
invoke(Excel.ActiveWorkbook,'Save');
Excel.Quit
Excel.delete
clear Excel

%% Termination Statements

disp(['Code completed after ', num2str(toc), ' seconds'])

%-------------------------------------------------------END OF SCRIPT-----------------------------------------------------------------------------

The xlswrite1.m function should run as-is; however, lines 118 -- 129 can be commented out since the Excel file is created before calling xlswrite, in the event that it does not already exist a priori.

My experience with this function REDUCED THE RUNTIME DOWN FROM 45 MIN TO UNDER 1 MINUTE!!!

Thanks to Matt Swartz! This function has saved my post-processing regimen.

Hi all,
I got the error:
Error in xlswrite1 (line 142)
error('MATLAB:xlswrite:SelectDataRange',lasterr);

I guess, it is because of the too big size of my resulting excel table. However, it seems that this problem can be solved somehow. Can anybody share the solution, please?

Best,

Ian Blum

I really love this function, just having a recent problem though. When the particular data I want to write is a 80x6 cell with each cell containing a string of text, the entire dataset doesn't get entered into the excel file. I have no such problem when the cell contains a matrix of numbers or if it contains a cell with a single string of text (1x1 cell). I looked at the code and I cannot for the life of me figure out what is wrong.

I have verified that it can properly detect the size of the cell and the best I can figure is that set(Excel.selection,'Value',A) somehow does stumbles with a multi-entry cell of m x n when each cell entry contains a string.

Any help or a workaround would be greatly appreciated

ceinem

Ltsmb

Try this code. And try the solutions of @Kevin Gaukel and @Bharath, but still the same error:
Error using Interface.000208DB_0000_0000_C000_000000000046/Open
Invoke Error, Dispatch Exception:
Source: Microsoft Excel
Description: Impossible d'ouvrir le fichier « results_S1.xlsx » car son format ou son extension n'est pas valide. Vérifiez que le
fichier n'est pas endommagé et que son extension correspond bien au format du fichier.
Help File: xlmain11.chm
Help Context ID: 0

Error in errorsS1 (line 67)
invoke(Excel.Workbooks,'Open',xlsfile);

Any help please...Thank you

Yu Li

what a huge descrease in my exporting the file!!!

in my case there is a lot of sheet need to be write into the excel, it save me a lot of time while using 'excelwrite1'.

thank you so much.

This increased the speed of my program by 15X! After debugging using others helpful comments, my script would run a few loops successfully but then start creating bad excel files with missing data etc. I looked in the task manager and there were dozens of open excel processes in the background. I found that adding

system('taskkill /F /IM EXCEL.EXE');

after the clear Excel helped by closing any open excel processes.

With all the corrections suggested by the other users, it works like a charm! Thanks everyone!

Is this confirmed to work when writing to different excel files?

Hello,

Testing the function, I receive the error ACR had in May 24, 2016:

Error using xlswrite1 (line 142)

Error in xlswrite1 (line 142)
error('MATLAB:xlswrite:SelectDataRange',lasterr);

Any help will be appreciated.

Mathieu

What I did to make work this function with my program :
- Saveas(File) instead of Saveas(File,1)
- Excel=evalin('caller','Excel') instead of Excel=evalin('base','Excel') at the beginning of the function (because it is a subfunction)

But really great work! 2 seconds instead of 22 for me. Amazing !

ACR

All,

Ignore question. Issue resolved.

ACR

Hi All,

Testing the function, I receive the following error:

error('MATLAB:xlswrite:SelectDataRange',lasterr);

Example:

xlswrite1('test.xlsx',[1],1,'A1');

Error using xlswrite1 (line 142)
Error in xlswrite1 (line 142) error('MATLAB:xlswrite:SelectDataRange',lasterr);

* Another user, Irene posted this error message on 7.23.14.

I'm running MatLab 8.6.0.267246 (R2015b).

Any help is greatly appreciated.

Hello,
I get the following error message:
Error using evalin
Undefined function or variable 'Excel'.

Pls help me!

Hariprasad

My bad. I made the change as shown by @Christine. Changed
ExcelWorkbook.SaveAs(File,1);
to
ExcelWorkbook.SaveAs(File);
and it worked.

Meade

@Daniel

Make sure your file name includes the extension. Ex: 'C:\Folder\Myfile.xlsx'
I think you're asking the Excel object to save a file without knowing what it is. Just a thought.

When using xlswrite1, I havae been having some issues with it correctly writing data to the spread sheet; it doesnt upload at all. When uploading this:

%comment
Excel = actxserver ('Excel.Application');
File='C:\YourFileFolder\FileName';
if ~exist(File,'file')
ExcelWorkbook = Excel.workbooks.Add;
ExcelWorkbook.SaveAs(File,1);
ExcelWorkbook.Close(false);
end
invoke(Excel.Workbooks,'Open',File);
I get this: Error using Interface.Microsoft_Excel_11.0_Object_Library._Workbook/SaveAs.

I cannot use xlswrite due to the amount of things I have, but xlswrite1 isnt working. How can I fix xlswrite1 so it not only doesnt give me this error, but it allows for the data to be transferred to the excel document. This is adding onto my last question which can be viewed here: http://www.mathworks.com/matlabcentral/answers/266354-how-can-i-use-xlswrite-in-a-nested-forloop-without-overwriting

Meade

For those having trouble using xlswrite1 within a function:

Current syntax fails:
Excel = evalin('base','Excel');

As others have suggested, you can change 'base' to 'caller', but I believe this is limited to when used as a subfunction.

Another solution is to have Matlab check for all open ActiveX COM connections (whether in the base workspace or not).
Excel = actxGetRunningServer('Excel.Application');

Either way, Thanks @Matt Swartz for the great submission!

cai onion

I try use xlswrite1 to write the same data into multiple excel files once,
For example,
xlswrite1(Directory1,data);
xlswrite1(Directory2,data);
xlswrite1(Directory3,data);

However, I got a error, which means that the opened and the saving files have the same file name.

I just wanted to say that my co-workers and I use this function ALL the time. Great stuff.

Lun Lee

This is really awesome, run super fast now! Thanks a lot :)

Ariel

Perfect, thanks.

Hi,

I want to use it in GUI. I have used it but it gives error.
Kinldy help me out.

Thanks,
Suraj

Christine

@Bharath and Kevin: thanks for the advice of using

ExcelWorkbook.SaveAs(fname);

instead of

ExcelWorkbook.SaveAs(fname,1);

I had the same problem.

@Kevin: to avoid using an Excel instance named exactly 'Excel', you can just add an additional argument 'Excel' to the function and delete the line

Excel = evalin('base','Excel');

at the very beginning of the function.

Then you can use for example the following code:

e = actxserver('excel.application');
if ~exist(File,'file')
excelWb = e.workbooks.Add;
excelWb.SaveAs(File);
excelWb.Close(false);
end
invoke(e.Workbooks,'Open',File);
xlswrite1(File,data,sheet,range,e);

Hope that helps.

Best
Christine

Bharath,

I just found that out myself. Now the sheets are present again. I don't know if this is Microsoft or my own oversight, but conditions changed.

Thank you for confirming that.

Bharath

********Regarding the .xlsx file issue**********

Just found out the solution for error mentioned by me and Kevin Gaukel. But unlike him my Excel file is created with Sheets, yet i still had the "Invoke Error, Dispatch Exception: " error.

I found out that in the saving line
>>ExcelWorkbook.SaveAs(fname,1);
the optional argument 1 is making the errors.
If i change the line to
>>ExcelWorkbook.SaveAs(fname);
it works perfectly.

That argument i think is an Enumeration Constant specifying the file format.
link to the method:
https://msdn.microsoft.com/en-us/library/office/ff841185.aspx

Bharath

I am having the same issue as mentioned by Kevin Gaukel.
I have tried the solution provided by him, but still couldn't resolve the issue.
Is there any other way.

P.S : There is no error while using invoke if i create a ".xls" file. The issue is only with the ".xlsx" file.

I was having a very frustrating situation happen recently where (out of nowhere), the program as shown below would not work. I would get

Error using Interface.000208DB_0000_0000_C000_000000000046/Open
Invoke Error, Dispatch Exception:
Source: Microsoft Office Excel
Description: Excel cannot open the file 'testfile.xlsx' because the file format or file
extension is not valid. Verify that the file has not been corrupted and that the file extension
matches the format of the file.
Help File: C:\Program Files (x86)\Microsoft Office\Office12\1033\XLMAIN11.CHM
Help Context ID: 0

Further, the Excel file would not open, claiming the file was invalid or corrupted.

Well, I just learned why - and it's a warning based on a recent Microsoft update.

When I stepped through a similar program where it called an existing file, it worked without problems. However, when I did a Excel.Workbooks.Add then saved the file, I crashed when I reopened the file. I then added a step after the Workbooks.Add

set(Excel,'Visible',1);

and found the problem. The Excel File was opening...WITHOUT a single TAB. No sheets to store data. I suspect a recent Microsoft update created this problem.

What I did to solve this is to do the following:

Excel = actxserver('Excel.Application');
fname = fullfile(pwd,'testfile.xlsx');
if ~exist(fname,'file')
ExcelWorkbook = Excel.Workbooks.Add;
>>> ExcelWorkbook.Sheets.Add;
ExcelWorkbook.SaveAs(fname,1);
ExcelWorkbook.Close(false);
end
invoke(Excel.Workbooks,'Open',fname);

and the errors stopped.

If you have this problem, try that and it may disappear.

Good program. Main thing - which seems to be missing from my list is that the "Excel" name is actually used in the xlswrite1 function instead of an arbitrary name (like hExcel for the handle). This is due to the evalin('base','Excel') function. That makes the function "not self contained" like xlswrite. However, the xlswrite1 is MUCH faster than xlswrite on my specific requirement. As a result, I am rating it 5 stars.

Sasquatch

Took me literally 2 hours to get both xlswrite1 and xlsread1 working but I realized I wasn't READING and I got it to work.

For those of you who are trying to follow along but get errors like "Excel Object does not exist" and the crazy "Interface.000208DB" error please follow along:

1) make sure you have added this file to your path. best to just put it in your Documents/MATLAB folder (i'm on windows)
2) be sure to include the code listed in the description before you call any read1/write1 files:

Excel = actxserver ('Excel.Application');
File='C:\Users\aathar\Documents\MATLAB\EnzymeTest.xlsx';
if ~exist(File,'file')
ExcelWorkbook = Excel.workbooks.Add;
ExcelWorkbook.SaveAs(File,1);
ExcelWorkbook.Close(false);
end
try
invoke(Excel.Workbooks,'Open',File);
catch
Excel.Quit;
Excel.delete;
clear Excel;
Excel = actxserver('Excel.Application');
invoke(Excel.Workbooks,'Open',File);
end

3) before you end your function include this code: Excel.ActiveWorkbook.Save;
Excel.Quit
Excel.delete
clear Excel

4) use the tic/toc features to find out how much faster it is. For me it is at least 4x faster which will save me 30+ minutes

Thanks for the help everyone!

kyle

Hello, I am encountering a problem with this function when writing an array with a large number of columns. The data gets written, but the Excel range to write to is incorrect, so it ends up writing a whole bunch of columns with #N/A.

I've dug into the xlswrite1 function and it appears the problem is in the function dec2base27(d). For my example below, the end column should be DGB, but it creates it as GGB. I don't see this problem with smaller data sets however.

My example:
Excel = actxserver('Excel.Application');
% Show window (optional).
Excel.Visible = 0;
% Open file located in the current folder.
Workbook = Excel.Workbooks.Open('TEST.xlsx');

data = ones(50,2887);
xlswrite1('TEST.xlsx',data,'Sheet1','B1');

Workbook.Save;
Excel.Quit;
Excel.release;

Thank you very much!

Clare

To follow up on the error I saw before... I added a try - catch block. In case invoke fails, try again. This solved my problem posted below.

try
invoke(Excel.Workbooks,'Open',fileNameFull);
catch
Excel.Quit;
Excel.delete;
clear Excel;
Excel = actxserver('Excel.Application');
invoke(Excel.Workbooks,'Open',fileNameFull);
end

Clare

Error using Interface.000208DB_0000_0000_C000_000000000046/Open
Invoke Error, Dispatch Exception:
Source: Microsoft Excel
Description: Open method of Workbooks class failed
Help File: xlmain11.chm
Help Context ID: 0

This is the error I message I received.

Irene

Hello, I have a little problem running this function.
I opened the activex server and all, specifing an existing .xls file, and also changed the second line of the script with 'caller', but still is giving me an error.

The error I got is:

Error using xlswrite1 (line 143)
Error: Object returned error code: 0x800A03EC

Error in xlswrite1 (line 143)
error('MATLAB:xlswrite:SelectDataRange',lasterr);

The error seems to be in the range I specify, but I call it as I used to do with xlswrite, so having something like 'A1:B1'.

I'm working on windows 7, matlab 2012b and excel 2007 (italian version, thus the sheet is originally named as 'Foglio1' instead of 'Sheet1', but I also have manually changed it in the xls file).
Thank you very much in advance.

Sheldon

ActiveX is not available on Mac OS. So this solution is Windows only. If you are doing a lot of xls writing on a Mac, I'd suggest using csvwrite, which is much quicker than xlswrite, and then just opening the csv in Excel.

A csv file, is a text document, with each column separated by a comma and each row on a new line. It can be opened in Excel, and quite easily saved as an excel file.

katerina

i changed from 'base' to 'caller' on line two, and also checked the task manager. it still doesn't work. what am i missing?? i would love to benefit from faster excel accesses.

Clark Gee

Bert

Christine

@Mikaela: Line 2 in xlswrite1 should be

Excel=evalin('base','Excel');

Did you name the handle to the ActiveX Server 'Excel', like in

Excel = actxserver ('Excel.Application');?

For xlswrite1, you need the variable 'Excel' in your workspace. If you started the ActiveX Server like

E = actxserver ('Excel.Application');

you have to change Line 2 of xlswrite1 into

Excel=evalin('base','E');

But, in addition, I don't think that it works with Mac OS. If you get it run on a Mac, please let me know how. :)

Mikaela

I'm also having the following error:

Error using evalin
Undefined function or variable 'Excel'.

Error in xlswrite1 (line 2)
Excel=evalin('caller','Excel');

Perhaps this is not compatible with Microsoft Excel 2008 for Mac?

Never mind, the problem did not return after a restart.
Thanks for the file =)

Thanks for the program, it works really fast. However, I get this error when i'm writing to an xlsx file

Error using Interface.Microsoft_Excel_14.0_Object_Library.Workbooks/Open
Invoke Error, Dispatch Exception:
Source: Microsoft Excel
Description: Excel cannot open the file 'Splinefit.xlsx' because the file format or file
extension is not valid. Verify that the file has not been corrupted and that the file
extension matches the format of the file.
Help File: xlmain11.chm
Help Context ID: 0

Error in HDD_spline_fit (line 35)
ExcelWorkbook = Excel.workbooks.Open(File);
In which Splinefit.xlsx is the target file.

If I try to write to an .xls file, thus Splinefit.xls, the error does not arise.
As I'm using a predefined xlsx file, switching to .xls does not really work for me.

Thanks in advance for your time!

Carlos

I am using xlsread1 and xlswrite1 inside a for loop with an excel sheet with enabled iterative calculation. What happens is that the iterative calculation is turn off by himself, what makes that I get wrong results. if I use the original xlsread and xlswrite of MATLAB works well, but is quite slow so I need to use xlsread1 and xlswrite1. Can someone help me with this problem?

I read xlsfiles by opening my own ActiveX-Server for a while. Suddenly I got the error:
Interface.000208DB_0000_0000_C000_000000000046/Open

After a while I checked the taskmgr and saw that there was still an Excel-task. After killing it the error was gone... happened that because of a breakpoint I did not stop the ActiveX-Server which caused some problems...

Annick

Hello,

Is there any equivalent of this for xlsread?

I am getting the following error:
Error using xlsread (line 247)
Error registering event(s), Advise failed

Error in MoT_automated_bigTable_RunSet (line 119)
couples(5,r)=xlsread(DataDHS,'Sheet1',sprintf('F%d',r+1)); %SC neg Ncirc

And I guess it's because I am reading an excel file many times in a loop.

Any advice would be much appreciated!

many thanks,

Annick

For those who are getting an error like this:

Error using
Interface.000208DB_0000_0000_C000_000000000046/Open
Invoke Error, Dispatch Exception:
Source: Microsoft Office Excel
Description: 'Desktop\filename.xlsx'....

change invoke(Excel.Workbooks,'Open',File);

to:
ExcelWorkbook = Excel.workbooks.Open(File);

and then for the save and close commands:

ExcelWorkbook.Save
ExcelWorkbook.Close(false) % Close Excel workbook.
Excel.Quit;
delete(Excel);

Leo

Thank you

Richa

Including the entire path in the file name fixed my error.

Richa

I am still getting the error:

Error using
Interface.000208DB_0000_0000_C000_000000000046/Open
Invoke Error, Dispatch Exception:
Source: Microsoft Office Excel
Description: 'Desktop\filename.xlsx'
could not be found. Check the spelling of the file
name, and verify that the file location is correct.

If you are trying to open the file from your list of
most recently used files, make sure that the file
has not been renamed, moved, or deleted.
Help File: C:\Program Files (x86)\Microsoft
Office\Office12\1033\XLMAIN11.CHM
Help Context ID: 0

I changed line 2 from 'base' to 'caller' but it did not help. Has anyone found a solution to this?

Hi

I am a beginner in Matlab. I am using xlswrite function. But it takes lots of time. I saw your solution. I downloaded xlswrite1. I changed my program as you said. But it showing an error,
I run the following code
Excel = actxserver ('Excel. Application');
File=Site_File;
if ~exist(File,'file')
ExcelWorkbook = Excel.workbooks.Add;
ExcelWorkbook.SaveAs(File,1);
ExcelWorkbook.Close(false);
end
invoke(Excel.Workbooks,'Open',File);
status=xlswrite1(Site_File, Output, Range);
nvoke(Excel.ActiveWorkbook,'Save');
Excel.Quit
Excel.delete
clear Excel

%where site_file is the output file.

??? Invoke Error, Dispatch Exception:
Source: Microsoft Excel
Description: 'CEll.xls' could not be found. Check the spelling of the file
name, and verify that the file location is correct.

If you are trying to open the file from your list of most recently used files, make
sure that the file has not been renamed, moved, or deleted.
Help File: xlmain11.chm
Help Context ID: 0

Error in ==> KL_Database_Process_Shared_RTT_Operator>CopyData at 675
invoke(Excel.Workbooks,'Open',File);

Error in ==> KL_Database_Process_Shared_RTT_Operator>Generate_Report_7 at 489
CopyData(Site_File,'F51:H79',Operator1,'1',n);

Error in ==> KL_Database_Process_Shared_RTT_Operator at 125
[b,Site_File]=Generate_Report_7(n,Set_Date);

I followed your steps and don’t know how to solve this problem.

Please help me.

Sam

ERROR:

I have run the file with the following exaclty as descripted by the author, but it does not work (Excel 2007, Matlab 7.4.0):
--------------------------

Excel = actxserver ('Excel.Application');
File='junk.xlsx';
if ~exist(File,'file')
ExcelWorkbook = Excel.workbooks.Add;
ExcelWorkbook.SaveAs(File,1);
ExcelWorkbook.Close(false);
end
invoke(Excel.Workbooks,'Open',File);

xlswrite1(File,[1 2 3])

invoke(Excel.ActiveWorkbook,'Save');
Excel.Quit
Excel.delete
clear Excel

---

error:

??? Invoke Error, Dispatch Exception:
Source: Microsoft Office Excel
Description: Excel cannot open the file 'junk.xlsx' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.
Help File: C:\Program Files\Microsoft Office\Office12\1033\XLMAIN11.CHM
Help Context ID: 0

Error in ==> test_xlswrite1 at 8
invoke(Excel.Workbooks,'Open',File);
invoke(Excel.ActiveWorkbook,'Save');
Excel.Quit
Excel.delete
clear Excel

Abdull

Matt,

You are my man. Brilliant!

Abdull

I still get error message even though, I have changed line 2 from base to caller.
Please help me.

---Error message start---
??? Error using ==> evalin
Undefined function or variable 'Excel'.

Error in ==> xlswrite1 at 2
Excel=evalin('caller','Excel');
---Error message end---

Luis Andres

Ngai-Hang

I found that this didn't work with Office 2007 files, i.e. with .xlsm. I managed to get xlswrite2007 to work though with some modifications.

It is very great
Thank you very much

Christopher

Hello all,

I'm still getting an error after changing line 2 to Excel=evalin('caller,'Excel'); I've gone to my task manager and closed all excel processes. What do I need to change to get this code to work??? Thank you in advance.

Chris

Mike

For those still getting an error after changing line 2 to

Excel = evalin('caller','Excel');

Go to Task Manager, see if the Excel process is still running. Errors stop the program from getting to the Close line. So change line 2, end the Excel process in Task Manager. I did this and it now works great!

Geoff

Thank you Matt! I had about 400 calls to xlswrite which took about 11mins, now takes 9sec! Massive improvement!

Thanks also to Martin Rouse for solving the issue of calling xlswrite1 within a function.

Artik Crazy

Thank you a lot!
This function helped me very much.
Overall run time of my simulation now is almost 10 times faster!
Block that was build of loop iterations on xlswrite now works 20 times faster.

TideMan

Worked like a charm once I changed 'base' to 'caller' in Excel=evalin('base','Excel');
as suggested by Martin Rouse.

Rachel

Does need something in the actual script rather than in the description on this page to avoid the "Error using ==> evalin " error. Not immediately obvious that it requires some lines before and after you call it otherwise.
Time saved by this is blissful!

Rachel

Harry

Matt,

you are my main man... Big ups..

Cheers

Anping

It reduces my computing time from 20s to 2s.

lh ‹

When I run the program, the result:??? Error using ==> evalin
Undefined function or variable 'Excel'.

So how to make excel com available? thx!

Oleg Komarov

I would leave in the function:
<pre class = "code">

Excel = actxserver ('Excel.Application');
Excel.Workbooks.Open('C:\YourAddInFolder\AddInNameWithExtension');
Excel.Workbooks.Item('AddInNameWithExtension').RunAutoMacros(1);
File='C:\YourFileFolder\FileName';
if ~exist(File,'file')
ExcelWorkbook = Excel.Workbooks.Add;
ExcelWorkbook.SaveAs(File,1);
ExcelWorkbook.Close(false);
end
Excel.Workbooks.Open(File);
</pre>

Instead when it checks:
if nargin < 1 || isempty(file)
error('MATLAB:xlsread:FileName','Filename must be specified.');
end

I would substitute the error with:
Excel.ActiveWorkbook.Save;
Excel.Quit
Excel.delete

This way all u have to do is:
xlsread("firstfile"......)
xlsread("secondfile"......)
...and so on until
xlsread() % close teh connection!

That would code saving and clean.

Travis

Ana, I am getting the same result, I have used this code for a few months now with no issue until this.

alei

I've got an error message saying that 'Excel' is not defined.

??? Error using ==> evalin
Undefined function or variable 'Excel'.

Error in ==> xlswrite1 at 2
Excel=evalin('base','Excel');

Anyone know how to fix it? Thx

Aviator

I'm getting the following error. It seems Matlab cannot save/close the spreadsheet.

??? Invoke Error, Dispatch Exception:
Source: Microsoft Office Excel
Description: 'My_File.xls' is read-only. To save a copy, click OK,
then give the workbook a new name in the Save As dialog box.
Help File: C:\Program Files\Microsoft Office\Office12\1033\XLMAIN11.CHM
Help Context ID: 0

Error in ==> Line 280
invoke(Excel.ActiveWorkbook,'Save');

Anybody know what can be done to fix this? (by the way, I'm using Office 2007, in compatibility mode) ; Thanks

Travis

Is there any way to adjust this so that it writes to office 2007 (.xlsx)?

Great job¡ thank you, its very usefull

katie

awesome. I was wondering if this could be modified to write to multiple files within a single loop?

Thank you for your good job; I just want to ask you if there is anything similar for xlsread. It's so bad leaving the excel com server open each time we run xlsread.

Thanks a lot!

Mark Hayworth

Note: If you call this from within your own function instead of the MATLAB command line, you will need to change the second line to
Excel = evalin('caller', 'Excel');
(instead of Excel = evalin('base', 'Excel'); like he has it)

Plus all the error messages arguments need to be changed to "MATLAB:xlswrite1" instead of "MATLAB:xlswrite"

He also needs to add the description at the top of the m-file. As it is, there is no description in the file itself and one would have to return here to the file exchange to get it. It should be also in the first few lines of the m-file.

magda anusca

N Rolfes

worked like a godsend, turned a several hour job into a 10 minute task. Thank you!

Also, thanks for the tip Martin as I used this as well.

Cristi Pedotto

This just saved me so much time! My code processes 10x faster! Thanks so much!

Martin Rouse

Excellent solution to an annoying problem! Works very quickly!

1 point i would like to add, if executing xlswrite1 within a function or GUI, change line 2;

Excel=evalin('base','Excel');

to;

Excel=evalin('caller','Excel');

Many thanks

Angie Blue

Perfect :) Thanks a million, you saved a lot of time and effort.

Tal Raviv

This works remarkably quickly. Thanks for solving the problem for all of us!

Andrej Skraba

This is a very good solution. However, there should be some attention paid on closing the Excel session.

A great addition.

Rahul Bagdia

Thanks a lot Matt. It works awesomely fast.

Alan L

Excellent. Works perfect.

Peter Suh

Very very excellent!!

The best part about this is that did eliminate the xlswrite bug which would leave the excel com server open each time it ran xlswrite. This did save me lots and lots of time and energy..I was rewriting my own xlswrite function.

Unai Cornes

THANKS A LOT!!!! It's amazing how fast the program goes now!!

Thank you, Matt.

Stacey Chang

cool stuff!! I appreciated ur code so much! But I couldn't run my loop over 65535 times (I need to run the loop for a million times), hope that you could help me with this...big thanks!!

MATLAB Release Compatibility
Created with R14SP3
Compatible with any release
Platform Compatibility
Windows macOS Linux

Discover Live Editor

Create scripts with code, output, and formatted text in a single executable document.


Learn About Live Editor