I have various tables generated in a Mathematica application and need to export this to MSExcel formatted in a specific way.

The Excel formatting has to be generated by Mathematica. I am not familiar with .Net (or .NetLink), but after searching, found this very useful code by Chris Degnan.

PutIntoExcel[data_List, cell_String, file_String] :=
  Module[{rows, cols, excel, workbook, worksheet, srcRange},
  {rows, cols} = Dimensions[data];
  excel = CreateCOMObject["Excel.Application"];
  If[! NETObjectQ[excel], Return[$Failed],
 excel[Visible] = True;
 workbook = excel@Workbooks@Add[];
 worksheet = workbook@Worksheets@Item[1];
 srcRange = worksheet@Range[cell]@Resize[rows, cols];
 srcRange@Value = data;
 srcRange@Interior@Color = 13959039;
 (* OLE colours from http://www.endprod.com/colors/ *)
 worksheet@Range["E5:F5"]@Font@Bold = True;
 worksheet@Range["E5:F5"]@Interior@Color = 61166;
 worksheet@Range["E6:E9"]@Font@Color = 255;
 (* Reset the numeric values to get the correct type *)
 worksheet@Range["E6:E9"]@Value = Rest[data[[All, 1]]];

data = {{"Year", "Cartoon"}, {1928, "Mickey Mouse"}, {1934,
    "Donald Duck"}, {1940, "Bugs Bunny"}, {1949, "Road Runner"}};
outputfile = "C:\\Temp\\demo.xlsx";
PutIntoExcel[data, "E5", outputfile];

Print[Panel[TableForm[data, TableSpacing -> {2, 4}]]];

This explains in detail how to format colours, but I run into problems with borders.

This code does work:

worksheet@Range["B3:C4"]@Borders@Color = 255;

However, specifying specific parts of the border does not:

worksheet@Range["B3:C4"]@Borders[xlDiagonalDown]@Color = 255;

and I get this error:

NET::nocomprop: No property named xlDiagonalDown exists for the given COM object.

Specifying the weight of the line like this:

worksheet@Range["B3:C4"]@Borders@Weight = xlThick;

gives a different error:

NET::methodargs: Improper arguments supplied for method named Weight.

Can anyone suggest what may be wrong?

Then after exporting a fancy formatted table to Excel, I need to export an Excel formula into the formatted cells, to enable the Excel user to modify and play with their own input data.

  • $\begingroup$ I am getting the following error when running your code "A .NET exception occurred: \ "System.Reflection.TargetInvocationException: Exception has been \ thrown by the target of an invocation. ---> \ System.Runtime.InteropServices.COMException: Microsoft Excel cannot \ access the file 'C:\\ Temp \\ 534E8A50'. There are several possible \ reasons:" $\endgroup$
    – William
    Jan 15, 2016 at 17:37

2 Answers 2


Excel VBA enumeration values cannot be accessed symbolically through COM. We must use the corresponding numeric values found by consulting the Microsoft Excel object model enumeration reference.

The relevant enumerations in this case are XlBordersIndex (xlDiagonalDown = 5) and XlBorderWeight (xlThick = 4).

Once we know the enumeration values, the code is straight-forward:

xlDiagonalDown = 5;
xlThick = 4;
borders = range@Borders@Item[xlDiagonalDown];
borders@Weight = xlThick;

Side Note: Complications

Take note of the use of Item in the Borders@Item[xlDiagonalDown] expression. If we wrote simply Borders[xlDiagonalDown], we would get an error message complaining that there is no such property. The reason is that Mathematica models COM properties using definitions that hold their arguments. Borders is a property, so a direct argument of xlDiagonalDown remains unevaluated and is interpreted as a (non-existent) subproperty name. Borders@Item, on the other hand, is a method. Method arguments are not held, so xlDiagonalDown gets evaluated to its numeric value. It is possible to use the Borders property directly, albeit in ugly fashion:

With[{dd = xlDiagonalDown}, borders = range@Borders[dd]]
(* or *)
borders = range@Borders[#] &@ xlDiagonalDown
(* or *)
borders = range@Borders[5]

Complete Example

Here is a complete example, using Item:


$outputFile = "C:\\Temp\\demo.xlsx";
Quiet @ DeleteFile @ $outputFile;

NETBlock @ Module[{xl, book, sheet, range, borders, xlDiagonalDown, xlThick}
, xlDiagonalDown = 5
; xlThick = 4
; xl = CreateCOMObject["Excel.Application"]
; book = xl@Workbooks@Add[]
; sheet = book@Worksheets@Item[1];
; range = sheet@Range["B2:G6"]
; borders = range@Borders@Item[xlDiagonalDown]
; borders@Color = 255
; borders@Weight = xlThick
; book@SaveAs[$outputFile]
; book@Close[]
; xl@Quit[]


SystemOpen @ $outputFile
(* DeleteFile @ $outputFile *)

excel screenshot showing diagonal borders


Formulas can be written into spreadsheet cells using the Range.Formula property. Such formulas must be expressed in Excel syntax. Here is an example with a formula that uses relative cell references and computes the Fibonacci sequence:

NETBlock @ Module[{xl, book, sheet}
, xl = CreateCOMObject["Excel.Application"]
; book = xl@Workbooks@Add[]
; sheet = book@Worksheets@Item[1];
; sheet@Range["A1:A2"]@Formula = 1
; sheet@Range["A3:A20"]@Formula = "=A1+A2"
; book@SaveAs[$outputFile]
; book@Close[]
; xl@Quit[]


SystemOpen @ $outputFile
(* DeleteFile @ $outputFile *)

screenshot showing Excel formulas

  • $\begingroup$ (+1) What borders@Color = 255 mean? Removing or changing this command does not change anything in my case (I use Excel 2003), the borders are black. $\endgroup$ Dec 17, 2013 at 6:58
  • 1
    $\begingroup$ As I understand it, this sets the colour of the border and 255 is Red. OLE colours from endprod.com/colors I am using Excel 2010 and it works - unfortunately dont have a 2003 version to verify. $\endgroup$
    – BlueMac
    Dec 17, 2013 at 8:48
  • $\begingroup$ @WReach Thanks for detailed reply! Any idea re the 2nd part of teh question - exporting excel style formulas to the same cells where we applied the borders? $\endgroup$
    – BlueMac
    Dec 17, 2013 at 8:56
  • $\begingroup$ See the new "Formulas" section. $\endgroup$
    – WReach
    Dec 21, 2013 at 22:24
  • 1
    $\begingroup$ @BlueMac Sorry, I have no insight on that behaviour. I just now tried to assign multiple formulas from an array using VBA directly inside Excel (2007, in my case). It didn't work either, although it failed in a slightly different way from when I performed the operation through COM from Mathematica. Perhaps formula array assignment is broken in Excel? $\endgroup$
    – WReach
    Jan 11, 2014 at 23:50

I suggest write code formatting borders as following avoiding using the Enum values directly.


Scan[(range@Borders[#]@Weight = XlBorderWeight`xlThick)&,{NETObjectToExpression[XlBordersIndex`xlDiagonalDown]}];

and ColorIndex refers to this chart because of no finding color enum names, i.e.:

range@Interior@ColorIndex = 19;(*Light Yellow*)

enter image description here

Update: see more on ColorIndex here.


Your Answer

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

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