[R] how to export ".xls" file with colorful cells?

[Kevin Wright]

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