I am working on a project where I need to automate the production of spreadsheets of data from a particular type of data-frame. I have read a useful explanation of how to do this here, and based on this explanation, I have created some code in R.Studio (professional version 1.1.383) using the xlsx package (version 0.5.7). My code generates a spreadsheet of data from an existing data frame TABLE. Here is the code I am using, with a mock data frame used to create a reproducible example:
#In this code we create a mock data frame called TABLE
#In practice we have a larger data frame with data that is not reproduced here
TABLE <- data.frame(Var1 = c(1, 6, 3, 9, 10, 4, 3, 5, 2, 2),
Var2 = c(18.1, 14.2, 12.0, 21.3, 33.4, 16.0, 15.4, 8.6, 6.7, 12.9),
Var3 = c(0, 1, 1, 0, 1, 1, 0, 0, 0, 0));
#Create workbook, sheet, columns and styles
WB <- createWorkbook(type = "xlsx");
SHEET <- createSheet(WB, sheetName = "My Data");
rows <- createRow(SHEET, rowIndex = 1:5);
cells <- createCell(rows, colIndex = 1:(1+ncol(TABLE)));
STYLE_TITLE <- CellStyle(WB) +
Font(WB, name = "Calibri", heightInPoints = 16,
color = "black", isBold = TRUE, isItalic = FALSE);
#Add data from data frame TABLE
addDataFrame(TABLE, SHEET, startRow = 4L, startColumn = 2L,
colnamesStyle = STYLE_COLNAMES, row.names = FALSE);
#Add title
setCellValue(cells[[2, 2]], "This is my spreadsheet title");
setCellStyle(cells[[2, 2]], STYLE_TITLE);
#Save workbook
saveWorkbook(WB, "My Pretty Workbook.xlsx");
This code successfully generates the spreadsheet I want, and everything looks how I want it, except that the title comes up in white font, which means they can't be seen on the white background of the spreadsheet. This seems to me to be contrary to the part of my code where I specify the Font with color = "black". I have also tried using color = "#000000" and this gets the same result.
Why is my title and information text coming up in white font? How do I fix this?
If you type STYLE_TITLE in the console, you can see in the output (look under $font$ref), that the color is being set to FFFFFF, which is white. Below is what I see in the console (I've shown only the relevant portion of the output). Note the FFFFFF on the last line:
STYLE_TITLE <- STYLE_TITLE <- CellStyle(WB) +
Font(WB, name = "Calibri", heightInPoints = 16,
color = "black", isBold = TRUE, isItalic = FALSE)
STYLE_TITLE
$font $ref [1] "Java-Object{<xml-fragment xmlns:main=\"http://schemas.openxmlformats.org /spreadsheetml/2006/main\">\n <main:name val=\"Calibri\"/>\n <main:sz val=\"16.0 \"/>\n <main:color rgb=\"FFFFFF\"/>\n <main:b val=\"true\"/>\n</xml-fragment>}"
I don't know why this is occurring or how to get the Font function to convert "black" or "#000000" into the proper color code of 000000. However, I did find (strangely) that other color encodings work properly. For example, you can use the color "#010101", which is almost black:
STYLE_TITLE <- STYLE_TITLE <- CellStyle(WB) +
Font(WB, name = "Calibri", heightInPoints = 16,
color = "#010101", isBold = TRUE, isItalic = FALSE)
STYLE_TITLE
$font $ref [1] "Java-Object{<xml-fragment xmlns:main=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\">\n <main:name val=\"Calibri\"/>\n <main:sz val=\"16.0\"/>\n <main:color rgb=\"010101\"/>\n <main:b val=\"true\"/>\n</xml-fragment>}"
This and other color names and rgb color specifications (e.g., "red", "purple", "#F372B6") are also encoded properly in the Font output object, and the colors are rendered as expected in the spreadsheet output.
I know you wanted 100% black, but if you can settle for 99.6% black, this workaround should get the job done.