How do I write an excel file with specified formats for my data entries?
36 次查看(过去 30 天)
显示 更早的评论
I have a large data file that I am currently turning into a table with headers, and then exporting to excel with array2table and then writetable. Is it possible to specify the formats of the data in different columns? Some I would like to have only 2 places after the decimal, like 0.12, others 4, like 0.1234, and some I would like to have in the format denoted in Excel as ##0.00E+0. This gives 2 places after the decimal, and forces the number to the right of the E to be a multiple of 3 (so you can easily think milli, micro, mega at a glance).
0 个评论
回答(2 个)
Image Analyst
2024-8-1,18:56
Yes, it's certainly possible. Probably the easiest way is to just make up an Excel workbook with all the columns formatted the way you want. You can put in fixed text into cells, shade or colorize cells, set the font size or color, set the number of decimal places, whatever you want. Then save it as something like "Excel Results Template.xlsx". Then you can make a copy of that when you're ready to export results from your program and write to it. All your formatting should stay intact and the data should appear as you'd expect. For example:
inputFolder = pwd; % Template is in the same folder as our m-file.
templateFullFileName = fullfile(inputFolder, 'Excel Results Template.xlsx');
outputFolder = 'C:\whatever\results'; % Folder where you want your output workbook to go.
excelFullFileName = fullfile(outputFolder, 'My Results.xlsx');
if isfile(templateFullFileName)
% Template found. Make a copy of it.
copyfile(templateFullFileName, excelFullFileName);
% Export your data to it.
writematrix(yourdata, excelFullFileName); % Or write cell if you have mixed text and numeric cells.
else
% Template not found. Just write data into a new, unformatted workbook.
fprintf('Excel Template file not found: "%s".\n', templateFullFileName);
writematrix(yourdata, excelFullFileName); % Or write cell if you have mixed text and numeric cells.
end
message = sprintf('Exported data to "%s"', excelFullFileName);
fprintf('%s\n', message) % Print info to command window.
% Popup a modal dialog box with the info:
uiwait(helpdlg(message));
If you really want to do it ALL from MATLAB, then you have to use ActiveX (Windows) and you can reference my attached demos for that, but the way I showed you above is definitely WAY easier.
2 个评论
Walter Roberson
2024-8-1,19:07
The difference between this answer and the answer I gave, is that this answer involves creating display formats for cells while leaving the content of cells unchanged. For example 0.123456 might be formatted to show 0.12 using display format, but the 0.123456 would still be stored.
Hmmm... thinking about it more, if you had 0.10 that you convert to text '0.10' and write that, then excel is almost certainly going to default to representing it as numeric 0.10 and the default format would be 0.1 . So you might need to use the technique here of setting up template cell formats, and combine that with round() of what you write.
Walter Roberson
2024-8-1,19:02
Leaving aside the ##0.00E+0, the problem with using round is that if you round(), trailing zeros will be removed... so for example 0.10 would appear as 0.1 if you just used round. So you need to convert to text.
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!