Hi,
It looks like evaluating formulas that involve external references has not been
implemented for XSSFWorkbook yet. However, it does work for HSSFWorkbook and it
is pretty straightforward:
In this sample code I just set up the environment for working with three
HSSFWorkbooks, I make changes in some of the cells, then evaluate the formulas
in the other workbooks to update them appropriately, and finally write them to
a file. :
****************************************************************************************
InputStream inp1= new FileInputStream("wb1.xls");
Workbook wb1= WorkbookFactory.create(inp1);
InputStream inp2= new FileInputStream("wb2.xls");
Workbook wb2= WorkbookFactory.create(inp2);
InputStream inp3= new FileInputStream("wb3.xls");
Workbook wb3= WorkbookFactory.create(inp3);
HSSFFormulaEvaluator fe1= new
HSSFFormulaEvaluator((HSSFWorkbook)wb1);
HSSFFormulaEvaluator fe2= new
HSSFFormulaEvaluator((HSSFWorkbook)wb2);
HSSFFormulaEvaluator fe3= new
HSSFFormulaEvaluator((HSSFWorkbook)wb3);
String[] names= {"wb1.xls","wb2.xls", "wb3"};
HSSFFormulaEvaluator[] evaluators={fe1, fe2, fe3};
HSSFFormulaEvaluator.setupEnvironment(names, evaluators);
//HERE UPDATE SOME OF THE CELLS IN THE WORKBOOKS
//Now evaluate the formulas in the workbooks
fe1.evaluateAll();
fe2.evaluateAll();
fe3.evaluateAll();
FileOutputStream fileOut1= new FileOutputStream("wb1.xls");
wb1.write(fileOut1);
fileOut1.close();
FileOutputStream fileOut2= new FileOutputStream("wb2.xls");
wb2.write(fileOut2);
fileOut2.close();
FileOutputStream fileOut3= new FileOutputStream("wb3.xls");
wb3.write(fileOut3);
fileOut3.close();
*******************************************************************************************************
ForkedEvaluator can be used only with HSSFWorkbooks as well. However, you have
to know which cell you want to evaluate, and evaluate it separately, you cannot
just call evaluateAll():
**************************************************************************************
InputStream inp1= new FileInputStream("wb1.xls");
Workbook wb1= WorkbookFactory.create(inp1);
InputStream inp2= new FileInputStream("wb2.xls");
Workbook wb2= WorkbookFactory.create(inp2);
//assume that wb1 has a numerical cell at sheet 0 and update it to 4
wb1.getSheetAt(0).getRow(0).getCell(0).setCellValue(4);
ForkedEvaluator fe1= ForkedEvaluator.create(wb1, null, null);
ForkedEvaluator fe2= ForkedEvaluator.create(wb2, null, null);
String[] names= {"wb1.xls", "wb2.xls"};
ForkedEvaluator[] evaluators= {fe1, fe2};
ForkedEvaluator.setupEnvironment(names, evaluators);
//assume that cell at index(1,0) from wb2 is referencing the
updated cell from wb1 above, and evaluate it
fe2.evaluate("Sheet1",1,0);
FileOutputStream fileOut1= new FileOutputStream("wb1.xls");
wb1.write(fileOut1);
fileOut1.close();
FileOutputStream fileOut2= new FileOutputStream("wb2.xls");
wb2.write(fileOut2);
fileOut2.close();
Cheers,
Dimitar
********************************************************************************************
-----Original Message-----
From: Mihaylov, Dimitar [mailto:[email protected]]
Sent: 10 July 2012 15:54
To: POI Users List
Subject: RE: Evaluating formulas containing references to external workbooks
Thanks for the advice. I will try using the ForkedEvaluator and if it works, I
will provide some example code for the Quick Guide.
Thanks,
Dimitar
-----Original Message-----
From: Mark Beardsley [mailto:[email protected]]
Sent: 10 July 2012 15:32
To: [email protected]
Subject: Re: Evaluating formulas containing references to external workbooks
I think that you need to take a look at the ForkedEvaluator class -
http://poi.apache.org/apidocs/org/apache/poi/ss/formula/eval/forked/ForkedEvaluator.html
I have not had a play with this for a very long time now and cannot promise
that it will do what you require but there is a static setupEnvornment
method which is described thus;
*static void setupEnvironment(java.lang.String[] workbookNames,
ForkedEvaluator[] evaluators)
Coordinates several formula evaluators together so that formulas
that involve external references
can be evaluated.*
I am going to guess that you need to create a ForkedEvaluator for each
workbook and then pass these objects as an array, along with an array of the
workbook's names, to this method. That is a guess though as I have not had
the need nor the opportunity to look into using this class. If you do make
any progress, would you consider posting some code that may be included into
the Quick Guide?
Yours
Mark B
--
View this message in context:
http://apache-poi.1045710.n5.nabble.com/Evaluating-formulas-containing-references-to-external-workbooks-tp5710423p5710426.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
