Formatting Excel Borders with .Net

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

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.