Multiple styles () apply to the same line with openxlsx package

Question

I started using the openxlsx R openxlsx recently because it does not require the rJava or Java package at all. I am trying to write dates to a column, but row formatting does not apply to the final date column in my xlsx file.

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

As a result, I got an image that was formatted almost correctly, but there are no shaded colors in the date column.

Am I missing something? I read the documentation, but cannot find a way to apply formatting to date fields. I tried specifically formatting the date column and then running addStyle() after applying date formatting, but it still doesn't work.

How does the addStyle() function not apply lightStyle or darkStyle to a date column?

Any help would be appreciated.

Answer 1

The style is erased using the date column style. Calling writeData () before adding any styles and setting stack = TRUE when calling addStyle () will fix this. I will do a code change on github https://github.com/awalker89/openxlsx so as not to overwrite the styles.