poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From David Fisher <dfis...@jmlafferty.com>
Subject Re: Help with SUM function
Date Tue, 31 Aug 2010 22:12:38 GMT
Hi Jim,

Cell names should work fine, but you may want to use Apache POI 3.7b2 as there have been bug
fixes.

http://poi.apache.org/spreadsheet/quick-guide.html#NamedRanges

Named cells are really convenient. Here is a code fragment that fills a HashMap with numeric
values from all the named cells on a worksheet.

        Workbook wb = new HSSFWorkbook(new ByteArrayInputStream(bytes));
        Sheet sh = wb.getSheetAt(0);
        FormulaEvaluator wbeval = wb.getCreationHelper().createFormulaEvaluator();

        DecimalFormat fmt = new DecimalFormat("###.00");

        //collect tagged cells into a map
        Map<String, String> model = new HashMap<String, String>();
        for (int i = 0; i < wb.getNumberOfNames(); i++) {
            Name nm = wb.getNameAt(i);
            if(nm.isDeleted()) continue;

            String key = nm.getNameName();

            String nameFmla = nm.getRefersToFormula();
            CellReference ref = new CellReference(nameFmla);

            Row row = sh.getRow(ref.getRow());
            if (row != null) {
                Cell cell = row.getCell(ref.getCol());
                if (cell != null) {
                    try {
                        // try to evaluate the cell
                        CellValue cv = wbeval.evaluate(cell);
                        if (cv != null && cv.getCellType() == Cell.CELL_TYPE_NUMERIC)
{
                            double dval = cv.getNumberValue();
                            model.put(key, fmt.format(dval));
                        }
                    } catch (RuntimeException e){
                        // YK: catch any errors thrown by the formula evaluator
                        // the safe fallback is to retrieve the cached formula result
                        if (cell.getCachedFormulaResultType() == Cell.CELL_TYPE_NUMERIC){
//ensure that the cell is numeric
                            double dval = cell.getNumericCellValue();
                            model.put(key, fmt.format(dval));
                        }
                    }
                }
            }
        }

It works currently with poi-3.6-20091214, Tomcat 6 and Java 6.

Note this includes formula evaluation. Errors from the formula evaluator such as external
references which POI will not follow are caught and the result cached by Excel is used instead.

(Thanks Yegor! Probably this ought to be added to the poi documentation on the busy developer's
guide.)

Regards,
Dave

On Aug 31, 2010, at 2:06 PM, Jim Bury wrote:

> Could I do something with cell names? I haven't been able to get it to keep the cell
names when I generate the spreadsheet... Is there a trick or is it not doable?
> 
> Jim
> 
> -----Original Message-----
> From: Michael Zalewski [mailto:zalewski@optonline.net] 
> Sent: Tuesday, August 31, 2010 3:13 PM
> To: user@poi.apache.org
> Subject: Re: Help with SUM function
> 
> Sounds like you are having a column with Subtotals and Grand Totals. The SUM
> function that yields your grand total does not need to pick out ranges. Just run
> the SUM function over the entire column
> 
> For example
>   A             B
> 1 Supplier #1   1.00 
> 2               2.00
> 3 Subtotal      @SUM(B1:B2)
> 4 Supplier #2   3.00
> 5               4.00
> 6 Subtotal      @SUM(B4:B5)
> 7 GRAND TOTAL   @SUM(B1:B5)
> 
> You would think that the GRAND TOTAL would be double the correct result, because
> it looks like the formula includes the subtotals at B3 and B6. But such is not
> the case. The SUM function will ignore cells which contain subtotals from cells
> already included in the SUM.
> 
> I'm not sure that the POI Formula Evaluator behaves this way. But Excel does.
> 
> 
> 
> 
> ---------------------------------------------------------------------
> 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
> 


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


Mime
View raw message