How to allow filtering by year, month, date in XLSX generated by openxlsx

Question

I've started to generate XLSX files instead of CSV through openxlsx. However, I'm experiencing a different behavior in what concerns Date filtering.

I've generated the following dummy code:

library(openxlsx)

df <- data.frame(ID=c(1,2,3,4,5,6), Date=c("1900-01-12","2010-12-29","1934-03-17", "1989-09-19","1978-11-27","2010-01-13"))

write.csv(df, "dateTestCSV.csv")

# Create the workbook
wb = createWorkbook()

hs <- createStyle(fontColour = "#ffffff", fgFill = "#4F80BD",
                  halign = "center", valign = "center", textDecoration = "bold", 
                  border = "TopBottomLeftRight")

addWorksheet(wb=wb, sheetName = "Test", gridLines=T, zoom=70)

writeData(
  wb,     
  sheet = "Test",
  x = df,
  withFilter=T,
  borders="all",
  borderStyle="thin",
  headerStyle=hs
)       

setColWidths(wb, sheet = "Test", cols=1:ncol(df), widths = "auto")

openxlsx::saveWorkbook(wb, "dateTestXLSX.xlsx", overwrite=T)

The first file generated, dateTestCSV.csv, is comma separated values file. And it looks like it follows:

If I add a filter to the Date column, it will look like follows:

However, when I create the XSLX file with filters, such filter looks like it follows:

It can be seen that Excel is filtering by absolute values, and doesn't group dates by year, month and/or day.

What am I dooing wrong?

score 1 · Accepted Answer · answered Mar 18 '19 at 16:29

You need to make 2 changes to your code as below, and the filters should work fine:

df <- data.frame(ID=c(1,2,3,4,5,6), 
   Date=c("1900-01-12","2010-12-29","1934-03-17", "1989-09-19","1978-11-27","2010-01-13"),
   stringsAsFactors = F) # Ensure your dates are initially strings and not factors

# Actually convert the character dates to Date before writing them to excel
df$Date <- as.Date(df$Date)

How to allow filtering by year, month, date in XLSX generated by openxlsx

1 Answers1