setCellStyle-使用XLConnect将单元格样式（百分比）应用于矩阵 [英] setCellStyle - apply cell style (percent) to matrix using XLConnect

问题描述

我的问题基于上一个问题使用Mirai的格式进行格式化XLConnect 。我无法从@joran实施此解决方案，我想我可能不是唯一遇到此问题的人。

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.

我想导出相关矩阵（10x10）以使其出色。

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)

此行遇到问题

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

我无法应用整个矩阵的样式。引起麻烦的是参数 row 和 col 。

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

结果如下图所示。

我现在尝试了无数种组合。非常感谢任何帮助

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

推荐答案

请执行以下操作：

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

这篇关于setCellStyle-使用XLConnect将单元格样式（百分比）应用于矩阵的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！