如何用R实现类Excel的数据布局格式化并导出至Excel

两种实现思路 #

1. 先在DataFrame中处理数据再导出 #

这种方式先计算汇总行、调整显示格式，再导出到Excel，适合需要静态结构化数据的场景，跨平台无需依赖Excel环境。

代码实现

library(dplyr)
library(openxlsx)

# 原始数据
df <- data.frame(
  CatNumber = c("CTE00555", "CTE00555", "CTE00555"),
  PolicyLayer = c("Multi Layer Policy #", "Multi Layer Policy #", "Multi Layer Policy #"),
  PolicyNumber = c("SBA1", "SBA2", "SBA3"),
  Insured= c("DisneyCo", "DisneyCo", "DisneyCo"),
  GrossP = c(35000, 4000, 1000),
  GrossComm = c(9000, 1000, 200)
)

# 生成汇总行
summary_row <- df %>%
  group_by(CatNumber, PolicyLayer) %>%
  summarise(
    Insured = "",
    GrossP = sum(GrossP),
    GrossComm = sum(GrossComm),
    .groups = "drop"
  ) %>%
  mutate(PolicyNumber = "")

# 合并数据并调整显示格式
result_df <- bind_rows(summary_row, df) %>%
  arrange(CatNumber, PolicyLayer, PolicyNumber == "") %>% # 汇总行前置
  mutate(
    CatNumber = ifelse(duplicated(CatNumber), "", CatNumber), # 重复CatNumber留空
    PolicyLayer = ifelse(PolicyNumber != "", paste0("    ", PolicyNumber), PolicyLayer), # 子项替换为缩进的PolicyNumber
    PolicyNumber = NULL # 移除多余列
  )

# 导出到Excel并设置缩进格式
wb <- createWorkbook()
addWorksheet(wb, "PivotResult")
writeData(wb, "PivotResult", result_df, rowNames = FALSE)

# 给子项设置缩进样式
indent_style <- createStyle(textIndent = 1)
conditionalFormatting(wb, "PivotResult", cols = 2, rows = 2:nrow(result_df),
                      rule = 'LEFT(B2,4)="    "', style = indent_style)

saveWorkbook(wb, "pivot_static_result.xlsx", overwrite = TRUE)

2. 直接用R操作Excel生成可交互透视表 #

这种方式将原始数据导入Excel后，用代码模拟手动创建透视表的步骤，生成的透视表保留Excel原生交互性（可手动调整字段），适合需要完整透视表功能的场景。

代码实现（Windows环境，依赖RDCOMClient）

library(RDCOMClient)

# 启动Excel应用
excel_app <- COMCreate("Excel.Application")
excel_app[['Visible']] <- FALSE # 后台运行

# 创建工作簿并写入原始数据
wb <- excel_app$Workbooks()$Add()
ws_raw <- wb$Worksheets(1)
ws_raw$Name <- "RawData"
write_data <- function(ws, df) {
  ws$Range(ws$Cells(1,1), ws$Cells(nrow(df)+1, ncol(df)))$Value <- rbind(names(df), df)
}
write_data(ws_raw, df)

# 创建透视表工作表
ws_pivot <- wb$Worksheets()$Add()
ws_pivot$Name <- "PivotTable"

# 添加透视表
pt <- ws_pivot$PivotTables()$Add(
  SourceData = ws_raw$UsedRange(),
  TableDestination = ws_pivot$Range("A1")
)

# 设置行字段
pt$PivotFields("CatNumber")$Orientation <- 1 # xlRowField
pt$PivotFields("PolicyLayer")$Orientation <- 1
pt$PivotFields("PolicyNumber")$Orientation <- 1

# 设置值字段（求和）
pt$AddDataField(pt$PivotFields("GrossP"), "Sum of GrossP", -4157) # xlSum
pt$AddDataField(pt$PivotFields("GrossComm"), "Sum of GrossComm", -4157)

# 模拟Excel操作步骤
pt$LayoutRowDefault <- 1 # 切换为表格布局（Tabular Form）
pt$PivotFields("PolicyNumber")$Subtotals(1) <- FALSE # 关闭PolicyNumber的小计
pt$PivotFields("PolicyLayer")$LayoutForm <- 2 # 显示下一字段标签（对应Display Labels from next field）

# 保存并关闭
wb$SaveAs(normalizePath("pivot_interactive_result.xlsx"))
excel_app$Quit()

总结 #

• 若只需静态表格格式，优先选第一种方法，跨平台且无环境限制；
• 若需要可交互的原生Excel透视表，选第二种方法（仅Windows可用），完全还原手动操作效果。

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