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:[email protected]] > Sent: Tuesday, August 31, 2010 3:13 PM > To: [email protected] > 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: [email protected] > For additional commands, e-mail: [email protected] > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [email protected] > For additional commands, e-mail: [email protected] > --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
