Create different xlsx sheet header colors in R

Question

I would like to have different header colors for the xlsx sheet I'm creating. However, I'm only able to create a single color for the header with the below code using xlsx package. Is there a way to do multiple colors with this package? Thank you!

## create workbook with styles
wb <- createWorkbook(type="xlsx")
TABLE_COLNAMES_STYLE <- CellStyle(wb) + Font(wb, isBold = TRUE) + Fill(foregroundColor="light blue") + Alignment(wrapText=T, horizontal = "ALIGN_CENTER")
TABLE_STYLE <- CellStyle(wb) + Alignment(wrapText=T, h="ALIGN_RIGHT", v = "VERTICAL_TOP") 

## load worksheet 1
sheet <- xlsx::createSheet(wb, sheetName="Sheet")

### load data to worksheet with styles
cell.format <- rep(list(TABLE_STYLE), (dim(iris)[2]))
xlsx::addDataFrame(data.frame(iris), sheet, row.names=FALSE, colStyle=cell.format, colnamesStyle = TABLE_COLNAMES_STYLE)
setColumnWidth(sheet, colIndex = c(1), colWidth = 15)
setColumnWidth(sheet, colIndex = c(2), colWidth = 50)
setColumnWidth(sheet, colIndex = c(3:(ncol(iris)-1)), colWidth = 15)
xlsx::createFreezePane(sheet, 2,2,2,3)

This is what I'm getting from the above code:

This is what I'd like to achieve with multiple header colors for easier differentiation of the columns:

user_1100107 · Answer

I don't know the xlsx package. Below is how to do with openxlsx.

library(openxlsx)

## Create a new workbook
wb <- createWorkbook("John Doe")

## Add a worksheets
addWorksheet(wb, "Iris")

## write data to worksheet 1
writeData(wb, sheet = 1, iris)

## create and add a style to the column headers
headerStyle1 <- createStyle(
  fontSize = 14, fontColour = "#FFFFFF",
  fgFill = "#4F81BD", halign = "center"
)
addStyle(wb, sheet = 1, headerStyle1, rows = 1, cols = 1:4, gridExpand = TRUE)
headerStyle2 <- createStyle(
  fontSize = 14, fontColour = "yellow",
  fgFill = "purple", halign = "center"
)
addStyle(wb, sheet = 1, headerStyle2, rows = 1, cols = 5)

## save
saveWorkbook(wb, "Iris.xlsx", overwrite = TRUE)

Create different xlsx sheet header colors in R

1 Answers