Openxlsx multiple validations corrupts output file

Question

I'm trying to add multiple validations and add formulas to an excel file. Here is the code I use:

library(openxlsx)
fileTemplate <- 'New01.xlsx'
wbTemplate <- loadWorkbook(fileTemplate)
addWorksheet(wbTemplate, "Sheet1")
writeData(wbTemplate, "Sheet1", dataset)
len <- NROW(dataset)
dataValidation(wbTemplate, 2, col = 2, rows = 2:len, type = "list", value = "'Data Validation'!$A$2:$A$19")
dataValidation(wbTemplate, 2, col = 3, rows = 2:len, type = "list", value = "'Data Validation'!$B$2:$B$501")
dataValidation(wbTemplate, 2, col = 5, rows = 2:len, type = "list", value = "'Data Validation'!$C$2:$C$6")
openXL(wbTemplate)

if I just use one dataValidation it opens okey, if more than one it complains about the file being corrupt...

There's a developmental version of `openxlsx` that solves this issue. See below for how you import it into [tag:r]. — Cristian E. Nuno, Feb 21 '18 at 15:55

Cristian E. Nuno · Accepted Answer · 2018-02-20T03:17:55.287

2

Unfortunately, this looks like a bug found in data validation type 'list' fails when there are more than one on a sheet #266.

Fortunately, there's a pull request that attempts to fix this issue. Using devtools::dev_mode(), you can install the tkunstek/openxlsx version without having to remove and reinstall the CRAN version of openxlsx.

# install the devtools package
install.packages( pkgs = "devtools" )

# load necessary packages
library( devtools )

# create a new library for storing installed packages.
dev_mode(on = TRUE )

# download the PR request that fixes
# the dataValidation error
install_github( repo = "tkunstek/openxlsx" )

# load the library
library( openxlsx )

# create workbook
wb <- createWorkbook()

# initialize worksheet
addWorksheet( wb = wb, sheetName = "Sheet1" )

# add iris to Sheet1
writeData( wb = wb
           , sheet = "Sheet1"
           , x = iris
)

# add Excel data validation to cells
dataValidation( wb = wb
                , sheet = "Sheet1"
                , cols = 1:4
                , rows = 2:( 1 + nrow( iris ) )
                , type = "decimal"
                , operator = "between"
                , value = c( 0, 10 )
)
dataValidation( wb = wb
                , sheet = "Sheet1"
                , cols = 5
                , rows = 2:( 1 + nrow( iris ) )
                , type = "textLength"
                , operator = "lessThanOrEqual"
                , value = 10
                )

# view the data in Excel
# notice that the file is no longer corrupt
openXL( file = wb )

# turn off dev_mode
dev_mode( on = FALSE )  # return to CRAN version of openxlsx

# end of script

Answer comes from How to install development version of R packages github repository.

edited Feb 20 '18 at 03:17

answered Feb 18 '18 at 23:21

Cristian E. Nuno

2,822
2
19
33

Thanks for the solution. I'm trying it now, it complains: d> install_github( repo = "tkunstek/openxlsx" ) Downloading GitHub repo tkunstek/openxlsx@master from URL https://api.github.com/repos/tkunstek/openxlsx/zipball/master Installation failed: Could not find build tools necessary to build openxlsx d> .... The suggested solution it seems is to install Rtools, but it is not applicable to me as I'm on Mac... – user1298416 Feb 22 '18 at 03:58
Ok, after installing fortran and xcode-select --install I was able to install_github( repo = "tkunstek/openxlsx" ). But now I have this error: d> dataValidation(wbTemplate, 2, col = 2, rows = 2:len, type = "list", value = "'Data Validation'!$A$2:$A$19") Error in .Call("openxlsx_convert_to_excel_ref", PACKAGE = "openxlsx", : "openxlsx_convert_to_excel_ref" not available for .Call() for package "openxlsx" – user1298416 Feb 22 '18 at 06:08
@user1298416 I see in the your `value` argument you're explicitly typing "'Data Validation'$A$2:$A$19". According to [the help file](https://cran.r-project.org/web/packages/openxlsx/openxlsx.pdf), `type = "list"` requires an explicit reference to the sheet name supplied in the `sheet` argument inside of the `value` argument. Try this instead: `value = "'Sheet1'$A$2:$A$19"`. – Cristian E. Nuno Feb 22 '18 at 14:14
@user1298416 I didn't know that from your OP, thank you for clarifying. I don't know how to solve that error. – Cristian E. Nuno Feb 22 '18 at 17:26
1

what ended up working - first, I ran regular openxlsx to do modifications to my file, output and saved the file, then used aspiringurbandatascientist's suggested code (opened the file, did validations, saved the file). – user1298416 Feb 23 '18 at 02:51
Any News when this will be fixed in the CRAN Version? – Max M Apr 17 '19 at 11:45
@MaxM not that I'm aware of seeing as how the issue is over 2 years old. Hope the maintainers get back to you on GitHub! – Cristian E. Nuno Apr 21 '19 at 02:26
1

Used `install_github("ycphs/openxlsx")` and it actually worked for me! Cell referencing is weird, it works for me in the following two formats: `sheet!$A$1:$A$2` and `'sheet'!$A$1:$A$2` – MokeEire Feb 27 '20 at 00:19

Openxlsx multiple validations corrupts output file

1 Answers1

Linked