5
$\begingroup$

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?

$\endgroup$
  • $\begingroup$ you maybe want to look at this: wolfram.com/products/applications/excel_link $\endgroup$ – george2079 Oct 26 '16 at 14:46
  • $\begingroup$ @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). $\endgroup$ – Michael Stern Oct 26 '16 at 14:51
  • $\begingroup$ @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. $\endgroup$ – Rolf Mertig Oct 26 '16 at 15:11
  • $\begingroup$ @RolfMertig What is createCellStyle[]? $\endgroup$ – Michael Stern Oct 26 '16 at 15:16
  • 1
    $\begingroup$ Strongly related: mathematica.stackexchange.com/q/15786/280 $\endgroup$ – Alexey Popkov Mar 19 '17 at 8:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.