You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by bu...@apache.org on 2010/06/07 08:43:59 UTC
DO NOT REPLY [Bug 49397] New: VLOOKUP, OFFSET and COUNTA functions
doesn't evaluate
https://issues.apache.org/bugzilla/show_bug.cgi?id=49397
Summary: VLOOKUP, OFFSET and COUNTA functions doesn't evaluate
Product: POI
Version: 3.6
Platform: PC
OS/Version: Windows Vista
Status: NEW
Severity: blocker
Priority: P2
Component: HSSF
AssignedTo: dev@poi.apache.org
ReportedBy: united_zele_3@hotmail.com
Created an attachment (id=25538)
--> (https://issues.apache.org/bugzilla/attachment.cgi?id=25538)
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.
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
DO NOT REPLY [Bug 49397] VLOOKUP, OFFSET and COUNTA functions
doesn't evaluate
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=49397
Yegor Kozlov <ye...@dinom.ru> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEW |RESOLVED
Resolution| |INVALID
--- Comment #3 from Yegor Kozlov <ye...@dinom.ru> 2010-07-29 02:29:06 EDT ---
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
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
DO NOT REPLY [Bug 49397] VLOOKUP, OFFSET and COUNTA functions
doesn't evaluate
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=49397
--- Comment #2 from Pilekes <ch...@cofely.nl> 2010-06-15 05:29:13 EDT ---
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...
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
DO NOT REPLY [Bug 49397] VLOOKUP, OFFSET and COUNTA functions
doesn't evaluate
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=49397
--- Comment #1 from united_zele_3@hotmail.com 2010-06-07 02:44:48 EDT ---
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);
}
}
}
}
}
}
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org