You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Darío Vasconcelos <da...@gmail.com> on 2009/01/06 21:02:22 UTC
Conceptual question, using HSSF as a recalculation engine
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
Re: Conceptual question, using HSSF as a recalculation engine
Posted by Darío Vasconcelos <da...@gmail.com>.
Thanks Dave,
actually it took me a while to clean this spreadsheet and I believe it now
doesn't have any user defined functions, but I'll double check.
So cells refering to yet-unparsed formulas shouldn't be a problem? Does HSSF
read all the file and create a graph or something similar, in order to
figure out which cells affect which cells? Or is it more of a brute force
approach?
BTW, I tried turning on the debug flag in log4j, but the amount of
information is overwhelming and I didn't know where to start.
Regards,
On Tue, Jan 6, 2009 at 5:03 PM, David Fisher <df...@jmlafferty.com> wrote:
> 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 <jo...@gmail.com>
>> 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: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
--
Here's a rule I recommend: Never practice two vices at once.
- Tallulah Bankhead
Re: Conceptual question, using HSSF as a recalculation engine
Posted by David Fisher <df...@jmlafferty.com>.
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 <jo...@gmail.com>
> 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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Conceptual question, using HSSF as a recalculation engine
Posted by Darío Vasconcelos <da...@gmail.com>.
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 <jo...@gmail.com> 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
Re: Conceptual question, using HSSF as a recalculation engine
Posted by Josh Micich <jo...@gmail.com>.
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
>
Re: Conceptual question, using HSSF as a recalculation engine
Posted by Peter Arrenbrecht <pe...@gmail.com>.
2009/1/6 Darío Vasconcelos <da...@gmail.com>:
> 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.
Sorry for the late answer, but you might want to look at
http://formulacompiler.org. It compiles the formulae in an
Excel/OpenOffice spreadsheet to a JVM byte code implementation of the
same computation.
-parren
> 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
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Conceptual question, using HSSF as a recalculation engine
Posted by Josh Micich <jo...@gmail.com>.
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
Re: Conceptual question, using HSSF as a recalculation engine
Posted by Darío Vasconcelos <da...@gmail.com>.
Hi Josh,
thanks for your response and your insight. In my particular case, the XLS
file is too complex, so much that no one knows anymore how it works.
Additionally, the advantage of letting the end user change the business
rules directly in the Excel source file is very attractive.This is why HSSF
seemed like a good idea.
I understand that formula dependency tracking is a complex task. I'm going
to try to reproduce the behavior on a simple XLS file to make it a little
bit easier to do the testing. I'm thinking maybe the actual problem are
named fields, because at my first tests some named fields caused exceptions.
What I mean by "after" an "before" is precisely row/column placement in the
spreadsheet. My guess was that it made no difference, but when all is
failing, anything looks suspicious.
Thanks again for your tips and advice. I might indeed add you to my chat
roster if I need some assistance, thanks.
Dario.
On Tue, Jan 6, 2009 at 6:33 PM, Josh Micich <jo...@gildedtree.com> 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. Specifically
> relating
> to the functionality you are describing, there have been many improvements
> as of late including:
> - New implementations for missing built-in functions
> - Fixes for bugs in existing built-in functions
> - Lazy evaluation of area and reference Evals (speed improvement)
> - Compact representation of formula Ptg arrays and non-expansion of shared
> formulas (memory improvement)
> - Dependency-aware formula result caching (speed improvement)
>
> Whether what you are doing is a good idea is a very subjective question. A
> java-only based evaluation solution may be better than one that uses (java
> together with) formulas from an XLS file. Ease of maintenance is a major
> consideration. Java is arguably a better language than Excel formulas.
> Java
> source is easier to browse and refactor. Having one less language in use
> also helps for better focus of skills, fewer moving parts and less plumbing
> code. Another consideration is memory footprint and execution speed. POI
> has improved much lately, but it is still relatively easy to implement
> formulas directly in java that outperform formulas interpreted via POI.
>
> Having said that, there may be compelling reasons why a POI/XLS based
> evaluation solution is chosen:
> - The people who maintain the business logic currently in the Excel
> spreadsheet(s) may not comfortable in maintaining that logic anywhere else.
> - The logic in the spreadsheet may be so large/complex that it is too
> expensive to port (the spreadsheet becomes a 'black box').
>
> For what it's worth, the company I work for has used POI for exactly this
> kind of solution, so we've gained a lot of insight into some of the
> considerations (beyond core POI functionality). I'm always happy to chat
> if
> you want to contact me directly.
>
> > 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.
>
> This sounds like a bug to me. This is new functionality, so a bug wouldn't
> be surprising.
>
> Formula dependency tracking was a complex addition to POI (necessary for
> performance). It works by caching evaluation results for every cell
> evaluated (directly and indirectly). For each evaluated cell, POI
> remembers
> which cells were used ('sensitive input cells') to calculate the result.
> As
> each cell is 'used' during a cell evaluation, POI also remembers which
> formula cell(s) are 'consuming' the value. A client of the formula
> evaluator can notify the evaluator that a cell has changed. When this
> happens, the formula evaluator recurses up the tree of 'consuming cells'
> and
> clears the cached value and dependency information. Another subtle
> use-case
> is when a formula cell changes definition. In this case, the the formula
> evaluator needs to also clear the 'consuming cell' link from all 'sensitive
> input cells' of the changing formula cell.
>
>
> As you can imagine, testing this functionality for correctness is tedious.
> There are existing junit tests (TestEvaluationCache) that seem to show very
> similar use-cases working but it is always useful to receive test cases
> that
> show new bugs. Can you make a simple example which demonstrates the
> behaviour you described?
>
>
> > 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.
>
> I am not sure what you mean by "before" and "after" here. If you are just
> talking about row/column placement in the spreadsheet, this is not a
> problem
> - POI dependency tracking work by unique cell location, and does not
> consider direction.
>
> > evaluator.notifySetFormula(celda);
> > evaluator.clearAllCachedResultValues();
>
> BTW - you never need to call both of these methods together. There are two
> choices:
> (a) track every changing input cell and call notifySetFormula() before
> evaluating. If you do this, clearAllCachedResultValues() is not needed.
> (b) don't bother traking cell changes individually, just call
> clearAllCachedResultValues() after making changes but before evaluating.
> Approach (a) works if you are changing formulas and values of cells one at
> a
> time. It is also preferred because the performance hit with
> clearAllCachedResultValues() may be large. On the other hand, if you make
> big changes like adding or removing sheets, or shifting rows, you need to
> use approach (b).
> (Perhaps the javadocs need improving.)
>
> Hope this helps,
> Josh
>
--
Here's a rule I recommend: Never practice two vices at once.
- Tallulah Bankhead
Re: Conceptual question, using HSSF as a recalculation engine
Posted by Josh Micich <jo...@gildedtree.com>.
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. Specifically relating
to the functionality you are describing, there have been many improvements
as of late including:
- New implementations for missing built-in functions
- Fixes for bugs in existing built-in functions
- Lazy evaluation of area and reference Evals (speed improvement)
- Compact representation of formula Ptg arrays and non-expansion of shared
formulas (memory improvement)
- Dependency-aware formula result caching (speed improvement)
Whether what you are doing is a good idea is a very subjective question. A
java-only based evaluation solution may be better than one that uses (java
together with) formulas from an XLS file. Ease of maintenance is a major
consideration. Java is arguably a better language than Excel formulas. Java
source is easier to browse and refactor. Having one less language in use
also helps for better focus of skills, fewer moving parts and less plumbing
code. Another consideration is memory footprint and execution speed. POI
has improved much lately, but it is still relatively easy to implement
formulas directly in java that outperform formulas interpreted via POI.
Having said that, there may be compelling reasons why a POI/XLS based
evaluation solution is chosen:
- The people who maintain the business logic currently in the Excel
spreadsheet(s) may not comfortable in maintaining that logic anywhere else.
- The logic in the spreadsheet may be so large/complex that it is too
expensive to port (the spreadsheet becomes a 'black box').
For what it's worth, the company I work for has used POI for exactly this
kind of solution, so we've gained a lot of insight into some of the
considerations (beyond core POI functionality). I'm always happy to chat if
you want to contact me directly.
> 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.
This sounds like a bug to me. This is new functionality, so a bug wouldn't
be surprising.
Formula dependency tracking was a complex addition to POI (necessary for
performance). It works by caching evaluation results for every cell
evaluated (directly and indirectly). For each evaluated cell, POI remembers
which cells were used ('sensitive input cells') to calculate the result. As
each cell is 'used' during a cell evaluation, POI also remembers which
formula cell(s) are 'consuming' the value. A client of the formula
evaluator can notify the evaluator that a cell has changed. When this
happens, the formula evaluator recurses up the tree of 'consuming cells' and
clears the cached value and dependency information. Another subtle use-case
is when a formula cell changes definition. In this case, the the formula
evaluator needs to also clear the 'consuming cell' link from all 'sensitive
input cells' of the changing formula cell.
As you can imagine, testing this functionality for correctness is tedious.
There are existing junit tests (TestEvaluationCache) that seem to show very
similar use-cases working but it is always useful to receive test cases that
show new bugs. Can you make a simple example which demonstrates the
behaviour you described?
> 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.
I am not sure what you mean by "before" and "after" here. If you are just
talking about row/column placement in the spreadsheet, this is not a problem
- POI dependency tracking work by unique cell location, and does not
consider direction.
> evaluator.notifySetFormula(celda);
> evaluator.clearAllCachedResultValues();
BTW - you never need to call both of these methods together. There are two
choices:
(a) track every changing input cell and call notifySetFormula() before
evaluating. If you do this, clearAllCachedResultValues() is not needed.
(b) don't bother traking cell changes individually, just call
clearAllCachedResultValues() after making changes but before evaluating.
Approach (a) works if you are changing formulas and values of cells one at a
time. It is also preferred because the performance hit with
clearAllCachedResultValues() may be large. On the other hand, if you make
big changes like adding or removing sheets, or shifting rows, you need to
use approach (b).
(Perhaps the javadocs need improving.)
Hope this helps,
Josh
Re: Conceptual question, using HSSF as a recalculation engine
Posted by David Fisher <df...@jmlafferty.com>.
Anthony,
> Must agree with you about HSSF; I think it is - to quote a comic
> hero of mine - a 'cracking' API. The ideal, I think, would be to
> split it into two streams; one concerned with supporting a product
> that allows us to create and read BIFF8 files and another that deals
> with formulae as it alone is a very challenging aspect and something
> I associate more with the Excel the application than the files it
> produces. The first could be used to parse Excel files, creating a
> series of objects that together represent the file. This could be
> passed to the latter - the calculation engine - that would support
> performing operations with the sheet and any formulae it contained.
Actually we are combining OOXML and BIFF - HSSF / XSSF - http://poi.apache.org/spreadsheet/index.html
I do think that there are probably a huge number of spreadsheets out
there used to collect information AND there is a huge need to pull
that information out in an organized manner.
There is good reason to be able to evaluate on the server, if only to
assure that the spreadsheet is consistent.
I wonder if a simple Servlet would be a good example. UPLOAD an xls /
xlsx. Recalc - for any number of cycles. Display named ranges.
Download resulting file. This could be a handy sample tool that
someone could build on for their particular application.
Another reason to be able to evaluate the formulas and functions used
in a spreadsheet could be to look for and possibly disable UDFs to
follow a clear security policy even if the spreadsheet is actually
used in OpenOffice, OpenOffice.org, or Microsoft Office.
> Understand about OpenOffice - and I have to admit that it can be
> slow; certainly connecting to OpenOffice or bootstrapping as they
> call it takes a couple of seconds. Will keep my eyes open regarding
> other solutions. Finally, have you thought about working with Nick
> and the others on developing POI's formula support? Maybe your
> company would support POI or allow you to work on and then submit
> patches, that way we all win.
Before Yegor and I started using POI we created simple "CVS" style XLS
and made PPT using PICT file imports driven by a Java COM bridge. So,
we went and improved HSLF to do what we needed.
The current set of active committers to the project represent really
the second and third "generation". We are trying to keep this a
crackling API!
All contributions are welcome. Examples and documentation are always
appreciated. Submit a patch to bugzilla.
Best Regards,
Dave
>
>
> --- On Wed, 1/7/09, Darío Vasconcelos <da...@gmail.com>
> wrote:
> From: Darío Vasconcelos <da...@gmail.com>
> Subject: Re: Conceptual question, using HSSF as a recalculation engine
> To: "POI Users List" <us...@poi.apache.org>, pythonaddict@yahoo.com
> Date: Wednesday, January 7, 2009, 7:19 AM
>
> Hi,
>
> actually I've also been looking into using UNO, seems promising, the
> only
> problem being the platform: I need AIX and there is no official
> support for
> it. But in Windows it works, all I need is to do some other tests.
>
> But I would prefer to use POI since it seems to me it is a "cleaner"
> solution: with OpenOffice, the number of components is daunting, and
> the
> documentation is at its best sparse. Using HSSF sounds like a better
> idea
> but as you mention, maybe it is an API more meant to creating and
> reading
> files. I'm also looking at COM but it would obviously force us to use
> Windows at some point at the backend.
>
> Please do send me any URLs that you find because I've looked at a
> couple of
> commercial products with not very convincing results.
>
> Regards,
>
>
> On Wed, Jan 7, 2009 at 1:40 AM, Anthony Andrews
> <py...@yahoo.com>wrote:
>
>> Hello David,
>>
>> May I throw a spanner into the works.
>>
>> To my mind, HSSF/HSSF is an API that is used to create or read
>> files. I
>> would argue that if you want a calculation engine then there are
>> other
>> solutions and the one I would look at - well two really - is either
>> controling Excel itself via OLE/COM or installing OpenOffice and
>> using UNO
>> (Unoversal Network Objects) to accomplish a similar task. The
>> advantages
> UNO
>> has over OLE is that the OpenOffice API supports full access via
>> Java (and
>> other languages) and OpenOffice itself is platform independent. Using
> either
>> of these approaches would allow you to interact with a live, running
>> application and to take advatage of the calculation engine built into
> those
>> pieces of software.
>>
>> Alternatively, there are pieces of software that I have seen kicking
> around
>> that claim to emulate Excel's support for formulae. If I come across
> one
>> again this morning, I will send you the URL.
>>
>> --- On Tue, 1/6/09, Darío Vasconcelos <da...@gmail.com>
> wrote:
>> From: Darío Vasconcelos <da...@gmail.com>
>> Subject: Conceptual question, using HSSF as a recalculation engine
>> To: user@poi.apache.org
>> Date: Tuesday, January 6, 2009, 12:02 PM
>>
>> 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
>>
>>
>>
>>
>>
>
>
>
> --
> Here's a rule I recommend: Never practice two vices at once.
> - Tallulah Bankhead
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Conceptual question, using HSSF as a recalculation engine
Posted by Anthony Andrews <py...@yahoo.com>.
Must agree with you about HSSF; I think it is - to quote a comic hero of mine - a 'cracking' API. The ideal, I think, would be to split it into two streams; one concerned with supporting a product that allows us to create and read BIFF8 files and another that deals with formulae as it alone is a very challenging aspect and something I associate more with the Excel the application than the files it produces. The first could be used to parse Excel files, creating a series of objects that together represent the file. This could be passed to the latter - the calculation engine - that would support performing operations with the sheet and any formulae it contained.
Understand about OpenOffice - and I have to admit that it can be slow; certainly connecting to OpenOffice or bootstrapping as they call it takes a couple of seconds. Will keep my eyes open regarding other solutions. Finally, have you thought about working with Nick and the others on developing POI's formula support? Maybe your company would support POI or allow you to work on and then submit patches, that way we all win.
--- On Wed, 1/7/09, Darío Vasconcelos <da...@gmail.com> wrote:
From: Darío Vasconcelos <da...@gmail.com>
Subject: Re: Conceptual question, using HSSF as a recalculation engine
To: "POI Users List" <us...@poi.apache.org>, pythonaddict@yahoo.com
Date: Wednesday, January 7, 2009, 7:19 AM
Hi,
actually I've also been looking into using UNO, seems promising, the only
problem being the platform: I need AIX and there is no official support for
it. But in Windows it works, all I need is to do some other tests.
But I would prefer to use POI since it seems to me it is a "cleaner"
solution: with OpenOffice, the number of components is daunting, and the
documentation is at its best sparse. Using HSSF sounds like a better idea
but as you mention, maybe it is an API more meant to creating and reading
files. I'm also looking at COM but it would obviously force us to use
Windows at some point at the backend.
Please do send me any URLs that you find because I've looked at a couple of
commercial products with not very convincing results.
Regards,
On Wed, Jan 7, 2009 at 1:40 AM, Anthony Andrews
<py...@yahoo.com>wrote:
> Hello David,
>
> May I throw a spanner into the works.
>
> To my mind, HSSF/HSSF is an API that is used to create or read files. I
> would argue that if you want a calculation engine then there are other
> solutions and the one I would look at - well two really - is either
> controling Excel itself via OLE/COM or installing OpenOffice and using UNO
> (Unoversal Network Objects) to accomplish a similar task. The advantages
UNO
> has over OLE is that the OpenOffice API supports full access via Java (and
> other languages) and OpenOffice itself is platform independent. Using
either
> of these approaches would allow you to interact with a live, running
> application and to take advatage of the calculation engine built into
those
> pieces of software.
>
> Alternatively, there are pieces of software that I have seen kicking
around
> that claim to emulate Excel's support for formulae. If I come across
one
> again this morning, I will send you the URL.
>
> --- On Tue, 1/6/09, Darío Vasconcelos <da...@gmail.com>
wrote:
> From: Darío Vasconcelos <da...@gmail.com>
> Subject: Conceptual question, using HSSF as a recalculation engine
> To: user@poi.apache.org
> Date: Tuesday, January 6, 2009, 12:02 PM
>
> 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
>
>
>
>
>
--
Here's a rule I recommend: Never practice two vices at once.
- Tallulah Bankhead
Re: Conceptual question, using HSSF as a recalculation engine
Posted by Darío Vasconcelos <da...@gmail.com>.
Hi,
actually I've also been looking into using UNO, seems promising, the only
problem being the platform: I need AIX and there is no official support for
it. But in Windows it works, all I need is to do some other tests.
But I would prefer to use POI since it seems to me it is a "cleaner"
solution: with OpenOffice, the number of components is daunting, and the
documentation is at its best sparse. Using HSSF sounds like a better idea
but as you mention, maybe it is an API more meant to creating and reading
files. I'm also looking at COM but it would obviously force us to use
Windows at some point at the backend.
Please do send me any URLs that you find because I've looked at a couple of
commercial products with not very convincing results.
Regards,
On Wed, Jan 7, 2009 at 1:40 AM, Anthony Andrews <py...@yahoo.com>wrote:
> Hello David,
>
> May I throw a spanner into the works.
>
> To my mind, HSSF/HSSF is an API that is used to create or read files. I
> would argue that if you want a calculation engine then there are other
> solutions and the one I would look at - well two really - is either
> controling Excel itself via OLE/COM or installing OpenOffice and using UNO
> (Unoversal Network Objects) to accomplish a similar task. The advantages UNO
> has over OLE is that the OpenOffice API supports full access via Java (and
> other languages) and OpenOffice itself is platform independent. Using either
> of these approaches would allow you to interact with a live, running
> application and to take advatage of the calculation engine built into those
> pieces of software.
>
> Alternatively, there are pieces of software that I have seen kicking around
> that claim to emulate Excel's support for formulae. If I come across one
> again this morning, I will send you the URL.
>
> --- On Tue, 1/6/09, Darío Vasconcelos <da...@gmail.com> wrote:
> From: Darío Vasconcelos <da...@gmail.com>
> Subject: Conceptual question, using HSSF as a recalculation engine
> To: user@poi.apache.org
> Date: Tuesday, January 6, 2009, 12:02 PM
>
> 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
>
>
>
>
>
--
Here's a rule I recommend: Never practice two vices at once.
- Tallulah Bankhead
Re: Conceptual question, using HSSF as a recalculation engine
Posted by Anthony Andrews <py...@yahoo.com>.
Hello David,
May I throw a spanner into the works.
To my mind, HSSF/HSSF is an API that is used to create or read files. I would argue that if you want a calculation engine then there are other solutions and the one I would look at - well two really - is either controling Excel itself via OLE/COM or installing OpenOffice and using UNO (Unoversal Network Objects) to accomplish a similar task. The advantages UNO has over OLE is that the OpenOffice API supports full access via Java (and other languages) and OpenOffice itself is platform independent. Using either of these approaches would allow you to interact with a live, running application and to take advatage of the calculation engine built into those pieces of software.
Alternatively, there are pieces of software that I have seen kicking around that claim to emulate Excel's support for formulae. If I come across one again this morning, I will send you the URL.
--- On Tue, 1/6/09, Darío Vasconcelos <da...@gmail.com> wrote:
From: Darío Vasconcelos <da...@gmail.com>
Subject: Conceptual question, using HSSF as a recalculation engine
To: user@poi.apache.org
Date: Tuesday, January 6, 2009, 12:02 PM
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