主要用到了?xlsx?和?rJava?包，打開 Excel 文件，讀取各表格數據，再寫入到匯總表。

loadLibrary <- function(pkgs) {uninstalledPkgs <- pkgs[!(pkgs %in% installed.packages()[, "Package"])]if (length(uninstalledPkgs)) {install.packages(uninstalledPkgs)}for (pkg in pkgs) {library(pkg, character.only = TRUE, quietly = TRUE)}
}

setwd("/path/to/work/dir")            # 工作目錄
fileName = "summary_sheet_demo.xlsx"  # 處理的表格文件
summaryName = "匯總"                  # 匯總的 sheet 表名
summarySheet = FALSE                  # 對象變量、忽略
startRow = 2                          # 匯總表中操作起始行

options(repos = "https://mirrors.tuna.tsinghua.edu.cn/CRAN/")

loadLibrary(c("xlsx", "rJava"))

wb <- loadWorkbook(fileName)

# 獲取所有表格
sheets <- getSheets(wb)# 循環所有表格，找出需要寫入的表
for (sheet in sheets) {sheetName <- sheet$getSheetName()if (sheetName == summaryName) {summarySheet <- sheet}
}if (summarySheet == FALSE) {stop(paste("表:", summaryName, "未找到"))
}# 指定Date格式(此處可忽略)
# options(xlsx.date.format='yyyy/MM/dd')# 遍歷所有表格
for (sheet in sheets) {# 過濾掉需寫入的表sheetName <- sheet$getSheetName()if (sheetName == summaryName) {next}# 獲取表格【內容行數】rowNum <- sheet$getLastRowNum()print(paste("表名:", sheetName, "總共:", rowNum, "行,", sep = " "))# 讀取表格內容 參數 colClasses 指定每列的類型(實際是指定處理該列的類/對象)data <- read.xlsx2(fileName, sheetName = sheetName, header = FALSE, startRow = 2, colClasses = c("character", "Date", "integer", "integer", rep("numeric", 2), "integer"))print(data)# 將表格內容寫入匯總的那張表addDataFrame(data, summarySheet, col.names = FALSE, row.names = FALSE, startRow = startRow)# 累加行數startRow <- startRow + rowNum
}

saveWorkbook(wb, fileName)

# 包加載/安裝包
loadLibrary <- function(pkgs) {uninstalledPkgs <- pkgs[!(pkgs %in% installed.packages()[, "Package"])]if (length(uninstalledPkgs)) {install.packages(uninstalledPkgs)}for (pkg in pkgs) {library(pkg, character.only = TRUE, quietly = TRUE)}
}# 自定義配置
setwd("/path/to/work/dir")            # 工作目錄
fileName = "summary_sheet_demo.xlsx"  # 處理的表格文件
summaryName = "匯總"                  # 匯總的 sheet 表名
summarySheet = FALSE                  # 對象變量、忽略
startRow = 2                          # 匯總表中操作起始行# 設置CRAN
options(repos = "https://mirrors.tuna.tsinghua.edu.cn/CRAN/")# 加載依賴包
loadLibrary(c("xlsx", "rJava"))# 打開Excel表格
wb <- loadWorkbook(fileName)
# 獲取所有表格
sheets <- getSheets(wb)# 循環所有表格，找出需要寫入的表
for (sheet in sheets) {sheetName <- sheet$getSheetName()if (sheetName == summaryName) {summarySheet <- sheet}
}if (summarySheet == FALSE) {stop(paste("表:", summaryName, "未找到"))
}# 指定Date格式(此處可忽略)
# options(xlsx.date.format='yyyy/MM/dd')# 遍歷所有表格
for (sheet in sheets) {# 過濾掉需寫入的表sheetName <- sheet$getSheetName()if (sheetName == summaryName) {next}# 獲取表格【內容行數】rowNum <- sheet$getLastRowNum()print(paste("表名:", sheetName, "總共:", rowNum, "行,", sep = " "))# 讀取表格內容 參數 colClasses 指定每列的類型(實際是指定處理該列的類/對象)data <- read.xlsx2(fileName, sheetName = sheetName, header = FALSE, startRow = 2, colClasses = c("character", "Date", "integer", "integer", rep("numeric", 2), "integer"))print(data)# 將表格內容寫入匯總的那張表addDataFrame(data, summarySheet, col.names = FALSE, row.names = FALSE, startRow = startRow)# 累加行數startRow <- startRow + rowNum
}# 最后需要把對象內容寫入文件中
saveWorkbook(wb, fileName)