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

59 vues (au cours des 30 derniers jours)
Felix Eichin
Felix Eichin le 11 Avr 2022
Commenté : Felix Eichin le 26 Avr 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?

Réponse acceptée

AndresVar le 11 Avr 2022
Modifié(e) : AndresVar le 11 Avr 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
function writetableTemplate(...)
setTemplateProps(...) % to fix it
more info:
  2 commentaires
AndresVar le 11 Avr 2022
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
Felix Eichin le 26 Avr 2022
Thanks a lot. This worked flawlessly.

Connectez-vous pour commenter.

Plus de réponses (0)




Community Treasure Hunt

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

Start Hunting!

Translated by