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

6 views (last 30 days)
Felix Eichin
Felix Eichin on 11 Apr 2022
Commented: Felix Eichin on 26 Apr 2022
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?

Accepted Answer

AndresVar
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
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:
  2 Comments

Sign in to comment.

More Answers (0)

Products


Release

R2021b

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!