openxlsx的writeData()报XML错误，但write_xlsx()正常的问题排查

问题背景 #

我有一个包含字符型、数值型及混合类型列的数据框df，部分列的字符串含括号等标点，或如“Random string 7/19/2023”这类带日期的混合内容。

使用openxlsx包执行以下代码：

file <- createWorkbook('New Workbook.xlsx')
addWorksheet('Test Sheet Name')
writeData(file, sheet = 'Test Sheet Name', df)

打开生成的工作簿时，出现Excel错误：

Removed part: /xl/sharedStrings.xml part with error. (Strings) illegal xml character. Line 100 column 183

但使用write_xlsx()函数（代码如下）生成的文件可正常打开：

write_xlsx(df, "C:\\Users\\User\\Documents\\Random File Name.xlsx")

差异原因 #

• XML字符处理逻辑不同：openxlsx默认用共享字符串（sharedStrings.xml）存储重复文本，生成该文件时不会自动过滤XML标准里的非法字符（比如ASCII控制字符、未转义的&/</>等），这类字符会破坏XML结构，导致Excel解析失败。
• write_xlsx的自动容错：write_xlsx（来自writexl包）内部会自动检测并处理非法XML字符，同时其默认的字符串存储策略不需要依赖共享字符串文件，从根源避免了这类解析错误。

解决方法 #

方法1：预处理数据清除非法XML字符 #

对数据框的字符型列进行清洗，移除或转义XML非法字符：

# 定义清洗函数
clean_xml_chars <- function(x) {
  if (is.character(x)) {
    # 移除ASCII控制字符（保留制表符、换行、回车）
    x <- gsub("[\\x00-\\x08\\x0B\\x0C\\x0E-\\x1F]", "", x)
    # 转义XML特殊字符
    x <- gsub("&", "&amp;", x)
    x <- gsub("<", "&lt;", x)
    x <- gsub(">", "&gt;", x)
    x <- gsub('"', "&quot;", x)
    x <- gsub("'", "&apos;", x)
  }
  return(x)
}

# 清洗数据框的字符列
df_cleaned <- dplyr::mutate(df, dplyr::across(where(is.character), clean_xml_chars))

# 修正openxlsx代码（补全参数并保存）
file <- createWorkbook('New Workbook.xlsx')
addWorksheet(file, 'Test Sheet Name')
writeData(file, sheet = 'Test Sheet Name', df_cleaned)
saveWorkbook(file, 'New Workbook.xlsx', overwrite = TRUE)

注意：原代码存在两处疏漏——addWorksheet缺少第一个参数（workbook对象）、未调用saveWorkbook保存文件，这也是可能导致异常的隐性问题。

方法2：禁用openxlsx的共享字符串功能 #

通过writeData的sharedStrings = FALSE参数，跳过共享字符串存储，直接将文本写入单元格，避免生成易出错的sharedStrings.xml文件：

file <- createWorkbook('New Workbook.xlsx')
addWorksheet(file, 'Test Sheet Name')
writeData(file, sheet = 'Test Sheet Name', df, sharedStrings = FALSE)
saveWorkbook(file, 'New Workbook.xlsx', overwrite = TRUE)

这种方法适合数据量较小的场景，文件体积会略有增加，但能快速绕过XML字符错误问题。

方法3：直接使用write_xlsx函数 #

如果不需要openxlsx的高级功能（比如单元格格式设置、多工作表复杂逻辑），直接使用write_xlsx即可，它的自动字符处理机制能彻底避免这类解析错误，代码更简洁。

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