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: [email protected]
For additional commands, e-mail: [email protected]
