How to set up writetable so that the generated Excel file follows a certain display format?

Below is my code to write some data into Excel:

            T1 = table(Cruise, Station_no, Lon, Lat, Year, Month, Day, Time, Pressure, Depth);
            
            writetable(T1, ['outputs/', app.FileName.Value,'.xlsx'])

The code works, but the format of the data inside the generated Excel file is kind of messy. Here are what I hope the generated Excel file will look like:

(a) The Longitude and latitude columns should be displayed in a format like %.4f. I do not want to trim any decimal digits off, because the precision of the data is important. All I want is to set up the display format. This is similar to the acton of clicking "Format cells" and then setting up the decial places inside Excel.

(b) Similarly, I want the pressure and depth columns to be formated like %.2f.

(c) All columns should be centered.

Is this even possible using Matlab writetable?

Answer 1

[Guillaume]

Is this even possible using Matlab writetable?

No, writetable just put data in the spreadsheet cells and never touches any formatting, leaving it to the default. Note that xlswrite does the same.

My recommendation would be to create a template excel file (in excel) with the formatting you want, and every time you use writetable copy your template as a new xlsx file and use writetable to write into that file. The copying of the template can be done within matlab.

Another option is to use Excel COM interface from matlab to modify the formatting of the file after it's bee written by writetable but that's a lot more effort than the first option, and slower as well since the same file has to be opened twice (once by writetable once for your edit).

[Leon]

Many thanks for the recommendations, Guillaume!

[alexander fastiggi]

Could you give me an example of how to write that

[Guillaume]

an example of how to write that

"that" refers to what?

[Erik Wåhlin]

Thanks Guillaume!

How do I (in Matlab) copy the Excel template into an xlsx?

[Guillaume]

For the record, my suggestion was to create an excel file (the template) that has all the formatting you need and is just missing the actual data. So, when you want to save your data in excel, you just copy the template and write to that new file. You then have the matlab data with the template formatting.

xltemplate = 'C:\somewhere\template.xlsx';  %the template file
xldatafile = 'C:\somewhereelse\outputfile.xlsx';  %the excel file to write
[copied, msg] = copyfile(xltemplate, xldatafile);
assert(copied, 'Failed to copy template into "%s" because %s', xldatafile, msg);
writetable(sometable, xldatafile, 'UseExcel', true);

[Alex Calder]

I use exactly this method for a tool which I want to deploy on both PC and linux. It works perfectly on PC (with Excel installed) but on linux (RHEL), where Excel isn't -- and can't be -- installed, writing data to any cell clears all formatting in that cell (including number format, so formulae appear as text).

Is this due to the lack of an Excel installation on the linux system, or a limitation of writetable on linux?

Does anyone have a solution/workaround?

[Guillaume]

"Is this due to the lack of an Excel installation on the linux system, or a limitation of writetable on linux?"

A bit of both. On Windows with excel installed, writetable, when useExcel is true (default prior to 2019b) basically starts excel and paste the data directly in the spreadsheet, thus preserving all the formatting.

On Windows without excel or when useExcel is false (now the default), and on all other OSes (even those with some form of Excel), writetable directly edits the content of the excel file. It's a lot more work and so far, indeed formatting is not preserved.

I'm afraid there's no workaround short of waiting for Mathworks to improve writetable to support formatting (if that's in the plan) or writing your own excel format parser. You can download all 1124 pages (!) of the specification of the xls format from Microsoft here and the specification of the xlsx format here for the baseline Office OpenXML format and here for Microsoft xlsx extension to it. Lots of reading!

[Alex Calder]

Thanks Guillaume. Balancing the importance of my tool and the amount of effort (and reading!) required to fix a (relatively) small limitation of functionality, I'll make do with the current limitations of writetable on the various platforms.

Nevertheless, many thanks for clarifying the limitations of writetable and giving some background. Just a cursory search around these Answers pages highlights that writetable is still relatively immature as a writer and there's significant demand from the community for improved functionality. Let's hope there are developments planned for R2020a and beyond. :)