# Export data to Excel with adjusted column size

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:

And I need something like this:

Some clue?

-
Murta :I am also faceing this problem..did you get it? –  subbu Nov 3 '12 at 6:56
No.. I think that there is no simple way to do that. –  Murta Nov 3 '12 at 15:35
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 7: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
show 1 more comment

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:

1. Export some date into excel xlsx file
2. Create the script.vbs (formatExcelTable.vbs)
3. 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(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:

Excel file pos-formatting:

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 5 at 20:32
@AlexeyPopkov, post updated. I'll make a better format example as promised in the post. –  Murta Jan 5 at 22:08
@AlexeyPopkov, now I posted the more complete version. –  Murta Jan 6 at 2:52
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 6 at 7: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 6 at 10:40