CODEWITHSUNDEEP
ropenxlsx
alexb523
alexb523

Reputation: 728

openxlsx - fill row row based on text in another column

I am trying to fill a row in an excel workbook with openxlsx based on text in a column from that row. For example, if cell C3 contains A then highlight row 3.

Ideally, this would not be conditionalFormatting. I would like it be the actual color of the row based on the text in a cell, but conditionalFormatting seems to be the only option. If I am able to make it the actual color, please let me know.

The code below shows that you can highlight a row based on the value of cell in that row, but not if you are trying to use text. In addition, if the type is set to contains then it does not seem to be accepting any styles.

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

Upvotes: 5

Views: 2858

Answers (2)

jimjamslam
jimjamslam

Reputation: 2067

It seems like you're struggling with the fact that you want to apply conditional formatting to a column based on a value in a different column (which is a documented feature when type = "expression" but not when type = "contains") but want that value to be text, not numeric.

Thankfully, though, the expressions allowed by type = "expression" can include text values! This won't work if you actually do need to conditionally highlight based on another column that contains text, but it will if you only need the other column to exactly match text.

library(tidyverse)
library(openxlsx)

# prepare test data
test_data <-
  mtcars %>%
  rownames_to_column() %>%
  as_tibble() %>%
  select(rowname, cyl) %>%
  mutate(first_letter = substr(rowname, 1, 1))

# load the data into a few worksheets
test_wb <- createWorkbook()
addWorksheet(test_wb, 'by_number')
addWorksheet(test_wb, 'by_letter')
addWorksheet(test_wb, 'by_word')
writeData(test_wb, 'by_number', test_data)
writeData(test_wb, 'by_letter', test_data)
writeData(test_wb, 'by_word', test_data)

# create the test style
flagged_style <- createStyle(
  fontColour = '#274e13', bgFill = '#b6d7a8')
all_rows <- 2:(nrow(test_data) + 1)

Test data based on the mtcars dataset

Here are three examples:

  1. Conditionally formatting rows based on the value of cyl (inequality)
  2. Conditionally formatting rows based on the value of first_letter (equality)
  3. Conditionally formatting rows based on the value of rowname (equality)
# apply formatting...

# ... by numeric value (inqueality)
conditionalFormatting(test_wb, 'by_number',
  cols = 1:3, rows = all_rows, rule = '$B2<=6',
  style = flagged_style)

# ... by text equality (letter)
conditionalFormatting(test_wb, 'by_letter',
  cols = 1:3, rows = all_rows, rule = '$C2=="M"',
  style = flagged_style)

# ... by text equality (word)
conditionalFormatting(test_wb, 'by_word',
  cols = 1:3, rows = all_rows, rule = '$A2=="Merc 230"',
  style = flagged_style)

saveWorkbook(test_wb, 'test.xlsx')

Here're the results (I've pasted them next to each other):

enter image description here

If you do need a "contains" rule, rather than strict equality, you might need to use a dummy column. I don't think you'll be able to delete it after applying the formatting, unfortunately, as the conditional formatting is stored as a rule in the Excel workbook rather than being "baked in" when you saveWorkbook().

Upvotes: 3

Arkning
Arkning

Reputation: 181

Is this the result you're expecting ?

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)

Upvotes: 5

Related Questions