rexcelxlsx

List of data.frame's to individual excel worksheets - R


I have a list of data.frame's that I would like to output to their own worksheets in excel. I can easily save a single data frame to it's own excel file but I'm not sure how to save multiple data frames to the their own worksheet within the same excel file.

library(xlsx)
write.xlsx(sortedTable[1], "c:/mydata.xlsx")

Solution

  • Specify sheet name for each list element.

    library(xlsx)
    file <- paste("usarrests.xlsx", sep = "")
    write.xlsx(USArrests, file, sheetName = "Sheet1") 
    write.xlsx(USArrests, file, sheetName = "Sheet2", append = TRUE)
    

    Second approach as suggested by @flodel, would be to use addDataFrame. This is more or less an example from the help page of the said function.

    file <- paste("usarrests.xlsx", sep="")
    wb <- createWorkbook()
    sheet1 <- createSheet(wb, sheetName = "Sheet1")
    sheet2 <- createSheet(wb, sheetName = "Sheet2")
    
    addDataFrame(USArrests, sheet = sheet1)
    addDataFrame(USArrests * 2, sheet = sheet2)
    saveWorkbook(wb, file = file)
    

    Assuming you have a list of data.frames and a list of sheet names, you can use them pair-wise.

    wb <- createWorkbook()
    datas <- list(USArrests, USArrests * 2)
    sheetnames <- paste0("Sheet", seq_along(datas)) # or names(datas) if provided
    sheets <- lapply(sheetnames, createSheet, wb = wb)
    void <- Map(addDataFrame, datas, sheets)
    saveWorkbook(wb, file = file)