4
$\begingroup$

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.

$\endgroup$
3
$\begingroup$

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

Some other methods were suggested here:

$\endgroup$
3
$\begingroup$

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:

enter image description here

$\endgroup$
  • $\begingroup$ With which version of Excel have you tested your solution? With Excel 2003 it fails :( ... $\endgroup$ – Alexey Popkov Mar 19 '17 at 11:46
  • $\begingroup$ @AlexeyPopkov Tested on Office 2010. Try naming file demo.xls for earlier versions. $\endgroup$ – Chris Degnen Mar 19 '17 at 13:25
  • $\begingroup$ Thank you, after changing output file to "D:\\Temp\\demo.xls" it works! $\endgroup$ – Alexey Popkov Mar 19 '17 at 13:50
  • $\begingroup$ What is the purpose of LoadNETType["System.GC"]; GC`Collect[]? $\endgroup$ – Alexey Popkov Mar 19 '17 at 13:56
  • $\begingroup$ @AlexeyPopkov It's garbage collection. $\endgroup$ – Chris Degnen Mar 19 '17 at 21:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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