如何用R语言基于两个DataFrame生成客户专属多Sheet Excel文件

核心思路 #

• 提取两个DataFrame的共同客户，确保只处理两边都存在的客户
• 针对每个客户创建独立Excel工作簿
• 先处理df.1的客户数据，按prod拆分生成对应Sheet
• 再将df.2中该客户的专属数据添加为新Sheet
• 最后保存该客户的Excel文件

修改后的完整代码 #

library(tidyverse)
library(openxlsx)
library(janitor)

# 原始数据
df.1 <- tribble(
  ~customer  ,~period, ~cost1, ~cost2 , ~prod,
  'cust1',  '202201', 5, 10, 'online',
  'cust1',  '202202', 5, 10, 'online',
  'cust1',  '202203', 5, 10, 'in-person',
  'cust1',  '202204', 5, 10, 'in-person',
  'cust2',  '202203', 5, 10,'online',
  'cust2',  '202204', 5, 10, 'in-person',
  'cust2',  '202202', 5, 10, 'online',
  'cust3',  '202204', 5, 10, 'online',
  'cust4',  '202101', NA, NA, 'online',
  'cust4',  '202102', NA,10, 'online'
)

df.2 <- tribble(
  ~customer  ,~period1, ~period2, ~period3 , ~period4,
  'cust1', '202201' , '202202', '202203', '202204',
  'cust2', '202201' , '202202', '202203', '202204',
  'cust3', '202201' , '202202', '202203', '202204',
  'cust4', '202201' , '202202', '202203', '202204')

# 导出Sheet的函数（保留原格式逻辑）
export_sheet <- function(wb, customer, data, sheet) {
  addWorksheet(wb, sheet)
  # 写入客户名称标题
  writeData(
    wb,
    paste0("Client Name: ", customer),
    sheet = sheet,
    startCol = 1, startRow = 3
  )
  # 写入数据
  writeData(wb, data, sheet = sheet, startRow = 6)
  # 给cost1和cost2列添加百分比格式（适配不同数据集）
  if ("cost1" %in% colnames(data)) {
    addStyle(wb, sheet,
             cols = which(colnames(data) == "cost1"),
             rows = 6 + seq_len(nrow(data)),
             style = createStyle(numFmt = "0.0%")
    )
  }
  if ("cost2" %in% colnames(data)) {
    addStyle(wb, sheet,
             cols = which(colnames(data) == "cost2"),
             rows = 6 + seq_len(nrow(data)),
             style = createStyle(numFmt = "0.0%")
    )
  }
}

# 获取两个数据集的共同客户
common_customers <- intersect(unique(df.1$customer), unique(df.2$customer))

# 遍历每个共同客户生成Excel
walk(common_customers, function(cust) {
  # 创建新工作簿
  wb <- createWorkbook()
  
  # 处理df.1的客户数据：按prod拆分生成Sheet
  df.1 %>%
    filter(customer == cust) %>%
    janitor::remove_empty(which = "cols") %>%
    split(.$prod) %>%
    iwalk(~ export_sheet(wb = wb, customer = cust, data = .x, sheet = .y))
  
  # 处理df.2的客户数据：添加单独Sheet
  df.2_cust <- df.2 %>% filter(customer == cust)
  export_sheet(wb = wb, customer = cust, data = df.2_cust, sheet = "Period Summary")
  
  # 保存工作簿
  saveWorkbook(wb, paste0(cust, ".xlsx"), overwrite = TRUE)
})

关键修改说明 #

1. 共同客户筛选：用intersect获取两边都存在的客户，避免生成无对应数据的Excel
2. 统一客户循环：不再分别拆分两个DataFrame，针对每个客户一次性处理两类数据，保证工作簿唯一性
3. 函数鲁棒性优化：原函数固定给第3、4列加格式，修改为按列名匹配，避免df.2数据列数不同导致报错
4. Sheet命名明确：给df.2的Sheet指定名称Period Summary，方便区分两类数据

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