R 脚本读取汇总 Excel 表格数据

年轻不如一拼

主要用到了 xlsx 和 rJava 包，打开 Excel 文件，读取各表格数据，再写入到汇总表。

下图为处理前的原始数据表格：


下图为处理后的数据：


代码实现
安装&加载包的函数实现。installed.packages() 函数获取所有已安装的包，用以排除已安装的包。install.packages() 函数安装指定的包。library() 加载引入包。

1. loadLibrary <- function(pkgs) {
   
2.     uninstalledPkgs <- pkgs[!(pkgs %in% installed.packages()[, "Package"])]
   
3.    
   
4.     if (length(uninstalledPkgs)) {
   
5.         install.packages(uninstalledPkgs)
   
6.     }
   
7.    
   
8.     for (pkg in pkgs) {
   
9.         library(pkg, character.only = TRUE, quietly = TRUE)
   
10.     }
    
11. }
    

复制代码

自定义设置。涉及表格文件路径、汇总表名等。

1. setwd("/path/to/work/dir")            # 工作目录
   
2. fileName = "summary_sheet_demo.xlsx"  # 处理的表格文件
   
3. summaryName = "汇总"                  # 汇总的 sheet 表名
   
4. summarySheet = FALSE                  # 对象变量、忽略
   
5. startRow = 2                          # 汇总表中操作起始行

复制代码

设置 CRAN 源。官方默认源可能比较慢，所以选择国内的源很重要。此处选择了清华 CRAN 源，其他的可参考 CRAN Mirrors:https://cran.r-project.org/mirrors.html。

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

复制代码

加载依赖包。用到了上述写的安装加载包的函数。

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

复制代码

打开工作表。loadWorkbook() 打开指定路径的表格文件，并加载到工作簿对象中。

1. wb <- loadWorkbook(fileName)

复制代码

遍历查询&汇总数据。

1. # 获取所有表格
   
2. sheets <- getSheets(wb)
   
3. 
   
4. # 循环所有表格，找出需要写入的表
   
5. for (sheet in sheets) {
   
6.     sheetName <- sheet$getSheetName()
   
7.     if (sheetName == summaryName) {
   
8.         summarySheet <- sheet
   
9.     }
   
10. }
    
11. 
    
12. if (summarySheet == FALSE) {
    
13.     stop(paste("表:", summaryName, "未找到"))
    
14. }
    
15. 
    
16. # 指定Date格式(此处可忽略)
    
17. # options(xlsx.date.format='yyyy/MM/dd')
    
18. 
    
19. # 遍历所有表格
    
20. for (sheet in sheets) {
    
21.     # 过滤掉需写入的表
    
22.     sheetName <- sheet$getSheetName()
    
23.     if (sheetName == summaryName) {
    
24.         next
    
25.     }
    
26.    
    
27.     # 获取表格【内容行数】
    
28.     rowNum <- sheet$getLastRowNum()
    
29.     print(paste("表名:", sheetName, "总共:", rowNum, "行,", sep = " "))
    
30.    
    
31.     # 读取表格内容 参数 colClasses 指定每列的类型(实际是指定处理该列的类/对象)
    
32.     data <- read.xlsx2(fileName, sheetName = sheetName, header = FALSE, startRow = 2, colClasses = c("character",
    
33.         "Date", "integer", "integer", rep("numeric", 2), "integer"))
    
34.     print(data)
    
35.    
    
36.     # 将表格内容写入汇总的那张表
    
37.     addDataFrame(data, summarySheet, col.names = FALSE, row.names = FALSE, startRow = startRow)
    
38.    
    
39.     # 累加行数
    
40.     startRow <- startRow + rowNum
    
41. }

复制代码

把对象内容写入文件中保存。

1. saveWorkbook(wb, fileName)

复制代码

完整代码

1. # 包加载/安装包
   
2. loadLibrary <- function(pkgs) {
   
3.     uninstalledPkgs <- pkgs[!(pkgs %in% installed.packages()[, "Package"])]
   
4.    
   
5.     if (length(uninstalledPkgs)) {
   
6.         install.packages(uninstalledPkgs)
   
7.     }
   
8.    
   
9.     for (pkg in pkgs) {
   
10.         library(pkg, character.only = TRUE, quietly = TRUE)
    
11.     }
    
12. }
    
13. 
    
14. # 自定义配置
    
15. setwd("/path/to/work/dir")            # 工作目录
    
16. fileName = "summary_sheet_demo.xlsx"  # 处理的表格文件
    
17. summaryName = "汇总"                  # 汇总的 sheet 表名
    
18. summarySheet = FALSE                  # 对象变量、忽略
    
19. startRow = 2                          # 汇总表中操作起始行
    
20. 
    
21. # 设置CRAN
    
22. options(repos = "https://mirrors.tuna.tsinghua.edu.cn/CRAN/")
    
23. 
    
24. # 加载依赖包
    
25. loadLibrary(c("xlsx", "rJava"))
    
26. 
    
27. # 打开Excel表格
    
28. wb <- loadWorkbook(fileName)
    
29. # 获取所有表格
    
30. sheets <- getSheets(wb)
    
31. 
    
32. # 循环所有表格，找出需要写入的表
    
33. for (sheet in sheets) {
    
34.     sheetName <- sheet$getSheetName()
    
35.     if (sheetName == summaryName) {
    
36.         summarySheet <- sheet
    
37.     }
    
38. }
    
39. 
    
40. if (summarySheet == FALSE) {
    
41.     stop(paste("表:", summaryName, "未找到"))
    
42. }
    
43. 
    
44. # 指定Date格式(此处可忽略)
    
45. # options(xlsx.date.format='yyyy/MM/dd')
    
46. 
    
47. # 遍历所有表格
    
48. for (sheet in sheets) {
    
49.     # 过滤掉需写入的表
    
50.     sheetName <- sheet$getSheetName()
    
51.     if (sheetName == summaryName) {
    
52.         next
    
53.     }
    
54.    
    
55.     # 获取表格【内容行数】
    
56.     rowNum <- sheet$getLastRowNum()
    
57.     print(paste("表名:", sheetName, "总共:", rowNum, "行,", sep = " "))
    
58.    
    
59.     # 读取表格内容 参数 colClasses 指定每列的类型(实际是指定处理该列的类/对象)
    
60.     data <- read.xlsx2(fileName, sheetName = sheetName, header = FALSE, startRow = 2, colClasses = c("character",
    
61.         "Date", "integer", "integer", rep("numeric", 2), "integer"))
    
62.     print(data)
    
63.    
    
64.     # 将表格内容写入汇总的那张表
    
65.     addDataFrame(data, summarySheet, col.names = FALSE, row.names = FALSE, startRow = startRow)
    
66.    
    
67.     # 累加行数
    
68.     startRow <- startRow + rowNum
    
69. }
    
70. 
    
71. # 最后需要把对象内容写入文件中
    
72. saveWorkbook(wb, fileName)

复制代码

表格附件: