poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Dominik Stadler <dominik.stad...@gmx.at>
Subject Re: Tables and subtotals
Date Tue, 17 May 2016 08:41:06 GMT

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

Can you also check with a recent 3.15-nightly snapshot from
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.


On Tue, May 10, 2016 at 5:40 PM, Kuhns, John <jkuhns@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

  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message