rreporters

Conditional formatting FlexTable

I am looking for a way to conditionally format a flextable with ReporteRs using percentage figures. Below is a small example.

packs <- list("ReporteRs","scales")
lapply(packs,require,character.only = TRUE)

dat <- cbind.data.frame(rep("ABCD",2),c(0.07,0.11),c(0.03,0.01))
colnames(dat) <- c("A","B","C")
dat[,2] <- percent(dat[,2])

pp = pptx()
pp = addSlide(pp, "Title and Content")
datft = FlexTable(data = dat)
cname <- c("B","C") 

for (i in cname) {
   if (i=="B") {
   datft[dat[, i] <= 10, i] = cellProperties( background.color = "green" )
   datft[dat[, i] > 10, i] = cellProperties( background.color = "red" )
   } else if (i=="C") {
   datft[dat[, i] <= 0.02, i] = cellProperties( background.color = "green" )
   datft[dat[, i] > 0.02, i] = cellProperties( background.color = "red" )   
   }
}
pp = addFlexTable(pp, datft)
writeDoc(pp, paste(getwd(),"/example.pptx",sep=""))

This works fine for column C but obviously not column B, as it's not numeric. I couldn't figure out a way to apply a function that formats the values to percentage figures after the backgroundcolor is changed.

Solution

  • Try this (with the explanation in the comment but after testing):

    for (i in cname) {
   if (i=="B") {
   datft[as.numeric( sub("%", "", dat[, i])) <= 10, i] = cellProperties( background.color = "green" )
   datft[ as.numeric( sub("%", "", dat[, i])) > 10, i] = cellProperties( background.color = "red" )
   } else if (i=="C") {
   datft[dat[, i] <= 0.02, i] = cellProperties( background.color = "green" )
   datft[dat[, i] > 0.02, i] = cellProperties( background.color = "red" )   
   }
}