0

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

eipi10
  • 91,525
  • 24
  • 209
  • 285
Jacob
  • 406
  • 3
  • 19

1 Answers1

2

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")
eipi10
  • 91,525
  • 24
  • 209
  • 285
  • This worked beautifully! I have since decided to just create the workbook object though, since it also gives me more flexibility with formatting – Jacob Jan 09 '20 at 16:18