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





  • Prev by Date: Re: Constructing a huge graph
  • Next by Date: Re: How to create this kind of list
  • Previous by thread: Re: Formatting in XLS(X) files
  • Next by thread: Re: Formatting in XLS(X) files