Hi,
My problem is, I have a very complex XLS file (some 33,000 formulas,
unordered and scattered all over the place) which I was initially trying to
fully implement in Java. When the complexity of the document struck us, we
wondered if POI could do the job. So the idea is to:
* Read some input data from a text file
* Open the file via HSSF
* Feed all the inputs to the cells
* Tell HSSF to recalculate all cells
* Retrieve the value of some of the results.
The XLS file is then dismissed because we're saving these results to a
database.
So this is my question: is this a good idea? Is POI supposed to do such
things? Because so far, my tests have shown that the recalculation isn't
fully working: if I change the value of A1, and A2 and A3 have formulas that
depend on its value, only A2 is updated but A3 not. Of course this is a
trivial example: in reality, some cells have more than 20 levels of
dependency, sometimes the formulas refer to cells that are "before" them,
other times refer to cells that are after.
The process I'm using is as follows:
* Open the workbook
* Create a HSSFFormulaEvaluator
* Get the value of a cell (with getSheetAt, getRow, getCell)
* Set its value to some double
* Traverse all rows and all cells (using sheet.lastRowNum() and
row.getLastCellNum() and do an evaluateFormulaCell on each one
* Get the value of a result cell
And the value isn't even the same as Excel shows before changing the value
of the cell. I'm worried because for some process we're implementing a
MonteCarlo method, thus we're going to to this whole process 500 times,
retrieving the results each cycle, and plotting these points in graphs.
The actual code is as follows:
FileInputStream fis = new
FileInputStream("/Users/dariovasconcelos/Documents/desa/QA76/Bancomext/src_paramext/poi/riesgo4.xls");
HSSFWorkbook wb = new HSSFWorkbook(fis); //or new
HSSFFormulaEvaluator evaluator = new
HSSFFormulaEvaluator(wb);
BufferedWriter out = new BufferedWriter(new
FileWriter("/Users/dariovasconcelos/Documents/desa/QA76/Bancomext/src_paramext/poi/sinCambios.txt"));
HSSFSheet hoja = wb.getSheetAt(0);
HSSFRow renglon = hoja.getRow(686);
HSSFCell celda = renglon.getCell(8);
celda.setCellValue((double)0.483);
evaluator.notifySetFormula(celda);
evaluator.clearAllCachedResultValues();
n = recalculateAll(wb, evaluator, out, n);
And recalculateAll is coded like this:
private int recalculateAll(HSSFWorkbook wb,
HSSFFormulaEvaluator evaluator, BufferedWriter out, int
n) {
try {
// Recalculo todos los renglones
//for(int sheetNum = 0; sheetNum <
wb.getNumberOfSheets();sheetNum++) {
for(int sheetNum = 0; sheetNum < 1; sheetNum++) {
//int sheetNum =0;
HSSFSheet sheet = wb.getSheetAt(sheetNum);
for (int i = 0; i < sheet.getLastRowNum(); i++) {
HSSFRow r = sheet.getRow(i);
if (r == null)
continue;
for (int j=0; j < r.getLastCellNum(); j++) {
HSSFCell c = r.getCell(j);
if (c == null)
continue;
int tipo = c.getCellType();
int row = c.getRowIndex();
int col = c.getColumnIndex();
switch (tipo) {
case HSSFCell.CELL_TYPE_NUMERIC:
miPrint(out, n++, sheetNum, row, col ,
"" + c.getNumericCellValue());
break;
/*
case HSSFCell.CELL_TYPE_STRING:
miPrint(out, n++, sheetNum, row, col,
c.getStringCellValue());
break;
*/
case HSSFCell.CELL_TYPE_FORMULA:
miPrint(out, n++, sheetNum, row, col,
c.getCellFormula().replace("$",
""));
int tipoRes =
evaluator.evaluateFormulaCell(c);
if (tipoRes ==
HSSFCell.CELL_TYPE_NUMERIC)
miPrint(out, n++, sheetNum, row,
col,
"" +
c.getNumericCellValue());
else if (tipoRes ==
HSSFCell.CELL_TYPE_STRING)
miPrint(out, n++, sheetNum, row,
col,
c.getStringCellValue());
else if (tipoRes ==
HSSFCell.CELL_TYPE_ERROR)
miPrint(out, n++, sheetNum, row,
col,
"" +
c.getErrorCellValue());
/*
HSSFCell celdaTmp =
evaluator.evaluateInCell(c);
if (celdaTmp.getCellType()
== HSSFCell.CELL_TYPE_NUMERIC)
miPrint(out, n++, sheetNum, row,
col,
"" +
c.getNumericCellValue());
*/
break;
}
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
return n;
}
"miPrint" is simply a routine to print the value of the cell to a Buffered
Writer.
Regards,
Dario
--
Here's a rule I recommend: Never practice two vices at once.
- Tallulah Bankhead
