Add a filter to an openxlsx object with a query activated

Question

I'm dumping into a file a report, and I'm using openxlsx to do so, as it can be seen below:

library(openxlsx)

set.seed(100)

dataset <- data.frame(A=runif(100),B=runif(100),C=sample(c("A","B","C"), 100, replace=T))

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

wb = createWorkbook()

  addWorksheet(wb = wb, sheetName = "example", gridLines = T, zoom = 70)
  writeData(
    wb, 
    sheet = "example", 
    x = dataset, 
    withFilter=T,
    borders="all",
    borderStyle="thin",
    headerStyle=hs
  ) 
  # The width of the columns will be automatically adjusted
  setColWidths(wb, sheet = "example", cols=1:ncol(dataset), widths = "auto")

saveWorkbook(wb, "example.xlsx", overwrite=T)

I've activated the withFilter option, so that the end user will be able to select only subsets of data. However, I'd like to activate one filter so that by default only a subset is shown; e.g. I'd like to show only records where the column C equals to "B".

I've reviewed the documentation of the openxlsx package, but I can't find how to do it anywhere.

Frederiksen

I ran into exactly the same problem. did you find out how to do this by now? – Habitable

@JoostKeuskamp, I couldn't fine it anywhere. Sorry. – Frederiksen

Answer 1

you can do this sort of active filtering with VBS, but the excel file must be actively open. This could be run after the example.xlsx file is created as you have in post.

In this snippet, I am using base R to create a file that is my VBS script-to-be. Then writeLines to, ahem, write lines to the VBS script. This VBS lines/code will open the example.xlsx. In this demo, this file lives here: H:\\R\\practice\\excel\\example.xlsx

After setting up some defaults, it applies the autofilter. The 3 indicates that it is the 3rd column and the \"C\" is where you would change the value to be filtered to. Thus, filter data to show rows where column 3 is equal to "C".

Then filtered version is saved as new file example_C.xlsx to show difference/retain original. Then close the workbook and quit the application.

These lines are written to the H:/R/practice/excel/active_filter.vbs script and then connection is closed.

finally use shell.exe to run vbs script. This script should quickly run, and you should see the file open briefly, filter, then close. If you don't want this then set xlApp.Visible = FALSE

fileConn <- file("H:/R/practice/excel/active_filter.vbs")
 writeLines(c("Dim xlApp, xlBook, xlSht",
             "Dim filename",
             "filename = \"H:\\R\\practice\\excel\\example.xlsx\"",
             "Set xlApp = CreateObject(\"Excel.Application\")",
             "xlApp.Visible = TRUE",
             "set xlBook = xlApp.WorkBooks.Open(filename)",
             "set xlSht = xlApp.Worksheets(1)",
             "xlApp.Application.DisplayAlerts = False",
             "xlBook.ActiveSheet.Range(\"A1\").AutoFilter 3, \"C\"",
             "xlBook.SaveAs \"H:\\R\\practice\\excel\\example_C.xlsx\"",
             "xlBook.Close",
             "xlApp.Quit"),
             fileConn)

close(fileConn)

shell.exec("H:/R/practice/excel/active_filter.vbs")

Checking both files:

Grapery