You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by "Mihaylov, Dimitar" <Di...@prospect-fs.com> on 2012/07/10 15:06:57 UTC
Evaluating formulas containing references to external workbooks
Hi everyone,
I am writing an application that has to update cells in an excel spreadsheet, and then read values from a sheet in another workbook that contains reference to the first workbook, and should be updated appropriately. Is there any way of evaluating formulas across multiple workbooks, as it is obviously not as simple as evaluating a single workbook with no external references- just calling evaluateAll() on a FormulaEvaluator.
I tried using classes from org.apache.poi.ss.formula<http://us.mg4.mail.yahoo.com/org/apache/poi/ss/formula/package-summary.html> but my code seems not to be working, and there is not much documentation for this package.
......
CollaboratingWorkbooksEnvironment cwe = null;
try {
FileInputStream inp= new FileInputStream(dnvAccelerations_path);
XSSFWorkbook wb= new XSSFWorkbook(inp);
XSSFEvaluationWorkbook ewb= XSSFEvaluationWorkbook.create(wb);
IStabilityClassifier isc = null;
UDFFinder udf = null;
WorkbookEvaluator wbe= new WorkbookEvaluator(ewb, isc, udf);
FileInputStream inp2= new FileInputStream(dnvBlackbox_path);
XSSFWorkbook wb2= new XSSFWorkbook(inp2);
XSSFEvaluationWorkbook ewb2= XSSFEvaluationWorkbook.create(wb2);
WorkbookEvaluator wbe2= new WorkbookEvaluator(ewb2, isc, udf);
FileInputStream inp3= new FileInputStream(dnvReactions_path);
XSSFWorkbook wb3= new XSSFWorkbook(inp3);
XSSFEvaluationWorkbook ewb3= XSSFEvaluationWorkbook.create(wb3);
WorkbookEvaluator wbe3= new WorkbookEvaluator(ewb3, isc, udf);
String[] names= {"1.2.1 DNV Accelerations - LL.xlsm", "1.2.2 DNV BlackBox LL.xlsm", "1.2.3 DNV Reactions LL.xlsm"};
WorkbookEvaluator[] evaluators= {wbe, wbe2, wbe3};
cwe.setup(names, evaluators);
Sheet sheet= wb2.getSheet("Vessel Acc to Reactions");
Row row= sheet.getRow(28);
Cell cell= row.getCell(6);
System.out.println(cell.getCellFormula());
EvaluationSheet esheet2= ewb2.getSheet(0);
EvaluationCell ecell2= esheet2.getCell(28,6);
System.out.println(ecell2.getNumericCellValue());
ValueEval veval2= (NumberEval)((NumericValueEval)wbe2.evaluate(ecell2));
System.out.println(""+ veval2.toString());
.....................................
I also noticed that when getting the contents of the cell containing the formula with the external reference, the path to the external workbook is replaced by a number in square brackets followed by the name of the sheet: e.g
'[1]Vessel Accelerations'!$H$21 .
Any help or advice would be much appreciated, and I apologise if the topic has already been discussed but I couldn't find anything in the previous messages in the mail list.
Thanks
Dimitar
RE: Evaluating formulas containing references to external workbooks
Posted by "Mihaylov, Dimitar" <Di...@prospect-fs.com>.
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:Dimitar.Mihaylov@prospect-fs.com]
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:markbrdsly@tiscali.co.uk]
Sent: 10 July 2012 15:32
To: user@poi.apache.org
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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Evaluating formulas containing references to external workbooks
Posted by "Mihaylov, Dimitar" <Di...@prospect-fs.com>.
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:markbrdsly@tiscali.co.uk]
Sent: 10 July 2012 15:32
To: user@poi.apache.org
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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Evaluating formulas containing references to external workbooks
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Evaluating formulas containing references to external
workbooks
Posted by Nick Burch <ap...@gagravarr.org>.
On Sun, 10 Mar 2013, sudarshan89 wrote:
> Any idea when this feature will get implemented ?
Probably about 1-2 days after some kind soul sends in a suitable patch to
implement it...
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Evaluating formulas containing references to external workbooks
Posted by sudarshan89 <su...@outlook.com>.
Any idea when this feature will get implemented ?
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Evaluating-formulas-containing-references-to-external-workbooks-tp5710423p5712297.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org