Export data to Excel with adjusted column size

Question

I'm trying to export some data with formats into Excel. It works ok, but the columns size get not properly adjusted. I don't want to use some complicated solution based on this example.

In my tests, I tried a code like that:

data = {{"Column 1", "Column 2"}, {1, 4}, {2, 5}, {3, 6}};
data[[1]] = Style[#, Bold, Red, FontSize -> 15] & /@ data[[1]]
Export["data.xlsx", data, "FormattedData"]

I get this result:

enter image description here

And I need something like this:

enter image description here

Some clue?

This issue surely could be solved by using the XML format instead of xlsx for exporting. But is is not simple... — Alexey Popkov, Dec 17 '12 at 10:33
@Murta This issue is easily fixed once the file is opened in excel by selecting all of the affected columns and double clicking in the gap between any of the column heads. If you have a lot of files you could write a simple batch macro to do this. — geordie, May 16 '13 at 07:31
@geordie Hi. Yes, I understand, the problem is that this file is automatically created and delivered by mail using MMA. So I would like to make it automatically. — Murta, May 16 '13 at 17:26
@Murta Fair enough, then the XML route suggested by Alexey Popkov might be the way to go. — geordie, May 16 '13 at 21:26

Murta · Answer 1 · 2014-01-06T22:53:41.813

After some long time, I found a solution using VBScript. Below a script example to format the columns size. Its just a text file with the vbs extension, that has to be executed in the same directory of your xlsx file ("data.xlsx" in this example).

Dim objXLApp, objXLWb, objXLWs

Set objXLApp = CreateObject("Excel.Application")

objXLApp.Visible = False

Set objFSO = CreateObject("Scripting.FileSystemObject") 
strFilePath = objFSO.GetAbsolutePathName(".")

Set objXLWb = objXLApp.Workbooks.Open(strFilePath & "\data.xlsx")

Set objXLWs = objXLWb.Sheets(1)

objXLWs.Columns("A:C").entirecolumn.autofit

objXLWb.Save
objXLWb.Close (False)

Set objXLWs = Nothing   
Set objXLWb = Nothing

objXLApp.Quit
Set objXLApp = Nothing

The bad part is that it needs to run in Windows to work (I'm used to work on Mac) and that Excel needs to be installed in your machine (in the XLM way proposed by @AlexeyPopkov, you don't need windows or excel). The good part is that you can do a lot of formatting in an automated and much simpler way. The columns size was just one example. Now I can create some reports in Excel with MMA that are not so ugly.

You can call your script from Mathematica using Run command after export your XLSX file. I'm organizing my code. As soon as possible I'll update this post with a richer format example.

Update

Here is the new version, completely in WL to:

Export some date into excel xlsx file
Create the script.vbs (formatExcelTable.vbs)
Export and execute de script.vbs

In this version, I have a more interestin script, to create a Excel named table with some formatting. Here is the code:

script="
Dim objXLApp, objXLWb, objXLWs
Dim lo

Const xlSrcRange = 1
Const xlLeft  =-4131
Const xlCenter=-4108
Const xlRight =-4152

Set objXLApp = CreateObject(\"Excel.Application\")
objXLApp.Visible = False

Set objFSO = CreateObject(\"Scripting.FileSystemObject\") 
strFilePath = objFSO.GetAbsolutePathName(\".\")

Set objXLWb = objXLApp.Workbooks.Open(strFilePath & \"\\data.xlsx\")
Set objXLWs = objXLWb.Sheets(1)

'~~> Hidding Gridlines
objXLApp.ActiveWindow.DisplayGridlines = false

'~~> Creating Excel Table
objXLWs.ListObjects.Add xlSrcRange, objXLWs.UsedRange, ,XlYes 'Range(\"A1:c4\")
objXLWs.ListObjects(1).Name = \"Table1\"
objXLWs.ListObjects(\"Table1\").TableStyle = \"TableStyleMedium4\"

'~~> Formatting Table Columns
With objXLWs.Range(\"Table1[Date]\")
        .ClearFormats
        .NumberFormat = \"dd/mm/yyyy\"
        .HorizontalAlignment = xlCenter
        .Font.Name = \"Calibri\"
        .entirecolumn.autofit
End With

With objXLWs.Range(\"Table1[Value]\")
        .ClearFormats
        .NumberFormat = \"#,##0.00\"
        .HorizontalAlignment = xlCenter
        .Font.Name = \"Calibri\"
        .entirecolumn.autofit
End With

With objXLWs.Range(\"Table1[Part]\")
        .ClearFormats
        .NumberFormat = \"0.0%\"
        .HorizontalAlignment = xlRight
        .Font.Name = \"Calibri\"
        .entirecolumn.autofit
End With


'objXLWb.Save  'Save the original file
'~~> Save as Excel File (xls) to retain format
objXLWb.SaveAs strFilePath & \"\\dataFormat.xlsx\", 51

'~~> File Formats
'51 = xlOpenXMLWorkbook (without macro's in 2007-2010, xlsx)
'52 = xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007-2010, xlsm)
'50 = xlExcel12 (Excel Binary Workbook in 2007-2010 with or without macro's, xlsb)
'56 = xlExcel8 (97-2003 format in Excel 2007-2010, xls)

objXLWb.Close (False)

Set objXLWs = Nothing   
Set objXLWb = Nothing

objXLApp.Quit
Set objXLApp = Nothing
";

SetDirectory@NotebookDirectory[];
data={{
        {"Date","Value","Part"}
        ,{{2014,1,1},4,0.5}
        ,{{2014,2,1},5,0.3}
        ,{{2014,3,1},6,0.2}
     }
};
Export["data.xlsx",data]
Export["formatExcelTable.vbs",script,"Text"];
Run["formatExcelTable.vbs"]

Excel file pre-formatting:

pre formatting image

Excel file pos-formatting:

por formatting image

It's very easy to change the script, in order to do new kinds of formatting.

Please add a description how to use it. Do you use this script in an automatized way from inside of Mathematica? — Alexey Popkov, Jan 05 '14 at 20:32
@AlexeyPopkov, post updated. I'll make a better format example as promised in the post. — Murta, Jan 05 '14 at 22:08
Unfortunately it does not work correctly with Excel 2003 which I currently use: an error on line 24 is always appear (index is out of allowed range). But partially it does the job. — Alexey Popkov, Jan 06 '14 at 07:41
It's this line objXLWs.ListObjects(\"Table1\").TableStyle = \"TableStyleMedium4\". These named table objects are new in 2007. You can try work with normal Ranges, it should work. Or you can upgrade your Excel! :) — Murta, Jan 06 '14 at 10:40
@AlexeyPopkov can you try with this new code. I have add this line objXLWs.ListObjects(1).Name = "Table1" after objXLWs.ListObjects.Add xlSrcRange, objXLWs.UsedRange, , XlYes — Murta, Jan 06 '14 at 22:36
Now I get error on line 25. Object does not support this property or method: 'ListObjects(...).TableStyle'. — Alexey Popkov, Jan 07 '14 at 19:41
Yes!.. this line Excel 2003 is not compatible. You can comment it. — Murta, Jan 07 '14 at 20:02
Now I get "Unknown error" on line 28 which contains With objXLWs.Range("Table1[Date]"). — Alexey Popkov, Jan 07 '14 at 20:13
Yes, all code that used the named table (in this case Table1) will not work in 2003. — Murta, Jan 07 '14 at 20:14

Export data to Excel with adjusted column size

1 Answers1

Update