Openxlsx 多次验证损坏输出文件

我正在尝试添加多个验证并将公式添加到 Excel 文件。这是我使用的代码：

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)

如果我只使用一个数据验证，它会打开正常，如果超过一个，它会抱怨文件已损坏......

不幸的是，这看起来像是在当一张表上有多个数据时，数据验证类型“列表”失败 #266 https://github.com/awalker89/openxlsx/issues/266.

幸运的是，有一个拉取请求试图解决此问题 https://github.com/tkunstek/openxlsx/commit/26a6f6013b955c054a6e5c05577a28dec917f794. Using devtools::dev_mode() https://www.rdocumentation.org/packages/devtools/versions/1.13.3/topics/dev_mode，您可以安装tkunstek/openxlsx https://github.com/tkunstek/openxlsx版本，无需删除并重新安装 CRAN 版本openxlsx https://cran.r-project.org/web/packages/openxlsx/openxlsx.pdf.

# 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

答案来自如何安装 R 包的开发版本 github 存储库 https://stackoverflow.com/questions/9656016/how-to-install-development-version-of-r-packages-github-repository.