How do I write an excel file with specified formats for my data entries?
36 Ansichten (letzte 30 Tage)
Ältere Kommentare anzeigen
johnmmiller1st
am 1 Aug. 2024 um 18:28
Kommentiert: johnmmiller1st
am 5 Aug. 2024 um 16:28
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 Kommentare
Antworten (2)
Image Analyst
am 1 Aug. 2024 um 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 Kommentare
Walter Roberson
am 1 Aug. 2024 um 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
am 1 Aug. 2024 um 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 Kommentare
Siehe auch
Kategorien
Mehr zu Spreadsheets finden Sie in Help Center und File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!