CODEWITHSUNDEEP
javaapache-poi
Adrian Cox
Adrian Cox

Reputation: 6334

How to add table heading drop-down with Apache Poi

I'm generating Excel tables with Apache POI, but my generated tables lack the drop-down menu on each header that appear when I "format as table" in Excel itself.

I'd like to generate this:

Table with menu button

But instead I get this:

Table without menu button

I'm following this blog post, and my code looks like this:

        XSSFTable table = sheet.createTable();
        table.setDisplayName("Data");
        CTTable ctTable = table.getCTTable();
        ctTable.setDisplayName("Data");
        ctTable.setId(1L);
        ctTable.setName("DATA");
        CTTableStyleInfo table_style = ctTable.addNewTableStyleInfo();
        table_style.setName("TableStyleMedium9");
        table_style.setShowColumnStripes(false);
        table_style.setShowRowStripes(true);

Each column is then created like this:

            CTTableColumn column = ctColumns.addNewTableColumn();
            column.setName(headers.get(i));
            column.setId(i + 1);

What am I missing?

Upvotes: 7

Views: 4235

Answers (2)

Adrian Cox
Adrian Cox

Reputation: 6334

Thanks to Alan Hay for the clue - the solution is to add an auto-filter, but this needs to be added as a CTAutoFilter for each individual column of the CTTable. The working solution looks like this:

    CTTableColumns ctColumns = ctTable.addNewTableColumns();
    CTAutoFilter autofilter = ctTable.addNewAutoFilter();
    ctColumns.setCount(table_headers.size());

    for(int i = 0; i < table_headers.size(); i++) {
        CTTableColumn column = ctColumns.addNewTableColumn();
        column.setName(table_headers.get(i));
        column.setId(i + 1);
        CTFilterColumn filter = autofilter.addNewFilterColumn();
        filter.setColId(i + 1);
        filter.setShowButton(true);
    }

When auto-sizing columns, it's also necessary to add extra width for the drop down menu:

    for(int i = 0; i < table_headers.size(); i++) {
        sheet.autoSizeColumn(i);
        // Include width of drop down button
        sheet.setColumnWidth(i, sheet.getColumnWidth(i) + 1000);
    }

Upvotes: 10

Alan Hay
Alan Hay

Reputation: 23246

It is not exactly clear from the example you quoted whether applying the Table styling should create the filter dropdowns for you or not.

However you can explicitly call setAutoFilter() as below to have the filter dropdowns set.

e.g.

CellReference start = table.getStartCellReference();
CellReference end= table.getEndCellReference();
sheet.setAutoFilter(new CellRangeAddress(...);

Upvotes: 1

Related Questions