How Can I Enable Sorting/Filtering on XSSFTable Columns Using Apache POI?

Question

I'm working on an application that fetches database records and creates an excel doc from that data.

The excel doc is generated fine and all the data is readable; as of a previous answer from this forum the table is also appropriately generated (the header row remains visible even when I've scrolled past it, so the table is definitely present). However, I had expected that once I had a table I'd be able to sort and filter the columns as is the case when you 'Insert -> Table' in excel, but there are no such options when I open the doc.

I don't see a setFitlerable or setSortable or anything like that on the XSSFTable or XSSFTableColumn classes... How do I enable sorting/filtering on the table columns?

Table creation code follows, if it's useful:

//Create table
CellReference topLeft = new CellReference(sheet.getRow(3).getCell(0));
CellReference bottomRight = new CellReference(sheet.getRow(nextRow-1).getCell(3));
AreaReference tableArea = workbook.getCreationHelper().createAreaReference(topLeft, bottomRight);
XSSFTable dataTable = sheet.createTable(tableArea);

dataTable.setName("TableData" + EXCEL_OBJECT_NUMBER);
dataTable.setDisplayName("TableData" + EXCEL_OBJECT_NUMBER);

XSSFTableColumn column = dataTable.getColumns().get(0);
column.setId(1);
column.setName("COLUMN1");

column = dataTable.getColumns().get(1);
column.setId(2);
column.setName("COLUMN2");

column = dataTable.getColumns().get(2);
column.setId(3);
column.setName("COLUMN3");

column = dataTable.getColumns().get(3);
column.setId(4);
column.setName("COLUMN4");

Answer 1

If dataTable is a XSSFTable and tableArea is the AreaReference of that XSSFTable, then the following code sets the auto filters into the table headers as Excel also does it:

dataTable.getCTTable().addNewAutoFilter().setRef(tableArea.formatAsString());

Complete example:

import java.io.FileOutputStream;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.util.AreaReference;
import org.apache.poi.ss.util.CellReference;

import java.util.GregorianCalendar;

class CreateExcelTable {

 public static void main(String[] args) throws Exception {

  Object[][] data = new Object[][] {
   new Object[] {"Text", "Date", "Number", "Boolean"},
   new Object[] {"Text 1", new GregorianCalendar(2020, 0, 1), 1234d, true},
   new Object[] {"Text 2", new GregorianCalendar(2020, 1, 15), 5678d, true},
   new Object[] {"Text 3", new GregorianCalendar(2020, 2, 1), 90.1234, false},
   new Object[] {"Text 4", new GregorianCalendar(2020, 3, 15), 567.89, false}
  };

  try (XSSFWorkbook workbook = new XSSFWorkbook();
       FileOutputStream fileout = new FileOutputStream("Excel.xlsx") ) {

   XSSFCellStyle dateCellStyle = workbook.createCellStyle();
   dateCellStyle.setDataFormat(14);

   XSSFSheet sheet = workbook.createSheet();
   XSSFRow row = sheet.createRow(0);
   XSSFCell cell = row.createCell(0);
   cell.setCellValue("Lorem ipsum");
   row = sheet.createRow(1);
   cell = row.createCell(0);
   cell.setCellValue("semit dolor");

   int nextRow = 3;
   int nextCol = 0;
   for (Object[] dataRow : data) {
    row = sheet.createRow(nextRow++);
    nextCol = 0;
    for (Object value : dataRow) {
     cell = row.createCell(nextCol++);
     if (value instanceof String) cell.setCellValue((String)value);
     else if (value instanceof GregorianCalendar) {
      cell.setCellValue((GregorianCalendar)value);
      cell.setCellStyle(dateCellStyle);
     }
     else if (value instanceof Double) cell.setCellValue((Double)value);
     else if (value instanceof Boolean) cell.setCellValue((Boolean)value);
    }
   }

   CellReference topLeft = new CellReference(sheet.getRow(3).getCell(0));
   CellReference bottomRight = new CellReference(sheet.getRow(nextRow-1).getCell(3));
   AreaReference tableArea = workbook.getCreationHelper().createAreaReference(topLeft, bottomRight);
   XSSFTable dataTable = sheet.createTable(tableArea);
   //dataTable.setName("Table1");
   dataTable.setDisplayName("Table1");

   //this styles the table as Excel would do per default
   dataTable.getCTTable().addNewTableStyleInfo();
   XSSFTableStyleInfo style = (XSSFTableStyleInfo)dataTable.getStyle();
   style.setName("TableStyleMedium2");
   style.setShowColumnStripes(false);
   style.setShowRowStripes(true);

   //this sets auto filters
   dataTable.getCTTable().addNewAutoFilter().setRef(tableArea.formatAsString());

   workbook.write(fileout);
  }

 }
}