rexcelopenxlsx

Relatively call up all columns in a worksheet using setColWidths() for auto-sizing

I'm preparing an Excel workbook using the openxlsx package, and I want to use setColWidths() to auto-resized the columns in each sheet. Since I have multiple sheets and I want my code to be relative, I need to write the cols= argument to call on all columns in the sheet for autosizing. However, it appears that I can only use specific indices to fill that argument, and it appears that argument is required for the function.

Now, I can certainly count how many columns I have in one sheet and call that specific index value, but since this project involves many sheets, that quickly becomes tedious and the code will not be adaptable to different sheets. Is there a relative way to call up all columns in a sheet within setColWidths()?

Below is my dummy data for reference (just one sheet in this example). This dummy data will produce the desired output, but the cols= argument isn't relative as written here. Is there a relative way to call all columns in the sheet?

library(tidyverse)
library(readxl)
library(writexl)
library(openxlsx)

#### Sample Data ####

month_2024<-structure(list(`CAL MONTH` = c("Jan 2024", "Feb 2024", "Mar 2024", 
"Apr 2024", "May 2024", "Jun 2024", "Jul 2024", "Aug 2024", "Sep 2024", 
"Oct 2024", "Total"), `Total Number of Interactions` = c(17L, 
11L, 18L, 9L, 15L, 29L, 28L, 16L, 10L, 13L, 166L)), row.names = c(NA, 
-11L), class = "data.frame")

####
                       
output<-createWorkbook()

addWorksheet(output,"2024 Analysis")

writeData(output, "2024 Analysis", `month_2024`, 
          startCol = 1, 
          startRow = 1, 
          colNames = TRUE, 
          rowNames = FALSE, 
          keepNA = TRUE)

setHeaderFooter(output, 
                sheet = "2024 Analysis", 
                header = c(NA, "2024 Analysis", NA), 
                footer = c("Data Window: January 1st, 2024 - &[Date] unless otherwise stated", NA, NA))

setColWidths(output, "2024 Analysis", cols = 1:2, widths="auto")

saveWorkbook(output, "Monthly Reporting.xlsx", overwrite = TRUE)
Solution

  • As indicated by the conversation in the comments, OP is writing data from R to Excel. Therefore, the solution to their problem is to write

    setColWidths(output, "2024 Analysis", cols = 1:ncol(df), widths="auto")

    where df is the data.frame whose contents is being written to the current sheet.