使用R的openxlsx读写.xlsm文件后，Excel提示文件损坏或格式无效

用户尝试用R的openxlsx包读取.xlsm文件，写入数据后保存为.xlsm和.xlsx格式，但Excel打开时出现以下错误：

• 保存为xlsx格式：提示“Microsoft Excel无法打开或修复此工作簿，因为文件已损坏”
• 保存为xlsm格式：提示“文件格式或文件扩展名无效。请验证文件未损坏且扩展名与文件格式匹配”

用户提供的代码：

datDir<-directory<-dirmm <- getwd()
args <- c(dir)
setwd(args[1])
files <-  list.files(datDir, pattern = ".xls",full.names=F)
input_Excel<- 
  files[1]
library(openxlsx) 

#input_Excel <- "foo.xlsm"
wb <- loadWorkbook(paste0(dirmm,"\", input_Excel)) 
writeData(wb, "sheet 1", "namething", startCol = 2, startRow = 1)

 saveWorkbook(wb, overwrite = TRUE, file = paste0(dir, "\", namer".xlsm"))
 saveWorkbook(wb, overwrite = TRUE, file = paste0(dir, "\", namer".xlsx"))

问题分析与修正方案 #

代码存在多处错误导致文件保存损坏：

1. 路径拼接转义错误
   手动拼接路径时用了单个反斜杠\"，这在R中是无效的转义字符，会导致路径拼接错误，读取的工作簿对象损坏。建议用file.path()自动处理路径分隔符，避免手动转义。

2. 未定义变量

   • dir变量未赋值，args <- c(dir)会报错，后续setwd(args[1])逻辑无效。
   • namer变量未定义，保存文件时的文件名拼接失败，生成无效路径。

3. loadWorkbook格式未指定
   读取.xlsm文件时需要明确指定format = "xlsm"，否则openxlsx可能无法正确识别宏文件结构。

4. saveWorkbook参数顺序错误
   saveWorkbook的正确参数顺序是(wb, file, overwrite)，用户将overwrite放在了file前面，导致保存逻辑错误。

修正后的代码 #

# 设置工作目录
datDir <- getwd()

# 匹配.xls或.xlsm文件（正则表达式确保准确匹配）
files <- list.files(datDir, pattern = "\\.xls(m)?$", full.names = FALSE)
input_Excel <- files[1]

library(openxlsx)

# 读取xlsm文件，指定格式
wb <- loadWorkbook(file.path(datDir, input_Excel), format = "xlsm")

# 写入数据到指定工作表（明确参数名避免歧义）
writeData(wb, sheet = "sheet 1", x = "namething", startCol = 2, startRow = 1)

# 定义输出文件名
output_filename <- "modified_file"

# 保存为xlsm格式
saveWorkbook(wb, file = file.path(datDir, paste0(output_filename, ".xlsm")), overwrite = TRUE)

# 保存为xlsx格式
saveWorkbook(wb, file = file.path(datDir, paste0(output_filename, ".xlsx")), overwrite = TRUE)

关键修正说明 #

• 使用file.path()拼接路径，自动适配Windows/macOS的路径分隔符，避免转义错误。
• 读取.xlsm文件时指定format = "xlsm"，确保宏文件结构被正确解析。
• 修正saveWorkbook的参数顺序，保证文件保存逻辑正确。
• 定义缺失的变量，确保路径和文件名有效。
• 优化文件匹配的正则表达式，避免匹配到非目标文件。

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