R:在不阅读整个工作簿的情况下将工作表追加到excel工作簿 [英] R: Append a worksheet to an excel workbook without reading the entire workbook

问题描述

我有一个26 mb的excel工作簿，我正在尝试向其中添加42 kb的工作表.使用openxlsx包，我有以下代码:

I have a 26 mb excel workbook to which I am trying to add a 42 kb worksheet. Using the openxlsx package, I have the following code:

wb_object <- loadWorkbook(to_name2)
addWorksheet(wb_object, "New Data")
writeData(wb_object, sheet = "New Data", m_data)
saveWorkbook(wb_object, to_name2, overwrite = TRUE)

我注意到的是，这段代码需要大约2分钟的时间来执行.我相信R会读取整个26 mb的文件，然后附加42 kb的工作表.有什么方法可以将42 kb工作表附加到26 mb工作簿中，而不必读取26 mb文件?每次运行将节省2分钟.

What I have noticed is that this code takes about 2 minutes to execute. I believe R is reading in the entire 26 mb file and then appending the 42 kb worksheet. Is there any way to append the 42 kb worksheet to the 26 mb workbook without having to read in the 26 mb file? Would save 2 minutes per run.

推荐答案

我通常使用 openxlsx ，但是我不确定 openxlsx 是否可以添加无需先将Excel工作簿加载到R中即可将工作表转换为Excel文件.但是，使用 xlsx 包，您可以 添加新工作表而无需加载Excel文件.例如，如果您的文件是"test.xlsx"，则可以执行以下操作:

I generally use openxlsx, but I'm not sure if openxlsx has a way to add a worksheet to an Excel file without first loading the Excel workbook into R. However, with the xlsx package, you can add a new worksheet without loading the Excel file. For example, if your file is "test.xlsx", then you could do:

library(xlsx)

write.xlsx(new_data, "test.xlsx", sheetName="New_Sheet", append=TRUE)

如果我需要将任何内容保存在Excel文件中，通常我会尝试在R中执行所有操作，然后将需要写入的所有内容写到Excel文件的末尾.但是，如果您需要添加到现有的Excel文件中，则上面的代码提供了执行此操作的选项.

If I need to save anything in an Excel file, I generally try to do everything in R and then write whatever needs to go into the Excel file at the end. However, if you need to add to an existing Excel file, the above code provides an option to do that.

这篇关于R:在不阅读整个工作簿的情况下将工作表追加到excel工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！