Apache POI Excel Table-TotalsRow

Question

When writing a tool to export result sets to Excel, I ran into one problem. I successfully create tables with formatting and filters in column headers, no problem. The problem is that I can’t figure out how to make the full ranks “work”. I want to use true complete rows so that they respond to the filters applied, but so far I can either get a row with subtotals that function, but are not part of the table, or I can get an empty intermediate row.

I believe that there should be some kind of magic, for example, a formula evaluator or something similar, but I still have to stumble upon it in javadocs or sample code. I use the code for this location with the following changes. Inside a loop that sets column headers:

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

Then outside the loop:

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

Unlucky if I add an empty row for totals, it is formatted as part of the table, but the values ​​are not displayed. If I set a formula for any of the common cells, this formula works, but Excel does not like the table and removes the common row, although the formula is and works, and not as a common row.

When I look at the raw XML below, it is almost indistinguishable from the Excel spreadsheet stored for the spreadsheet, while the worksheet is significantly different.

UPDATE: I have long left this project, recently returned to it. I abandoned the POI by doing this automatically, and instead turned to trying to execute a backdoor through DOM manipulation.

I'm so close I can't give up It all comes down to the namespace problem on the last sheet. 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); 

creates the following in sheet1.xml:

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

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

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

If I manually edit the sheet inside the archive and remove the namespace tag or classifier, it will work! I cannot figure out how to solve the NS problem without saving the workbook, and then continue to work and fix the problems with the IO file. Does anyone have any hints of this?

Answer 1

If I use the correct full URI for the namespace, the book saves, and everything is fine, until I try to evaluate the formula using the POI. I use the setForceFormulaRecalculation workbook and it works when Excel is open. This is still a mistake in the POI, I believe, but I need a quick fix, and it does it for me.

Answer 2

Note. I understand that this is a very old post, but just in case, someone in the future may find a solution for this, since I just ran into the same problem ...

You are on the right track, but I don’t see where you are applying the formula to the cell in the actual common row. If you have not set this, a common line will be present, but will be empty when you open the file.

When creating a table

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

When creating columns

 column.setTotalsRowFunction(STTotalsRowFunctionImpl.COUNT); 

After filling in all the data in the table

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

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