Openxlsx 条件格式化分隔列

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

Openxlsx Conditional Formatting separated columns

问题

I've noticed something unusual with the conditionalFormatting() function in the Openxlsx package. When you specify a vector of columns, such as c(2,4,6), the function doesn't highlight those three columns specifically, but rather all columns from #2-#6. Consider the code below:

df <- data.frame(One = c('Dog', 'Dog'),
Two = c('Cat', 'Cat'),
Three = c('Bird', 'Bird'),
Four = c('Cow', 'Cow'),
Five = c('Horse', 'Horse'),
Six = c('Lion', 'Lion'),
Seven = c('Tiger', 'Tiger'))

style1 <- createStyle(fontName = 'Calibri',
fontSize = 11,
bgFill = "#FFC7CE")

conditionalFormatting(wb,
sheet,
rows = 1:nrow(df),
cols = c(2,4,6), #<--- treated as all columns in range 2-6
type = 'contains',
rule = "A", #<---highlight all rows that contain text
style = style1)

However, if you were to use the addStyle() function, then it seems as if it would color those three columns specifically, rather than the entire range:

style2 <- createStyle(fontName = "Calibri",
fontSize = 11,
fgFill = "#FFC7CE")

addStyle(wb,
sheet,
rows = 1:nrow(df),
cols = c(2,4,6), #<---treated as columns 2,4,6
style = style2,
gridExpand = TRUE)

I've tried using a for loop to see if it tricks it into formatting them correctly, but it doesn't.

cols.to.format <- c(2,4,6)
for(col in cols.to.format){
conditionalFormatting(wb,
sheet,
rows = 1:nrow(df),
cols = col,
style = style2,
gridExpand = TRUE)
}

Is there a way to use conditional formatting to format a specific segment of columns rather than a range?

英文:

I've noticed something unusual with the conditionalFormatting() function in the Openxlsx package. When you specify a vector of columns, such as c(2,4,6), the function doesn't highlight those three columns specifically, but rather all columns from #2-#6. Consider the code below:

  1. df &lt;- data.frame(One = c(&#39;Dog&#39;, &#39;Dog&#39;),
  2.                  Two = c(&#39;Cat&#39;, &#39;Cat&#39;),
  3.                  Three = c(&#39;Bird&#39;, &#39;Bird&#39;), 
  4.                  Four = c(&#39;Cow&#39;, &#39;Cow&#39;),
  5.                  Five = c(&#39;Horse&#39;, &#39;Horse&#39;),
  6.                  Six = c(&#39;Lion&#39;, &#39;Lion&#39;),
  7.                  Seven = c(&#39;Tiger&#39;, &#39;Tiger&#39;))
  8.     
  9.     style1 &lt;- createStyle(fontName = &#39;Calibri&#39;, 
  10.     fontSize = 11,
  11.     bgFill = &quot;#FFC7CE&quot;)
  12.     
  13.     conditionalFormatting(wb, 
  14.     sheet, 
  15.     rows = 1:nrow(df),
  16.     cols = c(2,4,6), #&lt;--- treated as all columns in range 2-6
  17.     type = &#39;contains&#39;,
  18.     rule = &quot;A&quot;, #&lt;---highlight all rows that contain text 
  19.     style = style1)

However, if you were to use the addStyle() function, then it seems as if it would color those three columns specifically, rather than the entire range:

  1. style2 &lt;- createStyle(fontName = &quot;Calibri&quot;, 
  2. fontSize = 11,
  3. fgFill = &quot;#FFC7CE&quot;)
  5. addStyle(wb, 
  6. sheet, 
  7. rows = 1:nrow(df),
  8. cols = c(2,4,6), #&lt;---treated as columns 2,4,6
  9. style = style2,
  10. gridExpand = TRUE)

I've tried using a for loop to see if it tricks it into formatting them correctly, but it doesn't.

  1. cols.to.format &lt;- c(2,4,6)
  2. for(col in cols.to.format){
  3. conditionalFormatting(wb, 
  4. sheet,
  5. rows = 1:nrow(df),
  6. cols = col,
  7. style = style2, 
  8. gridExpand = TRUE)
  9. }

Is there a way to use conditional formatting to format a specific segment of columns rather than a range?

答案1

得分: 1

Looping should work. conditionalFormatting in the open xml standard allows either a single cell or a cell range, but nothing like "A1:A2;C1:C2" and this is what openxlsx tries to honor. The only issue here is, that a warning could be shown to the user.

For reference this is the loop in openxlsx2:

  1. library(openxlsx2)
  3. df <- data.frame(
  4.   One   = c('Dog', 'Dog'),
  5.   Two   = c('Cat', 'Cat'),
  6.   Three = c('Bird', 'Bird'), 
  7.   Four  = c('Cow', 'Cow'),
  8.   Five  = c('Horse', 'Horse'),
  9.   Six   = c('Lion', 'Lion'),
  10.   Seven = c('Tiger', 'Tiger')
  11. )
  13. wb <- wb_workbook()$add_worksheet()$add_data(x = df)
  15. cols.to.format <- c(2,4,6)
  16. for(col in cols.to.format){
  17.   wb$add_conditional_formatting(
  18.     rows = 1:nrow(df),
  19.     cols = col,
  20.     type = 'containsText',
  21.     rule = "A")
  22. }
  24. if (interactive()) wb$open()

Openxlsx 条件格式化分隔列

Edit: What OP really wanted:

  1. library(openxlsx2)
  3. df <- data.frame(
  4.   One   = c('Dog', 'Dog'),
  5.   Two   = c('Cat', 'Cat'),
  6.   Three = c('Bird', 'Bird'), 
  7.   Four  = c('Cow', 'Cow'),
  8.   Five  = c('Horse', 'Horse'),
  9.   Six   = c('Lion', 'Lion'),
  10.   Seven = c('Tiger', 'Tiger')
  11. )
  13. wb <- wb_workbook()$
  14.   add_worksheet()$
  15.   add_data(x = df)
  17. cols.to.format <- c(4,6)
  18. for(col in cols.to.format){
  19.   wb$add_conditional_formatting(
  20.     rows = 1:nrow(df) + 1L,
  21.     cols = col,
  22.     rule = 'B2="Cat"')
  23. }
  25. if (interactive()) wb$open()

Openxlsx 条件格式化分隔列

英文:

Looping should work. conditionalFormatting in the open xml standard allows either a single cell or a cell range, but nothing like "A1:A2;C1:C2" and this is what openxlsx tries to honor. The only issue here is, that a warning could be shown to the user.

For reference this is the loop in openxlsx2:

  1. library(openxlsx2)
  3. df &lt;- data.frame(
  4.   One   = c(&#39;Dog&#39;, &#39;Dog&#39;),
  5.   Two   = c(&#39;Cat&#39;, &#39;Cat&#39;),
  6.   Three = c(&#39;Bird&#39;, &#39;Bird&#39;), 
  7.   Four  = c(&#39;Cow&#39;, &#39;Cow&#39;),
  8.   Five  = c(&#39;Horse&#39;, &#39;Horse&#39;),
  9.   Six   = c(&#39;Lion&#39;, &#39;Lion&#39;),
  10.   Seven = c(&#39;Tiger&#39;, &#39;Tiger&#39;)
  11. )
  13. wb &lt;- wb_workbook()$add_worksheet()$add_data(x = df)
  15. cols.to.format &lt;- c(2,4,6)
  16. for(col in cols.to.format){
  17.   wb$add_conditional_formatting(
  18.     rows = 1:nrow(df),
  19.     cols = col,
  20.     type = &#39;containsText&#39;,
  21.     rule = &quot;A&quot;)
  22. }
  24. if (interactive()) wb$open()

Openxlsx 条件格式化分隔列

Edit: What OP really wanted:

  1. library(openxlsx2)
  3. df &lt;- data.frame(
  4.   One   = c(&#39;Dog&#39;, &#39;Dog&#39;),
  5.   Two   = c(&#39;Cat&#39;, &#39;Cat&#39;),
  6.   Three = c(&#39;Bird&#39;, &#39;Bird&#39;), 
  7.   Four  = c(&#39;Cow&#39;, &#39;Cow&#39;),
  8.   Five  = c(&#39;Horse&#39;, &#39;Horse&#39;),
  9.   Six   = c(&#39;Lion&#39;, &#39;Lion&#39;),
  10.   Seven = c(&#39;Tiger&#39;, &#39;Tiger&#39;)
  11. )
  13. wb &lt;- wb_workbook()$
  14.   add_worksheet()$
  15.   add_data(x = df)
  17. cols.to.format &lt;- c(4,6)
  18. for(col in cols.to.format){
  19.   wb$add_conditional_formatting(
  20.     rows = 1:nrow(df) + 1L,
  21.     cols = col,
  22.     rule = &#39;B2=&quot;Cat&quot;&#39;)
  23. }
  25. if (interactive()) wb$open()

Openxlsx 条件格式化分隔列

huangapple
  • 本文由 发表于 2023年6月8日 17:05:21
  • 转载请务必保留本文链接：https://go.coder-hub.com/76430258.html
  • conditional-formatting
  • openxlsx
  • r
匿名

发表评论

匿名网友

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

确定