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 <[email protected]> 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:[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] > >
