Re: Formatting in XLS(X) files
- To: mathgroup at smc.vnet.net
- Subject: [mg122152] Re: Formatting in XLS(X) files
- From: "Chris Degnen" <degnen at cwgsy.net>
- Date: Sun, 16 Oct 2011 16:44:58 -0400 (EDT)
- Delivered-to: l-mathgroup@mail-archive0.wolfram.com
Erratum:
This line is corrected thus:
(* Reset the numeric values to get the correct type *)
worksheet@Range["E6:E9"]@Value = List /@ Rest[data[[All, 1]]];
The reason it is there at all is that the data in
srcRange@Value = data;
is of mixed types, and Excel receives it all as text.
Chris Degnen just wrote:
>
> 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
>