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