如何将数据从R中的数据框中追加到已经存在的Excel工作表中 [英] How do I append data from a data frame in R to an Excel sheet that already exists

问题描述

我已经在R中创建了数十个数据框，并希望将它们全部附加到Excel文件中的一张纸上.

I have created dozens of data frames in R and would like to append them all to one sheet in an Excel file.

以下是我尝试找到答案的两个页面(我没有10个声誉，因此无法粘贴访问过的所有四个网页网址)

Here are two of the pages I have looked at in an attempt to find an answer (I don't have 10 reputations so I can't paste all four webpage urls I have visited):

将数据写入使用R包xlsx的Excel文件 作者说:您还可以使用addDataFrame函数的startRow和startCol参数将数据框添加到表单中的特定起始位置." 这是建议的代码:

Write data to Excel file using R package xlsx The author says: "You can also add the dataframes to a particular starting place in the sheet using the startRow and startCol arguments to the addDataFrame function." Here is the suggested code:

workbook.sheets workbook.test addDataFrame(x = sample.dataframe, sheet = workbook.test,
   row.names = FALSE, startColumn = 4) # write data to sheet starting on line 1, column 4
saveWorkbook(workbook.sheets, "test.excelfile.xlsx") # and of course you need to save it.

基于此建议，这是我在RStudio中的尝试:

Based on this suggestion, this was my attempt in RStudio:

addDataFrame(df_fl1, sheet = "AllData2.xlsx", startRow = 712)

这是R的输出: sheet $ getWorkbook中的错误:$运算符对原子向量无效

This was R's output: Error in sheet$getWorkbook : $ operator is invalid for atomic vectors

我也尝试过此页面:

有关将Excel文件读取和导入到R中的教程 但是，如果要将数据帧写入到已经存在的文件中，则可以执行以下命令:"

Tutorial on Reading and Importing Excel Files into R "If, however, you want to write the data frame to a file that already exists, you can execute the following command:"

write.xlsx(df, 
           "<name and extension of your existing file>", 
           sheetName="Data Frame"
           append=TRUE)
write.xlsx(df_fl3, "AllData2.xlsx", sheetName="Salinity1", append=TRUE)

我尝试了这段代码，它覆盖了工作表中已经存在的数据.如何将数据框中的数据附加到Excel工作表中?

I tried this code and it overwrote the data that was already in the sheet. How can I append data from the data frames into an Excel sheet?

推荐答案

附加到现有的Excel工作表有点麻烦.而是将所有Excel数据文件读入R，在R中合并它们，然后将单个合并的数据帧写入新的Excel文件中(如果不需要将数据包含在Excel中，则将其写入csv文件中工作簿).有关简便方法和困难方法，请参见下面的代码.

Appending to an existing Excel worksheet is a bit of a pain. Instead, read all of your Excel data files into R, combine them within R, and then write the single combined data frame to a new Excel file (or write to a csv file if you don't need the data to be in an Excel workbook). See code below for both the easy way and the hard way.

简便方法:在R中完成所有工作，并在最后保存单个组合数据框

Easy Way: Do all the work in R and save a single combined data frame at the end

例如，如果您所有的Excel数据文件都在当前工作目录中，并且每个Excel文件中的第一个工作表都包含该数据，则可以执行以下操作:

For example, if all of your Excel data files are in the current working directory and the first worksheet in each Excel file contains the data, you could do the following:

library(xlsx)

# Get file names
file.names = list.files(pattern="xlsx$")

# Read them into a list
df.list = lapply(file.names, read.xlsx, sheetIndex=1, header=TRUE)

然后将它们组合成一个数据帧并写入磁盘:

Then combine them into a single data frame and write to disk:

df = do.call(rbind, df.list)

write.xlsx(df, "combinedData.xlsx", sheetName="data", row.names=FALSE)

硬方式:将连续的数据框追加到预先存在的Excel工作表中

创建一个我们要写入Excel的数据框列表(如上所述，在您的实际用例中，您会将数据文件读入R中的列表).在这里，我们将使用内置的iris数据框进行说明:

Create a list of data frames that we want to write to Excel (as discussed above, in your actual use case, you'll read your data files into a list in R). We'll use the built-in iris data frame for illustration here:

df.list = split(iris, iris$Species)

要将每个数据框写到一个Excel工作表中，首先，创建一个Excel工作簿以及我们要在其中写入数据的工作表:

To write each data frame to a single Excel worksheet, first, create an Excel workbook and the worksheet where we want to write the data:

wb = createWorkbook()
sheet = createSheet(wb, "data")

# Add the first data frame
addDataFrame(df.list[[1]], sheet=sheet, row.names=FALSE, startRow=1)

现在使用循环附加所有剩余的数据帧.每次递增startRow，以便将下一个数据帧写入正确的位置.

Now append all of the remaining data frames using a loop. Increment startRow each time so that the next data frame is written in the correct location.

startRow = nrow(df.list[[1]]) + 2    

for (i in 2:length(df.list)) {

  addDataFrame(df.list[[i]], sheet=sheet, row.names=FALSE, col.names=FALSE, 
               startRow=startRow)

  startRow = startRow + nrow(df.list[[i]])

  }

保存工作簿:

saveWorkbook(wb, "combinedData.xlsx")

如果要在Excel工作表的各个部分中布置各种汇总表，并使它们看起来都很美观，则

addDataFrame很有用.但是，如果您只是将原始数据合并到一个数据文件中，我认为在R中完成所有工作，然后将合并的数据框架最后写入Excel工作表(或csv文件)要容易得多.

addDataFrame is useful if you want to layout various summary tables in various parts of an Excel worksheet and make it all look nice for presentation. However, if you're just combining raw data into a single data file, I think it's a lot easier to do all the work in R and then just write the combined data frame to an Excel worksheet (or csv file) at the end.

这篇关于如何将数据从R中的数据框中追加到已经存在的Excel工作表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！