poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Kuhns, John" <jku...@AimNTLS.com>
Subject RE: Excel TotalsRow
Date Wed, 07 Oct 2015 14:40:45 GMT
Thanks for the suggestions, but they don't fix my issue. The problem is adding the formula
to the cells calculates properly, however the cell is no longer in the TotalsRow and Excel
issues a warning when parsing the table. I can either get the TotalsRow with nothing in it,
or I can get formulas that function but aren't in the TotalsRow. It may be that this is not
implemented? Here's a snippet of the code:

        CTTableColumns columns = cttable.addNewTableColumns();
        int columnCount = endCell.getColumnIndex() - startCell.getColumnIndex() + 1;
        columns.setCount(columnCount);
         for (int i = 0; i < columnCount; i++)
         {
         CTTableColumn column = columns.addNewTableColumn();
         column.setId(i+1);
         column.setName("Column" + i);      
         if(i == 0)
        	 column.setTotalsRowLabel("Totals:");
         else
        	 column.setTotalsRowFunction(STTotalsRowFunctionImpl.COUNT);
         }           
         // cttable.setTotalsRowShown(true);
         cttable.setTotalsRowCount(1);

This gives me a blank (but properly formatted) message if the final row in my table range
has empty strings in the last row's cells, all cells, even the "Totals:" is blank. It opens
fine in Excel, and if I set the first column to anything at all the other cells in the TotalsRow
populate instantly with the count, as they should. If I place a subtotal formula addressing
the correct cells in the final row, Excel complains about the table and removes the total
row, but the formulas are processed properly and contain the correct values. It's frustrating.


-----Original Message-----
From: Terry Birch [mailto:tjfbirch@bellsouth.net] 
Sent: Wednesday, October 07, 2015 9:00 AM
To: user@poi.apache.org
Subject: Re: Excel TotalsRow

Kuhns, John <jkuhns <at> AimNTLS.com> writes:

> 
> What is the proper way to add a TotalsRow to an XSSFTable? I can get 
> an
empty TotalsRow, but when I try to add a
> function Excel complains and removes the total row. Thanks!
> 
> 
> Sent via the Samsung Galaxy S™II Skyrocket™ an AT&T 4G LTE smartphone.
> 


After creating a new row, I add a formula to a cell like this:
...other code...
workString = "sum(G" + firstStyleRow + ":" + "G" + (rowIndex) + ")"; cell.setCellType(Cell.CELL_TYPE_FORMULA);
cell.setCellFormula(workString);
...other code...

and don't forget to refresh the formulas such as: 
XSSFFormulaEvaluator.evaluateAllFormulaCells(workbook)
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org For additional commands, e-mail: user-help@poi.apache.org

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Mime
View raw message