MathGroup Archive 2011

[Date Index] [Thread Index] [Author Index]

Search the Archive

Re: Formatting in XLS(X) files

  • To: mathgroup at smc.vnet.net
  • Subject: [mg122151] Re: Formatting in XLS(X) files
  • From: "Chris Degnen" <degnen at cwgsy.net>
  • Date: Sun, 16 Oct 2011 16:44:47 -0400 (EDT)
  • Delivered-to: l-mathgroup@mail-archive0.wolfram.com
  • References: <j73gkt$fui$1@smc.vnet.net>

Josselin Noirel wrote:
>
> Dear all,
>
> I'm wondering if there's any way an XLS or XLSX file one exports can
> get some nice formatting: bold face and/or background colour for some
> of the cells?  I'm compiling some results for collaborators and beyond
> the numerical values, I'd like trends to be easily spotted by them.
>
>>From the examples, it looks like it's a definite NO unfortunately.
>
> Thanks very much,
>
> Joss
>

Here is a demo which it sounds like you could easily customise for
what you require.  It is based mainly on the NETLink ExcelPieChart.nb
demo (ref. 1), which succinctly explains what functions like NetBlock[]
and InstallNET[] do.

(* code start *)

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}]]];

(* code end *)


References:

1. Calling .NET from Mathematica, Introduction,
     Calling COM Objects, ExcelPieChart.nb
http://reference.wolfram.com/mathematica/NETLink/tutorial/CallingNETFromMathematica.html

2. Re: Access to Open Excel Cell
http://forums.wolfram.com/mathgroup/archive/2010/Jan/msg00910.html

3. Understanding the Excel Object Model
       from a .NET Developer's Perspective
http://msdn.microsoft.com/en-us/library/aa168292(v=office.11).aspx





  • Prev by Date: Find two numbers a,b such us: a+b=5432 & LCM[a,b]=223020
  • Next by Date: Re: Formatting in XLS(X) files
  • Previous by thread: Re: Formatting in XLS(X) files
  • Next by thread: Re: Formatting in XLS(X) files