Hiding Columns in Excel Workbooks using openxlsx

Question

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.

Schaefer

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.. – Structure

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. – Schaefer

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).. – Structure

Answer 1

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

Philippic

@arun Vinoth, what about rows? How to hide rows? – Penology

Answer 2

I discovered an accidental backdoor by using NA

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

Littrell

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 – Pub

Brilliant! "Backdoor" or "Undocumented" - it works! – Implied

Answer 3

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)

Brillatsavarin