使用openxlsx与writeData并行导出时中心Excel文件写入失败求助

PSOCK集群的工作机制是每个子进程会复制一份导出的对象副本，你在子进程里修改Workbook_Central其实只是修改了本地副本，主进程里的原对象完全没变化，最后保存的还是初始状态的原对象，所以中心文件没有写入内容。

方案1：先并行生成汇总数据，主进程统一写入（推荐） #

这是最简洁高效的方式，避免在并行进程中操作Excel对象，先收集所有汇总数据，再由主进程统一写入中心文件：

library(openxlsx)
library(parallel)

NameOutputFolder <- "Output"
NameOutputSubfolder <- "Individual files"

OutputFolder <- file.path(".", NameOutputFolder)
if(!dir.exists(OutputFolder))   dir.create(OutputFolder)
OutputSubfolder <- file.path(".", NameOutputFolder, NameOutputSubfolder)
if(!dir.exists(OutputSubfolder))    dir.create(OutputSubfolder)

OutputFile_Central <- file.path(OutputFolder, "Excel_Central.xlsx")

# 初始化中心工作簿
Workbook_Central <- createWorkbook()
addWorksheet(wb = Workbook_Central, sheetName = "Summary", zoom = 80, gridLines = FALSE)

no_cores <- detectCores()
MyCluster <- makePSOCKcluster(no_cores - 1)
clusterEvalQ(MyCluster, {
    library(openxlsx)
})
clusterExport(MyCluster, "OutputSubfolder")

# 并行生成独立文件，同时返回需汇总的数据
summary_data <- parLapply(cl = MyCluster, X = 1:10, fun = function(Row){
    OutputFile_Individual <- file.path(OutputSubfolder, paste0("Excel_Individual_", Row, ".xlsx"))
    Workbook_Individual <- createWorkbook()
    # 可在此为独立文件添加内容，例如：writeData(Workbook_Individual, "Sheet1", Row)
    saveWorkbook(wb = Workbook_Individual, file = OutputFile_Individual, overwrite = TRUE)
    # 返回当前行的汇总数据
    return(Row)
})
stopCluster(MyCluster)

# 主进程统一写入汇总数据
summary_df <- data.frame(Value = unlist(summary_data))
writeData(wb = Workbook_Central, sheet = "Summary", x = summary_df, startCol = 1, startRow = 1)
saveWorkbook(wb = Workbook_Central, file = OutputFile_Central, overwrite = TRUE)

方案2：子进程生成临时汇总文件，主进程合并 #

和你猜想的思路一致，每个子进程生成专属临时Excel文件，最后由主进程合并所有临时文件内容到中心文件：

library(openxlsx)
library(parallel)
library(purrr)

NameOutputFolder <- "Output"
NameOutputSubfolder <- "Individual files"
TempFolder <- file.path(OutputFolder, "Temp_Summary")

OutputFolder <- file.path(".", NameOutputFolder)
if(!dir.exists(OutputFolder))   dir.create(OutputFolder)
OutputSubfolder <- file.path(".", NameOutputFolder, NameOutputSubfolder)
if(!dir.exists(OutputSubfolder))    dir.create(OutputSubfolder)
if(!dir.exists(TempFolder))    dir.create(TempFolder)

OutputFile_Central <- file.path(OutputFolder, "Excel_Central.xlsx")

no_cores <- detectCores()
MyCluster <- makePSOCKcluster(no_cores - 1)
clusterEvalQ(MyCluster, {
    library(openxlsx)
})
clusterExport(MyCluster, c("OutputSubfolder", "TempFolder"))

parLapply(cl = MyCluster, X = 1:10, fun = function(Row){
    # 生成独立文件
    OutputFile_Individual <- file.path(OutputSubfolder, paste0("Excel_Individual_", Row, ".xlsx"))
    Workbook_Individual <- createWorkbook()
    saveWorkbook(wb = Workbook_Individual, file = OutputFile_Individual, overwrite = TRUE)
    
    # 生成临时汇总文件
    temp_wb <- createWorkbook()
    addWorksheet(temp_wb, "Temp")
    writeData(temp_wb, "Temp", Row, startCol = 1, startRow = Row)
    temp_file <- file.path(TempFolder, paste0("temp_summary_", Sys.getpid(), "_", Row, ".xlsx"))
    saveWorkbook(temp_wb, temp_file, overwrite = TRUE)
})
stopCluster(MyCluster)

# 主进程合并临时文件到中心Excel
Workbook_Central <- createWorkbook()
addWorksheet(wb = Workbook_Central, sheetName = "Summary", zoom = 80, gridLines = FALSE)

temp_files <- list.files(TempFolder, full.names = TRUE)
walk(temp_files, function(file){
    temp_wb <- loadWorkbook(file)
    temp_data <- read.xlsx(temp_wb, sheet = "Temp")
    # 写入对应行位置
    writeData(Workbook_Central, "Summary", temp_data, startCol = 1, startRow = which(!is.na(temp_data)))
})

saveWorkbook(wb = Workbook_Central, file = OutputFile_Central, overwrite = TRUE)
# 清理临时文件
unlink(TempFolder, recursive = TRUE)

方案3：共享内存/文件锁（进阶） #

如果汇总数据量极大，可使用bigmemory或filelock包实现共享内存或文件锁，确保多进程安全写入中心文件，但操作复杂度较高，适合特定大数据场景，一般前两种方案足以满足需求。

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