Preserve Existing Data Validation

[anneschwal]

Expected Behavior

I'm loading an excel workbook that has list type data validation set up in several columns, I'd expect that to be preserved when the file is loaded and then saved.

Actual Behavior

When I load and then re-save the file, the data validation from the original workbook is gone.

Steps to Reproduce the Problem

(please attach an example xlsx file if possible)

Blank Data Collection Template 2017-2018 vFINAL.xlsx

template <- loadWorkbook("Blank Data Collection Template 2017-2018 vFINAL.xlsx")
saveWorkbook(template, "sample template.xlsx", overwrite = TRUE)

• Version of openxlsx: 4.0.17
• Version of R: 3.4.0

Thank You!!

[awalker89]

The commit above fixes this - still testing to see if anything has broken as a result of the fix though.

[awalker89]

Please update with

devtools::install_github("awalker89/openxlsx")

and let me know if you still have issues.

[anneschwal]

Awesome, working great now - Thank you!

[anneschwal]

Hello, I'm having a similar issue again trying to re-run the same files with a few minor modifications. Now when I have drop downs in the excel template, the resulting excel file is not able to open. I'm getting this message from Excel: Replaced Part: /xl/worksheets/sheet1.xml part with XML error. Undeclared prefix. Line 1, column 1066942.

[HJAllen]

I can report similar. When opening a workbook with simple text entries, adding data to it, and previewing, I get the "We found a problem..." dialogue. Click yes to repair and get:
Removed Part: /xl/styles.xml part with XML error. (Styles) Undeclared prefix. Line 1, column 1541.
Repaired Records: Cell information from /xl/worksheets/sheet1.xml part
Used Range is A1:U14

dat <- data.frame(FldID = c(190022L, 190002L, 190011L, 190050L))
  rm(wb)
  xlF <- file.path(path$wd_docs,
                   paste0("TTEB_.xlsx"))
  wb <- openxlsx::loadWorkbook(xlF)
  
  openxlsx::writeData(wb, sheet = names(wb)[1], startRow = 10, startCol = 1, x = dat)
  openxlsx::openXL(wb)

- Session info ------------------------------------------------------------------------------------
 setting  value                       
 version  R version 3.5.3 (2019-03-11)
 os       Windows 10 x64              
 system   x86_64, mingw32             
 ui       RStudio                     
 language (EN)                        
 collate  English_United States.1252  
 ctype    English_United States.1252  
 tz       America/New_York            
 date     2019-05-14                  

- Packages ----------------------------------------------------------------------------------------
 package     * version    date       lib source                  
 assertthat    0.2.1      2019-03-21 [1] CRAN (R 3.5.3)          
 base64enc     0.1-3      2015-07-28 [1] CRAN (R 3.5.0)          
 bit           1.1-14     2018-05-29 [1] CRAN (R 3.5.0)          
 bit64         0.9-7      2017-05-08 [1] CRAN (R 3.5.0)          
 cli           1.1.0      2019-03-19 [1] CRAN (R 3.5.3)          
 crayon        1.3.4      2017-09-16 [1] CRAN (R 3.5.1)          
 data.table    1.12.2     2019-04-07 [1] CRAN (R 3.5.3)          
 DBI           1.0.0      2018-05-02 [1] CRAN (R 3.5.1)          
 dbplyr        1.3.0      2019-01-09 [1] CRAN (R 3.5.3)          
 digest        0.6.18     2018-10-10 [1] CRAN (R 3.5.3)          
 dplyr         0.8.0.1    2019-02-15 [1] CRAN (R 3.5.2)          
 evaluate      0.13       2019-02-12 [1] CRAN (R 3.5.3)          
 fansi         0.4.0      2018-10-05 [1] CRAN (R 3.5.2)          
 flextable     0.5.2      2019-04-02 [1] CRAN (R 3.5.3)          
 forcats       0.4.0      2019-02-17 [1] CRAN (R 3.5.3)          
 gdtools       0.1.8      2019-04-02 [1] CRAN (R 3.5.3)          
 gfuns       * 0.0.0.9000 2019-03-12 [1] local (HJAllen/gfuns@NA)
 glue          1.3.1      2019-03-12 [1] CRAN (R 3.5.3)          
 hms           0.4.2      2018-03-10 [1] CRAN (R 3.5.1)          
 htmltools     0.3.6      2017-04-28 [1] CRAN (R 3.5.1)          
 knitr         1.22       2019-03-08 [1] CRAN (R 3.5.3)          
 LHfuns      * 0.0.0.9000 2018-12-18 [1] local (@0.0.0.9)        
 lubridate     1.7.4      2018-04-11 [1] CRAN (R 3.5.1)          
 magrittr    * 1.5        2014-11-22 [1] CRAN (R 3.5.1)          
 officer       0.3.3      2019-03-01 [1] CRAN (R 3.5.2)          
 openxlsx    * 4.1.0      2018-05-26 [1] CRAN (R 3.5.3)          
 pander        0.6.3      2018-11-06 [1] CRAN (R 3.5.1)          
 pillar        1.3.1      2018-12-15 [1] CRAN (R 3.5.2)          
 pkgconfig     2.0.2      2018-08-16 [1] CRAN (R 3.5.1)          
 purrr         0.3.2      2019-03-15 [1] CRAN (R 3.5.3)          
 R6            2.4.0      2019-02-14 [1] CRAN (R 3.5.3)          
 Rcpp          1.0.1      2019-03-17 [1] CRAN (R 3.5.3)          
 readr         1.3.1      2018-12-21 [1] CRAN (R 3.5.3)          
 rlang         0.3.4      2019-04-07 [1] CRAN (R 3.5.3)          
 RMariaDB      1.0.6      2018-05-06 [1] CRAN (R 3.5.1)          
 rmarkdown     1.12       2019-03-14 [1] CRAN (R 3.5.3)          
 rstudioapi    0.10       2019-03-19 [1] CRAN (R 3.5.3)          
 sessioninfo   1.1.1      2018-11-05 [1] CRAN (R 3.5.3)          
 stringi       1.4.3      2019-03-12 [1] CRAN (R 3.5.3)          
 stringr       1.4.0      2019-02-10 [1] CRAN (R 3.5.3)          
 tibble        2.1.1      2019-03-16 [1] CRAN (R 3.5.3)          
 tidyr         0.8.3      2019-03-01 [1] CRAN (R 3.5.3)          
 tidyselect    0.2.5      2018-10-11 [1] CRAN (R 3.5.1)          
 utf8          1.1.4      2018-05-24 [1] CRAN (R 3.5.1)          
 uuid          0.1-2      2015-07-28 [1] CRAN (R 3.5.0)          
 withr         2.1.2      2018-03-15 [1] CRAN (R 3.5.1)          
 xfun          0.6        2019-04-02 [1] CRAN (R 3.5.3)          
 xml2          1.2.0      2018-01-24 [1] CRAN (R 3.5.1)          
 zip           2.0.1      2019-03-11 [1] CRAN (R 3.5.3)