Anthony Andrews wrote:
Morning Tony,
Found a fix that appears to be in both 3.1 final and 3.5 beta 1
archives. There is a static method you can use like this;
cell = row.createCell((short)0);
cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
cell.setCellFormula("SUM(A1:A10)");
cell = row.createCell((short)1);
cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
cell.setCellFormula("SUM(B1:B10)");
row = sheet.createRow(11);
cell = row.createCell((short)1);
cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
cell.setCellFormula("IF(B11=0,0,A11/B11)");
HSSFFormulaEvaluator.evaluateAllFormulaCells(workbook);
I would imagine that if you have a workbook containing multiple sheets
where each sheet holds hundreds of cells with forumlae then there
could be a performance hit but it does seem to overcome the immediate
problem.
Make sure that you check the results; I did not, simply opening the
workbook and not seeing the #Value error message staring back at me
was enough!!
I wish I could replicate your results. Unfortunately, this still didn't
fix my problem. I will try the head branch from SVN today, if I can
figure out how to compile it ;)
Here the lastest code I've tried, and I always end up with the #Value.
I really do appreciate your attempts to help me.
Tony
-------------
import org.apache.poi.hssf.usermodel.*;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Random;
/**
*/
public class BorkedFormula
{
public static void main(String[] args)
{
final File outfile = new File("/tmp/borked.xls");
if (outfile.exists())
{
outfile.delete();
}
final HSSFWorkbook workbook = new HSSFWorkbook();
final HSSFSheet sheet = workbook.createSheet("Borked");
final HSSFFormulaEvaluator evaluator = new
HSSFFormulaEvaluator(sheet, workbook);
final Random random = new Random(System.currentTimeMillis());
HSSFRow row;
HSSFCell cell;
for (int i = 0; i < 10; i++)
{
row = sheet.createRow(i);
for (short col = 0; col < 2; col++)
{
double val = random.nextDouble() * 100;
cell = row.createCell(col);
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(val);
}
}
row = sheet.createRow(10);
evaluator.setCurrentRow(row);
cell = row.createCell((short)0);
cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
cell.setCellFormula("SUM(A1:A10)");
evaluator.evaluateFormulaCell(cell);
cell = row.createCell((short)1);
cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
cell.setCellFormula("SUM(B1:B10)");
evaluator.evaluateFormulaCell(cell);
row = sheet.createRow(11);
evaluator.setCurrentRow(row);
cell = row.createCell((short)1);
cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
cell.setCellFormula("IF(B11=0,0,A11/B11)");
evaluator.evaluateFormulaCell(cell);
HSSFFormulaEvaluator.evaluateAllFormulaCells(workbook);
FileOutputStream fos = null;
try
{
fos = new FileOutputStream(outfile);
workbook.write(fos);
}
catch (IOException e)
{
System.out.println("Whoops: " + e.getMessage());
System.exit(8);
}
finally
{
try
{
if (fos != null)
{
fos.close();
}
}
catch (IOException e)
{
System.out.println("Closing the stream failed, we have
issues.");
System.exit(9);
}
}
System.exit(0);
}
}
