You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by negatron <pa...@gmail.com> on 2014/12/05 11:27:04 UTC
Setting up workbook environment XSSFFormulaEvaluator
Hello,
I try to evaluate the value of the formulas contained in my Workbook
"main.xlsx".
In these formulas, I have references to an external xlsx file ("test.xlsx").
When I try to run the method "evaluateAllFormulaCells(wbMain)", I get the
error: "Could not resolve external workbook name 'test.xlsx'. Workbook
environment has not been set up".
So I try to set up the environment like this:
> //Defining my elements
> String nameWbMain = "main.xlsx";
> String nameWbTest = "test.xlsx";
> XSSFWorkbook wbMain = new XSSFWorkbook(new FileInputStream(nameWbMain));
> XSSFWorkbook wbTest = new XSSFWorkbook(new FileInputStream(nameWbTest));
> XSSFFormulaEvaluator feMain = new XSSFFormulaEvaluator(wbMain);
> XSSFFormulaEvaluator feTest = new XSSFFormulaEvaluator(wbTest);
>
> //Trying to set up my environment
> Map
> <
> String, FormulaEvaluator
> >
> mapFe = new Hashtable
> <
> String, FormulaEvaluator
> >
> ();
> mapFe.put(nameWbTest, feTest);
> feMain.setupReferencesWorkbooks(mapFe);
>
> //Trying to evaluate the formulas
> feMain.evaluateAll();
But I get the same error message (environment not set up).
I am certainly doing something wrong but I can't figure out what.
Patrick
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Setting-up-workbook-environment-XSSFFormulaEvaluator-tp5717305.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: Setting up workbook environment XSSFFormulaEvaluator
Posted by negatron <pa...@gmail.com>.
Nick Burch-2 wrote
> Could you open a bug for this in bugzilla?
> http://issues.apache.org/bugzilla/buglist.cgi?product=POI
>
> If you attach your code there, along with the spreadsheets needed to drive
> it, then they won't get lost
I opened the bug 57332
(https://issues.apache.org/bugzilla/show_bug.cgi?id=57332) with the
attachements.
Thank you for your help!
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Setting-up-workbook-environment-XSSFFormulaEvaluator-tp5717305p5717346.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: Setting up workbook environment XSSFFormulaEvaluator
Posted by Nick Burch <ap...@gagravarr.org>.
On Fri, 5 Dec 2014, negatron wrote:
> As I said, I am quite new in Java programming and I don't really know what
> you mean by "write a small unit test".
> Anyway I tried to write a little piece of code to expose the problem.
> I don't konw how I can attach the Excel files but these are simple files and
> I tried to describe them as accurately as possible in the comments.
Could you open a bug for this in bugzilla?
http://issues.apache.org/bugzilla/buglist.cgi?product=POI
If you attach your code there, along with the spreadsheets needed to drive
it, then they won't get lost
I won't have a chance to look at it for a few weeks at least, but it's
possible that someone else will, which'd be great! If not, the only other
thing I can suggest is comparing how the unit tests for this area compare
with your use case, and see if you can spot workarounds from that
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Setting up workbook environment XSSFFormulaEvaluator
Posted by negatron <pa...@gmail.com>.
Nick Burch-2 wrote
>
> negatron wrote
>> It seems that it was the method "evaluateAll()" the cause of my error.
>> When I go through my worksheet evaluating the value of each cell, I don't
>> get any error but it doesn't work neither...
> Hmm, it should work both ways. Can you write a small unit test, possibly
> coupled with a small excel file or two, which shows the problem?
As I said, I am quite new in Java programming and I don't really know what
you mean by "write a small unit test".
Anyway I tried to write a little piece of code to expose the problem.
I don't konw how I can attach the Excel files but these are simple files and
I tried to describe them as accurately as possible in the comments.
The code:
> import java.io.FileInputStream;
> import java.io.FileOutputStream;
> import java.io.IOException;
> import java.util.HashMap;
> import java.util.Map;
>
> import org.apache.poi.ss.usermodel.Cell;
> import org.apache.poi.ss.usermodel.FormulaEvaluator;
> import org.apache.poi.ss.usermodel.Name;
> import org.apache.poi.ss.usermodel.Workbook;
> import org.apache.poi.xssf.usermodel.XSSFWorkbook;
>
>
> public class evaluateExternalReference {
> public static void main(String[] args) {
> Name nameTest, nameTestAfter;
>
> /**
> * In the following workbook, there are three named ranges defined :
> * 1) Test: link to the external workbook Test1, Sheet1, cell B2
> * 2) Test1: same link
> * 3) Test2: link to the external workbook Test2, Sheet1, cell B2
> *
> * The cell B2 contains the named range Test
> */
> String nameWb = "Main.xlsx";
>
> /**
> * In the following workbook there is only the String "Test2" in cell B2
> of Sheet1
> */
> String nameLink = "Test2.xlsx";
>
> /**
> * In file Test1 there is only the String "Test1" in cell B2 of Sheet1
> */
>
> /**
> * We will try to:
> * 1) Change the named range Test to match the named range Test2
> * 2) Evaluate the value of the cell B2 in file Main.xlsx
> */
> try{
> FileInputStream fichIn = new FileInputStream(nameWb);
> Workbook wb = new XSSFWorkbook(fichIn);
> Workbook wbLink = new XSSFWorkbook(new FileInputStream(nameLink));
> //Defining the evaluators
> FormulaEvaluator feWb =
> wb.getCreationHelper().createFormulaEvaluator();
> FormulaEvaluator feLink =
> wbLink.getCreationHelper().createFormulaEvaluator();
>
> //Value of the cell
> Cell cell = wb.getSheetAt(0).getRow(1).getCell(1);
> System.out.println(cell.getStringCellValue()); //"Test1"
>
> //Set up environment
> Map
> <
> String, FormulaEvaluator
> >
> ht = new HashMap
> <
> String, FormulaEvaluator
> >
> ();
> ht.put(nameLink,feLink);
> ht.put(nameWb,feWb);
> feWb.setupReferencedWorkbooks(ht);
>
> //Loading the named range
> nameTest = wb.getName("Test");
> nameTestAfter = wb.getName("Test2");
> //Updating the named range "Test" to match "Test2" (external workbook)
> nameTest.setRefersToFormula(nameTestAfter.getRefersToFormula());
>
> //Evaluating the formulas
> feWb.evaluate(cell);
> System.out.println(cell.getStringCellValue()); //Should normally be
> "Test2"
>
> fichIn.close();
>
> //Output in a new file
> FileOutputStream fichOut = new FileOutputStream("test.xlsx");
> wb.write(fichOut);
> fichOut.close();
> wb.close();
> wbLink.close();
>
> }catch(IOException e){
> e.printStackTrace();
> }catch(RuntimeException e){
> e.printStackTrace();
> }
> }
>
> }
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Setting-up-workbook-environment-XSSFFormulaEvaluator-tp5717305p5717309.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: Setting up workbook environment XSSFFormulaEvaluator
Posted by Nick Burch <ap...@gagravarr.org>.
On Fri, 5 Dec 2014, negatron wrote:
> Nick Burch-2 wrote
>> Try looking at the testReferencesToOtherWorkbooks() method in
>> TestXSSFFormulaEvaluation for a good example of doing it
>>
>> https://svn.apache.org/repos/asf/poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java
>
> It seems that it was the method "evaluateAll()" the cause of my error.
> When I go through my worksheet evaluating the value of each cell, I don't
> get any error but it doesn't work neither...
Hmm, it should work both ways. Can you write a small unit test, possibly
coupled with a small excel file or two, which shows the problem?
We can then use that to work out what's going wrong, along with using it
to check it stays fixed once resolved!
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Setting up workbook environment XSSFFormulaEvaluator
Posted by negatron <pa...@gmail.com>.
Nick Burch-2 wrote
> Try looking at the testReferencesToOtherWorkbooks() method in
> TestXSSFFormulaEvaluation for a good example of doing it
>
> https://svn.apache.org/repos/asf/poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java
It seems that it was the method "evaluateAll()" the cause of my error.
When I go through my worksheet evaluating the value of each cell, I don't
get any error but it doesn't work neither...
When I open my workbook, I have cells with formulas using named range (to
external workbook: test.xlsx).
In my Java code, I change the reference of this named range to another
workbook (test2.xlsx).
But then when I evaluate the value of each cell, it keeps using the old
reference.
I suppose that the evaluation doesn't "evaluate" the value of the new named
range.
I really don't see how I can solve this problem.
I tried to close my workbook before reopen it and evaluate the value of the
formulas.
But I get an error saying that the workbook "test.xlsx" (old reference) is
not a valid workbook in my environment. The error sais also that the valid
references are "main.xlsx" and "test2.xlsx", which is correct.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Setting-up-workbook-environment-XSSFFormulaEvaluator-tp5717305p5717307.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: Setting up workbook environment XSSFFormulaEvaluator
Posted by Nick Burch <ap...@gagravarr.org>.
On Fri, 5 Dec 2014, negatron wrote:
> Hello,
>
> I try to evaluate the value of the formulas contained in my Workbook
> "main.xlsx".
> In these formulas, I have references to an external xlsx file ("test.xlsx").
>
> When I try to run the method "evaluateAllFormulaCells(wbMain)", I get the
> error: "Could not resolve external workbook name 'test.xlsx'. Workbook
> environment has not been set up".
> So I try to set up the environment like this:
Try looking at the testReferencesToOtherWorkbooks() method in
TestXSSFFormulaEvaluation for a good example of doing it
https://svn.apache.org/repos/asf/poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org