You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by "Kuhns, John" <jk...@AimNTLS.com> on 2016/05/09 17:32:16 UTC
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
Re: Tables and subtotals
Posted by Dominik Stadler <do...@gmx.at>.
Hi,
Can you produce a self-sufficient piece of code that allows to reproduce
this? Then we can take a closer look where the XML-namespace is set
incorrectly (I expect there is a small issue somewhere when the subtotal is
enabled).
Can you also check with a recent 3.15-nightly snapshot from
https://builds.apache.org/view/POI/job/POI/lastSuccessfulBuild/artifact/?
We did some changes tonamespace/XML handling since 3.14 which might affect
this as well.
If it is still not working with latest POI, then please open a
bugzilla-issue at https://bz.apache.org/bugzilla/ so we can handle
state/resolution/... more easily.
Dominik.
On Tue, May 10, 2016 at 5:40 PM, Kuhns, John <jk...@aimntls.com> wrote:
> 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:jkuhns@AimNTLS.com]
> Sent: Monday, May 09, 2016 1:32 PM
> To: user@poi.apache.org
> 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: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
RE: Tables and subtotals
Posted by "Kuhns, John" <jk...@AimNTLS.com>.
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:jkuhns@AimNTLS.com]
Sent: Monday, May 09, 2016 1:32 PM
To: user@poi.apache.org
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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org