search

awalker89 / openxlsx

R package for .xlsx file reading and writing.
Other
365 stars 78 forks source link

Error with sheetVisibility #514

Open rucheleh opened 4 years ago

rucheleh commented 4 years ago

Expected Behavior

I'd like to be able to load an Excel file with hidden worksheets and have them stay hidden when I write the file back out again. Relatedly, the sheetVisibility function is not allowing me to hide worksheets. I have 8 worksheets and would expect to be able to hide 2 of them.

Actual Behavior

If I hide the sheets in the original Excel file, they become unhidden when I load the workbook. If I try to hide them using sheetVisibility, I get an warning saying "Warning message: In value != exState : longer object length is not a multiple of shorter object length." When I save the workbook and open it in Excel, the worksheet I tried to hide is completely gone (and I'm not using "veryHidden").
I think it might be related to the fact that the workbook has xml charts and there are cell references across different worksheets. If I don't write any data to the file, but rather load, set sheetVisibility, and save, I am able to use sheetVisibility successfully.

Steps to Reproduce the Problem

(please attach an example xlsx file if possible)

  1. wb <- loadWorkbook("~/R training/templates/template_production_sample.xlsx")

  2. Run code that writes data and cell formatting to the workbook (there are 8 worksheets total)

  3. sheetVisibility(wb)[7]<- "hidden" sheetVisibility(wb)[8]<- "hidden" Receive warning message

  1. saveWorkbook(wb, paste0("~/R training/templates/template_productiontest", i, ".xlsx"), overwrite = T)

  2. Open workbook in Excel and worksheets 7 and 8 are gone and all of the charts referencing data in those cells are blank.

template_production_sample.xlsx

sessionInfo()

ycphs commented 4 years ago

Hi,

That is the old repository for this R Package.

Please use the new one:

https://github.com/ycphs/openxlsx