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