How do I write an excel file with specified formats for my data entries?

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).

Answer 1

[Image Analyst]

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.

[Walter Roberson]

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.

[johnmmiller1st]

Well I do like that the only thing changed is the display, I'm not trying to round off. Perhaps I was a little inexact in my statement of the problem. This does look like I could just tag this onto the end of my program and not have to worry about it, so long as the templates are always in the same folder as the data I'm working with. Thank you both, I'll have to check on this.

Answer 2

[Walter Roberson]

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.