Apache POI Excel Table-TotalsRow

Question

While writing a tool to export result sets to Excel I've come across one problem. I am successfully creating tables with formatting and filters on the column headers, no problem there. The problem is I can't figure out how to make the total rows "work." I want to use true total rows so they respond to the filters applied, but so far I can either get a row with subtotals that function but aren't a part of the table, or I can get a blank subtotal row.

I believe there must be some magic, like a formula evaluator or something similar, but I have yet to stumble on it in the javadocs or example code. I am using the code at this location with the following modifications. Inside the loop that sets the column headers:

     if(i == 0)
         column.setTotalsRowLabel("Totals:");
     else
         column.setTotalsRowFunction(STTotalsRowFunctionImpl.COUNT);          

Then outside the loop:

cttable.setTotalsRowShown(true);
cttable.setTotalsRowCount(1);

No luck, if I add a blank row for the totals, it is formatted as a part of the table, but no values show. If I set a formula for any of the total cells the formula works, but Excel doesn't like the table and removes the total row, although the formula is there and works, just not as a total row.

When I look at the raw XML underneath, it is virtually indistinguishable from an Excel sheet save for the table, while the work sheet is considerably different.

UPDATE: I have been away from this project for quite a while, recently turned back to it. I've given up on POI doing this automatically and instead have turned to trying to backdoor it through DOM manipulation.

I am so close I can't give up. This is all coming down to a namespace issue in the final worksheet. This code:

Element b = (Element) wb.getSheetAt(0).getCTWorksheet().getSheetData().getRowList().get(4).getCArray()[3].getDomNode();
Element f = b.getOwnerDocument().createElementNS("main", "f");
b.removeAttribute("t");
b.removeChild(b.getElementsByTagName("v").item(0));
f.appendChild(b.getOwnerDocument().createTextNode("SUBTOTAL(103,MYTABLE[Human])"));
b.appendChild(f);

produces the following in the sheet1.xml file:

<c r="D5">
    <main:f>SUBTOTAL(103,MYTABLE[Human])</f>
</c>

If I use createElement("f"), I get:

<c r="D5">
    <f xmlns="">SUBTOTAL(103,MYTABLE[Human])</f>
</c>

If I manually edit the sheet inside the archive and remove the namespace tag or qualifier, it works! I can't see how to solve the NS issue without saving the work book and then proceeding to open it up and fix the problems with file IO. Does anyone have any hints on this at all?

Answer 1

Note: I realize this is a very old post, but just in case anyone in the future may be looking for a solution to this as I just encountered the same issue...

You are on the right track, but I don't see where you are applying a formula to the cell in the actual total row. If you don't set this, the total row will be present but empty upon opening the file.

When creating the table

cttable.setTotalsRowShown(true);
cttable.setTotalsRowCount(1);
cttable.addNewAutoFilter();

When creating the columns

column.setTotalsRowFunction(STTotalsRowFunctionImpl.COUNT); 

After populating all of the table data

Row totalsRow = sheet.createRow(tableLastRow);
Cell totalsCell = totalsRow.createCell(lastColumnPosition);
totalsCell.setCellFormula("SUBTOTAL(103,MYTABLE[Human])");

You will have to populate the tableLastRow and lastColumnPosition for the values specific to your code. This works for me with Apache POI 3.17 and Excel 2016.

Answer 2

If I use the correct full URI for the namespace, the workbook saves and all is well as long as I don't try to evaluate the formula with POI. I use the workbook setForceFormulaRecalculation on and it works when Excel is opened. It's still a bug in POI I believe, but I needed a quick fix and this does it for me.