0

I am using cfspreadsheet in CF9 and I need to enable the auto filters, however the SpreadSheetAddAutoFilter() function was added in CF11. Upgrading isn't an option.

Is there a way to accomplish this?

Dan Bracuk
  • 20,699
  • 4
  • 26
  • 43
Jay2001
  • 59
  • 7
  • In CF9, cfspreadsheet had very limited functionality and the spreadsheet functions had a lot of bugs. If you choose to use one of the extensions mentioned below, you may also be able to "upgrade" the bundled POI version to something newer and more stable. Since you're on CF9, you must use the Javaloader.cfc. CF10+ include a built in version of it. I know you said upgrading isn't an option but .. CF9 is EOL'd and no longer receives important security updates, so ... – SOS May 16 '20 at 03:04

2 Answers2

3

AFAIK, your only alternative is the Apache POI project. Ben Nadel originally created a CFC wrapper for it, but Christopher Wigginton has a more current implementation. Not sure if either has an implementation of the filter function, but you take a look at an example of that from this SO answer.

Save the first and last cell from filter area, and execute:

sheet.setAutoFilter(new CellRangeAddress(firstCell.getRow(), lastCell.getRow(), firstCell.getCol(), lastCell.getCol()));
Adrian J. Moreno
  • 14,350
  • 1
  • 37
  • 44
0

If you do upgrade to something newer (at least ColdFusion 2016), I highly recommend using the open source "lucee-spreadsheet" library.

https://github.com/cfsimplicity/lucee-spreadsheet

It's fast and has many modern features including using the new "streaming" XML spreadsheet during population so that you don't encounter memory issues. The developer is very responsive and the documentation is good. The library also has a dedicated addAutofilter function.

(I'm adding this answer because I also used Ben Nadal's solution back when I used ColdFusion 8-10. I've found that using an updated POI library is faster and a lot less buggy than the native CFSpreadsheet tags.)

James Moberg
  • 4,360
  • 1
  • 22
  • 21