R help - Jul 2008 - how to export ".xls" file with colorful cells?

Hi all: 
I wanna know how to expoort ".xls" file. And in the exported
".xls" file, how to set different cells with different color?

Thanks a lot! 
My best.



------------------------------
*******************************************
Xin Meng 
Capitalbio Corporation
National Engineering Research Center 
for Beijing Biochip Technology 
BioPharma-informatics & Software Dept. 
Research Engineer
Tel: +86-10-80715888/80726868-6439
Fax: +86-10-010-80726898
Email??xmeng at capitalbio.com 
Address:18 Life Science Parkway, 
Changping District, Beijing 102206, China

on 07/23/2008 08:29 PM XinMeng wrote:
> Hi all: I wanna know how to expoort ".xls" file. And in the
exported
> ".xls" file, how to set different cells with different color?
> 
> Thanks a lot! My best.
You don't indicate what operating system you are using and that is 
important as most of the relevant R packages/functions are Windows specific.

If you are on Windows, then you might want to look at the xlsReadWrite 
CRAN package to address the basic process of creating Excel files.  More 
information is available via the R Wiki here:

http://wiki.r-project.org/rwiki/doku.php?id=tips:data-io:ms_windows

The being said, a quick review suggests that the packages do not offer 
any cell level formatting functions.

If you are looking to create formatted reports, I would urge you to 
consider looking at using R's Sweave package, which uses LaTeX to enable 
reproducible research. An alternative would be the odfWeave package, 
which enables you to create OpenOffice.org documents that can also then 
be saved as MS Office documents or printed to PDF files. For some ideas 
of how Sweave can be used, go here:

http://biostat.mc.vanderbilt.edu/twiki/bin/view/Main/StatReport

If you must stay with Excel (eg. job requires it) and want to automate 
such operations, and further, might be comfortable coding in Perl, you 
can look at the Spreadsheet::WriteExcel module on CPAN:

http://search.cpan.org/~jmcnamara/Spreadsheet-WriteExcel/lib/Spreadsheet/WriteExcel.pm

This module would provide for platform independent creation and cell 
level formatting of Excel files.

HTH,

Marc Schwartz

You didn't mention your OS but if its Windows then see:

http://tolstoy.newcastle.edu.au/R/help/05/07/8950.html

and try adding this line to that -- actually it gives me an
error message (from Excel?) using Excel 2007 on Vista,
RDCOMClient  0.92-0 and "R version 2.7.1 RC (2008-06-16 r45949)"
but it seems to work in any case:

	x12[["Interior"]][["ColorIndex"]] <- 3

You will want to look at a VBA book and translate what you find to
RDCOMClient notation in the manner of this example.

The rcom package can also be used in much the same way.


On Wed, Jul 23, 2008 at 9:29 PM, XinMeng <xmeng at capitalbio.com>
wrote:
> Hi all:
> I wanna know how to expoort ".xls" file. And in the exported
".xls" file, how to set different cells with different color?
>
> Thanks a lot!
> My best.
>
>
>
> ------------------------------
> *******************************************
> Xin Meng
> Capitalbio Corporation
> National Engineering Research Center
> for Beijing Biochip Technology
> BioPharma-informatics & Software Dept.
> Research Engineer
> Tel: +86-10-80715888/80726868-6439
> Fax: +86-10-010-80726898
> Email??xmeng at capitalbio.com
> Address:18 Life Science Parkway,
> Changping District, Beijing 102206, China
>
>
> ______________________________________________
> R-help at r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide
http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>
>

Xin Meng,

Ignore all the "you didn't tell us your operating system"
comments--I
will show you a simple, although very tedious way to do what you want
that is totally free of any operating system limitations.

Began by formatting a very simple Excel file with colored cells, then
save the file as a web page with a .html extension.

Now use your favorite text editor to open this .html file and study
the contents until you understand the XML code.  It is best to start
with extremely simple Excel files in order to understand the contents
of the .html file.

Now write an R script that uses 'cat' to output text that has been
properly formatted in the same style as the .html file.  Instead of
giving the filename a .html extension, you can give it a .xls
extension and then when you double-click on the file, it will open in
Excel.

Microsoft has changed the format of the XML code that it uses in Excel
workbooks saved as HTML files, but when I did this using Microsoft
Office 2000,  in the <style> section of the file I specified a bold
text green fill cell with this style:

.xlBoldGreenFill
	{mso-style-parent:style0;
	font-weight:700;
	font-family:Arial, sans-serif;
	mso-font-charset:0;
	text-align:center;
	background:#CCFFCC;
	mso-pattern:auto none;}

In my R script I then use code to specify the format of an individual
cell or multiple cells (here, 2 cells):

cat(" <td colspan=2 class=xlBoldGreenFill x:str=\" ", Yvar,
" \"> ",
Yvar,"  </td>\n")

Since you will need to build up the entire spreadsheet using output
like this, it works best to use loops and functions that can output
one line at a time.

Good luck,

Kevin Wright


On Wed, Jul 23, 2008 at 8:29 PM, XinMeng <xmeng at capitalbio.com>
wrote:
> Hi all:
> I wanna know how to expoort ".xls" file. And in the exported
".xls" file, how to set different cells with different color?
>
> Thanks a lot!
> My best.
>
>
>
> ------------------------------
> *******************************************
> Xin Meng
> Capitalbio Corporation
> National Engineering Research Center
> for Beijing Biochip Technology
> BioPharma-informatics & Software Dept.
> Research Engineer
> Tel: +86-10-80715888/80726868-6439
> Fax: +86-10-010-80726898
> Email??xmeng at capitalbio.com
> Address:18 Life Science Parkway,
> Changping District, Beijing 102206, China
>
>
> ______________________________________________
> R-help at r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide
http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>
>