writeData() results in corrupt xlsx if sheet name contains "&"

[klin333]

If writeData() writes to a sheet with "&" in the sheet name string, then resulting xlsx file from saveWorkbook() is corrupt and cannot be opened by Excel.

To Reproduce

library(openxlsx)

file <- 'debug_openxlsx.xlsx'
sheet <- 'a & b'

write.xlsx(datasets::iris, file, sheetName = sheet)  # written file can be opened in excel

wb <- loadWorkbook(file)
writeData(wb, sheet = sheet, 1, colNames = FALSE)
saveWorkbook(wb, file, overwrite = TRUE) # written file cannot be opened in excel

# excel error msg: 
# "We found a problem with some contents in 'debug_openxlsx.xlsx'.
#  Do you want us to try to recover as much as we can?
#  If you trust the source of this workbook, click Yes."
#
# When click yes in excel, it fails with error msg:
# "The workbook cannot be opened or repaired by Microsoft Excel because it is corrupt."

session info

> sessionInfo()
R version 4.1.3 (2022-03-10)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 10 x64 (build 19044)

Matrix products: default

locale:
[1] LC_COLLATE=English_Australia.1252  LC_CTYPE=English_Australia.1252    LC_MONETARY=English_Australia.1252
[4] LC_NUMERIC=C                       LC_TIME=English_Australia.1252    

attached base packages:
[1] graphics  grDevices utils     methods   base     

other attached packages:
[1] openxlsx_4.2.5

loaded via a namespace (and not attached):
[1] compiler_4.1.3 tools_4.1.3    Rcpp_1.0.9     stats_4.1.3    stringi_1.7.8  zip_2.1.1      renv_0.15.5   

Excel version Microsoft® Excel® for Microsoft 365 MSO (Version 2209 Build 16.0.15629.20152) 32-bit

[github-actions[bot]]

This issue is stale because it has been open 365 days with no activity. Remove stale label or comment or this will be closed in 7 days.

[github-actions[bot]]

This issue was closed because it has been stalled for 7 days with no activity.