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();
		}
	}

}