Formatting an entire excel workbook efficiently using the R xlsx package

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

If you can use other libraries maybe `addStyle` from the `openxlsx` package helps. It has the option `gridExpand` which lets you apply Styles to whole rectangles. It doen't have the ability to create named styles though. — snaut, Aug 10 '16 at 11:53
Any input/advice appreciated? Well then, do you have Excel.exe installed as VBA can style whole worksheet cells in one call with the [Range.Cells](https://msdn.microsoft.com/en-us/library/office/ff196273.aspx) property? But don't worry, if you have PC, the [rdcomclient](http://www.omegahat.net/RDCOMClient/) package can have R write the VBA! Never once coding in Excel but can access most of Excel object library! — Parfait, Aug 10 '16 at 12:12
Thanks snaut, will look into it. Trying my best to keep package installations to a minimum as it is a script that may have to be run on other PCs in future. But if it's much more computationally efficient then I'll probably use this. Thanks @Parfait, I did consider writing a VBA code in Excel but wanted to keep everything self-contained within R. Will look into the rdcomclient package, hadn't heard of it previously. Thanks — Alvo, Aug 10 '16 at 13:00

score 1 · Answer 1 · answered Aug 10 '16 at 21:56

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

Thanks @Parfait! Elegant solution. I ran a variation of [this](http://stackoverflow.com/questions/19404270/run-vba-script-from-r) in the end as I now had to replicate this for several files across a number of sub-folders. But I will keep Rdcomclient in mind for next time, as it keeps everything within the same R session, which I like. Much appreciated. — Alvo, Aug 11 '16 at 14:38

Formatting an entire excel workbook efficiently using the R xlsx package

1 Answers1

Linked