xlswrite to multiple new sheets

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

[Christian]

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.

Answer 1

[Kushagr Gupta]

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:

https://www.mathworks.com/matlabcentral/answers/101631-why-do-i-receive-an-error-error-code-0x800a03ec-when-using-xlswrite-in-matlab

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.

[Christian]

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

Answer 2

[Guillaume]

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.

[Christian]

Well, thank you!

You are right, I do only have very basic understanding of Matlab. I use Matlab to make things work so I often don't care about the background as long as I am able to get the results I want to get.

And yes, this is a "shitty" attitude, but I am using Matlab just for 2 months now and not even daily. But I am improving day by day and will try to get behind the code step by step.

So thank you for your advice!

Kind regards

Christian

[Abhijeet Gunjal]

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]

Have you experimented with using writearray() insted?