Bug 49397 - VLOOKUP, OFFSET and COUNTA functions doesn't evaluate
Summary: VLOOKUP, OFFSET and COUNTA functions doesn't evaluate
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.6-FINAL
Hardware: PC Windows Vista
: P2 blocker (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2010-06-07 02:43 UTC by united_zele_3
Modified: 2010-07-29 02:29 UTC (History)
0 users

the excel file (32.00 KB, application/vnd.ms-excel)
2010-06-07 02:43 UTC, united_zele_3

Note You need to log in before you can comment on or make changes to this bug.
Description united_zele_3 2010-06-07 02:43:56 UTC
Created attachment 25538 [details]
the excel file


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".

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.
Comment 1 united_zele_3 2010-06-07 02:44:48 UTC
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);
Comment 2 Pilekes 2010-06-15 05:29:13 UTC
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...
Comment 3 Yegor Kozlov 2010-07-29 02:29:06 UTC
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);