CODEWITHSUNDEEP
excelmatlab
user6110593
user6110593

Reputation:

xlswrite many cell arrays into Excel as seperate named sheets

I have many cell arrays like 

set1 = {'year' 'date' 'day'     'time'; 
        '2017' '0803' 'Monday'  '15.15'; 
        '2015' '0303' 'Tuesday' '08.20'} 

set2 = {'year' 'date' 'day'    'time'; 
        '2016' '0705' 'Friday' '17.15'; 
        '2013' '0310' 'Monday' '18.20'}

title = {'dataset1' 'dataset2'}

The cell arrays that I have are much longer (400-1000 rows) and I have about 20 different sets, but the number changes dependent on my GUI data. What I want to do is automatically export all of these arrays into a single Excel spreadsheet, with each set as a separate sheet with the sheet name specified in the "title" string.

So far I am using

[FileNameBodeWrite, PathNameBodeWrite] = uiputfile({'*.xls'},'Save As...', ...
                                                   [Title{1,1} '.xls']);
xlswrite([PathNameBodeWrite FileNameBodeWrite ],[Set1],1,'A1')

But that of course only works for one specific set. I want to include them all in one spreadsheet, potentially by using a loop but I'm not sure how to implement that?

Upvotes: 0

Views: 212

Answers (1)

Wolfie
Wolfie

Reputation: 30046

You can create a cell array of your sets

sets = {set1, set2} % and so on

Then simply loop through the sets. Using xlswrite would look like this

[FileNameBodeWrite,PathNameBodeWrite] = uiputfile({'*.xls'},'Save As', [Title{1,1} '.xls']);
for ii = 1:numel(sets)
    % If the sheet title{ii} doesn't exist, it will be created
    xlswrite([PathNameBodeWrite FileNameBodeWrite],sets{ii},title{ii},'A1');
end

Edit:

Assigning sets = {set1, set2} etc. is duplicating all of your data in memory. A more efficient way of referencing the sets would be to use anonymous function handles. Essentially we are pointing to the cell arrays instead of storing copies inside another cell array:

% Using the @ notation to define anonymous functions
sets = {@()set1, @()set2};
for ii = 1:numel(sets)
    % Note the brackets after sets{ii}(), this calls the function handle in sets{ii}
    xlswrite([PathNameBodeWrite FileNameBodeWrite],sets{ii}(),title{ii},'A1');
end

The difference can be seen with even this small example

sets = {set1, set2}; whos sets
>> Name   Size   Bytes  
   sets   1x2    3126   
sets = {@()set1, @()set2}; whos sets
>> Name   Size   Bytes  
   sets   1x2    288

Note that although the above should work, it will be pretty slow because you're opening and closing the file each time you use xlswrite. A quicker way would be to directly access the Excel object. As you're new to this, I'd maybe just stick to what works, but if you want to optimise things then the File Exchange function xlswrite1 makes it relatively easy.

Upvotes: 1

Related Questions