Search code examples
excelmatlabformattingexport-to-excel

Exporting data to Excel and applying a format to it

I want to Export a 1xm-Cellarray using xlswrite. The Cell Array consists of m cells each containing a ixn-Cellarray where i is mostly 2, but can also be 3,4,5 or 6. Here's an example of what the data would look like: 

a=[{{{'a'},{'b'},{'c'},{'d'};{'a'},{'b'},{'c'},{'d'}}},{{{'a'},{'b'},{'c'},{'d'};{'a'},{'b'},{'c'},{'d'};{'a'},{'b'},{'c'},{'d'}}},{{{'a'},{'b'},{'c'},{'d'};{'a'},{'b'},{'c'},{'d'}}}]

a = 

    {2x4 cell}    {3x4 cell}    {2x4 cell}

I want all the cells to be written underneath each other, but I want to be able to see in Excel which rows belonged to one cell. My idea was to put an empty row between on Array cell and another like this

exportTable=[];
for jj=1:numel(a)
    exportTable=[exportTable;a{jj};repmat({[]},1,18)];
end

and then exporting the exportTable using xlswrite, but this Looks pretty ugly in the exported sheet and is not easy to read.
Now I'm looking for a way to get the lines of each cell coloured in the same Colour either directly using the Export function in matlab or using Excel with a vector of the corresponding rows as input.
I could achieve the ending Indexes for each cell using 

rows=cumsum(cellfun(@(x) size(x,1),a))

rows =

     2     5     7

But I don't know how to colour rows in Excel based on rownumbers.
The desired Output for my example would look like this:
example output

Any help using Matlab or Excel is appreciated.

Solution 
  • rows = cumsum(cellfun(@(x) size(x,1),a))

%Create an Excel object.
e = actxserver('Excel.Application');

%Add a workbook.
eWorkbook = e.Workbooks.Add;
e.Visible = 1;

%Make the first sheet active.
eSheets = e.ActiveWorkbook.Sheets;
eSheet1 = eSheets.get('Item',1);
eSheet1.Activate

for i = 1:length(a)
    ai = table2array( cell2table( a{:,i} ) ); % sorry for this construction

    if mod(i,2)
        ai_color = 3;
    else
        ai_color = 4;
    end

    ai_range = ['A',num2str(rows(i)-size(ai,1)+1),':',char('A'-1+size(ai,2)),num2str(rows(i))]; % ... and this :)

    % Set the color of cells
    eSheet1.Range(ai_range).Interior.ColorIndex = ai_color;

    %Put MATLAB data into the worksheet.
    eActivesheetRange = get(e.Activesheet,'Range',ai_range);
    eActivesheetRange.Value = ai;
end


SaveAs(eWorkbook,'myfile.xlsx')

%If the Excel program displays a dialog box about saving the file, select the appropriate response to continue.

%If you saved the file, then close the workbook.
eWorkbook.Saved = 1;
Close(eWorkbook)

%Quit the Excel program and delete the server object.
Quit(e)
delete(e)

%Note:   Make sure that you close workbook objects you create to prevent potential memory leaks.