How to keep Matlab from messing up the formating in my excel file?
31 次查看(过去 30 天)
显示 更早的评论
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:
![](https://www.mathworks.com/matlabcentral/answers/uploaded_files/960200/image.png)
After appending the data I get this mess:
![](https://www.mathworks.com/matlabcentral/answers/uploaded_files/960205/image.png)
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?
0 个评论
采纳的回答
AndresVar
2022-4-11
编辑:AndresVar
2022-4-11
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 个评论
AndresVar
2022-4-11
alternative is you can add a script in excel to do it, there are some tutorials to change column width programatically within excel.
更多回答(0 个)
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!