Line 0
Link Here
|
|
|
1 |
package org.apache.poi.ss.formula; |
2 |
|
3 |
import java.io.File; |
4 |
import java.io.FileInputStream; |
5 |
import java.io.IOException; |
6 |
import java.io.InputStream; |
7 |
|
8 |
import org.apache.poi.hssf.usermodel.HSSFCell; |
9 |
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator; |
10 |
import org.apache.poi.hssf.usermodel.HSSFRow; |
11 |
import org.apache.poi.hssf.usermodel.HSSFSheet; |
12 |
import org.apache.poi.hssf.usermodel.HSSFWorkbook; |
13 |
import org.apache.poi.ss.usermodel.Cell; |
14 |
import org.apache.poi.ss.usermodel.FormulaEvaluator; |
15 |
|
16 |
import junit.framework.TestCase; |
17 |
|
18 |
public class TestMissingWorkbook extends TestCase { |
19 |
private static final String MAIN_WORKBOOK_FILENAME = "main.xls"; |
20 |
private static final String SOURCE_DUMMY_WORKBOOK_FILENAME = "source_dummy.xls"; |
21 |
private static final String SOURCE_WORKBOOK_FILENAME = "source.xls"; |
22 |
|
23 |
private static final String propertyKey = WorkbookEvaluator.class.getName() + ".IGNORE_MISSING_WORKBOOKS"; |
24 |
|
25 |
private HSSFWorkbook mainWorkbook; |
26 |
private HSSFWorkbook sourceWorkbook; |
27 |
|
28 |
@Override |
29 |
protected void setUp() throws Exception { |
30 |
mainWorkbook = openWorkbook("test-data/spreadsheet/missing_workbook/"+MAIN_WORKBOOK_FILENAME); |
31 |
sourceWorkbook = openWorkbook("test-data/spreadsheet/missing_workbook/"+SOURCE_WORKBOOK_FILENAME); |
32 |
|
33 |
assertNotNull(mainWorkbook); |
34 |
assertNotNull(sourceWorkbook); |
35 |
} |
36 |
|
37 |
private HSSFWorkbook openWorkbook(String pFileName) throws IOException { |
38 |
File lSourceFile = new File(pFileName); |
39 |
assertTrue(pFileName + " does not exist!", lSourceFile.exists()); |
40 |
|
41 |
InputStream lSourceFileInputStream = null; |
42 |
try { |
43 |
lSourceFileInputStream = new FileInputStream(lSourceFile); |
44 |
|
45 |
HSSFWorkbook lWorrkbook = new HSSFWorkbook(lSourceFileInputStream); |
46 |
|
47 |
return lWorrkbook; |
48 |
} finally { |
49 |
if (lSourceFileInputStream != null) { |
50 |
lSourceFileInputStream.close(); |
51 |
} |
52 |
} |
53 |
} |
54 |
|
55 |
public void testMissingWorkbookMissing() throws IOException { |
56 |
FormulaEvaluator evaluator = mainWorkbook.getCreationHelper().createFormulaEvaluator(); |
57 |
|
58 |
HSSFSheet lSheet = mainWorkbook.getSheetAt(0); |
59 |
HSSFRow lARow = lSheet.getRow(0); |
60 |
HSSFCell lA1Cell = lARow.getCell(0); |
61 |
|
62 |
assertEquals(Cell.CELL_TYPE_FORMULA, lA1Cell.getCellType()); |
63 |
try { |
64 |
evaluator.evaluateFormulaCell(lA1Cell); |
65 |
fail("Missing external workbook reference exception expected!"); |
66 |
}catch(RuntimeException re) { |
67 |
assertTrue("Unexpected exception: " + re, re.getMessage().indexOf(SOURCE_DUMMY_WORKBOOK_FILENAME) != -1); |
68 |
} |
69 |
} |
70 |
|
71 |
public void testMissingWorkbookMissingOverride() throws IOException { |
72 |
HSSFSheet lSheet = mainWorkbook.getSheetAt(0); |
73 |
HSSFCell lA1Cell = lSheet.getRow(0).getCell(0); |
74 |
HSSFCell lB1Cell = lSheet.getRow(1).getCell(0); |
75 |
HSSFCell lC1Cell = lSheet.getRow(2).getCell(0); |
76 |
|
77 |
assertEquals(Cell.CELL_TYPE_FORMULA, lA1Cell.getCellType()); |
78 |
assertEquals(Cell.CELL_TYPE_FORMULA, lB1Cell.getCellType()); |
79 |
assertEquals(Cell.CELL_TYPE_FORMULA, lC1Cell.getCellType()); |
80 |
|
81 |
FormulaEvaluator evaluator = mainWorkbook.getCreationHelper().createFormulaEvaluator(); |
82 |
|
83 |
System.setProperty(propertyKey, Boolean.toString(true)); |
84 |
assertEquals(Cell.CELL_TYPE_NUMERIC, evaluator.evaluateFormulaCell(lA1Cell)); |
85 |
assertEquals(Cell.CELL_TYPE_STRING, evaluator.evaluateFormulaCell(lB1Cell)); |
86 |
assertEquals(Cell.CELL_TYPE_BOOLEAN, evaluator.evaluateFormulaCell(lC1Cell)); |
87 |
|
88 |
assertEquals(10.0d, lA1Cell.getNumericCellValue(), 0.00001d); |
89 |
assertEquals("POI rocks!", lB1Cell.getStringCellValue()); |
90 |
assertEquals(true, lC1Cell.getBooleanCellValue()); |
91 |
} |
92 |
|
93 |
|
94 |
public void testExistingWorkbook() throws IOException { |
95 |
HSSFSheet lSheet = mainWorkbook.getSheetAt(0); |
96 |
HSSFCell lA1Cell = lSheet.getRow(0).getCell(0); |
97 |
HSSFCell lB1Cell = lSheet.getRow(1).getCell(0); |
98 |
HSSFCell lC1Cell = lSheet.getRow(2).getCell(0); |
99 |
|
100 |
assertEquals(Cell.CELL_TYPE_FORMULA, lA1Cell.getCellType()); |
101 |
assertEquals(Cell.CELL_TYPE_FORMULA, lB1Cell.getCellType()); |
102 |
assertEquals(Cell.CELL_TYPE_FORMULA, lC1Cell.getCellType()); |
103 |
|
104 |
HSSFFormulaEvaluator lMainWorkbookEvaluator = new HSSFFormulaEvaluator(mainWorkbook); |
105 |
HSSFFormulaEvaluator lSourceEvaluator = new HSSFFormulaEvaluator(sourceWorkbook); |
106 |
HSSFFormulaEvaluator.setupEnvironment( |
107 |
new String[]{MAIN_WORKBOOK_FILENAME, SOURCE_DUMMY_WORKBOOK_FILENAME}, |
108 |
new HSSFFormulaEvaluator[] {lMainWorkbookEvaluator, lSourceEvaluator}); |
109 |
|
110 |
assertEquals(Cell.CELL_TYPE_NUMERIC, lMainWorkbookEvaluator.evaluateFormulaCell(lA1Cell)); |
111 |
assertEquals(Cell.CELL_TYPE_STRING, lMainWorkbookEvaluator.evaluateFormulaCell(lB1Cell)); |
112 |
assertEquals(Cell.CELL_TYPE_BOOLEAN, lMainWorkbookEvaluator.evaluateFormulaCell(lC1Cell)); |
113 |
|
114 |
assertEquals(20.0d, lA1Cell.getNumericCellValue(), 0.00001d); |
115 |
assertEquals("Apache rocks!", lB1Cell.getStringCellValue()); |
116 |
assertEquals(false, lC1Cell.getBooleanCellValue()); |
117 |
} |
118 |
|
119 |
} |