You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by "J. Miguel" <jv...@gmail.com> on 2008/06/04 11:44:58 UTC

Problem evaluating formulas

Hello.

I am working with POI HSSF to read and write an .xls spreadsheet.

In my spreadsheet I have some cells with formulas, but I need to have values
in the cells, not formulas.

I tried to evaluate each cell of type CELL_TYPE_FORMULA and change the cell
values with the obtained results using this code.

for (Iterator cit = row.cellIterator(); cit.hasNext();) {
	HSSFCell c = (HSSFCell) cit.next();
	if (c.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
		evaluator.evaluateInCell(c);
	}
}

With the most of cells this works fine, i.e. cell wich have a formula that
is a single link to another cell (i.e. =VDC!CM20), but I have problems with
cells wich have more complex formulas like this:

=SI(Y(RESIDUO(SUMA(VDC!$BB$52:$BF$81);2)=0;RESIDUO(SUMA(VAC!$BB$52:$BF$81);2)=0;RESIDUO(SUMA(IDC!$BB$52:$BF$81);2)=0;RESIDUO(SUMA(IAC!$BB$52:$BF$81);2)=0;RESIDUO(SUMA(R!$BB$52:$BF$81);2)=0;RESIDUO(SUMA('f'!$BB$52:$BF$81);2)=0;RESIDUO(SUMA('C'!$BB$52:$BF$81);2)=0);"";VDC!DG20)
(NOTE: My Excell is in Spanish)

The code I wrote before works with this formula, but it is very very very
slow, it needs around 20 seconds to evaluate this formula. I don't know why
do this happen.

Can somebody help me???

I need evaluate this formulas in a quick mode. You can suggest me other
fragments of code or totally different solutions, I only need to change this
complex formulas with its resulting values. It is very important for me
solving this problem.

Thank you very much.
-- 
View this message in context: http://www.nabble.com/Problem-evaluating-formulas-tp17642921p17642921.html
Sent from the POI - User mailing list archive at Nabble.com.


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org


Re: Problem evaluating formulas

Posted by "J. Miguel" <jv...@gmail.com>.
Hi Axel.

Thank you very much, my problem is solved.

I had to add some lines to your code, but now it works perfect and very
quickly.
This is the final code.

if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
	Double numericCellValue = cell.getNumericCellValue();
	if (numericCellValue.isNaN()) {
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setCellValue(cell.getRichStringCellValue());
	} else {
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setCellValue(numericCellValue);
	}
}

On the other hand, I can explain you the purpose of evaluate cells values.
I need to have simple cells, cells with simple values and not formulas
because my formulas refer to cells in different sheets of the excell book.
In my application I need to delete some of these sheets so if I don't change
references for values in the new book (with only a few sheets) I will get a
lot of cell with reference to unexisting sheets, so I will get a lot of
Error cells. Furthermore after this preprocessing I will process the new and
simplified excell book using the OpenOffice.org API and it doesn't work
properly with all the excell functions but it works perfect with simple
value cells.

Thank you very much for you help.

Miguel.


-- 
View this message in context: http://www.nabble.com/Problem-evaluating-formulas-tp17642921p17644397.html
Sent from the POI - User mailing list archive at Nabble.com.


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org


Re: Problem evaluating formulas

Posted by Axel Rose <ax...@googlemail.com>.
Hi Miguel,

when just reading an existing Excel document I found it sufficient to just
read the value without evaluating.
Perhaps this is ok for you too

case HSSFCell.CELL_TYPE_FORMULA:
  String cellFormula = cell.getCellFormula();
  Double numericCellValue = cell.getNumericCellValue();
  if (numericCellValue.isNaN())
    System.out.println(cell.getRichStringCellValue().toString());
  else
    System.out.println(numericalCellValue);
  break;

To be honest I didn't quite got the purpose of evaluation cell values.
Wouldn't it be sufficient to put a formula in the cell and let the client
program evaluate it when opening some created doc?


HTH,
Axel.