ElizabethAB ElizabethAB - 1 month ago 16
R Question

Font Coloring Using xlsx Package in R

I am writing an R function to save out pre-formatted data frames. Part of the format template calls for changing the column heading font color. The cell styling returns the correct alignment and bolding, but the text is still black.

The below is a bare version to demonstrate my font coloring problem (just change the file_path variable to a location that exists).

library(xlsx)
file_path <- "C:/Users/.../Desktop/tst.xlsx"
wb <- createWorkbook()

headerStyle <- CellStyle(wb,
font = Font(wb, isBold=TRUE, color = "#ffffff"),
fill = Fill(foregroundColor = "#2db6e8",
pattern = "SOLID_FOREGROUND"),
alignment = Alignment(wrapText = TRUE,
horizontal = "ALIGN_CENTER",
vertical = "VERTICAL_CENTER")
)

x <- mtcars
sheet <- createSheet(wb, "test")

cellBlock <- CellBlock(sheet,
startRow = 1,
startCol = 1,
noRows = nrow(x) + 1,
noColumns = ncol(x) + 1,
create = TRUE)

CB.setRowData(cellBlock = cellBlock,
x = colnames(x),
rowIndex = 1,
colOffset = 1,
rowStyle = headerStyle +
Border(pen = "BORDER_MEDIUM", color = "black",
position = "BOTTOM"))

saveWorkbook(wb, file_path)

Answer

I was able to get white text using the color index from the INDEXED_COLORS_ constant which is 9 for white. For your example code it would read:

headerStyle <- CellStyle(wb,
          font = Font(wb, isBold=TRUE, color = "9"),
          fill = Fill(foregroundColor = "#2db6e8",
          pattern = "SOLID_FOREGROUND"),
          alignment = Alignment(wrapText = TRUE,
                            horizontal = "ALIGN_CENTER",
                            vertical = "VERTICAL_CENTER")
)