Formatting Excel Borders with .Net

Question

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.

Needs["NETLink`"]
PutIntoExcel[data_List, cell_String, file_String] :=
  Module[{rows, cols, excel, workbook, worksheet, srcRange},
  {rows, cols} = Dimensions[data];
  NetBlock[
  InstallNET[];
  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]]];
 workbook@SaveAs[file];
 workbook@Close[False];
 excel@Quit[];
 ]];
 LoadNETType["System.GC"];
 GC`Collect[]];

data = {{"Year", "Cartoon"}, {1928, "Mickey Mouse"}, {1934,
    "Donald Duck"}, {1940, "Bugs Bunny"}, {1949, "Road Runner"}};
outputfile = "C:\\Temp\\demo.xlsx";
Quiet[DeleteFile[outputfile]];
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.

Answer 1

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:

Needs["NETLink`"];
InstallNET[];
LoadNETType["System.GC"];

$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[]
]

GC`Collect[];

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

Formulas

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[]
]    

GC`Collect[];

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

Answer 2

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

LoadNETType["Microsoft.Office.Interop.Excel.XlBordersIndex"];    
LoadNETType["Microsoft.Office.Interop.Excel.XlBorderWeight"];

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*)

Update: see more on ColorIndex here.