如何用R搜索并替换Excel的sharedStrings.xml文件中的指定文本？

核心思路 #

xlsx本质是压缩包，其中sharedStrings.xml存储所有共享文本，我们可以通过解压-修改XML-重新压缩的流程实现批量替换，彻底替代手动操作。

步骤详解 #

1. 准备依赖工具包 #

需要用到xml2（解析XML）、zip（处理压缩包），先安装并加载：

install.packages(c("xml2", "zip"))
library(xml2)
library(zip)

2. 编写批量处理函数 #

这个函数会自动完成解压、替换、重新打包的全流程：

replace_keys_in_xlsx <- function(input_xlsx, output_xlsx, key_mapping) {
  # 创建临时目录存放解压后的文件
  temp_dir <- tempdir()
  unzip(input_xlsx, exdir = temp_dir)
  
  # 定位sharedStrings.xml的路径
  ss_path <- file.path(temp_dir, "xl", "sharedStrings.xml")
  if (!file.exists(ss_path)) {
    stop("该Excel文件未使用共享字符串存储，无法批量替换")
  }
  
  # 读取XML文档
  doc <- read_xml(ss_path)
  
  # 找到所有文本节点（注意处理xlsx的XML命名空间）
  text_nodes <- xml_find_all(doc, "//d:t", xml_ns(doc))
  
  # 遍历节点，匹配密钥并替换
  for (node in text_nodes) {
    current_text <- xml_text(node)
    if (current_text %in% names(key_mapping)) {
      xml_text(node) <- key_mapping[[current_text]]
    }
  }
  
  # 保存修改后的XML文件
  write_xml(doc, ss_path)
  
  # 重新打包成标准xlsx格式
  zip(output_xlsx, files = list.files(temp_dir, recursive = TRUE), 
      root = temp_dir, mode = "cherry-pick")
  
  # 自动清理临时文件
  unlink(temp_dir, recursive = TRUE)
}

3. 实际使用示例 #

先定义密钥与真实值的映射表，再单个或批量处理文件：

# 定义密钥-真实值的映射关系
key_map <- list(
  "DDR022-02-S2" = "真实项目名称A",
  "XYZ123-05" = "真实项目名称B"
)

# 处理单个Excel文件
replace_keys_in_xlsx("原始数据.xlsx", "去密钥后数据.xlsx", key_map)

# 批量处理文件夹下所有xlsx文件
input_dir <- "存放原始文件的文件夹路径"
output_dir <- "存放处理后文件的文件夹路径"
dir.create(output_dir, recursive = TRUE)

xlsx_files <- list.files(input_dir, pattern = "\\.xlsx$", full.names = TRUE)
for (file in xlsx_files) {
  output_file <- file.path(output_dir, basename(file))
  replace_keys_in_xlsx(file, output_file, key_map)
}

关键注意点 #

• 之前用xml_find_all失败，核心原因是没处理xlsx XML的命名空间，xml_ns(doc)会自动识别命名空间，避免节点找不到的问题
• 临时目录用tempdir()生成，函数执行后自动清理，不会留下冗余文件
• 重新打包用mode = "cherry-pick"，确保完全还原xlsx的原始文件结构，保留所有格式设置

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