Take the 2-minute tour ×
Mathematica Stack Exchange is a question and answer site for users of Mathematica. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 14 down vote accepted

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

excel screenshot showing diagonal borders

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

screenshot showing Excel formulas

share|improve this answer
    
(+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. –  Alexey Popkov Dec 17 '13 at 6:58
    
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. –  BlueMac Dec 17 '13 at 8:48
    
@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? –  BlueMac Dec 17 '13 at 8:56
    
See the new "Formulas" section. –  WReach Dec 21 '13 at 22:24
1  
@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? –  WReach Jan 11 at 23:50

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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