conditionalFormatting() on a character column in R with openxlsx package

Question

I am trying to create excel table outputs from within R using the openxlsx package. Before creating outputs I need to put a two types of conditional formatting on my values.

But I have not been able to accomplish this since I have numeric stored as text in R - this is a requirement for reporting purposes.

Here is a sample code:

library(openxlsx)

tablex <- cbind.data.frame(vec1 = c("120","ug/L","10 ","1.38 ","2.53 ","80.7 ","<5 ","<1 ",
                                    "<1 "," <1 ","<1 ","<1 ","<1 ","<1 ","<1 ","73.6 ","61.7 J+","43.9 ",
                                    "43.1 ","<20 ","<5 ","153 ","131 ","151 ","140 "))
tablex$vec1 <- as.character(tablex$vec1)

### Table 3A ####
tables <- createWorkbook()
addWorksheet(tables, "TableX")
writeData(tables, "TableX", tablex, startCol = 1, startRow = 1, 
          rowNames = FALSE, keepNA = T,na.string = "--")

## conditional formatting styles ###
cond.style1 <- createStyle(bgFill = "grey", fontColour = "#9C0006",  halign = "center")
cond.style2 <- createStyle(fontColour = "grey",  halign = "center")


## works incorrectly ####
conditionalFormatting(tables, "TableX",cols = 1,
                      rows = 3:26,
                      rule = ">=$A$2", style = cond.style1)

##does not work! ####
# conditionalFormatting(tables, "TableX", cols = 1,
#                       rows = 3:26, type = "contains",
#                       rule = " <", style = cond.style2)

saveWorkbook(tables, file = "./Output/TablesX.xlsx", overwrite = TRUE)

cond.style1 highlights the values that fit the rule correctly, but also highlights some additional values. '''cond.style2''' does not work at all. No errors come up in the R console, however when I open excel, it shoots an error Replaced Part: /xl/worksheets/sheet1.xml part with XML error. An attribute value must not contain '<'. Line 1, column 2145.

Any help on this issue is appreciated. thank you

score 0 · Accepted Answer · answered Jan 21 '21 at 05:42

Here is one hack we could come up with by basically forgetting about conditional formatting and applying a style to each cell individually :

tablex <- cbind.data.frame(vec1 = c("120","ug/L","10 ","1.38 ","2.53 ","80.7 ","<5 ","<1 ",
                                    "<1 "," <1 ","<1 ","<1 ","<1 ","<1 ","<1 ","73.6 ","61.7 J+","43.9 ",
                                    "43.1 ","<20 ","<5 ","153 ","131 ","151 ","140 "))
tablex$vec1 <- as.character(tablex$vec1)

### Table 3A ####
tables <- createWorkbook()
addWorksheet(tables, "TableX")
writeData(tables, "TableX", tablex, startCol = 1, startRow = 1, 
          rowNames = FALSE, keepNA = T,na.string = "--")


cond.style1 <- createStyle(bgFill = "grey", fontColour = "#9C0006",  halign = "center")
cond.style2 <- createStyle(fontColour = "grey",  halign = "center")

 
for(i in 3:nrow(tablex)) {
  # i = 2
  thresh <- as.numeric(tablex$vec1[1])
  cur.val <- tablex$vec1[i]
  cur.val <- extract_numeric(cur.val)
  
  
  if(!is.na(cur.val) & cur.val>= thresh ) {
    addStyle(tables, sheet = 1, cond.style1, rows = (i+1), cols = 1, gridExpand = TRUE)
  } else {
  print(i+1)
    }
  
}


saveWorkbook(tables, file = "./Output/TablesX.xlsx", overwrite = TRUE)

conditionalFormatting() on a character column in R with openxlsx package

1 Answers1