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

Example 1 with CreationHelper

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

the class WorkingWithPictures method main.

public static void main(String[] args) throws IOException {
    //create a new workbook
    //or new HSSFWorkbook();
    Workbook wb = new XSSFWorkbook();
    try {
        CreationHelper helper = wb.getCreationHelper();
        //add a picture in this workbook.
        InputStream is = new FileInputStream(args[0]);
        byte[] bytes = IOUtils.toByteArray(is);
        is.close();
        int pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);
        //create sheet
        Sheet sheet = wb.createSheet();
        //create drawing
        Drawing<?> drawing = sheet.createDrawingPatriarch();
        //add a picture shape
        ClientAnchor anchor = helper.createClientAnchor();
        anchor.setCol1(1);
        anchor.setRow1(1);
        Picture pict = drawing.createPicture(anchor, pictureIdx);
        //auto-size picture
        pict.resize(2);
        //save workbook
        String file = "picture.xls";
        if (wb instanceof XSSFWorkbook) {
            // NOSONAR
            file += "x";
        }
        OutputStream fileOut = new FileOutputStream(file);
        try {
            wb.write(fileOut);
        } finally {
            fileOut.close();
        }
    } finally {
        wb.close();
    }
}

Example 2 with CreationHelper

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

the class AligningCells method centerAcrossSelection.

/**
     * Center a text over multiple columns using ALIGN_CENTER_SELECTION
     *
     * @param wb the workbook
     * @param row the row to create the cell in
     * @param start_column  the column number to create the cell in and where the selection starts
     * @param end_column    the column number where the selection ends
     * @param valign the horizontal alignment for the cell.
     */
private static void centerAcrossSelection(XSSFWorkbook wb, XSSFRow row, int start_column, int end_column, VerticalAlignment valign) {
    CreationHelper ch = wb.getCreationHelper();
    // Create cell style with ALIGN_CENTER_SELECTION
    XSSFCellStyle cellStyle = wb.createCellStyle();
    cellStyle.setAlignment(HorizontalAlignment.CENTER_SELECTION);
    cellStyle.setVerticalAlignment(valign);
    // Create cells over the selected area
    for (int i = start_column; i <= end_column; i++) {
        XSSFCell cell = row.createCell(i);
        cell.setCellStyle(cellStyle);
    }
    // Set value to the first cell
    XSSFCell cell = row.getCell(start_column);
    cell.setCellValue(ch.createRichTextString("Align It"));
    // Make the selection
    CTRowImpl ctRow = (CTRowImpl) row.getCTRow();
    // Add object with format start_coll:end_coll. For example 1:3 will span from
    // cell 1 to cell 3, where the column index starts with 0
    //
    // You can add multiple spans for one row
    Object span = start_column + ":" + end_column;
    List<Object> spanList = new ArrayList<Object>();
    spanList.add(span);
    //add spns to the row
    ctRow.setSpans(spanList);
}

Example 3 with CreationHelper

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

the class AligningCells method createCell.

/**
     * Creates a cell and aligns it a certain way.
     *
     * @param wb     the workbook
     * @param row    the row to create the cell in
     * @param column the column number to create the cell in
     * @param halign the horizontal alignment for the cell.
     */
private static void createCell(XSSFWorkbook wb, XSSFRow row, int column, HorizontalAlignment halign, VerticalAlignment valign) {
    CreationHelper ch = wb.getCreationHelper();
    XSSFCell cell = row.createCell(column);
    cell.setCellValue(ch.createRichTextString("Align It"));
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setAlignment(halign);
    cellStyle.setVerticalAlignment(valign);
    cell.setCellStyle(cellStyle);
}

Example 4 with CreationHelper

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

the class TestXSSFComment method testBug58175a.

@Ignore("Used for manual testing with opening the resulting Workbook in Excel")
@Test
public void testBug58175a() throws IOException {
    Workbook wb = new SXSSFWorkbook();
    try {
        Sheet sheet = wb.createSheet();
        Row row = sheet.createRow(1);
        Cell cell = row.createCell(3);
        cell.setCellValue("F4");
        Drawing<?> drawing = sheet.createDrawingPatriarch();
        CreationHelper factory = wb.getCreationHelper();
        // When the comment box is visible, have it show in a 1x3 space
        ClientAnchor anchor = factory.createClientAnchor();
        anchor.setCol1(cell.getColumnIndex());
        anchor.setCol2(cell.getColumnIndex() + 1);
        anchor.setRow1(row.getRowNum());
        anchor.setRow2(row.getRowNum() + 3);
        // Create the comment and set the text+author
        Comment comment = drawing.createCellComment(anchor);
        RichTextString str = factory.createRichTextString("Hello, World!");
        comment.setString(str);
        comment.setAuthor("Apache POI");
        /* fixed the problem as well 
             * comment.setColumn(cell.getColumnIndex());
             * comment.setRow(cell.getRowIndex());
             */
        // Assign the comment to the cell
        cell.setCellComment(comment);
        OutputStream out = new FileOutputStream("C:\\temp\\58175.xlsx");
        try {
            wb.write(out);
        } finally {
            out.close();
        }
    } finally {
        wb.close();
    }
}

Example 5 with CreationHelper

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

the class TestXSSFComment method testBug58175.

@Test
public void testBug58175() throws IOException {
    Workbook wb = new SXSSFWorkbook();
    try {
        Sheet sheet = wb.createSheet();
        Row row = sheet.createRow(1);
        Cell cell = row.createCell(3);
        cell.setCellValue("F4");
        CreationHelper factory = wb.getCreationHelper();
        // When the comment box is visible, have it show in a 1x3 space
        ClientAnchor anchor = factory.createClientAnchor();
        anchor.setCol1(cell.getColumnIndex());
        anchor.setCol2(cell.getColumnIndex() + 1);
        anchor.setRow1(row.getRowNum());
        anchor.setRow2(row.getRowNum() + 3);
        XSSFClientAnchor ca = (XSSFClientAnchor) anchor;
        // create comments and vmlDrawing parts if they don't exist
        CommentsTable comments = ((SXSSFWorkbook) wb).getXSSFWorkbook().getSheetAt(0).getCommentsTable(true);
        XSSFVMLDrawing vml = ((SXSSFWorkbook) wb).getXSSFWorkbook().getSheetAt(0).getVMLDrawing(true);
        CTShape vmlShape1 = vml.newCommentShape();
        if (ca.isSet()) {
            String position = ca.getCol1() + ", 0, " + ca.getRow1() + ", 0, " + ca.getCol2() + ", 0, " + ca.getRow2() + ", 0";
            vmlShape1.getClientDataArray(0).setAnchorArray(0, position);
        }
        // create the comment in two different ways and verify that there is no difference
        XSSFComment shape1 = new XSSFComment(comments, comments.newComment(CellAddress.A1), vmlShape1);
        shape1.setColumn(ca.getCol1());
        shape1.setRow(ca.getRow1());
        CTShape vmlShape2 = vml.newCommentShape();
        if (ca.isSet()) {
            String position = ca.getCol1() + ", 0, " + ca.getRow1() + ", 0, " + ca.getCol2() + ", 0, " + ca.getRow2() + ", 0";
            vmlShape2.getClientDataArray(0).setAnchorArray(0, position);
        }
        CellAddress ref = new CellAddress(ca.getRow1(), ca.getCol1());
        XSSFComment shape2 = new XSSFComment(comments, comments.newComment(ref), vmlShape2);
        assertEquals(shape1.getAuthor(), shape2.getAuthor());
        assertEquals(shape1.getClientAnchor(), shape2.getClientAnchor());
        assertEquals(shape1.getColumn(), shape2.getColumn());
        assertEquals(shape1.getRow(), shape2.getRow());
        assertEquals(shape1.getCTComment().toString(), shape2.getCTComment().toString());
        assertEquals(shape1.getCTComment().getRef(), shape2.getCTComment().getRef());
        /*CommentsTable table1 = shape1.getCommentsTable();
            CommentsTable table2 = shape2.getCommentsTable();
            assertEquals(table1.getCTComments().toString(), table2.getCTComments().toString());
            assertEquals(table1.getNumberOfComments(), table2.getNumberOfComments());
            assertEquals(table1.getRelations(), table2.getRelations());*/
        assertEquals("The vmlShapes should have equal content afterwards", vmlShape1.toString().replaceAll("_x0000_s\\d+", "_x0000_s0000"), vmlShape2.toString().replaceAll("_x0000_s\\d+", "_x0000_s0000"));
    } finally {
        wb.close();
    }
}