How to keep Matlab from messing up the formating in my excel file?
6 views (last 30 days)
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?
AndresVar on 11 Apr 2022
Edited: AndresVar on 11 Apr 2022
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
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!
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
setTemplateProps(...) % to fix it