Summary: | VLOOKUP, OFFSET and COUNTA functions doesn't evaluate | ||
---|---|---|---|
Product: | POI | Reporter: | united_zele_3 |
Component: | HSSF | Assignee: | POI Developers List <dev> |
Status: | RESOLVED INVALID | ||
Severity: | blocker | ||
Priority: | P2 | ||
Version: | 3.6-FINAL | ||
Target Milestone: | --- | ||
Hardware: | PC | ||
OS: | Windows Vista | ||
Attachments: | the excel file |
i couldn't upload the code file, so here is it: File file = new File(path to file); InputStream is = new FileInputStream(file); HSSFWorkbook wb = new HSSFWorkbook(is); HSSFFormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator(wb); int countSheets = wb.getNumberOfSheets(); for (int i = 0; i < countSheets - 1; i++) { HSSFSheet sheet = wb.getSheetAt(i); String name = sheet.getSheetName().split("\\-")[1]; if (name.equals("MEETNET") { int countRows = sheet.getPhysicalNumberOfRows(); for (int i = 1; i < countRows; i++) { Integer internId = null; Integer internBereikId = null; for (int j = 0; j <countCells; j++) { HSSFCell cell = row.getCell(j); if (j == 1) { if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) { internBereikId = formulaEvaluator.evaluateFormulaCell(cell); } } } } } } I've got the same problem : <poi:cell NumberFormat="0" type="formula" value="COUNTA(A8:A#CurrentDataLine-2#)" index="#5+I#" /> It shows =COUNTA(A8:A354) in the cell, but the result is 1. After giving an Enter in de formulabar the 1 turns into 347... You are using a wrong method to retrieve results. FormulaEvaluator#evaluateFormulaCell evaluates the formula and returns the type of the formula result, not the result itself. In your case it returned 0 which corresponds to Cell.CELL_TYPE_NUMERIC, that is the result of formula was number. Use FormulaEvaluator#evaluate to get the result. The code below worked fine to me: HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(inputFile)); HSSFFormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator(wb); HSSFSheet sheet = wb.getSheet("20-MEETNET"); for (int i = 1; i <= sheet.getLastRowNum(); i++) { HSSFRow row = sheet.getRow(i); HSSFCell cell = row.getCell(1); CellValue val = formulaEvaluator.evaluate(cell); int internBereikId = (int)val.getNumberValue(); System.out.println(i + ": " + internBereikId); } Yegor |
Created attachment 25538 [details] the excel file Hi, Overview: I have a problem that when i read in an excel file and evaluate a function, he always returns the value 0. I attached the excel file. It has 2 sheets. The first sheet is "Bereik" and the second sheet is "Meetnet". In the sheet "Meetnet" in the second column i need this function that refers to the first sheet to know the intern id for the selected "Bereik" in "Meetnet". Reproduce: I attached a txt file with the code to reproduce the problem. I'm sorry but i couldn't do more because of the lack of time her at work. Expected result: The program should return the iternal id's of "Bereik". For example when the first column in "Meetnet" is "Europees" then the second column in "Meetnet" should have the value 3 because that's the id of "Europees" in the sheet "Bereik". The problem accured on Windows 7 instead of Windows Vista.