Is it possible to create an XLSX document with pre-configured sorting and filtering?

Question

We have a web tool that shows the user a data table and lets them a) export it to an XSLX and b) sort and filter the table on the web page. We would like for the exported spreadsheet to contain all of the data, but with filters (and, preferably, sorting) configured to mirror the selections on the web page at the time of export.

We're using as_xlsx to export the data directly from Oracle, but we're willing to look at more sophisticated approaches. I've looked at Apache POI, and I can't figure out if it can do what we want. Aside from that, something in the Java family would be preferable. XLSX export is a must, and the files should open in Excel on Windows warning-free.

Is this possible at all? If so, is there software that makes it easy?

Answer 1

It's possible with Apache Poi. Example:

final Date FILTER_DATE = /* ... */;

XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet();

// Insert data

for(int rownum = 0; rownum < 100; rownum++){
    XSSFRow row = sheet.createRow(rownum);

    String name = /* ... */;
    Date birthDate = /* ... */;

    row.createCell(0).setCellValue(name);
    row.createCell(1).setCellValue(birthDate);

    // Manually filter
    if ( ! (
        (name.equals("Alice")) &&
        (DateUtil.getExcelDate(birthDate >= DateUtil.getExcelDate(FILTER_DATE))
    )) {
        row.getCTRow().setHidden(true);
    }
}

// Create filters

sheet.setAutoFilter(CellRangeAddress.valueOf("A1:C100"));

CTAutoFilter autoFilter = sheet.getCTWorksheet().getAutoFilter();

CTFilterColumn nameFilterColumn = autoFilter.insertNewFilterColumn(0);
nameFilterColumn.setColId(0L);
CTFilter nameFilter = nameFilterColumn.addNewFilters().insertNewFilter(0);
nameFilter.setVal("Alice");

CTFilterColumn dateFilterColumn = autoFilter.insertNewFilterColumn(0);
dateFilterColumn.setColId(2L);
CTCustomFilter dateFilter = dateFilterColumn.addNewCustomFilters().addNewCustomFilter();
dateFilter.setOperator(STFilterOperator.GREATER_THAN_OR_EQUAL);
dateFilter.setVal(Double.toString(DateUtil.getExcelDate(FILTER_DATE)));

workbook.write(System.out);