private File writeSingleXLS() throws ReportException { File file = null; HSSFWorkbook wb = null; OutputStream out = null; try { file = getReportFile(); wb = new HSSFWorkbook(); ReportDefineFrm definedReport = executeFrm.getDefinedReport(); String sheetName = definedReport.getReportName(); HSSFSheet sheet = wb.createSheet(sheetName); sheet.setDisplayGridlines(false); // 不显示网格线 HSSFCellStyle dataStyle = createDataStyle(wb); HSSFCellStyle headerStyle = createHeaderStyle(wb); if (isFirstFile) { writeDataHeader(sheet, headerStyle); } writeData2Excel(sheet, dataStyle, headerStyle); autoReSizeSheet(sheet); out = new FileOutputStream(file); wb.write(out); isFirstFile = false; } catch (Throwable ex) { Logger.logError(ex); throw new ReportException(ex.getMessage()); } finally { try { if (out != null) { out.close(); } } catch (IOException ex) { Logger.logError(ex); } } return file; }
/** * 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); }
public void testFullColumnRefs() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("Sheet1"); HSSFRow row = sheet.createRow(0); HSSFCell cell0 = row.createCell(0); cell0.setCellFormula("sum(D:D)"); HSSFCell cell1 = row.createCell(1); cell1.setCellFormula("sum(D:E)"); // some values in column D setValue(sheet, 1, 3, 5.0); setValue(sheet, 2, 3, 6.0); setValue(sheet, 5, 3, 7.0); setValue(sheet, 50, 3, 8.0); // some values in column E setValue(sheet, 1, 4, 9.0); setValue(sheet, 2, 4, 10.0); setValue(sheet, 30000, 4, 11.0); // some other values setValue(sheet, 1, 2, 100.0); setValue(sheet, 2, 5, 100.0); setValue(sheet, 3, 6, 100.0); HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); assertEquals(26.0, fe.evaluate(cell0).getNumberValue(), 0.0); assertEquals(56.0, fe.evaluate(cell1).getNumberValue(), 0.0); }
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); }
/** * 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 setValue(HSSFSheet sheet, int rowIndex, int colIndex, double value) { HSSFRow row = sheet.getRow(rowIndex); if (row == null) { row = sheet.createRow(rowIndex); } row.createCell(colIndex).setCellValue(value); }
private static void floatVerify(String operator, HSSFWorkbook wb) { HSSFSheet s = wb.getSheetAt(0); // don't know how to check correct result .. for the moment, we just verify that the file can be // read. for (int x = 1; x < Short.MAX_VALUE && x > 0; x = (short) (x * 2)) { HSSFRow r = s.getRow(x); for (int y = 1; y < 256 && y > 0; y = (short) (y + 2)) { HSSFCell c = r.getCell(y); assertTrue("got a formula", c.getCellFormula() != null); assertTrue( "loop Formula is as expected " + x + "." + y + operator + y + "." + x + "!=" + c.getCellFormula(), (("" + x + "." + y + operator + y + "." + x).equals(c.getCellFormula()))); } } }
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 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())); }
/** 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 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 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); }
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); }
/** * Opens the sheet we wrote out by binomialOperator and makes sure the formulas all match what we * expect (x operator y) */ private static void binomialVerify(String operator, HSSFWorkbook wb) { HSSFSheet s = wb.getSheetAt(0); HSSFRow r = null; HSSFCell c = null; // get our minimum values r = s.getRow(0); c = r.getCell(1); assertTrue( "minval Formula is as expected 1" + operator + "1 != " + c.getCellFormula(), (("1" + operator + "1").equals(c.getCellFormula()))); for (int x = 1; x < Short.MAX_VALUE && x > 0; x = (short) (x * 2)) { r = s.getRow(x); for (int y = 1; y < 256 && y > 0; y++) { c = r.getCell(y); assertTrue( "loop Formula is as expected " + x + operator + y + "!=" + c.getCellFormula(), (("" + x + operator + y).equals(c.getCellFormula()))); } } // test our maximum values r = s.getRow(0); c = r.getCell(0); assertTrue( "maxval Formula is as expected", (("" + Short.MAX_VALUE + operator + Short.MAX_VALUE).equals(c.getCellFormula()))); }
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); }
/** * {@link HSSFFormulaEvaluator#evaluate(org.apache.poi.ss.usermodel.Cell)} should behave the same * whether the cell is <code>null</code> or blank. */ public void testEvaluateBlank() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); assertNull(fe.evaluate(null)); HSSFSheet sheet = wb.createSheet("Sheet1"); HSSFCell cell = sheet.createRow(0).createCell(0); assertNull(fe.evaluate(cell)); }
/** * Opens the sheet we wrote out by binomialOperator and makes sure the formulas all match what we * expect (x operator y) */ private static void operationalRefVerify(String operator, HSSFWorkbook wb) { HSSFSheet s = wb.getSheetAt(0); HSSFRow r = null; HSSFCell c = null; // get our minimum values r = s.getRow(0); c = r.getCell(1); // get our minimum values assertTrue( "minval Formula is as expected A2" + operator + "A3 != " + c.getCellFormula(), (("A2" + operator + "A3").equals(c.getCellFormula()))); for (int x = 1; x < Short.MAX_VALUE && x > 0; x = (short) (x * 2)) { r = s.getRow(x); for (int y = 1; y < 256 && y > 0; y++) { int refx1; int refy1; int refx2; int refy2; if (x + 50 < Short.MAX_VALUE) { refx1 = x + 50; refx2 = x + 46; } else { refx1 = x - 4; refx2 = x - 3; } if (y + 50 < 255) { refy1 = y + 50; refy2 = y + 49; } else { refy1 = y - 4; refy2 = y - 3; } c = r.getCell(y); CellReference cr = new CellReference(refx1, refy1, false, false); String ref = cr.formatAsString(); ref = cr.formatAsString(); cr = new CellReference(refx2, refy2, false, false); String ref2 = cr.formatAsString(); assertTrue( "loop Formula is as expected " + ref + operator + ref2 + "!=" + c.getCellFormula(), (("" + ref + operator + ref2).equals(c.getCellFormula()))); } } // test our maximum values r = s.getRow(0); c = r.getCell(0); assertEquals("B1" + operator + "IV255", c.getCellFormula()); }
/** * 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); }
/** test for bug 34021 */ public void testComplexSheetRefs() throws IOException { HSSFWorkbook sb = new HSSFWorkbook(); HSSFSheet s1 = sb.createSheet("Sheet a.1"); HSSFSheet s2 = sb.createSheet("Sheet.A"); s2.createRow(1).createCell(2).setCellFormula("'Sheet a.1'!A1"); s1.createRow(1).createCell(2).setCellFormula("'Sheet.A'!A1"); File file = TempFile.createTempFile("testComplexSheetRefs", ".xls"); sb.write(new FileOutputStream(file)); }
/** * 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 testDgRecordNumShapes() throws IOException { HSSFWorkbook wb = new HSSFWorkbook(); try { HSSFSheet sheet = wb.createSheet(); HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); EscherAggregate aggregate = HSSFTestHelper.getEscherAggregate(patriarch); EscherDgRecord dgRecord = (EscherDgRecord) aggregate.getEscherRecord(0).getChild(0); assertEquals(dgRecord.getNumShapes(), 1); } finally { wb.close(); } }
/* 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 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()); }
private void writeData2Excel(HSSFSheet sheet, HSSFCellStyle dataStyle, HSSFCellStyle headerStyle) throws ContainerException { RowSet searchResult = executeFrm.getSearchResult(); if (searchResult != null && !searchResult.isEmpty()) { List<ReportViewFrm> definedViews = executeFrm.getDefinedViews(); int fieldCount = definedViews.size(); int dataCount = searchResult.getSize(); int xlsRowNum = 0; for (int i = 0; i < dataCount; i++) { Row row = searchResult.getRow(i); xlsRowNum = i; if (isFirstFile) { xlsRowNum++; } HSSFRow xlsRow = sheet.createRow(xlsRowNum); xlsRow.setHeightInPoints(18); for (short j = 0; j < fieldCount; j++) { ReportViewFrm viewFrm = definedViews.get(j); String fieldName = viewFrm.getFieldName(); HSSFCell xlsCell = xlsRow.createCell(j); xlsCell.setCellStyle(dataStyle); HSSFRichTextString richText = new HSSFRichTextString(row.getStrValue(fieldName)); xlsCell.setCellValue(richText); } } } }
public void testBug45312() throws Exception { HSSFWorkbook wb = new HSSFWorkbook(); try { HSSFSheet sheet = wb.createSheet(); HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); { HSSFClientAnchor a1 = new HSSFClientAnchor(); a1.setAnchor((short) 1, 1, 0, 0, (short) 1, 1, 512, 100); HSSFSimpleShape shape1 = patriarch.createSimpleShape(a1); shape1.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE); } { HSSFClientAnchor a1 = new HSSFClientAnchor(); a1.setAnchor((short) 1, 1, 512, 0, (short) 1, 1, 1024, 100); HSSFSimpleShape shape1 = patriarch.createSimpleShape(a1); shape1.setFlipVertical(true); shape1.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE); } { HSSFClientAnchor a1 = new HSSFClientAnchor(); a1.setAnchor((short) 2, 2, 0, 0, (short) 2, 2, 512, 100); HSSFSimpleShape shape1 = patriarch.createSimpleShape(a1); shape1.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE); } { HSSFClientAnchor a1 = new HSSFClientAnchor(); a1.setAnchor((short) 2, 2, 0, 100, (short) 2, 2, 512, 200); HSSFSimpleShape shape1 = patriarch.createSimpleShape(a1); shape1.setFlipHorizontal(true); shape1.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE); } /*OutputStream stream = new FileOutputStream("/tmp/45312.xls"); try { wb.write(stream); } finally { stream.close(); }*/ checkWorkbookBack(wb); } finally { wb.close(); } }
public void testOpt() throws Exception { HSSFWorkbook wb = new HSSFWorkbook(); try { // create a sheet with a text box HSSFSheet sheet = wb.createSheet(); HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); HSSFTextbox textbox = patriarch.createTextbox(new HSSFClientAnchor()); EscherOptRecord opt1 = HSSFTestHelper.getOptRecord(textbox); EscherOptRecord opt2 = HSSFTestHelper.getEscherContainer(textbox).getChildById(EscherOptRecord.RECORD_ID); assertSame(opt1, opt2); } finally { wb.close(); } }
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); }
private void writeSheet( List<Map<String, String>> valueMaps, String worksheetName, HSSFWorkbook wb) { // Set column widths HSSFSheet sheet = wb.createSheet(worksheetName); sheet.setColumnWidth(Short.parseShort("0"), Short.parseShort("15000")); sheet.setColumnWidth(Short.parseShort("1"), Short.parseShort("30000")); // header style HSSFCellStyle headerStyle; HSSFFont headerFont = wb.createFont(); headerFont.setFontHeightInPoints((short) 11); headerStyle = wb.createCellStyle(); headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); headerStyle.setFillForegroundColor(HSSFColor.GREY_50_PERCENT.index); headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); headerStyle.setFont(headerFont); headerStyle.setWrapText(true); // header row HSSFRow headerRow = sheet.createRow(0); headerRow.setHeightInPoints(30); HSSFCell headerCell0 = headerRow.createCell((short) 0); HSSFCell headerCell1 = headerRow.createCell((short) 1); headerCell0.setCellStyle(headerStyle); setText(headerCell0, "Layer Name"); headerCell1.setCellStyle(headerStyle); setText(headerCell1, "Message"); int counter = 1; for (Map<String, String> valueMap : valueMaps) { HSSFRow dataRow = sheet.createRow(counter); String layer = valueMap.get("layer"); String status = valueMap.get("status"); status = HtmlUtils.htmlUnescape(status); HSSFCell currentCell0 = dataRow.createCell((short) 0); HSSFCell currentCell1 = dataRow.createCell((short) 1); setText(currentCell0, layer); setText(currentCell1, status); counter++; } }
public void testCorrectOrderInOptRecord() throws IOException { HSSFWorkbook wb = new HSSFWorkbook(); try { HSSFSheet sheet = wb.createSheet(); HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); HSSFTextbox textbox = patriarch.createTextbox(new HSSFClientAnchor()); EscherOptRecord opt = HSSFTestHelper.getOptRecord(textbox); String opt1Str = opt.toXml(); textbox.setFillColor(textbox.getFillColor()); EscherContainerRecord container = HSSFTestHelper.getEscherContainer(textbox); EscherOptRecord optRecord = container.getChildById(EscherOptRecord.RECORD_ID); assertEquals(opt1Str, optRecord.toXml()); textbox.setLineStyle(textbox.getLineStyle()); assertEquals(opt1Str, optRecord.toXml()); textbox.setLineWidth(textbox.getLineWidth()); assertEquals(opt1Str, optRecord.toXml()); textbox.setLineStyleColor(textbox.getLineStyleColor()); assertEquals(opt1Str, optRecord.toXml()); } finally { wb.close(); } }