Examples with CreationHelper org.apache.poi.ss.usermodel.CreationHelper used on opensource projects

Example 26 with CreationHelper

use of org.apache.poi.ss.usermodel.CreationHelper in project chilo-producer by cccties.

the class ExcelReader method getStringFormulaValue.

public String getStringFormulaValue(Cell cell) {
    assert cell.getCellType() == Cell.CELL_TYPE_FORMULA;
    CreationHelper helper = workBook.getCreationHelper();
    FormulaEvaluator evaluator = helper.createFormulaEvaluator();
    CellValue value = evaluator.evaluate(cell);
    switch(value.getCellType()) {
        case Cell.CELL_TYPE_STRING:
            return value.getStringValue();
        case Cell.CELL_TYPE_NUMERIC:
            return Double.toString(value.getNumberValue());
        case Cell.CELL_TYPE_BOOLEAN:
            return Boolean.toString(value.getBooleanValue());
        default:
            System.out.println(value.getCellType());
            return null;
    }
}

Example 27 with CreationHelper

use of org.apache.poi.ss.usermodel.CreationHelper in project poi by apache.

the class TestOLE2Embeding method testReallyEmbedSomething.

@Test
public void testReallyEmbedSomething() throws Exception {
    HSSFWorkbook wb1 = new HSSFWorkbook();
    HSSFSheet sheet = wb1.createSheet();
    HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
    byte[] pictureData = HSSFTestDataSamples.getTestDataFileContent("logoKarmokar4.png");
    byte[] picturePPT = POIDataSamples.getSlideShowInstance().readFile("clock.jpg");
    int imgIdx = wb1.addPicture(pictureData, HSSFWorkbook.PICTURE_TYPE_PNG);
    POIFSFileSystem pptPoifs = getSamplePPT();
    int pptIdx = wb1.addOlePackage(pptPoifs, "Sample-PPT", "sample.ppt", "sample.ppt");
    POIFSFileSystem xlsPoifs = getSampleXLS();
    int imgPPT = wb1.addPicture(picturePPT, HSSFWorkbook.PICTURE_TYPE_JPEG);
    int xlsIdx = wb1.addOlePackage(xlsPoifs, "Sample-XLS", "sample.xls", "sample.xls");
    int txtIdx = wb1.addOlePackage(getSampleTXT(), "Sample-TXT", "sample.txt", "sample.txt");
    int rowoffset = 5;
    int coloffset = 5;
    CreationHelper ch = wb1.getCreationHelper();
    HSSFClientAnchor anchor = (HSSFClientAnchor) ch.createClientAnchor();
    anchor.setAnchor((short) (2 + coloffset), 1 + rowoffset, 0, 0, (short) (3 + coloffset), 5 + rowoffset, 0, 0);
    anchor.setAnchorType(AnchorType.DONT_MOVE_AND_RESIZE);
    patriarch.createObjectData(anchor, pptIdx, imgPPT);
    anchor = (HSSFClientAnchor) ch.createClientAnchor();
    anchor.setAnchor((short) (5 + coloffset), 1 + rowoffset, 0, 0, (short) (6 + coloffset), 5 + rowoffset, 0, 0);
    anchor.setAnchorType(AnchorType.DONT_MOVE_AND_RESIZE);
    patriarch.createObjectData(anchor, xlsIdx, imgIdx);
    anchor = (HSSFClientAnchor) ch.createClientAnchor();
    anchor.setAnchor((short) (3 + coloffset), 10 + rowoffset, 0, 0, (short) (5 + coloffset), 11 + rowoffset, 0, 0);
    anchor.setAnchorType(AnchorType.DONT_MOVE_AND_RESIZE);
    patriarch.createObjectData(anchor, txtIdx, imgIdx);
    anchor = (HSSFClientAnchor) ch.createClientAnchor();
    anchor.setAnchor((short) (1 + coloffset), -2 + rowoffset, 0, 0, (short) (7 + coloffset), 14 + rowoffset, 0, 0);
    anchor.setAnchorType(AnchorType.DONT_MOVE_AND_RESIZE);
    HSSFSimpleShape circle = patriarch.createSimpleShape(anchor);
    circle.setShapeType(HSSFSimpleShape.OBJECT_TYPE_OVAL);
    circle.setNoFill(true);
    //        if (false) {
    //	        FileOutputStream fos = new FileOutputStream("embed.xls");
    //	        wb.write(fos);
    //	        fos.close();
    //        }
    HSSFWorkbook wb2 = HSSFTestDataSamples.writeOutAndReadBack(wb1);
    wb1.close();
    ByteArrayOutputStream bos = new ByteArrayOutputStream();
    HSSFObjectData od = wb2.getAllEmbeddedObjects().get(0);
    Ole10Native ole10 = Ole10Native.createFromEmbeddedOleObject((DirectoryNode) od.getDirectory());
    bos.reset();
    pptPoifs.writeFilesystem(bos);
    assertArrayEquals(ole10.getDataBuffer(), bos.toByteArray());
    od = wb2.getAllEmbeddedObjects().get(1);
    ole10 = Ole10Native.createFromEmbeddedOleObject((DirectoryNode) od.getDirectory());
    bos.reset();
    xlsPoifs.writeFilesystem(bos);
    assertArrayEquals(ole10.getDataBuffer(), bos.toByteArray());
    od = wb2.getAllEmbeddedObjects().get(2);
    ole10 = Ole10Native.createFromEmbeddedOleObject((DirectoryNode) od.getDirectory());
    assertArrayEquals(ole10.getDataBuffer(), getSampleTXT());
    xlsPoifs.close();
    pptPoifs.close();
    wb2.close();
}

Example 28 with CreationHelper

use of org.apache.poi.ss.usermodel.CreationHelper in project poi by apache.

the class TestXSSFCell method testCopyCellFrom_CellCopyPolicy_copyHyperlink.

@Test
public final void testCopyCellFrom_CellCopyPolicy_copyHyperlink() throws IOException {
    setUp_testCopyCellFrom_CellCopyPolicy();
    final Workbook wb = srcCell.getSheet().getWorkbook();
    final CreationHelper createHelper = wb.getCreationHelper();
    srcCell.setCellValue("URL LINK");
    Hyperlink link = createHelper.createHyperlink(HyperlinkType.URL);
    link.setAddress("http://poi.apache.org/");
    srcCell.setHyperlink(link);
    // Set link cell style (optional)
    CellStyle hlinkStyle = wb.createCellStyle();
    Font hlinkFont = wb.createFont();
    hlinkFont.setUnderline(Font.U_SINGLE);
    hlinkFont.setColor(IndexedColors.BLUE.getIndex());
    hlinkStyle.setFont(hlinkFont);
    srcCell.setCellStyle(hlinkStyle);
    // Copy hyperlink
    final CellCopyPolicy policy = new CellCopyPolicy.Builder().copyHyperlink(true).mergeHyperlink(false).build();
    destCell.copyCellFrom(srcCell, policy);
    assertNotNull(destCell.getHyperlink());
    assertSame("unit test assumes srcCell and destCell are on the same sheet", srcCell.getSheet(), destCell.getSheet());
    final List<XSSFHyperlink> links = srcCell.getSheet().getHyperlinkList();
    assertEquals("number of hyperlinks on sheet", 2, links.size());
    assertEquals("source hyperlink", new CellReference(srcCell).formatAsString(), links.get(0).getCellRef());
    assertEquals("destination hyperlink", new CellReference(destCell).formatAsString(), links.get(1).getCellRef());
    wb.close();
}

Example 29 with CreationHelper

use of org.apache.poi.ss.usermodel.CreationHelper in project poi by apache.

the class TestHSSFComment method insertComment.

private Comment insertComment(Drawing<?> drawing, Cell cell, String message) {
    CreationHelper factory = cell.getSheet().getWorkbook().getCreationHelper();
    ClientAnchor anchor = factory.createClientAnchor();
    anchor.setCol1(cell.getColumnIndex());
    anchor.setCol2(cell.getColumnIndex() + 1);
    anchor.setRow1(cell.getRowIndex());
    anchor.setRow2(cell.getRowIndex() + 1);
    anchor.setDx1(100);
    anchor.setDx2(100);
    anchor.setDy1(100);
    anchor.setDy2(100);
    Comment comment = drawing.createCellComment(anchor);
    RichTextString str = factory.createRichTextString(message);
    comment.setString(str);
    comment.setAuthor("fanfy");
    cell.setCellComment(comment);
    return comment;
}

Example 30 with CreationHelper

use of org.apache.poi.ss.usermodel.CreationHelper in project poi by apache.

the class TestHSSFPicture method bug49658.

@SuppressWarnings("resource")
@Test
public void bug49658() throws IOException {
    // test if inserted EscherMetafileBlip will be read again
    HSSFWorkbook wb = new HSSFWorkbook();
    byte[] pictureDataEmf = POIDataSamples.getDocumentInstance().readFile("vector_image.emf");
    int indexEmf = wb.addPicture(pictureDataEmf, HSSFWorkbook.PICTURE_TYPE_EMF);
    byte[] pictureDataPng = POIDataSamples.getSpreadSheetInstance().readFile("logoKarmokar4.png");
    int indexPng = wb.addPicture(pictureDataPng, HSSFWorkbook.PICTURE_TYPE_PNG);
    byte[] pictureDataWmf = POIDataSamples.getSlideShowInstance().readFile("santa.wmf");
    int indexWmf = wb.addPicture(pictureDataWmf, HSSFWorkbook.PICTURE_TYPE_WMF);
    HSSFSheet sheet = wb.createSheet();
    HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
    CreationHelper ch = wb.getCreationHelper();
    ClientAnchor anchor = ch.createClientAnchor();
    anchor.setCol1(2);
    anchor.setCol2(5);
    anchor.setRow1(1);
    anchor.setRow2(6);
    patriarch.createPicture(anchor, indexEmf);
    anchor = ch.createClientAnchor();
    anchor.setCol1(2);
    anchor.setCol2(5);
    anchor.setRow1(10);
    anchor.setRow2(16);
    patriarch.createPicture(anchor, indexPng);
    anchor = ch.createClientAnchor();
    anchor.setCol1(6);
    anchor.setCol2(9);
    anchor.setRow1(1);
    anchor.setRow2(6);
    patriarch.createPicture(anchor, indexWmf);
    wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
    byte[] pictureDataOut = wb.getAllPictures().get(0).getData();
    assertArrayEquals(pictureDataEmf, pictureDataOut);
    byte[] wmfNoHeader = new byte[pictureDataWmf.length - 22];
    System.arraycopy(pictureDataWmf, 22, wmfNoHeader, 0, pictureDataWmf.length - 22);
    pictureDataOut = wb.getAllPictures().get(2).getData();
    assertArrayEquals(wmfNoHeader, pictureDataOut);
    wb.close();
}