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

30 次查看（过去 30 天）
Leon
Leon 2019-8-21
评论： Alex Calder 2020-2-6
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?

请先登录，再回答此问题。

回答（1 个）

Guillaume
Guillaume 2019-8-21
编辑：Guillaume 2019-8-21
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 个评论
显示 6更早的评论
Guillaume
Guillaume 2020-2-5
"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 2020-2-6
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. :)

请先登录，再进行评论。

类别

在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

产品


版本

R2019a

Community Treasure Hunt

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

Start Hunting!

Translated by