search

awalker89 / openxlsx

R package for .xlsx file reading and writing.
Other
365 stars 78 forks source link

Reading named regions fails, if there is a sheet name which is a substring of the name of the region #444

Open laubwolf opened 5 years ago

laubwolf commented 5 years ago

This issue is related to #340 but the solution to close it didn't fix all possible cases.

Steps to Reproduce the Problem

  1. Create a Excel workbook with two sheets names "xx" and "yyy"

  2. In sheet "xx" create a named region with the name "yyy_dn" and put some values inside.

  3. Try to read the region with: openxlsx::read.xlsx(filename, colNames = FALSE, namedRegion = "yyy_dn")

  4. You'll get a warning, that no data was found. This is because, it tries to read it from the sheet yyy!

Solution

Replace readWorkbook.R lines 188 to 190 with something like the following (untested!): sheet <- sub("!.*$", "", region)

sessionInfo()

> sessionInfo()
R version 3.4.2 (2017-09-28)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows >= 8 x64 (build 9200)

Matrix products: default

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

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

other attached packages:
[1] openxlsx_4.1.0

loaded via a namespace (and not attached):
[1] MASS_7.3-51.1  compiler_3.4.2 tools_3.4.2    yaml_2.2.0     Rcpp_1.0.0     zip_1.0.0