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:
<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 see how to make it fully automatic, but 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?
Thanks,
John
-----Original Message-----
From: Kuhns, John [mailto:[email protected]]
Sent: Monday, May 09, 2016 1:32 PM
To: [email protected]
Subject: Tables and subtotals
Hello all.
I've asked this question before and it seems that a lot of the Excel
functionality for tables and subtotal rows is not fully functional in POI. At
the time I was using 3.9. I recently turned back to this project and upgraded
to the most recent stable POI release of 3.14. The same problems still exist.
If relevant I'm using Eclipse Mars and 64 bit Oracle JDK 8 build 91.
When I save the spreadsheet it always gives me this in the sheet1.xml document
in the archive for the cell that I set up to be the column total:
<c r="D5"/>
If I open it in Excel and then manually choose COUNT in the total row and save
it, the cell gets changed to this:
<c r="D5">
<f>SUBTOTAL(103,MyTable[MyColumn])</f>
<v>3</v>
</c>
The table1.xml document contains the following markup before and after, so I
believe this much is correct:
<tableColumn id="4" name="Human" totalsRowFunction="count"/>
Is it possible to insert my own XML into the DOM model? I've tried something
like this:
CTTableColumn column = columns.addNewTableColumn();
column.setTotalsRowFunction(STTotalsRowFunctionImpl.COUNT); //
I would expect this to be the only step necessary, except maybe a call to
evaluateAllFormulaCells (which I do)
/* the next three lines add the proper dom fragment to mimic
the above Excel-saved version, but the markup isn't there in the saved
spreadsheet */
Element f =
column.getDomNode().getOwnerDocument().createElement("f");
f.appendChild(column.getDomNode().getOwnerDocument().createTextNode("SUBTOTAL(103,MyTable[MyColumn])"));
column.getDomNode().appendChild(f);
When I inspect the element it does show up as I expect, but on saving the
spreadsheet and opening the archive the inserted element is gone. If anyone can
give me a pointer I'd appreciate it.
John
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
