wb_load() throws error if worksheets have "freeze panes" activated

[omertayyab]

Edit 01: Replaced file that has the SheetView elements present. Earlier upload didn't have these elements

wb_load is unable to create a workbook object if .xlsx file has frozen panes. Once those panes are unfrozen, the function works. The error thrown is:

Error in vapply(xml_nams, function(x) xml_node(sheetViews, "sheetViews",  : 
  values must be length 1,
 but FUN(X[[2]]) result is length 3

the sheetViews element wasn't present in the main xl/workbook.xml but it was present in the individual sheet .xml files. Once I deleted the elements from the .xml, the function started working. Removing can be done via directly editing the xml file or using Excel to unfreeze panes. Either way, the function starts to work.

The file I worked on has been attached
LFS_data_with_element.xlsx

I used the github version of the package to try this. Session info is below:

R version 4.2.2 (2022-10-31 ucrt)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 10 x64 (build 19044)

Matrix products: default

locale:
[1] LC_COLLATE=English_Canada.utf8  LC_CTYPE=English_Canada.utf8   
[3] LC_MONETARY=English_Canada.utf8 LC_NUMERIC=C                   
[5] LC_TIME=English_Canada.utf8    

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

other attached packages:
[1] openxlsx2_0.4.1.9000

loaded via a namespace (and not attached):
 [1] Rcpp_1.0.9        ps_1.7.1          prettyunits_1.1.1 crayon_1.5.1      withr_2.5.0      
 [6] rprojroot_2.0.3   R6_2.5.1          magrittr_2.0.3    zip_2.2.0         stringi_1.7.8    
[11] cli_3.3.0         curl_4.3.2        remotes_2.4.2     rstudioapi_0.14   callr_3.7.3      
[16] tools_4.2.2       compiler_4.2.2    processx_3.7.0    pkgbuild_1.3.1 

[JanMarvin]

Hi @omertayyab , I can not confirm this with openxlsx2 0.4.1. Do you use the current release? [Edit:] I assume you have uploaded the file after your repairs, could you see if you can create a file that causes the bug?

library(openxlsx2)
fl <- "~/Downloads/LFS_data.xlsx"
wb <- wb_load(fl)$save("/tmp/test.xlsx")
# xl_open("/tmp/test.xlsx")

[Edit2:] I assume the error is caused by multiple sheetViews entries, but from the documentation I was unable to guess how they are constructed.
[Edit3:] Okay, got it. freezed row and split view ...

[JanMarvin]

Hi @omertayyab , a potential fix is in #495. Could you please check if this works with your file? As explanation what is going on, if we split the sheetview with fixed cells and with a split workbook view we end up with something like:

<sheetView tabSelected="1" workbookViewId="0">
  <pane xSplit="7320" ySplit="1640"/>
  <selection pane="topRight"/>
  <selection pane="bottomLeft"/>
  <selection pane="bottomRight" activeCell="C5" sqref="C5"/>
</sheetView>

Our code looked for all elements in sheetView and started to extract them. Though this results in two errors. 1) The names are not unique and 2) our function expects the entire content as a single string. Not as four strings. This export is a bit borked for other reasons. If we create a freezePane ourselfs we will erase whatever we have in wb$worksheets[[sheet]]$freezePane and replace it with our strings.

Thanks for the report! I'll guess this will be merged into main this year, but a new release will follow sometime in Q1 2023.

[omertayyab]

Hey @JanMarvin, thanks for the guidance, I used the following command to re-install the package from the gh_issue_494 branch:
remotes::install_github("JanMarvin/openxlsx2@gh_issue_494")

This seems to have resolved the loading issue :)
I was able to load the file with the sheetViews elements present in it and then save it as a new file (to check if any formatting went away) and it all worked fine. You were right to point out that I mistakenly uploaded the .xlsx file which I had already edited to take the sheetViews elements out.
Thanks for the quick assist!