7

I'm trying to hide columns in an Excel workbook I'm creating using the openxlsx package in R, but I'm not having any luck. I could make a pre-formatted Excel workbook and write data to it, but the number of columns that need to be hidden is variable. I've tried using:

 setColWidths(wb, sheet = "Sheet 1", cols = col_list, widths = 0)

where "col_list" is the list of columns that need to be hidden. When I open the saved workbook, the column widths are actually 0.38. If anybody has any ideas on how I might go about this, it would be much appreciated.

lmo
  • 37,904
  • 9
  • 56
  • 69
sehock
  • 349
  • 5
  • 16
  • I might be wrong but I think if you tried to modify an excel workbook with R, it woudl make more sense for me to use VBA instead.. – timat Jan 31 '17 at 15:37
  • That's the thing though. I'm not actually modifying an existing Excel workbook. I'm creating one in R because all of the data that is being written to it is being processed in R, and thus the formatting for the workbook has to be dynamic. – sehock Jan 31 '17 at 15:43
  • Looking at the `openxlsx` doc it seems not possible, but you can put you're hidden column in another sheet, and you can hide this sheet using `sheetVisibility(wb)`.. – timat Jan 31 '17 at 15:57

3 Answers3

8

The documentation shows the fields for the formula as below:

setColWidths(wb, sheet, cols, widths = 8.43, hidden = rep(FALSE, length(cols)), ignoreMergedCells = FALSE)

So setting hidden = rep(TRUE, length(cols)) should hide your columns

Arun Vinoth-Precog Tech - MVP
  • 22,364
  • 14
  • 59
  • 168
Christian
  • 81
  • 1
  • 3
0

Documentation shows groupColumns() has an option for hidden. It worked better for me.

The man page notes that it conflicts with the parameter in setColWidths(). (I think conflicts with is too strong. It's just duplicate).

https://rdrr.io/cran/openxlsx/man/groupColumns.html

To @Sean in comments, you can hide rows with groupRows(hidden = TRUE).

groupRows(wb, sheet = "Sheet 1", rows = 6:12, hidden = TRUE)

Shane D
  • 101
0

I discovered an accidental backdoor by using NA

setColWidths(wb, sheet = "Sheet 1", cols = col_list, widths = NA)
Fevertree
  • 1
  • 1
    Hi Fevertree, thanks for answering. Just for the sake of precision, 'backdoor' is generally used to describe a security vulnerability. Are you instead referring to an undocumented function behaviour? A little bit more detail might be helpful here – Captain Hat Aug 30 '23 at 09:26