Writing data frames into multiple excel sheets

rm(list = setdiff(ls(), lsf.str())) # ==================================================== # write a dataframe to excel file # ==================================================== df2xls <- function(L, file, sh.names = "default",                    formats = "default", t.formats = FALSE,                    fnt.names = "Helvetica", fnt.metr = "default",                    col.widths = 48,                     col.names = TRUE, row.names = FALSE,                    to.floats = "default", python = "python",                    py.script = "default", sh.return = FALSE) {     # This function is just a wrapper of the original   # function 'dataframes2xls::write.xls'     # -- extract the dataframe from list L --   df.names <- names(L)   for (nm in df.names) {     assign(x=nm, value=as.data.frame(L[[nm]]), envir=.GlobalEnv)   }     # --  list all the extracted dataframe --   df_list <- paste(df.names,collapse=',')     # --- if sheet.names is not specified ---   if (sh.names=="default") {     sh.names <- df_list   }     # --- write to excel file ---   my.command <- "   dataframes2xls::write.xls(   x=_DF_LIST, file=file, sh.names = sh.names,   formats = formats, t.formats = t.formats, fnt.names = fnt.names, fnt.metr = fnt.metr,   col.widths = col.widths, col.names = col.names, row.names = row.names,   to.floats = to.floats, python = python,   py.script = py.script, sh.return = sh.return   )   "   my.command <- gsub(pattern='_DF_LIST', replacement=paste('c(',df_list,')',sep=''), x=my.command)     eval(parse(text = my.command))     # --- erase the variables after used ---   eval(parse(text=paste('rm(',df_list,',envir=.GlobalEnv)')))     # ------------------------------------------------------------   # Note: Normally, variables created in a function would be local.   # However, in this case, we will have to call package 'dataframes2xls'    # from python, which means the local variables would not be found.   # Therefore, in this function we have to assign the new variables in   # global environment, that is, envir=.GlobalEnv. Afterward, the variables are   # removed from the global environment as well.   # ------------------------------------------------------------     return(T) }     # ================================================ # get the type of variable # ================================================ typeVar <- function(x) {     # --- check if an entry is integer or not ---   IsInteger <- function(x) {     return(x%%1 == 0)   }     # --- main part starts here ---   if (class(x) %in% c('numeric')) {     if (all(IsInteger(x))) {       return('integer')     } else {       if (all(abs(x)>=0 & abs(x)<=1)) {         return('decimal0_1')       } else {         return('decimal')       }      }   } else {     return(class(x))   } }   # ================================================ # recommended format for each dataframe df # ================================================ getFormat <- function(df) {     # ----- function to output format for each column -----   varFormat <- function(var.type) {     switch(var.type,            'integer'={out='x:0'},            'decimal'={ out='x:0.0000' },            'decimal0_1'={ out='x:0.00%' },            'factor'={out='x:general'},            'character'={out='x:general'})     return(out)   }     # ----- figure the format for each column -----   col.type <- sapply(df,typeVar)   var.format <- sapply(col.type,varFormat)   for (i in 1:length(var.format)) {     var.format[i] <-gsub(pattern='x',replacement=i-1,x=var.format[i])   }   return(paste(var.format,collapse='::')) }         # ================================ #               DEMO # ================================   # ====== data preparation ====== df1 <- data.frame(x=c(1,2,3), y=c('a','b','c')) df2 <- data.frame(x=c(1,2,3,4), y=c('a','b','c','z'), z=c(0.1,0.23,0.343434,0.390223132)) # -- put all dataframes into a list -- L <- NULL L$p1 <- df1 L$p2 <- df2 # -- [optional] you might want to assign sheet names -- sheet.names <- 'page1,page2'   # -- specify the python interpretor -- # Note that the package dataframes2xls does not work with python3.X # By default I set it to 'python', but in my machine 'python' = python3.3,  # which does not work.  # That's why I have to use '/Anaconda/python' which is still python 2.7 python.interpretor <- '/Anaconda/python'    # -- your excel file name -- outname <- 'myxlsoutput.xls'   # -- simply run it! -- df2xls(L, file=outname, python = python.interpretor)   # -- a fancier example -- # we can format the output and name the sheets fmts = "0:0::1:general,0:0::1:general::2:0.0%" df2xls(L, file=outname, sh.names = sheet.names,        formats = fmts, python = python.interpretor)   # -- a more automatic way to get format -- fmts = paste(getFormat(df1), getFormat(df2),sep=',') df2xls(L, file=outname, sh.names = sheet.names,        formats = fmts, python = python.interpretor)

Created by Pretty R at inside-R.org