CODEWITHSUNDEEP
ropenxlsx
Mar
Mar

Reputation: 147

Conditional formatting with rule with value in between

I am trying to make conditional formatting for cells based on value between two values using library(openxlsx). The problem is with the first conditional formatting rule = c(0.015,0.020), it don't work.

## Dataframe
cost_table <- read.table(text = "FRUIT  COST  SUPPLY_RATE
1  APPLE   15   0.026377
2  ORANGE   14  0.01122
3  KIWI   13  0.004122
5  BANANA   11  0.017452
6  AVOCADO   10   0.008324       "  , header = TRUE)

## This is the line where I label the %.
cost_table$SUPPLY_RATE <- label_percent(accuracy = 0.01)(cost_table$SUPPLY_RATE)

## Creating workbook and sheet
Fruits_Table <- createWorkbook()
addWorksheet(Fruits_Table,"List 1")

writeData(Fruits_Table,"List 1",cost_table)


## Style color for conditional formatting
posStyle <- createStyle(fontColour = "#006100", bgFill = "#C6EFCE")
negStyle <- createStyle(fontColour = "#9C0006", bgFill = "#FFC7CE")

## Here is the error
conditionalFormatting(Fruits_Table, "List 1",
                      cols = 3,
                      rows = 2:6, rule = c(0.015,0.020), style = posStyle, type='between'
)

conditionalFormatting(Fruits_Table, "List 1",
                      cols = 3,
                      rows = 2:6, rule = ">0.020", style = negStyle
)

Upvotes: 1

Views: 242

Answers (1)

Jan Marvin
Jan Marvin

Reputation: 969

Replace this line

cost_table$SUPPLY_RATE <- label_percent(accuracy = 0.01)(cost_table$SUPPLY_RATE)

with this

class(cost_table$SUPPLY_RATE) <- "percentage"

You have replaced the numeric column with a character column and Excel got confused. Using percentage tells Excel to apply a percentage format to the cells.

Upvotes: 1

Related Questions