6

I export Mathematica tables to Excel every day, for sharing reports with non-Mathematica users. It exports the values without flaw, but ignores any formatting. Consider the following:

colHeadings = {"datum 1", "datum 2"};
rowHeadings = {"yesterday", "today", "tomorrow"};
data = {{1.2, 2.4}, {1.3, 2.1}, {1.31, 2.0}};
dataT = Prepend[Partition[Flatten[Transpose[{rowHeadings, data}]], 3],
   Flatten[{"", colHeadings}]];

I would like to export this to Excel with a modest amount of formatting – column borders would suffice. It's easy to format the data in Mathematica, some examples follow:

table1 = TableForm[dataT]

table2 = TableForm[data, TableHeadings -> {rowHeadings, colHeadings}]

grid1 = Grid[dataT, Alignment -> {{Right, {Decimal}}}, 
  Dividers -> {All, {True, True, {False}, True}}]

But no matter which of these I export to Excel, it comes out the same, as a raw data table.

Export[FileNameJoin[{$HomeDirectory, "Desktop", "dataT.xlsx"}], dataT]

Export[FileNameJoin[{$HomeDirectory, "Desktop", 
   "table1.xlsx"}], table1]

Export[FileNameJoin[{$HomeDirectory, "Desktop", 
   "table2.xlsx"}], table2] (* omits the column and row headings *)

Export[FileNameJoin[{$HomeDirectory, "Desktop", "grid1.xlsx"}], grid1]

Any suggestions, or am I asking for something that Mathematica just can't do?

Alexey Popkov
  • 61,809
  • 7
  • 149
  • 368
Michael Stern
  • 4,695
  • 1
  • 21
  • 37
  • you maybe want to look at this: https://www.wolfram.com/products/applications/excel_link/ – george2079 Oct 26 '16 at 14:46
  • @george2079 Oof. I hope I don't have to pay over $200 just to add borders around cells in Excel (if the Excel Link can even do that, it's unclear). – Michael Stern Oct 26 '16 at 14:51
  • @MichaelStern It is possible, but not out of the box. You have to use JLink and fiddle around. Export[xlsFile = FileNameJoin[{$HomeDirectory, "Desktop", "dataT.xlsx"}], dataT]; finstream = JavaNew["java.io.FileInputStream", xlsFile]; myXlsxWorkbook = JavaNew["org.apache.poi.xssf.usermodel.XSSFWorkbook", finstream]; style = myXlsxWorkbook@createCellStyle[]; cu = LoadJavaClass["org.apache.poi.ss.util.CellUtil"] and so on. Now you have to read the XSSF API. It is painful, but possible. No time right now. – Rolf Mertig Oct 26 '16 at 15:11
  • @RolfMertig What is createCellStyle[]? – Michael Stern Oct 26 '16 at 15:16
  • In the past (more than 10 years), I solved this problem by calling Excel via NETLink (Com Interface to Excel). It's not immediate and there are limitations, particularly this one – andre314 Oct 26 '16 at 16:12
  • @MichaelStern it is a Java method described here: – Rolf Mertig Oct 26 '16 at 16:53
  • 1
    Strongly related: http://mathematica.stackexchange.com/q/15786/280 – Alexey Popkov Mar 19 '17 at 08:54

0 Answers0