R xlsx Package: Set Interior/background color of Excel Cells

How to set the background color of cell by R xlsx library

By using the Fill function of the R xlsx package for Excel, you may set the Interior/background as well as foreground color of the cells and rows.

An example can be:

Fill(backgroundColor = "yellow",

                foregroundColor = "yellow",

                pattern         = "SOLID_FOREGROUND")

The example below shows applying background and foreground colors to cells by using this function. We will also share screenshots along with the full code to make things clear.

Steps to apply background color to the cells by xlsx package in R

Step 1

Load library:

library(xlsx)

Step 2

Specify the Workbook and its sheet where you want to apply the color:

wb_bg <- loadWorkbook(file="Products.xlsx")

AllList <- getSheets(wb_bg)

sheet <- AllList[[1]]

Step 3:

Use the Fill function and there you will provide the colors as below:

fill_style <- CellStyle(wb_bg) +

           Fill(backgroundColor = "orange",

                foregroundColor = "orange",

                pattern         = "SOLID_FOREGROUND")

Step 4

Set that color for the cell/rows where you want to apply it:

setCellStyle(cell, fill_style)

Step 5

Save the Workbook:

saveWorkbook(wb_bg, "Products.xlsx")

Combining all the above steps are given in the example below.

An example of adding background color in a sheet cell

The example below combined all code above and shared the output of the Excel sheet before and after the code execution:

By executing this R code below, we will set the 3^rd row's 2^nd cell (B3) color as orange. Have a look:

R Code:

#Load library
library(xlsx)

# Specify the Excel file and sheet
wb_bg <- loadWorkbook(file="Products.xlsx")
AllList <- getSheets(wb_bg)

sheet <- AllList[[1]]

# find the cell
row <- getRows(sheet, 3)
cells <- getCells(row, 2)
cell <- cells[[1]]

# Specify background and foreground colors
fill_style <- CellStyle(wb_bg) +

           Fill(backgroundColor = "orange",

                foregroundColor = "orange",

                pattern         = "SOLID_FOREGROUND")

# Apply color to the cell
setCellStyle(cell, fill_style)

saveWorkbook(wb_bg, "Products.xlsx")

Result sheet:

You Can see the B5 cell color is orange.

Setting a row background color example

The following example sets the color of row number 3 to green. See the code and output below:

R code:

#Load library
library(xlsx)

# Specify the Excel file and sheet
wb_bg <- loadWorkbook(file="Products.xlsx")
SheetList <- getSheets(wb_bg)
sheet <- SheetList[[1]]

# Setting the row for background color
row <- getRows(sheet, 3)
cel <- getCells(row)

# Specify color of row
fill_style <- CellStyle(wb_bg) +

           Fill(backgroundColor = "#80FF00",

                foregroundColor = "#80FF00",

                pattern         = "SOLID_FOREGROUND")

#Applying color to the row
for (i in names(cel)) {
  setCellStyle(cel[[i]], fill_style)
}

saveWorkbook(wb_bg, "Products.xlsx")

Output: