Bug 50384 - Some formulas/macros are giving #VALUE! error on Excel file(.XLS).
Summary: Some formulas/macros are giving #VALUE! error on Excel file(.XLS).
Status: RESOLVED DUPLICATE of bug 50607
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.7-FINAL
Hardware: PC All
: P1 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2010-11-30 09:46 UTC by Aniket
Modified: 2011-01-19 07:32 UTC (History)
1 user (show)



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Aniket 2010-11-30 09:46:35 UTC
Code : 
cell.setCellFormula(formulaValue);
cell.setCellType(Cell.CELL_TYPE_FORMULA);

formualValues not working :
=+(SUBTOTAL(109,(INDIRECT(ADDRESS(ROW()+2,COLUMN())):INDIRECT(ADDRESS(ROW()+1000,COLUMN())))))

=IF(ISBLANK('Export Data'!B3),"",IF(LEFT('Export Data'!B3,3)="%%%","",VALUE(TRIM(CLEAN('Export Data'!B3)))))
Comment 1 Nick Burch 2010-11-30 09:54:58 UTC
Are you remembering to re-calculate the formula values after changing the formula?
http://poi.apache.org/spreadsheet/eval.html
Comment 2 Yegor Kozlov 2010-11-30 10:32:26 UTC
Please post the full code to reproduce the problem, ideally a junit test case. If your code requires a template then attach it too. 

Yegor
Comment 3 Aniket 2010-12-01 01:41:30 UTC
Thanks all for your kind reply. :)
I tried to use FormulaEvaluator.evaluateInCell(Cell cell);
But it throws Not Implemented Yet exception.

Stack trace :
org.apache.poi.ss.formula.eval.NotImplementedException: Error evaluating cell 'Pivot Data'!B2
at org.apache.poi.ss.formula.WorkbookEvaluator.addExceptionInfo(WorkbookEvaluator.java:321)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:288)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:221)
at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateFormulaCellValue(HSSFFormulaEvaluator.java:320)
at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateInCell(HSSFFormulaEvaluator.java:240)
at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateInCell(HSSFFormulaEvaluator.java:46)


But when I click on the cell on the excel file and press enter formula gets evaluated. And for other simpler formulas I am not evaluating so excel does it itself but in case of the above mentioned formulas it is giving #VALUE! error on the excel file.

@Yegor : Our code is simple we do not do any manipulation with the formulas. We save the formulas and it's coordinate and later just populate them on the excel sheet :

 for (ExcelCoordinate formulaCoordinate : formulaCoordinates) {
      try {
        Sheet sheet = workbook.getSheetAt(formulaCoordinate.getSheet());
        if(sheet == null) {
          continue;
        }
        if(sheet.getSheetName().equals("Pivot Data") && !isCloned) {
          workbook.cloneSheet(formulaCoordinate.getSheet());
          isCloned = true;
        }
        Row row = sheet.getRow(formulaCoordinate.getRow());
        if(row == null) {
          continue;
        }
        Cell cell = ExportUtil.getCell(row, formulaCoordinate.getColumn());
        if(cell == null) {
          continue;
        }
        cell.setCellFormula(formulaCoordinate.getName());
        cell.setCellType(Cell.CELL_TYPE_FORMULA);
        //Cell evalutedCell = evaluator.evaluateInCell(cell);        	
      }

Also if possible help me out with how can I stop this #VALUE! error and let Excel do it's calcualtion.

Please Help!!!
Comment 4 Aniket 2010-12-02 01:23:54 UTC
Really Sorry!!!For being so impatient. Can someone just confirm that if it is a bug then in which release this will be resolved. Also it will be great help if there can be some workaround for now?
-- 
Thanks,
Aniket
Comment 5 Nick Burch 2010-12-02 06:47:32 UTC
Are you able to create a simple test for this? Ideally this would be a standalone unit test that creates a sheet, populates it with the formula, tries and fails to evaluate the formula. Failing that, an excel file with only a few cells populated, one of which holds the formula that you can't get evaluated?

Evaluation ought to work for most standard formula expressions, if there's a bug then we'll need more details to identify why + fix.
Comment 6 Aniket 2011-01-19 00:35:09 UTC
I have introduced two new excel function that were not implemented yet in POI. The functions are CLEAN and ADDRESS. I have submitted a Zip file that contains all the new files as well as the changed files. Kindly do the review so that I can check-in the files.

Thanks,
Aniket
Comment 7 Yegor Kozlov 2011-01-19 07:32:08 UTC
Support for CLEAN and ADDRESS was committed in r1060788

Yegor

*** This bug has been marked as a duplicate of bug 50607 ***