Opening an excel file with data validation corrupts the file

[g-hyo]

Expected Behavior

Open an xlsx file with data validation allowing list (other types of data validation may also trigger the issue)

Actual Behavior

Workbook becomes corrupted

Steps to Reproduce the Problem

1. Load an excel workbook that contains cells with any data validation

   Example: test_sheet.xlsx

   wb <- loadWorkbook(test_sheet.xlsx) openXL(wb)

sessionInfo()

R version 3.6.1 (2019-07-05) Platform: x86_64-w64-mingw32/x64 (64-bit) Running under: Windows 10 x64 (build 18363)

Matrix products: default

locale: [1] LC_COLLATE=English_Australia.1252 LC_CTYPE=English_Australia.1252 LC_MONETARY=English_Australia.1252 LC_NUMERIC=C
[5] LC_TIME=English_Australia.1252

attached base packages: [1] stats graphics grDevices utils datasets methods base

other attached packages: [1] openxlsx_4.1.4

loaded via a namespace (and not attached): [1] Rcpp_1.0.4 fansi_0.4.1 crayon_1.3.4 assertthat_0.2.1 commonmark_1.7 magrittr_1.5 zip_2.0.4 rlang_0.4.5
[9] stringi_1.4.6 cli_2.0.2 rstudioapi_0.11 fs_1.3.2 xml2_1.2.2 tools_3.6.1 srcproj_0.2.2 glue_1.3.2
[17] compiler_3.6.1 usethis_1.6.0

• Version of openxlsx: 4.1.4
• Version of R: 3.6.1

[mghoff]

Came here to make this exact issue post...

Load an existing workbook with a data validation list > overwrite some data in the workbook > save file with overwrite = TRUE > file is corrupted.

Tested with openXL(wb) as well as double clicking the file to open.

Looking forward to progress on this.

[mtarkoff]

Hi -- also getting an unable to open style error when using Data Validations. Specifically with Office365 version of Excel.

[ycphs]

Please check the current package repository ycphs/openxlsx.

[cedarmorton]

I seem to have the same or similar issue.

The spreadsheet I'm loading does have drop down lists in several worksheets. The error message when I try to open the Excel file is this: "The workbook cannot be opened or repaired by Microsoft Excel because it is corrupt." I have removed all comments and filters from the spreadsheet after reading a previous thread that these could be the issue. I also unfroze all panes just in case.

sessionInfo() R version 3.6.1 (2019-07-05) Platform: x86_64-apple-darwin15.6.0 (64-bit) Running under: macOS Catalina 10.15.6 Matrix products: default BLAS: /System/Library/Frameworks/Accelerate.framework/Versions/A/Frameworks/vecLib.framework/Versions/A/libBLAS.dylib LAPACK: /Library/Frameworks/R.framework/Versions/3.6/Resources/lib/libRlapack.dylib locale: [1] en_CA.UTF-8/en_CA.UTF-8/en_CA.UTF-8/C/en_CA.UTF-8/en_CA.UTF-8 attached base packages: [1] stats graphics grDevices utils datasets methods base
other attached packages: [1] openxlsx_4.2.2 RSQLite_2.1.2 loaded via a namespace (and not attached): [1] Rcpp_1.0.5 lattice_0.20-38 fansi_0.4.1 digest_0.6.25 crayon_1.3.4 assertthat_0.2.1 grid_3.6.1 DBI_1.0.0
[9] zip_2.1.1 stringi_1.4.5 rlang_0.4.7 cli_2.0.2 rstudioapi_0.11 blob_1.2.0 Matrix_1.2-17 vctrs_0.3.1
[17] tools_3.6.1 bit64_0.9-7 glue_1.4.1 bit_1.1-14 compiler_3.6.1 memoise_1.1.0

I did try to update the package per the instructions here https://github.com/ycphs/openxlsx but got a string of errors like this:

/Library/Developer/CommandLineTools/SDKs/MacOSX.sdk/usr/include/sys/resource.h:217:2: error: unknown type name 'uint64_t' uint64_t ri_resident_size; ^

Ending with this:

fatal error: too many errors emitted, stopping now [-ferror-limit=] 20 errors generated. make: *** [RcppExports.o] Error 1 ERROR: compilation failed for package ‘openxlsx’ removing ‘/Library/Frameworks/R.framework/Versions/3.6/Resources/library/openxlsx’ restoring previous ‘/Library/Frameworks/R.framework/Versions/3.6/Resources/library/openxlsx’ Error: Failed to install 'openxlsx' from GitHub: (converted from warning) installation of package ‘/var/folders/m3/vyl0mtld0cd7bqk5p53rrxw40000gp/T//RtmpNnkjic/file139335a2ccdd8/openxlsx_4.2.2.tar.gz’ had non-zero exit status