Re: Formatting in XLS(X) files
- To: mathgroup at smc.vnet.net
- Subject: [mg122156] Re: Formatting in XLS(X) files
- From: "Chris Degnen" <degnen at cwgsy.net>
- Date: Mon, 17 Oct 2011 08:08:55 -0400 (EDT)
- Delivered-to: l-mathgroup@mail-archive0.wolfram.com
Addendum:
It might save some puzzlement if I mention in setting Values from a list
Excel uses the type of the first variable, so
worksheet@Range["E6:E9"]@Value = {{1928.8}, {1934}, {1940}, {1949.6}}
arrives in an Excel column as 1928.8, 1934, 1940, 1949.9, whereas
worksheet@Range["E6:E9"]@Value = {{1928}, {1934.4}, {1940.5}, {1949.6}}
arrives as 1928, 1934, 1940, 1950.
I also generalised the formatting statements:-
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]]];
Chris Degnen wrote:
>
> 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
>>
>