openxlsx
openxlsx copied to clipboard
awalker89
Preserve Existing Data Validation
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!!
The commit above fixes this - still testing to see if anything has broken as a result of the fix though.
Please update with
devtools::install_github("awalker89/openxlsx")
and let me know if you still have issues.
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.
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)
