将多个data.frame导出到多个Excel工作表的简便方法 [英] Easy way to export multiple data.frame to multiple Excel worksheets

问题描述

我很惊讶地发现没有简单的方法可以将多个data.frame导出到Excel文件的多个工作表中?我尝试了 xlsx 软件包，似乎它只能写入一张纸(覆盖旧纸)；我也尝试过 WriteXLS 程序包，但是它总是让我出错...

I am surprised to find that there is no easy way to export multiple data.frame to multiple worksheets of an Excel file? I tried xlsx package, seems it can only write to one sheet (override old sheet); I also tried WriteXLS package, but it gives me error all the time...

我的代码结构是这样的:根据设计，对于每次迭代，输出数据帧(tempTable)和sheetName(sn)都会更新并导出到一个选项卡中.

My code structure is like this: by design, for each iteration, the output dataframe (tempTable) and the sheetName (sn) got updated and exported into one tab.

for (i in 2 : ncol(code)){ 
        ...
        tempTable <- ...
        sn <- ...
        WriteXLS("tempTable", ExcelFileName = "C:/R_code/../file.xlsx",
              SheetNames = sn);
}

我可以导出到多个cvs文件，但是必须有一种简单的方法可以在Excel中做到这一点，对吧?

I can export to several cvs files, but there has to be an easy way to do that in Excel, right?

推荐答案

您可以使用xlsx包写入多个工作表.您只需要为每个数据帧使用不同的sheetName，并需要添加append=TRUE:

You can write to multiple sheets with the xlsx package. You just need to use a different sheetName for each data frame and you need to add append=TRUE:

library(xlsx)
write.xlsx(dataframe1, file="filename.xlsx", sheetName="sheet1", row.names=FALSE)
write.xlsx(dataframe2, file="filename.xlsx", sheetName="sheet2", append=TRUE, row.names=FALSE)

另一个选项可以使您更好地控制格式和数据帧的放置位置，它可以完成R/xlsx代码中的所有操作，然后最后保存工作簿.例如:

Another option, one that gives you more control over formatting and where the data frame is placed, is to do everything within R/xlsx code and then save the workbook at the end. For example:

wb = createWorkbook()

sheet = createSheet(wb, "Sheet 1")

addDataFrame(dataframe1, sheet=sheet, startColumn=1, row.names=FALSE)
addDataFrame(dataframe2, sheet=sheet, startColumn=10, row.names=FALSE)

sheet = createSheet(wb, "Sheet 2")

addDataFrame(dataframe3, sheet=sheet, startColumn=1, row.names=FALSE)

saveWorkbook(wb, "My_File.xlsx")

如果您觉得它有用，可以使用一些有趣的帮助程序功能，这些功能使使用xlsx向电子表格中添加格式，元数据和其他功能更加容易: http://www.sthda.com/zh_CN/wiki/r2excel-read-write-and-format-easily-excel-files-using-r-软件

In case you might find it useful, here are some interesting helper functions that make it easier to add formatting, metadata, and other features to spreadsheets using xlsx: http://www.sthda.com/english/wiki/r2excel-read-write-and-format-easily-excel-files-using-r-software

这篇关于将多个data.frame导出到多个Excel工作表的简便方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！