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

69 Ansichten (letzte 30 Tage)
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?

Antworten (1)

Guillaume
Guillaume am 21 Aug. 2019
Bearbeitet: Guillaume am 21 Aug. 2019
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).
  8 Kommentare
Guillaume
Guillaume am 5 Feb. 2020
"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
Alex Calder am 6 Feb. 2020
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. :)

Melden Sie sich an, um zu kommentieren.

Tags

Produkte


Version

R2019a

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by