{"id":338,"date":"2013-11-25T13:01:24","date_gmt":"2013-11-25T18:01:24","guid":{"rendered":"http:\/\/homepages.uc.edu\/~yaozo\/wordpress\/?p=338"},"modified":"2013-11-25T13:01:24","modified_gmt":"2013-11-25T18:01:24","slug":"export-data-frames-to-multi-worksheet-excel-file","status":"publish","type":"post","link":"https:\/\/zhuoyao.net\/index.php\/2013\/11\/25\/export-data-frames-to-multi-worksheet-excel-file\/","title":{"rendered":"Export Data Frames To Multi-worksheet Excel File"},"content":{"rendered":"

A few weeks ago I needed to export a number of data frames to separate worksheets in an Excel file. Although one could output csv-files from R and then import them manually or with the help of VBA into Excel, I was after a more streamlined solution, as I would need to repeat this process quite regularly in the future.<\/p>\n

CRAN has several packages that offer the functionality of creating an Excel file, however several of them provide only the very basic functionality. The\u00a0R-wiki page<\/a>\u00a0on exchanging data between R and Windows applications focuses mainly on the data import problem.<\/p>\n

My objective was to find an export method that would allow me to easily split a larger dataframe by values of a given variable so that each subset would be exported to its own worksheet in the same Excel file. I tried out the different ways of achieving this and documented my findings below.<\/p>\n

 <\/p>\n

\n

<\/a>Data Preparation<\/h2>\n

The goal is to split the\u00a0iris<\/tt>\u00a0dataset by the unique values of\u00a0iris$Species<\/tt>, and export it to a new Excel file with worksheets: setosa, versicolor and virginica.<\/p>\n

Two ways of storing the information were used for the purposes of this exercise:<\/p>\n

<\/a>List of dataframes<\/h3>\n\n\n\n
\n
> library(plyr)<\/pre>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n
\n
> testlist <- dlply(iris, .(Species))<\/pre>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
<\/a>A character vector of containing names of data frames<\/h3>\n
First create the data frames, and then the character vector with the object names \u2013 now we will have three separate data frames called setosa, versicolor and virginica.<\/p>\n\n\n\n
\n
> d_ply(iris, .(Species), function(df) {\n     assign(as.character(df$Species[1]), df,\n         envir = .GlobalEnv)\n })\n> testchar <- as.character(unique(iris$Species))\n> testchar1 <- paste(testchar, collapse = \",\")<\/pre>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Storing data in a list of dataframes is obviously much more convenient, however, as you see later many (or most) functions don\u2019t accept lists as input.<\/p>\n
\n
<\/a>dataframes2xls<\/h2>\n\n\n\n
Note<\/span><\/b><\/td>\nRequires\u00a0Python (>= 2.4)<\/tt><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
dataframes2xls<\/tt><\/a>\u00a0saves dataframes to an xls file. Its main function\u00a0write.xls<\/tt>, is a wrapper around a utility called\u00a0xls2csv<\/tt>.\u00a0xls2csv<\/tt>\u00a0makes use of the\u00a0Python<\/tt>\u00a0module\u00a0pyExcelerator<\/tt><\/a>\u00a0and the\u00a0afm<\/tt>submodule of the\u00a0Python<\/tt>\u00a0module\u00a0matplotlib<\/tt><\/a>, both of which are included in\u00a0dataframes2xls<\/tt>.<\/p>\n\n\n\n
\n
> library(dataframes2xls)\n> dataframes2xls::write.xls(c(setosa, versicolor,\n     virginica), \"dataframes2xls.xls\")<\/pre>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Appending to an existing file not possible, this negates the use of list of dataframes as input. One major shortcoming is that one needs to specify the names of exported dataframes manually. Also, I was not able to pass sheet names to the function if there was more than one sheet.<\/p>\n
There is a way to work around the manual specification of data frames, but as you can see it is not very intuitive:<\/p>\n\n\n\n
\n
> eval(parse(text = paste(\"dataframes2xls::write.xls(c(\",\n     testchar1, \"),\", \"'dataframes2xls.xls')\")))<\/pre>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
\n
<\/a>WriteXLS<\/h2>\n\n\n\n
Note<\/span><\/b><\/td>\nRequires\u00a0Perl<\/tt>\u00a0with module\u00a0Text::CSV_XS<\/tt><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
WriteXLS<\/a>\u00a0is a \u201cPerl<\/tt>\u00a0based R function to create Excel (XLS) files from one or more data frames. Each data frame will be written to a separate named worksheet in the Excel spreadsheet. The worksheet name will be the name of the data frame it contains or can be specified by the user\u201d.<\/p>\n\n\n\n
\n
> library(WriteXLS)\n> WriteXLS(testchar, \"WriteXLS.xls\", perl = perl)<\/pre>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Like\u00a0dataframes2xls<\/tt>\u00a0WriteXLS<\/tt>\u00a0does not take lists as input, and therefore each of the data frames needs to be generated beforehand before calling the function(s). Also appending to a file is not possible as the Excel file, if it exists, is overwritten.<\/p>\n
\n
<\/a>xlsReadWrite(Pro)<\/h2>\n\n\n\n
Note<\/span><\/b><\/td>\nThis package currently works only on Windows machines.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
xlsReadWrite<\/a>\u00a0saves a data frame, matrix or vector as an Excel file in Excel 97-2003 file format.<\/p>\n\n\n\n
\n
> library(xlsReadWrite)\n> xlsReadWrite::write.xls(iris, \"xlsReadWrite.xls\")<\/pre>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
The dataframe can be written to one sheet only. It is not possible to split the data between separate sheets, as append data to existing files feature is available in the\u00a0Pro-version<\/a>.<\/p>\n
The\u00a0Pro-version<\/a>\u00a0has a 30-day trial, so I tried it out.<\/p>\n\n\n\n
\n
> library(xlsReadWritePro)<\/pre>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n
\n
> rfile <- \"xlsReadWritePro.xls\"\n> exc <- xls.new(rfile)\n> l_ply(testlist, function(x) {\n     sheet <- as.character(unique(x$Species))\n     xlsReadWritePro::write.xls(x, file = exc,\n         sheet = sheet, colNames = TRUE)\n })\n> xls.close(exc)<\/pre>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Task accomplished.<\/p>\n
The Pro-version has several other nice features, such as the ability to save images to an Excel file, or to write Excel formulas.<\/p>\n
For reference, the\u00a0licence costs<\/a>\u00a0are as follows.<\/p>\n