Example 8.38: WriteXLS to create spreadsheets

May 16, 2011

(This article was first published on SAS and R, and kindly contributed to R-bloggers)

In our last entry, we described reading Excel files. In this entry, we do the opposite: write native Excel files.


In R, the WriteXLS package provides this functionality. It uses perl to do the heavy lifting, and the main complication is to install this with the needed Perl modules. There are detailed instructions here. On Nick’s mac (which came with perl already installed), he needed to run the additional command:

cpan -i Text::CSV_XS

to add the needed functionality. Once this was done, he ran the commands:


which yielded the encouraging output:

Perl found.
All required Perl modules were found.

To generate the spreadsheet, the WriteXLS() function was called with a character vector or list containing the data frames to export, along with the name of the spreadsheet to create. Here we want to write out the HELP data frame.

HELP = read.csv("http://www.math.smith.edu/r/data/help.csv")
WriteXLS("HELP", "newhelp.xls")

It might be necessary to write multiple sheets to a single file. Here, as an example, we make a new table with just female subjects, then create an Excel file with the whole data and just the women.

helpfemale = subset(HELP, female==1)
WriteXLS(c("HELP", "helpfemale"), "newhelp.xls")


Several options exist in SAS for writing Excel files. The simplest may be through using the libname statement, but this appears to be platform dependent and we’ve had trouble using it. We’ve had more success with proc export, shown below.

data help;
set "c:\book\help.sas7bdat";

proc export data = help outfile = "c:\book\newhelp.xls"

proc export data = help (where=(female=1))
outfile = "c:\book\newhelp.xls" dbms = excel;
sheet="Females only";

The second proc export statement adds a new sheet to the existing Excel file, with the designated name. This sheet contains only women due to the data set option where (section 1.5.1).

To leave a comment for the author, please follow the link and comment on their blog: SAS and R.

R-bloggers.com offers daily e-mail updates about R news and tutorials on topics such as: Data science, Big Data, R jobs, visualization (ggplot2, Boxplots, maps, animation), programming (RStudio, Sweave, LaTeX, SQL, Eclipse, git, hadoop, Web Scraping) statistics (regression, PCA, time series, trading) and more...

If you got this far, why not subscribe for updates from the site? Choose your flavor: e-mail, twitter, RSS, or facebook...

Tags: , , , , ,

Comments are closed.


Mango solutions

RStudio homepage

Zero Inflated Models and Generalized Linear Mixed Models with R

Quantide: statistical consulting and training




CRC R books series

Contact us if you wish to help support R-bloggers, and place your banner here.

Never miss an update!
Subscribe to R-bloggers to receive
e-mails with the latest R posts.
(You will not see this message again.)

Click here to close (This popup will not appear again)