writetable() and formatting options implementation

13 visualizzazioni (ultimi 30 giorni)
dpb
dpb il 20 Apr 2025
Modificato: dpb il 20 Apr 2025
If Excel spreadsheet is already formatted that includes columns with both fixed-width and autofit columns, 'PreserveFormat',1 is not sufficient because 'AutoFitWidth' is true by default and overrides the existing formatting. On the other hand, also using 'AutoFitWidth',0 then kills the columns that have had their formatting width already set to be autofit -- Catch 22, "you can't get there from here!". There needs to be a super-strong 'PreserveAllFormats' or something similar that tells it to "Please just leave everything as it is, don't try to do anything, it's already been taken care of". Ended up having to call writetable twice, once for the fixed-width and once for the autofit columns.
A second gotcha' may not be solvable given Excel's aggressive handling of anything that even smells of possibly being date-related. The table has monthly columns of the form Jan25, Feb25, ....,etc. If use 'UseExcel',1 then Excel interprets and stores the variable names as dates which then are not read back in as the prior table variable names. This goes away and are written as text if don't use the Excel instance, but then one cannot write a dynamic formula such as =SUM(G2:G338) that is interpreted as the forumula in the spreadsheet rather than as just a string. While Excel programming is not Mathworks' problem, spreadsheets are so ubiquitous in working with non-MATLAB people that such issues impact the effort required to support office staff.
A couple additional features would also be quite useful for spreadsheet files...
  • 'WriteMode','abut' - append data to right of existing. Can be done by looking up last used column, of course, and setting the 'Range' starting location, but would be very handy "syntactic candy".
  • 'Before',sheet - insert the new sheet at the requested location in the workbook instead of only as new last sheet.

Risposte (0)

Prodotti

Community Treasure Hunt

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

Start Hunting!

Translated by