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?

  • $\begingroup$ Murta :I am also faceing this problem..did you get it? $\endgroup$
    – subbu
    Commented Nov 3, 2012 at 6:56
  • $\begingroup$ No.. I think that there is no simple way to do that. $\endgroup$
    – Murta
    Commented Nov 3, 2012 at 15:35
  • $\begingroup$ This issue surely could be solved by using the XML format instead of xlsx for exporting. But is is not simple... $\endgroup$ Commented Dec 17, 2012 at 10:33
  • $\begingroup$ @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. $\endgroup$
    – geordie
    Commented May 16, 2013 at 7:31
  • $\begingroup$ @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. $\endgroup$
    – Murta
    Commented May 16, 2013 at 17:26

1 Answer 1


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)


objXLWb.Close (False)

Set objXLWs = Nothing   
Set objXLWb = Nothing

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.


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:

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]\")
        .NumberFormat = \"dd/mm/yyyy\"
        .HorizontalAlignment = xlCenter
        .Font.Name = \"Calibri\"
End With

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

With objXLWs.Range(\"Table1[Part]\")
        .NumberFormat = \"0.0%\"
        .HorizontalAlignment = xlRight
        .Font.Name = \"Calibri\"
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

Set objXLApp = Nothing


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.

  • $\begingroup$ Please add a description how to use it. Do you use this script in an automatized way from inside of Mathematica? $\endgroup$ Commented Jan 5, 2014 at 20:32
  • $\begingroup$ @AlexeyPopkov, post updated. I'll make a better format example as promised in the post. $\endgroup$
    – Murta
    Commented Jan 5, 2014 at 22:08
  • $\begingroup$ @AlexeyPopkov, now I posted the more complete version. $\endgroup$
    – Murta
    Commented Jan 6, 2014 at 2:52
  • $\begingroup$ 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. $\endgroup$ Commented Jan 6, 2014 at 7:41
  • $\begingroup$ 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! :) $\endgroup$
    – Murta
    Commented Jan 6, 2014 at 10:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.