Search code examples
rrowcolxlconnectrep

setCellStyle - apply cell style (percent) to matrix using XLConnect

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.

Output xls

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

Solution

  • 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)