Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]
Groups > comp.soft-sys.math.mathematica > #5685
| From | "Chris Degnen" <degnen@cwgsy.net> |
|---|---|
| Newsgroups | comp.soft-sys.math.mathematica |
| Subject | Re: Formatting in XLS(X) files |
| Date | 2011-10-16 20:45 +0000 |
| Organization | Steven M. Christensen and Associates, Inc and MathTensor, Inc. |
| Message-ID | <j7ffpf$j7u$1@smc.vnet.net> (permalink) |
| 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
Back to comp.soft-sys.math.mathematica | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread
Formatting in XLS(X) files Josselin Noirel <commercialat@jnoireldot.fr> - 2011-10-12 07:46 +0000
Re: Formatting in XLS(X) files "andre.robin3" <andre.robin3@wanadoo.fr> - 2011-10-13 07:48 +0000
Re: Formatting in XLS(X) files Tyler <hayes.tyler@gmail.com> - 2011-10-14 09:55 +0000
Re: Formatting in XLS(X) files "Alexey Popkov" <lehin.p@gmail.com> - 2011-10-15 10:07 +0000
Re: Formatting in XLS(X) files "Chris Degnen" <degnen@cwgsy.net> - 2011-10-16 20:45 +0000
Re: Formatting in XLS(X) files Josselin Noirel <commercialat@jnoireldot.fr> - 2011-10-28 09:43 +0000
Re: Formatting in XLS(X) files "andre.robin3" <andre.robin3@wanadoo.fr> - 2011-10-29 11:18 +0000
csiph-web