ExcelLink: How to change cell properties like font family, size and borders?

Question

I just purchased the Mathematica Link for Excel. I can't figure out how to change the cell properties in excel using ExcelLink package? Can someone please help.

Answer 1

I haven't tried the most recent version of ExcelLink, but with the previous versions it was not possible. If you wish to generate formatted Excel tables from Mathematica, one way is to generate XML files with specific header which Excel recognizes as its native XML format called "XML Table". In the following threads I discuss this method (although the complete solution wasn't developed):

• How to export to Excel numbers as text fields from Mathematica?

• Why does Export drop namespace prefixes in the exported XML file?

Some other methods were suggested here:

• How to write data from Mathematica to an existing formated Excel file?

Answer 2

If you can't do it with Excel Link you can with NETLink. For example,

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[cell]@Resize[1, 2]@Font@Bold = True;
    worksheet@Range[cell]@Resize[1, 2]@Interior@Color = 61166;
    worksheet@Range[cell]@Offset[1, 0]@Resize[4, 1]@Font@Color = 255;
    (*Reset the numeric values to get the correct type*)
    worksheet@Range[cell]@Offset[1, 0]@Resize[4, 1]@NumberFormat = "0.000";
    worksheet@Range[cell]@Offset[1, 0]@Resize[4, 1]@Value =
      List /@ 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}]]];

Excel produced: