使用Openxlsx在R中对整个工作表进行条件格式设置

huangapple go评论65阅读模式
英文:

Conditional format entire sheet using Openxlsx in R

问题

我有R中的数据,目前是645行94列。但为了可重现的数据,我们只需使用“iris”数据集。

我想将其另存为带有条件格式的Excel文件,其中包含“A”的每个单元格都会被突出显示。我知道我可以像这样做:

wb <- createWorkbook()
addWorksheet(wb, "Data")
       
writeData(wb, "Data", iris)
conditionalFormatting(wb, "Data", cols = 1:5, rows = 1:151, type = "contains", rule = "A")

saveWorkbook(wb, "iris.xlsx", overwrite = TRUE)

但我的问题是,我必须手动输入:

cols = 1:5, rows = 1:151

但如果我只想要每个单元格呢?也就是说,下次运行脚本时,我的数据可能是650x100,我不想一直更改这个文本。如何使其默认为整个数据集?

英文:

I have data in R thats currently 645 rows by 94 columns. But for a reproducible data lets just take the "iris" dataset.

I want to save that out as an excel with conditioning formatting where every cell that contains "A" gets highlighted. I know I can do something like this:

wb &lt;- createWorkbook()
addWorksheet(wb, &quot;Data&quot;)
       
writeData(wb, &quot;Data&quot;, iris)
conditionalFormatting(wb, &quot;Data&quot;, cols = 1:5, rows = 1:151, type = &quot;contains&quot;, rule = &quot;A&quot;)

saveWorkbook(wb, &quot;iris.xlsx&quot;, overwrite = TRUE)

But my question is, I had to manually write in:

cols = 1:5, rows = 1:151

But what if I just want it to be every cell? I.e. my data thats 645x94 might be 650x100 the next time I run the script, and I dont want to keep changing that text. How can I just make it default to the whole thing?

答案1

得分: 0

使用一些基本函数来获取数据的大小。

尝试:

wb <- createWorkbook()
addWorksheet(wb, "Data")
       
writeData(wb, "Data", iris)
col_num <- ncol(iris)
row_num <- nrow(iris)
conditionalFormatting(wb, "Data", cols = 1:col_num, rows = 1:row_num, type = "contains", rule = "A")

saveWorkbook(wb, "iris.xlsx", overwrite = TRUE)
英文:

Use some basic functions to get the size of your data.

Try:

wb &lt;- createWorkbook()
addWorksheet(wb, &quot;Data&quot;)
       
writeData(wb, &quot;Data&quot;, iris)
col_num &lt;- ncol(iris)
row_num &lt;- nrow(iris)
conditionalFormatting(wb, &quot;Data&quot;, cols = 1:col_num, rows = 1:row_num, type = &quot;contains&quot;, rule = &quot;A&quot;)

saveWorkbook(wb, &quot;iris.xlsx&quot;, overwrite = TRUE)

huangapple
  • 本文由 发表于 2023年3月4日 03:55:07
  • 转载请务必保留本文链接:https://go.coder-hub.com/75631368.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定