Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]
Groups > comp.soft-sys.math.mathematica > #5607 > unrolled thread
| Started by | Josselin Noirel <commercialat@jnoireldot.fr> |
|---|---|
| First post | 2011-10-12 07:46 +0000 |
| Last post | 2011-10-29 11:18 +0000 |
| Articles | 7 — 5 participants |
Back to article view | Back to comp.soft-sys.math.mathematica
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
| From | Josselin Noirel <commercialat@jnoireldot.fr> |
|---|---|
| Date | 2011-10-12 07:46 +0000 |
| Subject | Formatting in XLS(X) files |
| Message-ID | <j73gkt$fui$1@smc.vnet.net> |
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
[toc] | [next] | [standalone]
| From | "andre.robin3" <andre.robin3@wanadoo.fr> |
|---|---|
| Date | 2011-10-13 07:48 +0000 |
| Message-ID | <j76548$29u$1@smc.vnet.net> |
| In reply to | #5607 |
My solution to this problem was to export the file without formatting, and then call Excel from Mathematica via NETLink. Then I useVisual Basic instructions to control Excel. All this done programmaticaly in a very few lines of code (~20) but if you have to discover NETLink and Visual Basic, it is time consuming. "Josselin Noirel" <commercialat@jnoireldot.fr> a écrit dans le message de news: j73gkt$fui$1@smc.vnet.net... > 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 > >
[toc] | [prev] | [next] | [standalone]
| From | Tyler <hayes.tyler@gmail.com> |
|---|---|
| Date | 2011-10-14 09:55 +0000 |
| Message-ID | <j790va$i2t$1@smc.vnet.net> |
| In reply to | #5607 |
Another option, if this is going to be a regular report generated I've used in the past is: [1] Create a blank template XLS file with the formatting you require/ want. TEMPLATE_some_report.xls [2] In your code, make a copy of the file templatedir/TEMPLATE_some_report.xls -> report/ some_report_file_yyyymmdd.xls [3] Control the writing of your output to the appropriate cells in "reportdir/some_report_yyyy_mm_dd.xls" I find this has the advantage of not having to worry about picking up VBA and the template can be altered fairly easily without messing with the main code. However, this does become problematic if the report is constantly changing and somewhat painful. Sorry I couldn't help with the actual question, but hopefully it might offer a quick workaround for you in the meantime. Cheers, t.
[toc] | [prev] | [next] | [standalone]
| From | "Alexey Popkov" <lehin.p@gmail.com> |
|---|---|
| Date | 2011-10-15 10:07 +0000 |
| Message-ID | <j7bm13$35f$1@smc.vnet.net> |
| In reply to | #5637 |
I used another way: 1) Export data from Mathematica to a text file in CVS format 2) Create a template Excel file which will auto-import this file as external data source. Such file may be created in Excel 2003 via the menu command Data-> Import external data-> Import data... You can tune it to automatically import data every time the file is opened or by demand. HTH, Alexey ----- Original Message ----- From: "Tyler" <hayes.tyler@gmail.com> Newsgroups: comp.soft-sys.math.mathematica Sent: Friday, October 14, 2011 1:55 PM Subject: Re: Formatting in XLS(X) files > Another option, if this is going to be a regular report generated I've > used in the past is: > > [1] Create a blank template XLS file with the formatting you require/ > want. > TEMPLATE_some_report.xls > > [2] In your code, make a copy of the file > > templatedir/TEMPLATE_some_report.xls -> report/ > some_report_file_yyyymmdd.xls > > [3] Control the writing of your output to the appropriate cells in > "reportdir/some_report_yyyy_mm_dd.xls" > > I find this has the advantage of not having to worry about picking up > VBA and the template can be altered fairly easily without messing with > the main code. However, this does become problematic if the report is > constantly changing and somewhat painful. > > Sorry I couldn't help with the actual question, but hopefully it might > offer a quick workaround for you in the meantime. > > Cheers, > > t. > >
[toc] | [prev] | [next] | [standalone]
| From | "Chris Degnen" <degnen@cwgsy.net> |
|---|---|
| Date | 2011-10-16 20:45 +0000 |
| Message-ID | <j7ffpf$j7u$1@smc.vnet.net> |
| In reply to | #5607 |
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
[toc] | [prev] | [next] | [standalone]
| From | Josselin Noirel <commercialat@jnoireldot.fr> |
|---|---|
| Date | 2011-10-28 09:43 +0000 |
| Message-ID | <j8dtgo$kg1$1@smc.vnet.net> |
| In reply to | #5607 |
On 2011-10-12 07:46:37 +0000, Josselin Noirel said: > 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. Thanks everyone for answering (and sorry for the very late reply). Not so easy then, I got really frustrated with Excel about it. NETLink could be the way forward, however I suspect that it won't work on a MacBook. Excel 2011 for Mac seems to have Visual Basic. Either VBA on Excel 2011: 1. I need to get Excel 2011, 2. I need to learn VBA (again) and its functions for formatting I learn NETLink and run the Mathematica code on a Windows machine. NETLink looks like a better option. I'll look in to that. Thanks again! Joss
[toc] | [prev] | [next] | [standalone]
| From | "andre.robin3" <andre.robin3@wanadoo.fr> |
|---|---|
| Date | 2011-10-29 11:18 +0000 |
| Message-ID | <j8gnda$5dt$1@smc.vnet.net> |
| In reply to | #5966 |
"Josselin Noirel" <commercialat@jnoireldot.fr> a écrit dans le message de news: j8dtgo$kg1$1@smc.vnet.net... > On 2011-10-12 07:46:37 +0000, Josselin Noirel said: > >> 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. > > Thanks everyone for answering (and sorry for the very late reply). Not > so easy then, I got really frustrated with Excel about it. > > NETLink could be the way forward, however I suspect that it won't work > on a MacBook. > > Excel 2011 for Mac seems to have Visual Basic. > > Either VBA on Excel 2011: 1. I need to get Excel 2011, 2. I need to > learn VBA (again) and its functions for formatting > > I learn NETLink and run the Mathematica code on a Windows machine. > > NETLink looks like a better option. I'll look in to that. Thanks again! > > Joss > > I don't understand very well the meaning of theses sentences. May be it is important to mention that I have used Excel 2000 on Windows XP.
[toc] | [prev] | [standalone]
Back to top | Article view | comp.soft-sys.math.mathematica
csiph-web