xlswrite to multiple new sheets

24 views (last 30 days)
Christian
Christian on 9 Mar 2017
Commented: Walter Roberson on 24 Jun 2021
Hello everybody,
I would like to export data to an excel file.
I import data from two different excel files and I want to export the results into one. The data which I want to export looks like this:
x -> 1000x2 double
y -> 1000x2 double
Now I want to create an excel file, with two sheets.
Sheet 1 = x(:,1) and y(:,1) -> Name of imported file #01
Sheet 2 = x(:,2) and y(:,2) -> Name of imported file #02
My code looks like this right now, but I'm not able to make it fully working:
for i=1:numel(Filename) % The names of the imported files are stored in Filename
data(i)= {[x(:,i) y(:,i)]};
sheet(i)=cell2mat(Filename(1,i));
end
filename = ['Results.xlsx'];
Variable_names={'x','y'};
units={'mm', 'mm'};
A={Variable_names;
units;
data};
xlswrite(filename,A,sheet);
%delete default sheets 1 to 3
excelFileName = 'Results.xlsx';
excelFilePath = pwd;
sheetName = 'Sheet';
objExcel = actxserver('Excel.Application');
objExcel.Workbooks.Open(fullfile(excelFilePath, excelFileName));
try
objExcel.ActiveWorkbook.Worksheets.Item([sheetName '1']).Delete;
objExcel.ActiveWorkbook.Worksheets.Item([sheetName '2']).Delete;
objExcel.ActiveWorkbook.Worksheets.Item([sheetName '3']).Delete;
catch
;
end
objExcel.ActiveWorkbook.Save;
objExcel.ActiveWorkbook.Close;
objExcel.Quit;
objExcel.delete;
Thank you for any advice
Cheers
Christian
  1 Comment
Christian
Christian on 9 Mar 2017
One of the errors I have to deal with is for example:
Error using xlswrite (line 219)
Error: Object returned error code: 0x800A03EC
This is the code where I get the error:
for i=1:numel(Filename)
data(i)= {[x(:,i) y(:,i)]};
sheet(i)=Filename(1,i);
filename = ['Data.xlsx'];
Variable_names={'x','y'};
units={'mm', 'mm'};
A={Variable_names;
units;
data};
xlswrite(filename,A,cell2mat(sheet(i)));
end
I have Excel 2010 installed on my computer. And I don't think that the data which contains 1000 values is too big for the export which is considered in several other answers on this error.

Sign in to comment.

Answers (2)

Kushagr Gupta
Kushagr Gupta on 17 Mar 2017
Data size is only one of the reasons why you could get the error code: 0x800A03EC when using xlswrite.
This MATLAB Answers post discusses other reasons that could be causing this error:
A simple illustration of another issue that can cause this error is:
>> xlswrite ('foo.xls','=====');
>> a{1} = '=============';
>> xlswrite('foo',a,'==','A1');
When you try to write a cell array into an Excel document, MATLAB tries to fit each cell into one Excel cell.
The symbol '=' is a keyword for Excel and it wants a function after it( for example '=SUM(...)'). So it returns an error in Excel, which is translated in the aforementioned exception in MATLAB.
Indeed, if you try to set a cell to '======' within Excel, you will receive a series of errors.
Thus, ensure that the data that you want to write using 'xlswrite' can otherwise be written in Excel.
  1 Comment
Christian
Christian on 20 Mar 2017
Hello Kushagr, I appreciate your answer!
I have no idea why, but I was able to solve my Problem by using the following code:
filename = ['Data.xlsx'];
Variable_names={'x','y'};
units={'mm', 'mm'};
for i=1:numel(Filename)
sheet(i)=Filename(1,i);
end
clear i
for i=1:numel(Filename)
xlswrite(filename,char(sheet(i)),'A1');
xlswrite(filename,char(sheet(i)),'A2');
xlswrite(filename,[x(:,i) y(:,i)],char(sheet(i)),'A3');
end

Sign in to comment.


Guillaume
Guillaume on 20 Mar 2017
Looking at your original code, and the code you say fixed your issue, it is clear that you don't understand how cell arrays work.
Given a cell array called Filename, where each cell contain a string, indexing Filename with round brackets () will return the subset of the cell array corresponding to the index(ices) as a cell array. Indexing Filename with curly brackets {} will return the contents at the index(ices).
In the two pieces of code, you're indexing using () so you get a cell array (with just one cell), then you either use cell2mat or char to convert that to a char array. You could just index that one cell with {1} instead , but since you wanted the content, your first indexing should have been {}. In effect:
filename = ['Data.xlsx'];
Variable_names={'x','y'};
units={'mm', 'mm'};
for i=1:numel(Filename)
xlswrite(filename,Filename{i},'A1');
xlswrite(filename,Filename{i},'A2');
xlswrite(filename,[x(:,i) y(:,i)],Filename{i},'A3');
end
Note: having two different variables, filename, and Filename that only differ by case is asking for trouble. Particularly as the Filename one does not contain filenames but sheetname.
Note 2: clear i before you create a new i variable is pointless.
  3 Comments
Abhijeet Gunjal
Abhijeet Gunjal on 24 Jun 2021
Edited: Abhijeet Gunjal on 24 Jun 2021
I am doing the exact same thing, but it still wont write the data.
in my code, i am first inputting a column of zeroes,
and then my actual data from next cell where the zeroes' column ended.
(Ex. zeroes from K6 to K20, and then data from K21)
but only zeroes columns are being written in excel file, but not the rest of the data.
The code is-
for i = 1:12
xlswrite(filename,zeroes(index(i),1),sheetname{i},K6);
xlswrite(filename,data{i}(:,13),sheetname{i},strcat('K',num2str(6+index(i))));
end
and I assure you, no data is missing, data{i} exists, its 13th column exists, the filename and address is inputted correctly, sheetname{i} is correct, index(i) also exists for every i.
And the loop also runs to completion.
But xlswrite does not do any effect after 2-3 loops(first xlswrite worked 3 times, second only 2 times)
please any solution will be helpful.
Walter Roberson
Walter Roberson on 24 Jun 2021
Have you experimented with using writearray() insted?

Sign in to comment.

Categories

Find more on Data Import from MATLAB in Help Center and File Exchange

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!