How to Color Cells with write.xlsx

The xlsx package in R provides functions that can be used to read, write and format excel files. In this article, we will look at one such use of the xlsx package to format and basically color the cells of an XLSX file using the XLSX package and R Programming Language.

Coloring Cells using XLSX Package

Coloring cell is a part of formatting and we can accomplish it with the help of the required methods listed below.

Syntax:

This method creates a Fill object which can be used for styling cells, and can be used along with CellStyle to color cells.

Fill(foregroundColor, backgroundColor, pattern)

where,

• foregroundColor: Takes hex colour value beginning with ‘#’, eg. “#00FF00” or a valid color belonging to colours(), eg. “green”.
• backgroundColor: Takes hex colour value beginning with ‘#’, eg. “#00FF00” or a valid color belonging to colours(), eg. “green”.
• pattern: Takes string value specifying the fill pattern style. Valid values come from constant “FILL_STYLES_”, eg. “SOLID_FOREGROUND”.

Syntax:

This method creates a cell style and returns it as a CellStyle object.

CellStyle(wb, fill, font, border, alignment, . . . )

where,

• wb: A workbook object.
• Fill: A Fill object returned by Fill().

Syntax:

setCellStyle(cell, cellStyle)

where,

• cell: A cell object. Single-cell to be formatted.
• cellStyle: A CellStyle object returned by CellStyle().

We will be working with the below sample.xlsx file for the demonstration purpose which was initially as shown below:

Sample.xlsx file for the demonstration purpose

You can check the full argument list and its value for Fill() and CellStyle().

R

# Import xlsx package
library(xlsx)

# Set working directory
setwd("D:/New folder/R")

# Load existing workbook to program
wb <- loadWorkbook(file="sample.xlsx")

# Get all sheets from loaded workbook
sheets <- getSheets(wb)

# Get specific sheet to work on
sheet <- sheets[[1]]

# Get all rows in the sheet
rows <- getRows(sheet, rowIndex = 2:5)

# Get cells you want to format
cells <- getCells(rows, colIndex = 2:3)

# Define cell style as required
fll <- Fill(backgroundColor="green",
            foregroundColor="green",
            pattern="SOLID_FOREGROUND")
br <- Border(color="grey",
             position=c("BOTTOM", "TOP",
                        "LEFT", "RIGHT"),
             pen="BORDER_THIN")
cs <- CellStyle(wb, dataFormat=NULL,
                alignment=NULL,
                border=br, fill=fll,
                font=NULL, cellProtection=NULL)

# loop over the each cells extracted for
# formatting and set the defined cell style
for (c in cells) {
  setCellStyle(c, cs)
}

# Save the workbook
saveWorkbook(wb,file='sample_colored.xlsx')

Output:

Changes made in the sample.xlsx file

Now let’s look at an example in which we will try to use conditional formatting to color cells in an xlsx file using xlsx package.

R

# Import xlsx package
library(xlsx)

# Set working directory
setwd("D:/Sem VIII/New folder/R")

# Load existing workbook to program
wb <- loadWorkbook(file="sample.xlsx")

# Get all sheets from loaded workbook
sheets <- getSheets(wb)

# Get specific sheet to work on
sheet <- sheets[[1]]

# Get all rows in the sheet
rows <- getRows(sheet, rowIndex = 2:5)

# Get cells you want to format
cells <- getCells(rows, colIndex = 2:3)

# Define cell style as required
fllGreen <- Fill(backgroundColor="green",
                 foregroundColor="green",
                 pattern="SOLID_FOREGROUND")
fllPink <- Fill(backgroundColor="pink",
                foregroundColor="pink",
                pattern="SOLID_FOREGROUND")
br <- Border(color="grey",
             position=c("BOTTOM", "TOP",
                        "LEFT", "RIGHT"),
             pen="BORDER_THIN")

# Define seperate style for different conditions
csPink <- CellStyle(wb, border=br, fill=fllPink)
csGreen <- CellStyle(wb, border=br, fill=fllGreen)

# loop over the each cells extracted for formatting
# and set the defined cell style
for (c in cells) {
  # Set different style to different
  # cells based on required condition
  if (getCellValue(c) > 15){
    # setting pink background to cells
    # with value higher than 15
    setCellStyle(c, csPink)
  }
  else {
    # setting green background to cells
    # with value lesser than or equal to 15
    setCellStyle(c, csGreen)
  }
}

# Save the workbook
saveWorkbook(wb, file='sample_colored_conditional.xlsx')

Output:

Conditional formatting using xlsx

Coloring Cells using openxlsx Package

Alternatively, we can also use the openxlsx package to color cells when writing to an Excel file. The required methods are: You can check the full argument list and its value for createStyle() and addStyle().

Here is an example of how to use the openxlsx package to write to an Excel file and color a specific cell.

R

# import the openxlsx library
library(openxlsx)

# create a workbook
wb <- createWorkbook()
addWorksheet(wb, "People")

# Create a dataframe to be added to the sheet
age <- c(12, 13, 14)
name <- c("John", "Martinez", "Gomez")
mydata <- data.frame(name, age)

# Write the data created above to the
# worksheet "People" on the workbook "wb"
writeData(wb, "People", mydata, startCol = 1,
          startRow = 1, rowNames = TRUE)

# Color the 1st column from 1st row to 4th
cellStyle <- createStyle(fontColour = "#00bb0f",
                         fgFill = "yellow")
addStyle(wb, "People", cellStyle,
         rows = 1:4, cols = 1)

# Set your working directory. Workbook will
# be saved in the set working directory
setwd("D:/New folder/R")

# Save the workbook with any preferred name
saveWorkbook(wb, "color_cell.xlsx", overwrite = TRUE)

Output:

Cell colored using openxlsx

Now let’s look at an example in which we will try to use conditional formatting to color cells in an xlsx file using openxlsx package.

R

# Import library
library(openxlsx)

# Create a workbook
wb <- createWorkbook()

# Set working director
setwd("D:/New folder/R")

# Add a worksheet
addWorksheet(wb, "Car_data")

# Create style
style1 <- createStyle(fontColour = "darkgreen",
                      bgFill = "green")

# Taking data from existing data
mydata <- mtcars[5:8, 1:3]

# inserting the data to the worksheet
# 'Car_data' in the workbook 'wb'
writeData(wb, "Car_data", mydata,
          startCol = 1, startRow = 1,
          rowNames = TRUE)

# Formatting using conditional formatting "between"
conditionalFormatting(wb, "Car_data", cols = 3,
                      rows = 1:11, style = style1,
                      type = "between", rule = c(4, 7))

# Saving the workbook
saveWorkbook(wb, "conditional_formatting_color.xlsx",
             overwrite = TRUE)

Output:

Cell colored using between conditional formatting in openxlsx