Formatting an entire excel workbook efficiently us

Question

Novice here on Stack Overflow so please bear with me - any help or advice gratefully appreciated!

I am trying to apply a single cell style to entire Excel workbooks that I have created using the write.xlsx function in R. I have looked online and the best I could come up with was the following approach.

Reformat<-function(filename){
  wb<-loadWorkbook(filename)
  sheets<-getSheets(wb)
  for (sheet in sheets){
    rows<-getRows(sheet)
    cells<-getCells(rows)
    cs <- CellStyle(wb) +
      Font(wb, heightInPoints=12,name="Calibri") +
      DataFormat("#,##0") +
      Alignment(h="ALIGN_CENTER")
    invisible(lapply(c(1:length(cells)), function(i) setCellStyle(cells[[i]], cs)))
  }
  saveWorkbook(wb,filename)
}

And then applying this function to each xlsx file I produced earlier.

However, this appears to be very computationally expensive, as I believe it is looping each and every cell in each row, and in each sheet of the workbook. This takes some time to run for some bigger Excel spreadsheets, and even some medium-sized ones (<1MB).

Is there a less computationally expensive way to achieve this? Say an equivalent of CellStyle that applies for a whole Sheet?

Thanks in advance - any input/advice appreciated!

Regards, Alch84

Answer 1

As mentioned, consider the RDCOMClient package to interface to the Excel object library with access to most of its methods and properties. Here, you can format cells all at once similar to selecting a region and using the Excel.exe GUI of Ribbon to format cells.

Below is a loop version iterating across all worksheets to modify formats accordingly. I show its counterpart in Excel VBA.

VBA Code (native interface, so no assignment of Excel.Application or constants like xlCenter)

Sub FormatCells()
    Dim i As Integer
    Dim rng As Range

    For i = 1 To ThisWorkbook.Worksheets.Count
        Set rng = ThisWorkbook.Worksheets(i).Range(ThisWorkbook.Worksheets(i).Cells.Address)

        rng.NumberFormat = "#,##0"
        rng.Font.Name = "Calibri"
        rng.HorizontalAlignment = xlCenter
    Next i

    ThisWorkbook.Close True
    Application.Quit

End Sub

R Code (foreign interface, so assignment of all objects needed)

library(RDCOMClient)

xlApp <- COMCreate("Excel.Application")
xlWbk <- xlApp$Workbooks()$Open("D:\\Freelance Work\\Scripts\\FormatXLCells.xlsx")

xlCenter <- -4108

for (i in 1:xlWbk$Worksheets()$Count()){
  xlWks <- xlWbk$Worksheets(i)

  rng <- xlWks$Range(xlWks$Cells()$Address())
  rng[['NumberFormat']] <- "#,##0"
  rng[['Font']][['Name']] <- "Calibri"
  rng[['Font']][['Color']] <- 1
  rng[['HorizontalAlignment']] <- xlCenter

}

xlWbk$Close(TRUE)                    # SAVE AND CLOSE WORKBOOK
xlApp$Quit()                         # CLOSE COM APP 

# FREE RESOURCES
xlWks <- xlWbk <- xlApp <- NULL
rm(rng, xlWks, xlWbk, xlApp)
gc()                                 # NEEDED TO EFFECTIVELY END EXCEL PROCESS