/** * All multi-binomial operator tests use this to create a worksheet with a huge set of x operator * y formulas. Next we call binomialVerify and verify that they are all how we expect. */ private static void binomialOperator(String operator) { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet s = wb.createSheet(); HSSFRow r = null; HSSFCell c = null; // get our minimum values r = s.createRow(0); c = r.createCell(1); c.setCellFormula(1 + operator + 1); for (int x = 1; x < Short.MAX_VALUE && x > 0; x = (short) (x * 2)) { r = s.createRow(x); for (int y = 1; y < 256 && y > 0; y++) { c = r.createCell(y); c.setCellFormula("" + x + operator + y); } } // make sure we do the maximum value of the Int operator if (s.getLastRowNum() < Short.MAX_VALUE) { r = s.getRow(0); c = r.createCell(0); c.setCellFormula("" + Short.MAX_VALUE + operator + Short.MAX_VALUE); } wb = HSSFTestDataSamples.writeOutAndReadBack(wb); binomialVerify(operator, wb); }
private static void floatTest(String operator) { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet s = wb.createSheet(); HSSFRow r = null; HSSFCell c = null; // get our minimum values r = s.createRow(0); c = r.createCell(1); c.setCellFormula("" + Float.MIN_VALUE + operator + Float.MIN_VALUE); for (int x = 1; x < Short.MAX_VALUE && x > 0; x = (short) (x * 2)) { r = s.createRow(x); for (int y = 1; y < 256 && y > 0; y = (short) (y + 2)) { c = r.createCell(y); c.setCellFormula("" + x + "." + y + operator + y + "." + x); } } if (s.getLastRowNum() < Short.MAX_VALUE) { r = s.createRow(0); c = r.createCell(0); c.setCellFormula("" + Float.MAX_VALUE + operator + Float.MAX_VALUE); } wb = HSSFTestDataSamples.writeOutAndReadBack(wb); floatVerify(operator, wb); }
/** * 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"); } }
/** Writes a function then tests to see if its correct */ public void refAreaArrayFunctionTest(String function) { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet s = wb.createSheet(); HSSFRow r = null; HSSFCell c = null; r = s.createRow(0); c = r.createCell(0); c.setCellFormula(function + "(A2:A4,B2:B4)"); c = r.createCell(1); c.setCellFormula(function + "($A$2:$A4,B$2:B4)"); wb = HSSFTestDataSamples.writeOutAndReadBack(wb); s = wb.getSheetAt(0); r = s.getRow(0); c = r.getCell(0); assertTrue( "function =" + function + "(A2:A4,B2:B4)", ((function + "(A2:A4,B2:B4)").equals(c.getCellFormula()))); c = r.getCell(1); assertTrue( "function =" + function + "($A$2:$A4,B$2:B4)", ((function + "($A$2:$A4,B$2:B4)").equals(c.getCellFormula()))); }
public void testClearShapesForPatriarch() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(); HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); patriarch.createSimpleShape(new HSSFClientAnchor()); patriarch.createSimpleShape(new HSSFClientAnchor()); patriarch.createCellComment(new HSSFClientAnchor()); EscherAggregate agg = HSSFTestHelper.getEscherAggregate(patriarch); assertEquals(agg.getShapeToObjMapping().size(), 6); assertEquals(agg.getTailRecords().size(), 1); assertEquals(patriarch.getChildren().size(), 3); patriarch.clear(); assertEquals(agg.getShapeToObjMapping().size(), 0); assertEquals(agg.getTailRecords().size(), 0); assertEquals(patriarch.getChildren().size(), 0); wb = HSSFTestDataSamples.writeOutAndReadBack(wb); sheet = wb.getSheetAt(0); patriarch = sheet.getDrawingPatriarch(); assertEquals(agg.getShapeToObjMapping().size(), 0); assertEquals(agg.getTailRecords().size(), 0); assertEquals(patriarch.getChildren().size(), 0); }
public void testRotation() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(); HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); HSSFSimpleShape rectangle = patriarch.createSimpleShape( new HSSFClientAnchor(0, 0, 100, 100, (short) 0, 0, (short) 5, 5)); rectangle.setShapeType(HSSFSimpleShape.OBJECT_TYPE_RECTANGLE); assertEquals(rectangle.getRotationDegree(), 0); rectangle.setRotationDegree((short) 45); assertEquals(rectangle.getRotationDegree(), 45); rectangle.setFlipHorizontal(true); wb = HSSFTestDataSamples.writeOutAndReadBack(wb); sheet = wb.getSheetAt(0); patriarch = sheet.getDrawingPatriarch(); rectangle = (HSSFSimpleShape) patriarch.getChildren().get(0); assertEquals(rectangle.getRotationDegree(), 45); rectangle.setRotationDegree((short) 30); assertEquals(rectangle.getRotationDegree(), 30); patriarch.setCoordinates(0, 0, 10, 10); rectangle.setString(new HSSFRichTextString("1234")); }
public void testAbsRefs() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet s = wb.createSheet(); HSSFRow r; HSSFCell c; r = s.createRow(0); c = r.createCell(0); c.setCellFormula("A3+A2"); c = r.createCell(1); c.setCellFormula("$A3+$A2"); c = r.createCell(2); c.setCellFormula("A$3+A$2"); c = r.createCell(3); c.setCellFormula("$A$3+$A$2"); c = r.createCell(4); c.setCellFormula("SUM($A$3,$A$2)"); wb = HSSFTestDataSamples.writeOutAndReadBack(wb); s = wb.getSheetAt(0); r = s.getRow(0); c = r.getCell(0); assertTrue("A3+A2", ("A3+A2").equals(c.getCellFormula())); c = r.getCell(1); assertTrue("$A3+$A2", ("$A3+$A2").equals(c.getCellFormula())); c = r.getCell(2); assertTrue("A$3+A$2", ("A$3+A$2").equals(c.getCellFormula())); c = r.getCell(3); assertTrue("$A$3+$A$2", ("$A$3+$A$2").equals(c.getCellFormula())); c = r.getCell(4); assertTrue("SUM($A$3,$A$2)", ("SUM($A$3,$A$2)").equals(c.getCellFormula())); }
public void testSheetFunctions() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet s = wb.createSheet("A"); HSSFRow r = null; HSSFCell c = null; r = s.createRow(0); c = r.createCell(0); c.setCellValue(1); c = r.createCell(1); c.setCellValue(2); s = wb.createSheet("B"); r = s.createRow(0); c = r.createCell(0); c.setCellFormula("AVERAGE(A!A1:B1)"); c = r.createCell(1); c.setCellFormula("A!A1+A!B1"); c = r.createCell(2); c.setCellFormula("A!$A$1+A!$B1"); wb = HSSFTestDataSamples.writeOutAndReadBack(wb); s = wb.getSheet("B"); r = s.getRow(0); c = r.getCell(0); assertTrue( "expected: AVERAGE(A!A1:B1) got: " + c.getCellFormula(), ("AVERAGE(A!A1:B1)").equals(c.getCellFormula())); c = r.getCell(1); assertTrue( "expected: A!A1+A!B1 got: " + c.getCellFormula(), ("A!A1+A!B1").equals(c.getCellFormula())); }
public void testDateFormulas() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet s = wb.createSheet("testSheet1"); HSSFRow r = null; HSSFCell c = null; r = s.createRow(0); c = r.createCell(0); HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm")); c.setCellValue(new Date()); c.setCellStyle(cellStyle); // assertEquals("Checking hour = " + hour, date.getTime().getTime(), // HSSFDateUtil.getJavaDate(excelDate).getTime()); for (int k = 1; k < 100; k++) { r = s.createRow(k); c = r.createCell(0); c.setCellFormula("A" + (k) + "+1"); c.setCellStyle(cellStyle); } HSSFTestDataSamples.writeOutAndReadBack(wb); }
/** * 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); }
public void testShapeIds() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet1 = wb.createSheet(); HSSFPatriarch patriarch1 = sheet1.createDrawingPatriarch(); for (int i = 0; i < 2; i++) { patriarch1.createSimpleShape(new HSSFClientAnchor()); } wb = HSSFTestDataSamples.writeOutAndReadBack(wb); sheet1 = wb.getSheetAt(0); patriarch1 = sheet1.getDrawingPatriarch(); EscherAggregate agg1 = HSSFTestHelper.getEscherAggregate(patriarch1); // last shape ID cached in EscherDgRecord EscherDgRecord dg1 = agg1.getEscherContainer().getChildById(EscherDgRecord.RECORD_ID); assertEquals(1026, dg1.getLastMSOSPID()); // iterate over shapes and check shapeId EscherContainerRecord spgrContainer = agg1.getEscherContainer().getChildContainers().get(0); // root spContainer + 2 spContainers for shapes assertEquals(3, spgrContainer.getChildRecords().size()); EscherSpRecord sp0 = ((EscherContainerRecord) spgrContainer.getChild(0)).getChildById(EscherSpRecord.RECORD_ID); assertEquals(1024, sp0.getShapeId()); EscherSpRecord sp1 = ((EscherContainerRecord) spgrContainer.getChild(1)).getChildById(EscherSpRecord.RECORD_ID); assertEquals(1025, sp1.getShapeId()); EscherSpRecord sp2 = ((EscherContainerRecord) spgrContainer.getChild(2)).getChildById(EscherSpRecord.RECORD_ID); assertEquals(1026, sp2.getShapeId()); }
private static void operationRefTest(String operator) { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet s = wb.createSheet(); HSSFRow r = null; HSSFCell c = null; // get our minimum values r = s.createRow(0); c = r.createCell(1); c.setCellFormula("A2" + operator + "A3"); for (int x = 1; x < Short.MAX_VALUE && x > 0; x = (short) (x * 2)) { r = s.createRow(x); for (int y = 1; y < 256 && y > 0; y++) { String ref = null; String ref2 = null; short refx1 = 0; short refy1 = 0; short refx2 = 0; short refy2 = 0; if (x + 50 < Short.MAX_VALUE) { refx1 = (short) (x + 50); refx2 = (short) (x + 46); } else { refx1 = (short) (x - 4); refx2 = (short) (x - 3); } if (y + 50 < 255) { refy1 = (short) (y + 50); refy2 = (short) (y + 49); } else { refy1 = (short) (y - 4); refy2 = (short) (y - 3); } c = r.getCell(y); CellReference cr = new CellReference(refx1, refy1, false, false); ref = cr.formatAsString(); cr = new CellReference(refx2, refy2, false, false); ref2 = cr.formatAsString(); c = r.createCell(y); c.setCellFormula("" + ref + operator + ref2); } } // make sure we do the maximum value of the Int operator if (s.getLastRowNum() < Short.MAX_VALUE) { r = s.getRow(0); c = r.createCell(0); c.setCellFormula("" + "B1" + operator + "IV255"); } wb = HSSFTestDataSamples.writeOutAndReadBack(wb); operationalRefVerify(operator, wb); }
/** 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); }
public void testTextForSimpleShape() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(); HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); HSSFSimpleShape shape = patriarch.createSimpleShape(new HSSFClientAnchor()); shape.setShapeType(HSSFSimpleShape.OBJECT_TYPE_RECTANGLE); EscherAggregate agg = HSSFTestHelper.getEscherAggregate(patriarch); assertEquals(agg.getShapeToObjMapping().size(), 2); wb = HSSFTestDataSamples.writeOutAndReadBack(wb); sheet = wb.getSheetAt(0); patriarch = sheet.getDrawingPatriarch(); shape = (HSSFSimpleShape) patriarch.getChildren().get(0); agg = HSSFTestHelper.getEscherAggregate(patriarch); assertEquals(agg.getShapeToObjMapping().size(), 2); shape.setString(new HSSFRichTextString("string1")); assertEquals(shape.getString().getString(), "string1"); assertNotNull( HSSFTestHelper.getEscherContainer(shape).getChildById(EscherTextboxRecord.RECORD_ID)); assertEquals(agg.getShapeToObjMapping().size(), 2); wb = HSSFTestDataSamples.writeOutAndReadBack(wb); wb = HSSFTestDataSamples.writeOutAndReadBack(wb); sheet = wb.getSheetAt(0); patriarch = sheet.getDrawingPatriarch(); shape = (HSSFSimpleShape) patriarch.getChildren().get(0); assertNotNull(HSSFTestHelper.getTextObjRecord(shape)); assertEquals(shape.getString().getString(), "string1"); assertNotNull( HSSFTestHelper.getEscherContainer(shape).getChildById(EscherTextboxRecord.RECORD_ID)); assertEquals(agg.getShapeToObjMapping().size(), 2); }
/** * 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()); }
public void testShapeFlip() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(); HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); HSSFSimpleShape rectangle = patriarch.createSimpleShape(new HSSFClientAnchor()); rectangle.setShapeType(HSSFSimpleShape.OBJECT_TYPE_RECTANGLE); assertEquals(rectangle.isFlipVertical(), false); assertEquals(rectangle.isFlipHorizontal(), false); rectangle.setFlipVertical(true); assertEquals(rectangle.isFlipVertical(), true); rectangle.setFlipHorizontal(true); assertEquals(rectangle.isFlipHorizontal(), true); wb = HSSFTestDataSamples.writeOutAndReadBack(wb); sheet = wb.getSheetAt(0); patriarch = sheet.getDrawingPatriarch(); rectangle = (HSSFSimpleShape) patriarch.getChildren().get(0); assertEquals(rectangle.isFlipHorizontal(), true); rectangle.setFlipHorizontal(false); assertEquals(rectangle.isFlipHorizontal(), false); assertEquals(rectangle.isFlipVertical(), true); rectangle.setFlipVertical(false); assertEquals(rectangle.isFlipVertical(), false); wb = HSSFTestDataSamples.writeOutAndReadBack(wb); sheet = wb.getSheetAt(0); patriarch = sheet.getDrawingPatriarch(); rectangle = (HSSFSimpleShape) patriarch.getChildren().get(0); assertEquals(rectangle.isFlipVertical(), false); assertEquals(rectangle.isFlipHorizontal(), false); }
public void testSumIf() { String function = "SUMIF(A1:A5,\">4000\",B1:B5)"; HSSFWorkbook wb = openSample("sumifformula.xls"); HSSFSheet s = wb.getSheetAt(0); HSSFRow r = s.getRow(0); HSSFCell c = r.getCell(2); assertEquals(function, c.getCellFormula()); wb = new HSSFWorkbook(); s = wb.createSheet(); r = s.createRow(0); c = r.createCell(0); c.setCellValue(1000); c = r.createCell(1); c.setCellValue(1); r = s.createRow(1); c = r.createCell(0); c.setCellValue(2000); c = r.createCell(1); c.setCellValue(2); r = s.createRow(2); c = r.createCell(0); c.setCellValue(3000); c = r.createCell(1); c.setCellValue(3); r = s.createRow(3); c = r.createCell(0); c.setCellValue(4000); c = r.createCell(1); c.setCellValue(4); r = s.createRow(4); c = r.createCell(0); c.setCellValue(5000); c = r.createCell(1); c.setCellValue(5); r = s.getRow(0); c = r.createCell(2); c.setCellFormula(function); HSSFTestDataSamples.writeOutAndReadBack(wb); }
private void processFile(String filename) throws Exception { HSSFRequest req = new HSSFRequest(); mockListen = new MockHSSFListener(); listener = new FormatTrackingHSSFListener(mockListen); req.addListenerForAllRecords(listener); HSSFEventFactory factory = new HSSFEventFactory(); try { InputStream is = HSSFTestDataSamples.openSampleFileStream(filename); POIFSFileSystem fs = new POIFSFileSystem(is); factory.processWorkbookEvents(req, fs); } catch (IOException e) { throw new RuntimeException(e); } }
/* 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); }
@Override public void setUp() { HSSFRequest req = new HSSFRequest(); mockListen = new MockHSSFListener(); listener = new SheetRecordCollectingListener(mockListen); req.addListenerForAllRecords(listener); HSSFEventFactory factory = new HSSFEventFactory(); try { InputStream is = HSSFTestDataSamples.openSampleFileStream("3dFormulas.xls"); POIFSFileSystem fs = new POIFSFileSystem(is); factory.processWorkbookEvents(req, fs); } catch (IOException e) { throw new RuntimeException(e); } }
public void testLogicalFormulas() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet s = wb.createSheet("A"); HSSFRow r = null; HSSFCell c = null; r = s.createRow(0); c = r.createCell(1); c.setCellFormula("IF(A1<A2,B1,B2)"); wb = HSSFTestDataSamples.writeOutAndReadBack(wb); s = wb.getSheetAt(0); r = s.getRow(0); c = r.getCell(1); assertEquals("Formula in cell 1 ", "IF(A1<A2,B1,B2)", c.getCellFormula()); }
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); }
/** Add 1+1 -- WHoohoo! */ public void testBasicAddIntegers() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet s = wb.createSheet(); HSSFRow r = null; HSSFCell c = null; // get our minimum values r = s.createRow(1); c = r.createCell(1); c.setCellFormula(1 + "+" + 1); wb = HSSFTestDataSamples.writeOutAndReadBack(wb); s = wb.getSheetAt(0); r = s.getRow(1); c = r.getCell(1); assertTrue("Formula is as expected", ("1+1".equals(c.getCellFormula()))); }
/** tests order wrting out == order writing in for a given formula */ private static void orderTest(String formula) { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet s = wb.createSheet(); HSSFRow r = null; HSSFCell c = null; // get our minimum values r = s.createRow(0); c = r.createCell(1); c.setCellFormula(formula); wb = HSSFTestDataSamples.writeOutAndReadBack(wb); s = wb.getSheetAt(0); // get our minimum values r = s.getRow(0); c = r.getCell(1); assertTrue("minval Formula is as expected", formula.equals(c.getCellFormula())); }
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 testStringFormulas() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet s = wb.createSheet("A"); HSSFRow r = null; HSSFCell c = null; r = s.createRow(0); c = r.createCell(1); c.setCellFormula("UPPER(\"abc\")"); c = r.createCell(2); c.setCellFormula("LOWER(\"ABC\")"); c = r.createCell(3); c.setCellFormula("CONCATENATE(\" my \",\" name \")"); HSSFTestDataSamples.writeOutAndReadBack(wb); wb = openSample("StringFormulas.xls"); s = wb.getSheetAt(0); r = s.getRow(0); c = r.getCell(0); assertEquals("UPPER(\"xyz\")", c.getCellFormula()); }
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()); }