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

From: Kevin Wright <kw.statr_at_gmail.com>
Date: Mon, 28 Jul 2008 10:21:23 -0500

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



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. Received on Mon 28 Jul 2008 - 16:08:35 GMT

Archive maintained by Robert King, hosted by the discipline of statistics at the University of Newcastle, Australia.
Archive generated by hypermail 2.2.0, at Mon 28 Jul 2008 - 16:32:36 GMT.

Mailing list information is available at https://stat.ethz.ch/mailman/listinfo/r-help. Please read the posting guide before posting to the list.

list of date sections of archive