R: Conditional formatting a column with percentage(%) value

Question

I will need to do color conditional formatting for 1 particular column, format it to percentage, and export the file as .xlsx. Note that I have 5 data frames that I will run this rule code with, and compile them into 1 workbook each in different sheets. I am stuck on the part where I can't seem to set the conditional rule if I formatted the percentage in it. And vice versa, if I conditional format it first, I'm not sure how I can format percentage for that column. Please refer to my code below.

## 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 %. However if I do that, conditional formatting will not recognize it in the rule
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")

## If Supply rate is above 1.5%, it will be green, if it's equivalent or below, it will be red
conditionalFormatting(Fruits_Table, "List 1",
                      cols = 3,
                      rows = 2:6, rule = "C2> 0.015", style = posStyle
)

conditionalFormatting(Fruits_Table, "List 1",
                      cols = 3,
                      rows = 2:6, rule = "C2<= 0.015", style = negStyle
)

The output should be as shown below.

Regarding Borderline info

What I'm looking at is to apply outside border for c2:c6.

To clarify my purpose, the final output will be shown as below. I have some other codes to format the borders for the headers and column A:B. Because of the percentage style, it affected my borderline.

user_14812170 · Answer

You don't need to use label_percent from scales package. You can apply the percentage format along with the color rules to the workbook by using style and then addStyle functions. Another thing, I found in the documented examples of conditionalFormatting that you don't need to specify the column name (such as C) in the rule argument if your rule apply to only one column with no relation to values in another column.

Here is the code that I used:

Fruits_Table <- createWorkbook()
addWorksheet(Fruits_Table,"List 1")
writeData(Fruits_Table,"List 1",cost_table)
conditionalFormatting(Fruits_Table, "List 1",
                      cols = 3,
                      rows = 2:6, rule = "> 0.015", style = posStyle) 
conditionalFormatting(Fruits_Table, "List 1",
                       cols = 3,
                        rows = 2:6, rule = "<= 0.015", 
                       style = negStyle)
percent_style <- createStyle(numFmt = "PERCENTAGE")
addStyle(Fruits_Table,"List 1", style = ,percent_style, rows = 2:6, cols = 3)

I tried that code and it works.

saveWorkbook(Fruits_Table, "my_fruits_table.xlsx", )

Updated to add borderline info

In case you want to create borderline along with the percentage format, you can use border and borderStyle as follows:


percent_border_style<- createStyle(numFmt = "PERCENTAGE", 
                       border = "TopBottomLeftRight", 
                       borderStyle = "medium" )

addStyle(Fruits_Table,"List 1", 
        style = ,percent_border_style, 
        rows = 2:6, cols = 3)

saveWorkbook(Fruits_Table, "borderline_fruits_table.xlsx", )

Here is the borderline result

In case you want to customize different styles to different cells, as you explained in your comment, you need to createStyle for a particular style, and then you use addStyle to apply that particular style to a particular cell. So, you need to specify the row and the column for each style. To keep the percentage format style, you also need to keep numFmt to each addStyle.

Here is an example code to apply outside borders to the targeted column. The code customizes borders to three groups of cells:

top_side_line <- createStyle(numFmt = "PERCENTAGE", 
                             border = "TopLeftRight", 
                             borderStyle = "medium")
side_line <- createStyle(numFmt = "PERCENTAGE", 
                             border = "LeftRight", 
                             borderStyle = "medium")
bottom_side_line <- createStyle(numFmt = "PERCENTAGE", 
                             border = "BottomLeftRight", 
                             borderStyle = "medium")
addStyle(Fruits_Table,"List 1", 
         style = top_side_line, rows = 2, cols = 3)
addStyle(Fruits_Table,"List 1", 
         style = side_line, rows = 3:5, cols = 3)
addStyle(Fruits_Table,"List 1", 
         style = bottom_side_line, rows = 6, cols = 3)
saveWorkbook(Fruits_Table, "newline_fruits_table.xlsx")

Here is the result:

R: Conditional formatting a column with percentage(%) value

1 Answers