How to keep Matlab from messing up the formating in my excel file?

I want to write a matlab script that creates nice looking excel files and stores some values in it.

My idea was to create a template in excel, copy that template and then append the data via matlab.

I use copyfile and writetable for that:

results = table([data1', data2', data3']);

copyfile('+res\template.xlsx', fileLocationResults); %Copies the template

writetable(results, fileLocationResults, 'WriteMode','Append'); %Appends the results to the template

While the initial Template looks good enough:

After appending the data I get this mess:

While it is'nt too difficult to clean this up manually I'd like to avoid this happening in the first place. Is there a way for keep the collum width when appending data via Matlab?

Answer 1

[AndresVar]

You can writetable and then fix the column width with an actxserver

fullFilename = fullfile(pwd,'mysheet.xlsx'); % or whatever name you picked

e = actxserver('Excel.Application'); % open excel
ewb=e.Workbooks; 
ef = ewb.Open(fullFilename); % open file
ews = ef.ActiveSheet; % get active sheet 
ewsRange = Range(ews,'A1:B1'); % get cell A1 and B1 for example
ewsRange.ColumnWidth = [100 200]; % change the column widths

% save, close, quit, delete!
ef.Save;
ef.Close;
e.Quit;
delete(e);

Here i set it to 100, but change it to what your column width.

OR have a helper function to get the columnwidth before you do the writetable. But i think you have to get each column. so you end up with

function writetableTemplate(...)

getTemplateProps(...)

writetable(...)

setTemplateProps(...) % to fix it

end

more info:

Create COM server - MATLAB actxserver (mathworks.com)

Write Spreadsheet Data Using Excel as Automation Server - MATLAB & Simulink (mathworks.com)

Change Row Height in Range of Spreadsheet Cells - MATLAB & Simulink (mathworks.com)

[AndresVar]

alternative is you can add a script in excel to do it, there are some tutorials to change column width programatically within excel.

[Felix Eichin]

Thanks a lot. This worked flawlessly.