setCellStyle - apply cell style (percent) to matri

问题:

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

回答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)