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





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