2

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?

mlanier
  • 167
  • 2
  • 3
  • 14
  • There was a typo. Should be temp, which is the data in addDataFrame – mlanier Oct 18 '19 at 17:40
  • 1
    (My edits are a suggestion only, not a criticism ... but I believe readability has an impact on getting readers to look at your code, and while the details of indentation are a personal thing (2 vs 4, space vs tab, imposed such as python), I think unindented and unspaced code can be very difficult to see where things break out. If you don't like it, feel free to rollback the edits.) – r2evans Oct 18 '19 at 17:41
  • Thank you r2evens, I was trying to tab over in the code formatter but it kept kicking me out of the code writer. shift + tab wasn't working either. – mlanier Oct 18 '19 at 17:43
  • Yes, I've run into that as well. I'm not typically "writing" code in the question, though: I use my R environment (emacs/ess for me, RStudio for many) to write and test-run code so that (1) indentation, highlighting, code-expansion works as usual, reducing typos; and (2) I know the code is minimal and self-contained without distractions of errors that have nothing to do with the question. It's a technique, granted, but I've seen way too many questions missing parens, commas, etc, that are only introduced within the SO editor but are very distracting nonetheless. – r2evans Oct 18 '19 at 18:19

1 Answers1

0

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

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

mlanier
  • 167
  • 2
  • 3
  • 14