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
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