如何在R中更新Excel透视表数据源并清除旧数据？

已完成将Power Query转换为R代码，需输出与原Excel文件完全一致的结果，核心需求是保留原有透视表的同时更新其数据源工作表。当前遇到的问题：

• 直接覆盖数据源工作表会触发openxlsx报错（提示无法覆盖表头）
• 删除工作表再重建同名表会断开透视表与数据源的链接
• 现有代码仅删除旧数据的行（保留表头）后写入新数据，但若旧数据集行数多于新数据，会残留过时数据导致透视表错误

现有代码：

# Load the copied workbook
library(openxlsx)
wb <- loadWorkbook(copied_file)

data <- readWorkbook(wb, sheet = "Data veckostatistik aktiv dos")

# Remove all but the first row (headers)
removeRow(wb, sheet = "Data veckostatistik aktiv dos", rows = 2:nrow(data))

# Add new worksheets and data
writeData(wb, "Data veckostatistik aktiv dos", test, startRow = 2, colNames = FALSE)

saveWorkbook(wb, copied_file, overwrite = TRUE)

示例数据：

• 新数据结构：

structure(list(region = c("10", "10", ...), ...), row.names = c(NA, -110L), class = c("tbl_df", "tbl", "data.frame"))

• 旧数据结构：

structure(list(region = c("10", "10", ...), ...), row.names = c(NA, -145L), class = c("tbl_df", "tbl", "data.frame"))

方法1：彻底删除旧数据行+写入新数据 #

现有代码的问题可能是removeRow未彻底删除所有旧数据行（比如工作表中存在超出nrow(data)的空行或隐藏行）。可以先获取工作表的总行数，再删除所有非表头行，确保数据源工作表仅保留表头，再写入新数据：

library(openxlsx)
wb <- loadWorkbook(copied_file)
sheet_name <- "Data veckostatistik aktiv dos"

# 获取工作表的总行数（包括空行）
sheet_info <- getSheetInfo(wb)[getSheetInfo(wb)$name == sheet_name, ]
total_rows <- sheet_info$rows

# 删除所有非表头行（第2行到最后一行）
if (total_rows >= 2) {
  removeRow(wb, sheet = sheet_name, rows = 2:total_rows)
}

# 写入新数据（从第2行开始，不写列名）
writeData(wb, sheet = sheet_name, x = test, startRow = 2, colNames = FALSE)

# 保存工作簿
saveWorkbook(wb, copied_file, overwrite = TRUE)

方法2：清空数据区域+调整透视表数据源范围（若透视表基于命名区域） #

如果透视表的数据源是命名区域，可以先清空数据源区域的内容，写入新数据后，重新定义命名区域的范围，确保透视表只读取有效数据：

library(openxlsx)
wb <- loadWorkbook(copied_file)
sheet_name <- "Data veckostatistik aktiv dos"
new_data <- test

# 1. 清空现有数据区域（保留表头）
deleteData(wb, sheet = sheet_name, rows = 2:(nrow(new_data)+1), cols = 1:ncol(new_data))

# 2. 写入新数据
writeData(wb, sheet = sheet_name, x = new_data, startRow = 2, colNames = FALSE)

# 3. 重新定义命名区域（假设透视表使用的命名区域为"DataRange"）
new_range <- paste0(sheet_name, "!$A$1:$", LETTERS[ncol(new_data)], "$", nrow(new_data)+1)
defineName(wb, name = "DataRange", rows = new_range)

# 保存工作簿
saveWorkbook(wb, copied_file, overwrite = TRUE)

方法3：使用openxlsx2库（对透视表支持更友好） #

openxlsx2是openxlsx的升级版，对Excel对象（包括透视表）的操作更灵活，可以直接更新透视表的数据源范围：

library(openxlsx2)
wb <- wb_load(copied_file)
sheet_name <- "Data veckostatistik aktiv dos"
new_data <- test

# 删除旧数据行
wb$remove_rows(sheet = sheet_name, rows = 2:wb_get_max_row(wb, sheet = sheet_name))

# 写入新数据
wb$add_data(sheet = sheet_name, x = new_data, start_row = 2, col_names = FALSE)

# 更新透视表数据源（假设透视表在"PivotSheet"工作表，名称为"PivotTable1"）
pivot_sheet <- "PivotSheet"
pivot_name <- "PivotTable1"
new_source_range <- paste0(sheet_name, "!A1:", LETTERS[ncol(new_data)], nrow(new_data)+1)
wb$pivot_table_update_source(sheet = pivot_sheet, pivot_table = pivot_name, data_source = new_source_range)

# 保存工作簿
wb_save(wb, copied_file, overwrite = TRUE)

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