http://blog.sina.com.cn/s/blog_6151984a0100sczi.html
当前EXCEL文件: workbook.xls
假设一个CELL的计算式如下:
| ='C:\testFB\[workbook1.xls]Test2'!B2+'C:\testFB\[workbook1.xls]测试1'!C3 |
1.不仅要打开当前EXCEL文档(本例是workbook.xls),也要打开所有计算式FORMULA引用的EXCEL文档(workbook1.xls)
不仅要为当前EXCEL文档建立HSSFFormulaEvaluator对象,也要为所有计算式FORMULA引用的EXCEL文档建立HSSFFormulaEvaluator对象
| String ROOT = "d:\\testFB\\";
String[] files = {"workbook.xls","workbook1.xls"};
HSSFWorkbook[] wbs = new HSSFWorkbook[files.length];
HSSFFormulaEvaluator[] evaluators =new HSSFFormulaEvaluator[files.length];
FileInputStream fileIn = null;
FileOutputStream fileOut = null;
for(int i=0;i<files.length;i++){
fileIn = new FileInputStream(files[i]);
POIFSFileSystem fs = new POIFSFileSystem(fileIn);
wbs[i] = new HSSFWorkbook(fs);
evaluators[i] = new HSSFFormulaEvaluator(wbs[i]);
}
|
2. 设置Evaluator对象环境
| HSSFFormulaEvaluator.setupEnvironment(files, evaluators); |
3. 对计算式进行Evalutor计算
|
for(int i=0;i<wbs[0].getNumberOfSheets();i++){
sheet1 = wbs[0].getSheetAt(i);
for (Cell cell : row) {
switch(cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
System.out.println("CELL_TYPE_STRING |"+cell.getRichStringCellValue().getString());
break;
case Cell.CELL_TYPE_NUMERIC:
if(DateUtil.isCellDateFormatted(cell)) {
System.out.println("CELL_TYPE_NUMERIC |"+cell.getDateCellValue());
} else {
System.out.println("CELL_TYPE_NUMERIC |"+cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_BOOLEAN:
System.out.println("CELL_TYPE_BOOLEAN |"+cell.getBooleanCellValue());
|