I have an excel document with 15 sheets that I update monthly. Currently, I have been reading in the document and manipulating it as follows
sheetNames <- openxlsx::getSheetNames("myDoc.xlsx")
oldData <- lapply(sheetNames, function(x) openxlsx::readWorkbook("YouTube Search Results.xlsx",
sheet=x, skipEmptyRows = FALSE))
names(oldData) <- sheetNames
finalDF <- Map(bind_rows, oldData, newData)
openxlsx::write.xlsx(finalDF, "YouTube Search Results.xlsx", overwrite = TRUE)
This works well for me being able to update each sheet according to the elements of list newData.
My problem is that I would like to resize ALL columns on ALL sheets. I currently can't do this because I'm not creating a workbook object in oldData, and setColWidths() requires a workbook object. Do I have to change how I read in my data, or is there an alternative to setting column widths without doing so?
I have seen responses such as R - Autofit Excel column width, but they don't refer to doing it to multiple sheets
For complete control over output formatting, you would need to create a workbook object. However, for basic formatting, write.xlsx allows you to pass some additional formatting parameters, including auto-resizing column widths to fit the width of the data (see the help for details). You just need to add colWidths="auto" to write.xlsx. Here's a reproducible example adapting your code:
library(openxlsx)
library(tidyverse)
# Create sample "old" and "new" Excel files
dl = list(mtcars=mtcars, iris=iris)
write.xlsx(dl, "myDoc.xlsx")
write.xlsx(dl, "YouTube Search Results.xlsx")
# Load old data add new data, and write to a new file
sheetNames <- getSheetNames("myDoc.xlsx")
oldData <- lapply(sheetNames, function(x) readWorkbook("YouTube Search Results.xlsx",
sheet=x, skipEmptyRows = FALSE))
newData <- lapply(sheetNames, function(x) readWorkbook("myDoc.xlsx",
sheet=x, skipEmptyRows = FALSE))
finalDF <- Map(bind_rows, oldData, newData)
write.xlsx(finalDF, "YouTube Search Results Updated.xlsx", colWidths="auto")