21 views (last 30 days)
Show older comments
johnmmiller1st on 1 Aug 2024 at 18:28
Commented: Walter Roberson on 1 Aug 2024 at 19:07
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 Comments Show -2 older commentsHide -2 older comments
Show -2 older commentsHide -2 older comments
Sign in to comment.
Sign in to answer this question.
Answers (2)
Image Analyst on 1 Aug 2024 at 18:56
Open in MATLAB Online
- Excel_put_formula_into_cell.m
- ExcelDemo.m
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.
1 Comment Show -1 older commentsHide -1 older comments
Show -1 older commentsHide -1 older comments
Walter Roberson on 1 Aug 2024 at 19:07
Direct link to this comment
https://www.mathworks.com/matlabcentral/answers/2142186-how-do-i-write-an-excel-file-with-specified-formats-for-my-data-entries#comment_3226796
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.
Sign in to comment.
Walter Roberson on 1 Aug 2024 at 19:02
No. You will need to use something like varfun to convert your numbers to appropriate text.
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 Comments Show -2 older commentsHide -2 older comments
Show -2 older commentsHide -2 older comments
Sign in to comment.
Sign in to answer this question.
See Also
Categories
MATLABData Import and AnalysisData Import and ExportStandard File FormatsSpreadsheets
Find more on Spreadsheets in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
An Error Occurred
Unable to complete the action because of changes made to the page. Reload the page to see its updated state.
Select a Web Site
Choose a web site to get translated content where available and see local events and offers. Based on your location, we recommend that you select: .
You can also select a web site from the following list
Americas
- América Latina (Español)
- Canada (English)
- United States (English)
Europe
- Belgium (English)
- Denmark (English)
- Deutschland (Deutsch)
- España (Español)
- Finland (English)
- France (Français)
- Ireland (English)
- Italia (Italiano)
- Luxembourg (English)
- Netherlands (English)
- Norway (English)
- Österreich (Deutsch)
- Portugal (English)
- Sweden (English)
- Switzerland
- Deutsch
- English
- Français
- United Kingdom(English)
Asia Pacific
- Australia (English)
- India (English)
- New Zealand (English)
- 中国
- 日本Japanese (日本語)
- 한국Korean (한국어)
Contact your local office
