Multiple styles() applied to the same row with package openxlsx

Question

I started using the R package openxlsx recently because it does not require the rJava package or Java at all. I'm trying to write dates to a column but row formatting isn't being applied to the final date column in my xlsx file.

Example data:

tmp <- structure(list(someNumbers = c(8L, 3L, 4L, 4L, 1L, 4L, 2L, 2L, 
       7L, 2L), someLetters = structure(c(9L, 4L, 1L, 2L, 7L, 3L, 4L, 
       6L, 8L, 5L), .Label = c("b", "d", "e", "h", "l", "n", "o", "t", 
       "u"), class = "factor"), moreNumbers = c(25L, 25L, 36L, 38L, 
       39L, 32L, 31L, 40L, 28L, 32L), moreStuff = c(0.37, 1.39, -2.27, 
       0.65, -0.71, -1.67, 0.71, 1.32, 2.14, 1.13), words = structure(c(5L, 
       3L, 7L, 1L, 2L, 6L, 4L, 10L, 9L, 8L), .Label = c("And another, but      
       maybe I'll make this one a little bit longer.", 
       "camera", "cat", "Ceramic penil holder made by yours truly.", 
       "dog", "headset", "Here's a list of words, almost like a    
       description.", 
       "Last", "Number 9.  Number 9.  Number 9.", "This phrase is not six     
       words long."
       ), class = "factor"), dates = structure(c(2000, 3217.55555555556, 
       4435.11111111111, 5652.66666666667, 6870.22222222222,     
       8087.77777777778, 
       9305.33333333333, 10522.8888888889, 11740.4444444444, 12958), class =    
       "Date")), .Names = c("someNumbers", 
       "someLetters", "moreNumbers", "moreStuff", "words", "dates"),    
       row.names = c(NA, 
       -10L), class = "data.frame")

and code:

#Setting date options
options("openxlsx.dateFormat" = "yyyy-mm-dd")

#Create the xlsx workbook and insert a worksheet
testing <- createWorkbook()
addWorksheet(testing, "openxlsxTesting")

#Set column widths
setColWidths(testing, "openxlsxTesting", cols = 1:ncol(tmp), widths =    
"auto")

#Create a header style
headerStyle <- createStyle(fontColour = "white", fgFill =  
"blue", halign = "left", valign = "center", textDecoration =  
"bold")

#Create styles to shade alternate rows
lightStyle <- createStyle(fontColour = "black", fgFill = "#E7E0C5", halign = 
"right", valign = "center")

darkStyle <- createStyle(fontColour = "black", fgFill = "#E1D8B7", halign =  
"right", valign = "center")


#A vector of the xlsx rows
colorRows <- 2:(nrow(tmp)+1)

#Apply the header style
addStyle(testing, "openxlsxTesting", headerStyle, rows = 1, cols = 
1:ncol(tmp), gridExpand = TRUE)

#Apply the alternate shading styles
addStyle(testing, "openxlsxTesting", lightStyle, rows  = 
colorRows[which(colorRows %% 2 == 0)], cols = 1:ncol(tmp), gridExpand = TRUE)

addStyle(testing, "openxlsxTesting", darkStyle, rows =  
colorRows[which(colorRows %% 2 == 1)], cols = 1:ncol(tmp), gridExpand = TRUE)

#Write data to the workbook
writeData(testing, "openxlsxTesting", tmp, startRow = 1, startCol = 1)

#Save workbook to getwd()
saveWorkbook(testing, "openXlsxTesting.xlsx", TRUE)

What I end up with is the image below which is almost formatted correctly, but the date column lacks the shaded colors.

Am I missing something? I've read the documentation but I can't figure out a way to apply formatting to date fields. I've tried specifically formatting the date column, then running addStyle() after applying date formatting, but it's still a no go.

How is it that the addStyle() function isn't applying the lightStyle or darkStyle to the date column?

Any help would be appreciated.

Westnorthwest

I found a work-around: if I convert any Date fields to as.character(), the addStyles() works across all rows. – Westnorthwest

Answer 1

The styling is being overwritten by the date column styling. Calling writeData() before adding any styles and setting stack = TRUE when calling addStyle() will fix this. I'll make a code change on github https://github.com/awalker89/openxlsx to prevent styles being overwritten.

Butts

Thanks for the response. I wasn't aware that writeData() should be called before addStyle(). – Westnorthwest