openxlsx - fill row row based on text in another column
Asked Answered
H

2

5

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
Hatchet answered 10/4, 2019 at 15:1 Comment(0)
V
5

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)
Volunteer answered 24/5, 2019 at 7:44 Comment(5)
thanks! this looks promising but i have not had time to check it out yet.Hatchet
this code highlights the first column blue, but not the row.Submaxillary
@Submaxillary because in the function addStyle() I've set the parameter cols to 1 if you want it to highlight the entire rows you should replace it by 1:ncol(my_data) where my_data is your data frameVolunteer
@Arkning. I am getting the correct rows highlighted, but not consistently (sometimes I only get part of the row highlighted). And other times I am getting the following error: Error in addStyle(wb = wb, sheet = "containsText", style = color, rows = which(grepl(a$letters, : Length of rows and cols must be equal.Submaxillary
@Submaxillary I need to see your code in order to know why it doesn't work, so if you have some way i can easily contact you and see your code (in comments or create a topic on stackoverflow or discord, linkedin, etc...) let me know i'd be glad to help you !Volunteer
S
3

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().

Silicious answered 24/2, 2020 at 5:23 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.