skip_hidden_cols doesn't appear to work

[MMMcMahon]

Hi there - I'm using Openxlsx2 (1.2, )Windows 10, R 4.3.1 and as indicated in the title, I don't find that the parameter "skip_hidden_cols" works in any of wb_to_df(), wb_read() or read_xlsx().

To test this, I simply hid a column and row in an excel file, and then ran each of the the various "file to df" functions twice - once skipping and once retaining the hidden rows and columns:

wb_to_df_all <- openxlsx2::wb_to_df(file = file, sheet = 1, skip_hidden_rows = FALSE, skip_hidden_cols = FALSE)
wb_to_df_visible <- openxlsx2::wb_to_df(file = file, sheet = 1, skip_hidden_rows = TRUE, skip_hidden_cols = TRUE)
  
read_xlsxall <- openxlsx2::read_xlsx(file = file, sheet = 1, skip_hidden_rows = FALSE, skip_hidden_cols = FALSE)
read_xlsx_visible <- openxlsx2::read_xlsx(file = file, sheet = 1, skip_hidden_rows = TRUE, skip_hidden_cols = TRUE)
  
wb_read_all <- openxlsx2::wb_read(file = file, sheet = 1, skip_hidden_rows = FALSE, skip_hidden_cols = FALSE)
wb_read_visible <- openxlsx2::wb_read(file = file, sheet = 1, skip_hidden_rows = TRUE, skip_hidden_cols = TRUE)

In each case, the functions respected the command for the hidden rows, but all columns were always returned - hidden or not.

Just thought you'd want to know. :)

[JanMarvin]

Hi @MMMcMahon , I cannot reproduce this atm. I hid column 2 of a 3 column dataframe and everything worked as expected. What kind of spreadsheet software and in which version do you use? Can you send me an example or attach it to this discussion?

[JanMarvin]

Ah, there seems to be an issue, when the package is not loaded

fl <- "~/Downloads/openxlsx2Example.xlsx"

df1 <- openxlsx2::wb_to_df(fl)
df2 <- openxlsx2::wb_to_df(fl, skip_hidden_rows = TRUE)
df3 <- openxlsx2::wb_to_df(fl, skip_hidden_cols = TRUE)
df4 <- openxlsx2::wb_to_df(fl, skip_hidden_rows = TRUE, skip_hidden_cols = TRUE)

dim(df1) # default
#> [1]  9 13
dim(df2) # skip hidden rows
#> [1]  8 13
dim(df3) # skip hidden cols
#> [1]  9 13
dim(df4) # skip hidden rows + skip hidden cols
#> [1]  8 13

[MMMcMahon]

Success! I don't think it was a version thing (I originally had Openxlsx2 v1.2, but upgraded to 1.8 during this troubleshooting).

I think it's user error - I was just using read_xlsx(), whereas you did wb_load() followed by wb_to_df(). If that was documented somewhere, and I missed it - my apologies for wasting your time.

Thanks for your help - it's much appreciated!

> read_xlsx<-openxlsx2::read_xlsx(file = "openxlsx2Example.xlsx", sheet = 1, skip_hidden_cols = TRUE)
> wb_load <- openxlsx2::wb_load("openxlsx2Example.xlsx")
> wb_load <- openxlsx2::wb_to_df(wb_load, skip_hidden_cols = TRUE)

> dim(read_xlsx)
[1]  9 13
> dim(wb_load)
[1]  9 12

[JanMarvin]

I got it and have opened a PR in #1122

[JanMarvin]

Thanks, should be fixed now!

[JanMarvin]

I think it's user error - I was just using read_xlsx(), whereas you did wb_load() followed by wb_to_df(). If that was documented somewhere, and I missed it - my apologies for wasting your time.

No, it was definitely a bug. We use wb_load() internally, if a path is shipped to wb_to_df(), but we use it with data_only = TRUE. This should import only the parts of a (the selected) worksheet that are useful to construct the output data frame. Previously we excluded the column information, which is required to identify hidden columns. I moved it a few lines and now the column information is included and everything works as expected.
Until the next release you'd have to fallback to wb_to_df(wb_load(...), ...) when using skip_hidden_cols = TRUE.

Thanks for your help - it's much appreciated!

Thank you for reporting, that was hard to spot!