setCellStyle - apply cell style (percent) to matri

Question

My question is based on an issue mentioned in a previous question Formatting of numbers using Mirai's XLConnect. I have trouble implementing this solution from @joran and I think I might not be the only person with this problem.

I want to export a correlation matrix(10x10) to excel. It is saved as a matrix called export.

library(XLConnect)
wb <- loadWorkbook(paste0("corr_test.xlsx"), create = TRUE)
prcntg <- createCellStyle(wb)
setDataFormat(prcntg, format = "0.00%")
createSheet(wb, name="corr")
writeWorksheet(wb,export,"corr",startRow = 2, startCol = 1, header = TRUE)
setColumnWidth(wb, sheet = "corr", column = 1:30, width = -1)
setCellStyle(wb, sheet = "corr", row= rep(3:12,times=10), col = rep(1:10,     
+times=12), cellstyle = prcntg)
saveWorkbook(wb)

I have trouble with this line

setCellStyle(wb, sheet = "corr", row= rep(3:12,times=10), col = rep(1:10,     
+times=12), cellstyle = prcntg)

I don't manage to apply the style to the whole matrix. It is about the arguments row and col that cause trouble.

The result looks like the picture below.

I've tried an endless number of combinations now. Any help is highly appreciated

Answer 1

The following should do it:

rc = expand.grid(row = 3:12, col = 1:10)
setCellStyle(wb, sheet = "corr", row= rc$row, col = rc$col, cellstyle = prcntg)