/**
* Sometimes the 'shared formula' flag (<tt>FormulaRecord.isSharedFormula()</tt>) is set when
* there is no corresponding SharedFormulaRecord available. SharedFormulaRecord definitions do not
* span multiple sheets. They are are only defined within a sheet, and thus they do not have a
* sheet index field (only row and column range fields).<br>
* So it is important that the code which locates the SharedFormulaRecord for each FormulaRecord
* does not allow matches across sheets.</br>
*
* <p>Prior to bugzilla 44449 (Feb 2008), POI <tt>ValueRecordsAggregate.construct(int, List)</tt>
* allowed <tt>SharedFormulaRecord</tt>s to be erroneously used across sheets. That incorrect
* behaviour is shown by this test.
*
* <p><b>Notes on how to produce the test spreadsheet</b>: The setup for this test
* (AbnormalSharedFormulaFlag.xls) is rather fragile, insomuchas re-saving the file (either with
* Excel or POI) clears the flag.<br>
*
* <ol>
* <li>A new spreadsheet was created in Excel (File | New | Blank Workbook).
* <li>Sheet3 was deleted.
* <li>Sheet2!A1 formula was set to '="second formula"', and fill-dragged through A1:A8.
* <li>Sheet1!A1 formula was set to '="first formula"', and also fill-dragged through A1:A8.
* <li>Four rows on Sheet1 "5" through "8" were deleted ('delete rows' alt-E D, not 'clear'
* Del).
* <li>The spreadsheet was saved as AbnormalSharedFormulaFlag.xls.
* </ol>
*
* Prior to the row delete action the spreadsheet has two <tt>SharedFormulaRecord</tt>s. One for
* each sheet. To expose the bug, the shared formulas have been made to overlap.<br>
* The row delete action (as described here) seems to to delete the <tt>SharedFormulaRecord</tt>
* from Sheet1 (but not clear the 'shared formula' flags.<br>
* There are other variations on this theme to create the same effect.
*/
public void testSpuriousSharedFormulaFlag() {
long actualCRC =
getFileCRC(
HSSFTestDataSamples.openSampleFileStream(ABNORMAL_SHARED_FORMULA_FLAG_TEST_FILE));
long expectedCRC = 2277445406L;
if (actualCRC != expectedCRC) {
System.err.println("Expected crc " + expectedCRC + " but got " + actualCRC);
throw failUnexpectedTestFileChange();
}
HSSFWorkbook wb =
HSSFTestDataSamples.openSampleWorkbook(ABNORMAL_SHARED_FORMULA_FLAG_TEST_FILE);
HSSFSheet s = wb.getSheetAt(0); // Sheet1
String cellFormula;
cellFormula = getFormulaFromFirstCell(s, 0); // row "1"
// the problem is not observable in the first row of the shared formula
if (!cellFormula.equals("\"first formula\"")) {
throw new RuntimeException("Something else wrong with this test case");
}
// but the problem is observable in rows 2,3,4
cellFormula = getFormulaFromFirstCell(s, 1); // row "2"
if (cellFormula.equals("\"second formula\"")) {
throw new AssertionFailedError("found bug 44449 (Wrong SharedFormulaRecord was used).");
}
if (!cellFormula.equals("\"first formula\"")) {
throw new RuntimeException("Something else wrong with this test case");
}
}
/**
* Test get new id for shapes from existing file File already have for 1 shape on each sheet,
* because document must contain EscherDgRecord for each sheet
*/
public void testAllocateNewIds() {
HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("empty.xls");
HSSFSheet sheet = wb.getSheetAt(0);
HSSFPatriarch patriarch = sheet.getDrawingPatriarch();
/** 2048 - main SpContainer id 2049 - existing shape id */
assertEquals(HSSFTestHelper.allocateNewShapeId(patriarch), 2050);
assertEquals(HSSFTestHelper.allocateNewShapeId(patriarch), 2051);
assertEquals(HSSFTestHelper.allocateNewShapeId(patriarch), 2052);
sheet = wb.getSheetAt(1);
patriarch = sheet.getDrawingPatriarch();
/** 3072 - main SpContainer id 3073 - existing shape id */
assertEquals(HSSFTestHelper.allocateNewShapeId(patriarch), 3074);
assertEquals(HSSFTestHelper.allocateNewShapeId(patriarch), 3075);
assertEquals(HSSFTestHelper.allocateNewShapeId(patriarch), 3076);
sheet = wb.getSheetAt(2);
patriarch = sheet.getDrawingPatriarch();
assertEquals(HSSFTestHelper.allocateNewShapeId(patriarch), 1026);
assertEquals(HSSFTestHelper.allocateNewShapeId(patriarch), 1027);
assertEquals(HSSFTestHelper.allocateNewShapeId(patriarch), 1028);
}
/** tests <tt>NameXPtg.toFormulaString(Workbook)</tt> and logic in Workbook below that */
public void testReadFormulaContainingExternalFunction() {
HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("externalFunctionExample.xls");
String expectedFormula = "YEARFRAC(B1,C1)";
HSSFSheet sht = wb.getSheetAt(0);
String cellFormula = sht.getRow(0).getCell(0).getCellFormula();
assertEquals(expectedFormula, cellFormula);
}
/**
* Test that the HSSFFormulaEvaluator can evaluate simple named ranges (single cells and
* rectangular areas)
*/
public void testEvaluateSimple() {
HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("testNames.xls");
HSSFSheet sheet = wb.getSheetAt(0);
HSSFCell cell = sheet.getRow(8).getCell(0);
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
CellValue cv = fe.evaluate(cell);
assertEquals(HSSFCell.CELL_TYPE_NUMERIC, cv.getCellType());
assertEquals(3.72, cv.getNumberValue(), 0.0);
}
public void testEvaluate() {
HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("externalFunctionExample.xls");
HSSFSheet sheet = wb.getSheetAt(0);
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
confirmCellEval(sheet, 0, 0, fe, "YEARFRAC(B1,C1)", 29.0 / 90.0);
confirmCellEval(sheet, 1, 0, fe, "YEARFRAC(B2,C2)", 0.0);
confirmCellEval(sheet, 2, 0, fe, "YEARFRAC(B3,C3,D3)", 0.0);
confirmCellEval(sheet, 3, 0, fe, "IF(ISEVEN(3),1.2,1.6)", 1.6);
confirmCellEval(sheet, 4, 0, fe, "IF(ISODD(3),1.2,1.6)", 1.2);
}
/**
* Example from
* http://office.microsoft.com/en-us/excel-help/intercept-function-HP010062512.aspx?CTT=5&origin=HA010277524
*/
public void testFromFile() {
HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("intercept.xls");
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
HSSFSheet example1 = wb.getSheet("Example 1");
HSSFCell a8 = example1.getRow(7).getCell(0);
assertEquals("INTERCEPT(A2:A6,B2:B6)", a8.getCellFormula());
fe.evaluate(a8);
assertEquals(0.048387097, a8.getNumericCellValue(), 0.000000001);
}
/**
* HSSFShape tree bust be built correctly Check file with such records structure: -patriarch
* --shape --group ---group ----shape ----shape ---shape ---group ----shape ----shape
*/
public void testDrawingGroups() {
HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("drawings.xls");
HSSFSheet sheet = wb.getSheet("groups");
HSSFPatriarch patriarch = sheet.getDrawingPatriarch();
assertEquals(patriarch.getChildren().size(), 2);
HSSFShapeGroup group = (HSSFShapeGroup) patriarch.getChildren().get(1);
assertEquals(3, group.getChildren().size());
HSSFShapeGroup group1 = (HSSFShapeGroup) group.getChildren().get(0);
assertEquals(2, group1.getChildren().size());
group1 = (HSSFShapeGroup) group.getChildren().get(2);
assertEquals(2, group1.getChildren().size());
}
/* assert shape properties when reading shapes from a existing workbook */
public void testReadExistingRectangle() {
HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("drawings.xls");
HSSFSheet sheet = wb.getSheet("rectangles");
HSSFPatriarch drawing = sheet.getDrawingPatriarch();
assertEquals(1, drawing.getChildren().size());
HSSFSimpleShape shape = (HSSFSimpleShape) drawing.getChildren().get(0);
assertEquals(shape.isNoFill(), false);
assertEquals(shape.getLineStyle(), HSSFShape.LINESTYLE_DASHDOTGEL);
assertEquals(shape.getLineStyleColor(), 0x616161);
assertEquals(HexDump.toHex(shape.getFillColor()), shape.getFillColor(), 0x2CE03D);
assertEquals(shape.getLineWidth(), HSSFShape.LINEWIDTH_ONE_PT * 2);
assertEquals(shape.getString().getString(), "POItest");
assertEquals(shape.getRotationDegree(), 27);
}
public void testReadExistingImage() {
HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("drawings.xls");
HSSFSheet sheet = wb.getSheet("pictures");
HSSFPatriarch drawing = sheet.getDrawingPatriarch();
assertEquals(1, drawing.getChildren().size());
HSSFPicture picture = (HSSFPicture) drawing.getChildren().get(0);
assertEquals(picture.getPictureIndex(), 2);
assertEquals(picture.getLineStyleColor(), HSSFShape.LINESTYLE__COLOR_DEFAULT);
assertEquals(picture.getFillColor(), 0x5DC943);
assertEquals(picture.getLineWidth(), HSSFShape.LINEWIDTH_DEFAULT);
assertEquals(picture.getLineStyle(), HSSFShape.LINESTYLE_DEFAULT);
assertEquals(picture.isNoFill(), false);
picture.setPictureIndex(2);
assertEquals(picture.getPictureIndex(), 2);
}
public void testParse() {
HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("externalFunctionExample.xls");
Ptg[] ptgs = HSSFFormulaParser.parse("YEARFRAC(B1,C1)", wb);
assertEquals(4, ptgs.length);
assertEquals(NameXPtg.class, ptgs[0].getClass());
wb.getSheetAt(0).getRow(0).createCell(6).setCellFormula("YEARFRAC(C1,B1)");
if (false) {
// In case you fancy checking in excel
try {
File tempFile = TempFile.createTempFile("testExtFunc", ".xls");
FileOutputStream fout = new FileOutputStream(tempFile);
wb.write(fout);
fout.close();
System.out.println("check out " + tempFile.getAbsolutePath());
} catch (IOException e) {
throw new RuntimeException(e);
}
}
}
public void testFormulas() {
FormulaRecord[] fRecs = mockListen.getFormulaRecords();
// Check our formula records
assertEquals(6, fRecs.length);
InternalWorkbook stubWB = listener.getStubWorkbook();
assertNotNull(stubWB);
HSSFWorkbook stubHSSF = listener.getStubHSSFWorkbook();
assertNotNull(stubHSSF);
// Check these stubs have the right stuff on them
assertEquals("Sheet1", stubWB.getSheetName(0));
assertEquals("Sheet1", stubHSSF.getSheetName(0));
assertEquals("S2", stubWB.getSheetName(1));
assertEquals("S2", stubHSSF.getSheetName(1));
assertEquals("Sh3", stubWB.getSheetName(2));
assertEquals("Sh3", stubHSSF.getSheetName(2));
// Check we can get the formula without breaking
for (int i = 0; i < fRecs.length; i++) {
HSSFFormulaParser.toFormulaString(stubHSSF, fRecs[i].getParsedExpression());
}
// Peer into just one formula, and check that
// all the ptgs give back the right things
Ptg[] ptgs = fRecs[0].getParsedExpression();
assertEquals(1, ptgs.length);
assertTrue(ptgs[0] instanceof Ref3DPtg);
Ref3DPtg ptg = (Ref3DPtg) ptgs[0];
HSSFEvaluationWorkbook book = HSSFEvaluationWorkbook.create(stubHSSF);
assertEquals("Sheet1!A1", ptg.toFormulaString(book));
// Now check we get the right formula back for
// a few sample ones
FormulaRecord fr;
// Sheet 1 A2 is on same sheet
fr = fRecs[0];
assertEquals(1, fr.getRow());
assertEquals(0, fr.getColumn());
assertEquals(
"Sheet1!A1", HSSFFormulaParser.toFormulaString(stubHSSF, fr.getParsedExpression()));
// Sheet 1 A5 is to another sheet
fr = fRecs[3];
assertEquals(4, fr.getRow());
assertEquals(0, fr.getColumn());
assertEquals("'S2'!A1", HSSFFormulaParser.toFormulaString(stubHSSF, fr.getParsedExpression()));
// Sheet 1 A7 is to another sheet, range
fr = fRecs[5];
assertEquals(6, fr.getRow());
assertEquals(0, fr.getColumn());
assertEquals(
"SUM(Sh3!A1:A4)", HSSFFormulaParser.toFormulaString(stubHSSF, fr.getParsedExpression()));
// Now, load via Usermodel and re-check
HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("3dFormulas.xls");
assertEquals("Sheet1!A1", wb.getSheetAt(0).getRow(1).getCell(0).getCellFormula());
assertEquals("SUM(Sh3!A1:A4)", wb.getSheetAt(0).getRow(6).getCell(0).getCellFormula());
}
private static HSSFWorkbook openSample(String sampleFileName) {
return HSSFTestDataSamples.openSampleWorkbook(sampleFileName);
}
/** Set things up, two spreadsheets for our testing */
public void setUp() {
doc = HSSFTestDataSamples.openSampleWorkbook("DateFormats.xls");
doc2 = HSSFTestDataSamples.openSampleWorkbook("StringFormulas.xls");
}