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
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
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 commentaires
AndresVar
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)

Produits


Version

R2021b

Community Treasure Hunt

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

Start Hunting!

Translated by