如何加速R语言中.xlsx工作表的覆写操作？

一、优化现有openxlsx代码（最快见效） #

你当前分多次调用writeData是速度慢的核心原因——每次调用都会触发与工作簿的IO交互，合并表头和数据后一次性写入能大幅减少开销：

gc()

# 简化文件匹配逻辑
filename <- list.files(pattern = "DATA_SF")
wb <- openxlsx::loadWorkbook(filename)

# 一次性写入Question_Text工作表
openxlsx::writeData(wb, "Question_Text", as.data.frame(Question_Text), startRow = 1, startCol = 1)

# 合并Split表的表头+数据，一次性写入
split_full <- rbind(as.data.frame(split_qualtricsheaders), as.data.frame(split_qualtrics))
openxlsx::writeData(wb, "Raw Data (Split)", split_full, startRow = 1, startCol = 1, colNames = FALSE)

# 合并Non-split表的表头+数据，一次性写入
nonsplit_full <- rbind(as.data.frame(nonsplit_qualitricsheaders), as.data.frame(nonsplit_qualitrics))
openxlsx::writeData(wb, "Raw Data (Non-split)", nonsplit_full, startRow = 1, startCol = 1, colNames = FALSE)

openxlsx::saveWorkbook(wb, file = filename, overwrite = TRUE)

二、替换为openxlsx2包（性能升级） #

openxlsx2是openxlsx的重写版本，底层优化了数据处理逻辑，处理大型数据集时速度显著更快，且完全兼容openxlsx的核心API，无需大幅修改代码：

gc()

filename <- list.files(pattern = "DATA_SF")
wb <- openxlsx2::wb_load(filename)

# 写入Question_Text
openxlsx2::wb_add_data(wb, "Question_Text", as.data.frame(Question_Text), start_row = 1, start_col = 1)

# 合并后写入Split表
split_full <- rbind(as.data.frame(split_qualtricsheaders), as.data.frame(split_qualtrics))
openxlsx2::wb_add_data(wb, "Raw Data (Split)", split_full, start_row = 1, start_col = 1, col_names = FALSE)

# 合并后写入Non-split表
nonsplit_full <- rbind(as.data.frame(nonsplit_qualitricsheaders), as.data.frame(nonsplit_qualitrics))
openxlsx2::wb_add_data(wb, "Raw Data (Non-split)", nonsplit_full, start_row = 1, start_col = 1, col_names = FALSE)

openxlsx2::wb_save(wb, file = filename, overwrite = TRUE)

三、readxl + writexl组合（适合无格式需求场景） #

如果不需要保留原有工作表的格式（如单元格样式、公式），可以直接读取不需要修改的工作表，再按原顺序新建工作簿写入，writexl的写入速度极快：

gc()

filename <- list.files(pattern = "DATA_SF")
# 获取原有工作表的顺序
existing_sheets <- readxl::excel_sheets(filename)
# 确定需要保留的非目标工作表
sheets_to_keep <- setdiff(existing_sheets, c("Question_Text", "Raw Data (Split)", "Raw Data (Non-split)"))

# 按原顺序构建新工作簿的内容
new_workbook <- list()
for (sheet in existing_sheets) {
  switch(sheet,
         "Question_Text" = new_workbook[[sheet]] <- as.data.frame(Question_Text),
         "Raw Data (Split)" = new_workbook[[sheet]] <- rbind(as.data.frame(split_qualtricsheaders), as.data.frame(split_qualtrics)),
         "Raw Data (Non-split)" = new_workbook[[sheet]] <- rbind(as.data.frame(nonsplit_qualitricsheaders), as.data.frame(nonsplit_qualitrics)),
         # 读取不需要修改的工作表
         new_workbook[[sheet]] <- readxl::read_excel(filename, sheet = sheet)
  )
}

# 写入新工作簿并覆盖原文件
writexl::write_xlsx(new_workbook, path = filename)

关键说明 #

• 避免使用xlsx包：依赖Java环境，内存管理问题难以解决，处理大型数据时极易崩溃，不推荐。
• 合并写入是核心：减少与工作簿的IO交互次数是提升速度的关键，不要拆分表头和数据分多次写入。

内容的提问来源于stack exchange，提问作者megmac