楚新元 | All in R

Welcome to R Square

用 openxlsx 定制报表后生成 PDF 文件

楚新元 / 2021-08-20

用 R 清洗加工数据非常方便，但是 R 输出的都是规整的数据框。现实当中每天要报的报表还有一些“乱七八糟”的要素，比如标题、报告日期、数据单位、制表、复核、保密等级等。当然有了最重要的数据部分，导出到 Excel 文件里手动添加剩余的哪些“乱七八糟”的要素也是可以的，如果这个报表只制作一次，我也更倾向于这样做，发挥 R 和 Excel 各自的优势。但是如果这个报表是每天都要报送的日报呢？我是绝对不能容忍每天手动做重复性的工作的，用 R 全自动化处理绝对是最佳选项。下面我们看一个例子：

加载相关 R 包

library(dplyr)
library(kableExtra)
library(openxlsx)
library(lubridate)

生成示例数据

R里面清洗加工数据这块的教程太多了，这里不是我要讲的重点，因此，这里直接随机生成一个数据框。

v = rnorm(168, 100, 5)
m = matrix(v, ncol = 8)
daily = as.data.frame(m)
colnames(daily) = paste0("x", 1:8)
daily %>% 
  kable() %>% 
  kable_styling(font_size = 12)

定制个性化报表

# 定义报告期
report_period = c("2020-04-08") 

# 计算报告期年、月、日
report_year = as.numeric(substr(report_period, 1, 4))
report_month = as.numeric(substr(report_period, 6, 7))
report_day = as.numeric(substr(report_period, 9, 10))

# 数据导入到Excel文件中

## 新建一个工作簿wb、新建一个工作表daily
wb = createWorkbook()
addWorksheet(wb, "daily", gridLines = FALSE)

## 设置全局列宽、行高、冻结活动单元格
setColWidths(
  wb, "daily", 
  cols = 1:8,
  widths = c(26, rep(15.5, 5), 12, 18)
)

setRowHeights(
  wb, "daily",
  rows = 1:(nrow(daily) + 4),
  heights = c(20, 35, rep(20, nrow(daily) + 2))
)

## 打印设置
pageSetup(
  wb, "daily",
  orientation = "landscape",
  scale = 97,
  left = 0.7,
  right = 0.7, 
  top = 0.75,
  bottom = 0.75,
  header = 0.3, 
  footer = 0.3,
  fitToWidth = TRUE,
  fitToHeight = TRUE,
  paperSize = 9,
  printTitleRows = NULL, 
  printTitleCols = NULL
)

## 保密提示
secret = c("★内部资料、严格保密")
style_secret = createStyle(
  halign = "right",
  valign = "center",
  wrapText = TRUE,
  fontColour = "red",
  fontSize = 11, 
  fontName = "Arial"
)

mergeCells(
  wb, "daily", 
  rows = 1:1, 
  cols = 1:8
)

addStyle(
  wb, "daily", 
  style = style_secret,
  rows = 1:1, 
  cols = 1:8
)

writeData(wb, "daily", secret, startRow = 1)

## 大标题设置
title = paste0(
  year(report_period), "年" ,
  month(report_period), "月",
  day(report_period), 
  "日经营数据日报"
)

style_title = createStyle(
  halign = "center",
  valign = "center",
  wrapText = TRUE,
  textDecoration = c("bold"),
  fontColour = "black",
  fontSize = 20, 
  fontName = "Arial"
)

mergeCells(
  wb, "daily", 
  rows = 2:2, 
  cols = 1:8
)

addStyle(
  wb, "daily", 
  style = style_title,
  rows = 2:2, 
  cols = 1:8
)

writeData(wb, "daily", title,  startRow = 2)

## 报告期设置
date = paste0("报告日期：", Sys.Date())

style_date = createStyle(
  halign = "right",
  valign = "center",
  wrapText = TRUE, 
  fontColour = "black",
  fontSize = 11, 
  fontName = "Arial")

mergeCells(wb, "daily", rows = 3:3, cols = 6:7)

addStyle(
  wb, "daily", style = style_date,
  rows = 3:3, cols = 6:7
)

writeData(wb, "daily", date,  startRow = 3, startCol = 6)

## 数据单位设置
unit = "单位：亿元、%"
style_unit = createStyle(
  halign = "center",
  valign = "center",
  wrapText = TRUE, 
  fontColour = "black",
  fontSize = 11, 
  fontName = "Arial")

mergeCells(wb, "daily", rows = 3:3, cols = 8:8)

addStyle(
  wb, "daily", 
  style = style_unit,
  rows = 3:3, 
  cols = 8:8
)

writeData(wb, "daily", unit, startRow = 3, startCol = 8)

## 表头部分设置
style_header = createStyle(
  textDecoration = "Bold",
  halign = "center",
  valign = "center",
  wrapText = TRUE,
  border = "TopBottomLeftRight",
  borderColour = "black",
  fontColour = "white",
  fgFill = "#4F81BD",
  fontSize = 11, 
  fontName = "Arial"
)

## 数据部分设置
style_data = createStyle(
  valign = "center",
  border = "TopBottomLeftRight",
  borderColour = "black",
  fontSize = 11, 
  fontName = "Arial",
  numFmt = "0.00" 
)

addStyle(
  wb, "daily", 
  style = style_data,
  rows = 5:(nrow(daily) + 4), 
  cols = 1:8,
  gridExpand = T
)

writeData(
  wb, "daily", 
  daily,
  headerStyle = style_header,
  startRow = 4
)

## 保存工作簿
saveWorkbook(
  wb, overwrite = TRUE, 
  paste0(report_period, "daily.xlsx")
)

导出为PDF格式

其实导出到 Excel 基本已经大功告成了，可是有的单位要求发布的报告必须是 PDF 格式的。如果单纯的需要PDF格式文件，我们可以考虑直接从示例数据导出到 PDF，而不需要先生成 Excel 文件再转为 PDF 格式，但是这里有个问题，就是数据部分倒是容易，但是报表里那一堆“乱七八糟”的要素，比如标题、报告日期、数据单位、制表、复核、保密等级等要素该如何放进去呢？位置又该怎么精准控制呢？通过已经定制好的 Excel 文件再转为 PDF 虽然有点绕远路了，但是能满足报表要求,所以这里仍然选择数据–> Excel 文件–> PDF 文件。

从 Excel 文件–> PDF 文件，这也不难，打开 Excel 后利用虚拟打印机打印为PDF格式即可，手工操作工作量也是可以忽略的了。但是，对于一个完美主义极客而言这是不够的，因为将来有可能一次生成的不是一张报表，所以，为了将来不时之需，这里仍难考虑通过程序解决。R 里面直接调用虚拟打印机将 Excel 工作表打印成 PDF 实现起来估计有点困难，利用 JAVA 实现的网上倒是有教程 JAVA 调用打印机输出 PDF 文件。因为 Excel 从 2007 开始可以直接将 Excel 工作表转为 PDF，这里我们考虑用 R 调用 VBA 实现。

# 调用 VBA 将 Excel 文件转为 PDF 格式
library(RDCOMClient)

# 定义 Excel 文件路径
xlFile = paste(
  "path/to/",   # 这里使用绝对路径
  "daily.xlsx", 
  sep = report_period
) 

xlApp = COMCreate("Excel.Application")
xlApp[["Visible"]] = TRUE 
wb = xlApp[["Workbooks"]]$Open(Filename = xlFile)
sht = wb[["Worksheets"]]$Item(1)
sht$Select()

pdfFile = paste(
  "path/to/", "daily.pdf", sep = report_period  # 这里使用绝对路径
) 
if (file.exists(pdfFile) == TRUE)  file.remove(pdfFile)

xlApp[["ActiveSheet"]]$ExportAsFixedFormat(
  IgnorePrintAreas = FALSE,
  Type = 0,    # 输出为 PDF
  Filename = pdfFile
  ) 

xlApp$Quit()  # 关闭 Excel

特别需要注意的是：