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
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.
Contact Us