Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]


Groups > comp.soft-sys.math.mathematica > #5607 > unrolled thread

Formatting in XLS(X) files

Started byJosselin Noirel <commercialat@jnoireldot.fr>
First post2011-10-12 07:46 +0000
Last post2011-10-29 11:18 +0000
Articles 7 — 5 participants

Back to article view | Back to comp.soft-sys.math.mathematica


Contents

  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

#5607 — Formatting in XLS(X) files

FromJosselin Noirel <commercialat@jnoireldot.fr>
Date2011-10-12 07:46 +0000
SubjectFormatting 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]


#5630

From"andre.robin3" <andre.robin3@wanadoo.fr>
Date2011-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]


#5637

FromTyler <hayes.tyler@gmail.com>
Date2011-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]


#5654

From"Alexey Popkov" <lehin.p@gmail.com>
Date2011-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]


#5685

From"Chris Degnen" <degnen@cwgsy.net>
Date2011-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]


#5966

FromJosselin Noirel <commercialat@jnoireldot.fr>
Date2011-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]


#5994

From"andre.robin3" <andre.robin3@wanadoo.fr>
Date2011-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