Search code examples
rxlsxxlsm

Why doesn't formatting for xlsm in R xlsx work?

So I am trying to load data and write it to an xlsm, but the formatting doesn't seem to work. It is writing rows with no borders (even though the template file has formatting), and I have tried locking the file to prevent editing. Nevertheless it won't write the formatting.

library(xlsx)
data = read.xlsx("my_data.xlsm", startRow=8)
pool_IDs = unique(data$Pay.pool.id)
for (i in pool_IDs) {
  temp = subset(data, Pay.pool.id==i)
  template = loadWorkBook('Template.xlsm')
  sheets = getSheets(template)
  sheet = sheets[[1]]
  cs1 = CellStyle(template) + Font(template)
  cs2 = CellStyle(template) + Font(template)
  cs3 = CellStyle(template) + Font(template) + Border()
  addDataFrame(temp, sheet, col.names=FALSE, row.names=FALSE, startRow=9,
               startColumn=1, colnamesStyle=cs2, rownamesStyle=cs1,
               colStyle=list('2'=cs2, '3'=cs2))
  print(paste("Processed pay pool", i)) 
  saveWorkbook(template, paste(i, ".xlsm", sep=''))
}

Has anyone seen this?

Solution

  • So cellStyles are not inherited by default from the workbook. You must explicitly define formatting:

    cs2 = CellStyle(template) + Font(template, color="blue")