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

42 Ansichten (letzte 30 Tage)
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?

Akzeptierte Antwort

AndresVar
AndresVar am 11 Apr. 2022
Bearbeitet: AndresVar am 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 Kommentare
AndresVar
AndresVar am 11 Apr. 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 am 26 Apr. 2022
Thanks a lot. This worked flawlessly.

Melden Sie sich an, um zu kommentieren.

Weitere Antworten (0)

Produkte


Version

R2021b

Community Treasure Hunt

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

Start Hunting!

Translated by