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