Dario,
You should try a recent svn. There were changes to improve named
ranges across separate worksheets.
I think you have a good idea. It allows all types of advantages. We
are doing something similar although we are focused now on the
creation side, we recognize the possibilities of reading in the users
adjustments.
You do need to be careful if your workbook uses any User Defined
Functions. These aren't implemented, I do begin to wonder if a plug-in
technology might allow people to roll in their own implementations of
custom functions.
Regards,
Dave
On Jan 6, 2009, at 4:47 PM, Darío Vasconcelos wrote:
Hi Josh,
I know, and I've done simple proof-of-concept tests where formulas are
evaluated and values are updated automatically, even if there is a
number of
formulas in the middle that need updating in the way.
But it's not working for this specific sheet, and now I'm
questioning myself
if HSSF should be used as an "in-server" Excel engine. Most of the
questions
that I've read in the mailing list regard creating Excel files from
scratch,
or modifying existing ones so the final user opens them later in
Excel. In
my case, all I want is HSSF to recalculate values based on existing
formulas
on an existing file and let me retrieve the result.
Regards,
On Tue, Jan 6, 2009 at 4:21 PM, Josh Micich <[email protected]>
wrote:
Hello Dario,
So this is my question: is this a good idea? Is POI supposed to do
such
things?
POI has supported formula evaluation since version 3. There have
been many
improvements as of late including
Speed optimisations
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
--
Here's a rule I recommend: Never practice two vices at once.
- Tallulah Bankhead
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
