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$

2 Answers 2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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