r - openxlsx - 根据另一列中的文本填充行行

Question

我试图用 openxlsx 在 Excel 工作簿中填充一行基于该行的列中的文本。例如，如果单元格 C3包含 A然后突出显示第 3 行。

理想情况下，这不会是 conditionalFormatting .我希望它是基于单元格中文本的行的实际颜色，但是 conditionalFormatting似乎是唯一的选择。如果我能够使它成为实际颜色，请告诉我。

下面的代码显示您可以根据 value 突出显示一行该行中的单元格，但如果您尝试使用文本，则不会。另外，如果type设置为 contains那么它似乎不接受任何样式。

wb <- createWorkbook()

addWorksheet(wb, "Dependent on")
addWorksheet(wb, "containsText")


negStyle <- createStyle(fontColour = "#9C0006", bgFill = "#FFC7CE")
posStyle <- createStyle(fontColour = "#006100", bgFill = "#C6EFCE")


## highlight row based on value in column 2
writeData(wb, "Dependent on", data.frame(x = 1:10, y = runif(10)), startRow = 15)
conditionalFormatting(wb, "Dependent on", cols=1:2, rows=16:25, rule="$B16<0.5", style = negStyle)
conditionalFormatting(wb, "Dependent on", cols=1:2, rows=16:25, rule="$B16>=0.5", style = posStyle)


## cells containing text
fn <- function(x) paste(sample(LETTERS, 10), collapse = "-")
writeData(wb, "containsText", data.frame(letters = sapply(1:10, fn), numbers = 1:10))
conditionalFormatting(wb, "containsText", cols = 1:2, rows = 1:10, type = "contains", rule = "A")


openXL(wb) ## opens a temp version

Answer 1

这是你期待的结果吗？

library(openxlsx)


fn <- function(x) paste(sample(LETTERS, 10), collapse = "-")
a <- data.frame(letters = sapply(1:10, fn), numbers = 1:10)

wb <- createWorkbook()

addWorksheet(wb, "containsText")
writeData(wb = wb, sheet = "containsText", x = a)
color <- createStyle(fgFill = "#00CCFF") #BLUE
addStyle(wb = wb, sheet = "containsText", style = color, rows = which(grepl(a$letters, pattern = "A")) + 1, cols = 1)
saveWorkbook(wb = wb, file = "test_color.xlsx", overwrite = TRUE)