Example usage for org.apache.poi.ss.usermodel ClientAnchor setCol1

List of usage examples for org.apache.poi.ss.usermodel ClientAnchor setCol1

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel ClientAnchor setCol1.

Prototype

public void setCol1(int col1);

Source Link

Document

Sets the column (0 based) of the first cell.

Usage

From source file:controller.VisitasController.java

public void exportExcel() {

    FileChooser fileChooser = new FileChooser();
    fileChooser.setTitle("Open Resource File");
    fileChooser.setInitialDirectory(new File(System.getProperty("user.home")));
    fileChooser.getExtensionFilters().add(new FileChooser.ExtensionFilter("XLS", "*.xls"));
    File f = fileChooser.showSaveDialog(null);

    try {/*from  www .j  a v a  2 s . c o m*/
        String filename = f.getAbsolutePath();
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("FirstSheet");
        int bool = 1;

        InputStream inputStream = getClass().getResourceAsStream("/images/excel-logo.jpg");

        byte[] imageBytes = IOUtils.toByteArray(inputStream);

        int pictureureIdx = workbook.addPicture(imageBytes, Workbook.PICTURE_TYPE_JPEG);

        inputStream.close();

        CreationHelper helper = workbook.getCreationHelper();

        Drawing drawing = sheet.createDrawingPatriarch();

        ClientAnchor anchor = helper.createClientAnchor();

        anchor.setCol1(1);
        anchor.setRow1(0);

        Picture pict = drawing.createPicture(anchor, pictureureIdx);

        HSSFRow rowhead = sheet.createRow(8);

        rowhead.createCell(0);
        rowhead.createCell(1).setCellValue("Cedula");
        rowhead.createCell(2).setCellValue("Cliente");
        rowhead.createCell(3).setCellValue("Contrato");
        rowhead.createCell(4).setCellValue("Plan");
        rowhead.createCell(5).setCellValue("Fecha");
        rowhead.createCell(6).setCellValue("Hora");
        rowhead.createCell(7).setCellValue("Invitados");
        makeRowBold(workbook, rowhead);

        for (int i = 0; i < table.getItems().size(); i++) {
            HSSFRow row = sheet.createRow(i + 9);
            Asistencia a = (Asistencia) table.getItems().get(i);
            row.createCell(1).setCellValue(a.getCedula());
            row.createCell(2).setCellValue(a.getNombre());
            row.createCell(3).setCellValue(a.getContrato());
            row.createCell(4).setCellValue(a.getPlan());
            row.createCell(5).setCellValue(a.getFecha());
            row.createCell(6).setCellValue(a.getHora());
            row.createCell(7).setCellValue(Integer.parseInt(a.getInvitados()));
            centerRow(workbook, row);
        }
        autoSizeColumns(workbook);
        pict.resize();
        FileOutputStream fileOut = new FileOutputStream(filename);
        workbook.write(fileOut);
        fileOut.close();

    } catch (Exception ex) {
        ex.printStackTrace();
    }

}

From source file:de.enerko.reports2.engine.Report.java

License:Apache License

/**
 * This method adds a new cell to the sheet of a workbook. It could 
 * (together with {@link #fill(Workbook, Cell, String, String, boolean)}) be moved to
 * the {@link CellDefinition} itself, but that would mean that the {@link CellDefinition} is
 * tied to a specific Excel API. Having those methods here allows the Report to become
 * an interface if a second engine (i.e. JXL) should be added in the future.
 * @param workbook/*from  w ww.  ja v  a 2  s  . c om*/
 * @param sheet
 * @param cellDefinition
 */
private void addCell(final Workbook workbook, final Sheet sheet, final CellDefinition cellDefinition) {
    final int columnNum = cellDefinition.column, rowNum = cellDefinition.row;

    Row row = sheet.getRow(rowNum);
    if (row == null)
        row = sheet.createRow(rowNum);

    Cell cell = row.getCell(columnNum);
    // If the cell already exists and is no blank cell
    // it will be used including all formating
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell = fill(workbook, cell, cellDefinition, false);
    }
    // Otherwise a new cell will be created, the datatype set and 
    // optionally a format will be created
    else {
        cell = fill(workbook, row.createCell(columnNum), cellDefinition, true);

        final Sheet referenceSheet;
        if (cellDefinition.getReferenceCell() != null
                && (referenceSheet = workbook.getSheet(cellDefinition.getReferenceCell().sheetname)) != null) {
            final Row referenceRow = referenceSheet.getRow(cellDefinition.getReferenceCell().row);
            final Cell referenceCell = referenceRow == null ? null
                    : referenceRow.getCell(cellDefinition.getReferenceCell().column);
            if (referenceCell != null && referenceCell.getCellStyle() != null)
                cell.setCellStyle(referenceCell.getCellStyle());
        }
    }

    // Add an optional comment      
    if (cellDefinition.hasComment()) {
        final CreationHelper factory = workbook.getCreationHelper();

        final Drawing drawing = sheet.createDrawingPatriarch();
        final ClientAnchor commentAnchor = factory.createClientAnchor();

        final int col1 = cellDefinition.comment.column == null ? cell.getColumnIndex() + 1
                : cellDefinition.comment.column;
        final int row1 = cellDefinition.comment.row == null ? cell.getRowIndex() : cellDefinition.comment.row;

        commentAnchor.setCol1(col1);
        commentAnchor.setRow1(row1);
        commentAnchor.setCol2(col1 + Math.max(1, cellDefinition.comment.width));
        commentAnchor.setRow2(row1 + Math.max(1, cellDefinition.comment.height));

        final Comment comment = drawing.createCellComment(commentAnchor);
        comment.setString(factory.createRichTextString(cellDefinition.comment.text));
        comment.setAuthor(cellDefinition.comment.author);
        comment.setVisible(cellDefinition.comment.visible);

        cell.setCellComment(comment);
    }
}

From source file:de.fme.alfresco.repo.web.scripts.DeclarativeSpreadsheetWebScript.java

License:Open Source License

/**
 * Generates the spreadsheet, based on the properties in the header
 *  and a callback for the body./* w  w  w.  ja  v  a  2s  .  c  o m*/
 */
public void generateSpreadsheet(Object resource, String format, WebScriptRequest req, Status status,
        Map<String, Object> model) throws IOException {
    Pattern qnameMunger = Pattern.compile("([A-Z][a-z]+)([A-Z].*)");

    // Build up the details of the header
    List<Pair<QName, Boolean>> propertyDetails = buildPropertiesForHeader(resource, format, req);
    String[] headings = new String[propertyDetails.size()];
    String[] descriptions = new String[propertyDetails.size()];
    boolean[] required = new boolean[propertyDetails.size()];
    for (int i = 0; i < headings.length; i++) {
        Pair<QName, Boolean> property = propertyDetails.get(i);
        if (property == null || property.getFirst() == null) {
            headings[i] = "";
            required[i] = false;
        } else {
            QName column = property.getFirst();
            required[i] = property.getSecond();

            // Ask the dictionary service nicely for the details
            PropertyDefinition pd = dictionaryService.getProperty(column);
            if (pd != null && pd.getTitle() != null) {
                // Use the friendly titles, which may even be localised!
                headings[i] = pd.getTitle();
                descriptions[i] = pd.getDescription();
            } else {
                // Nothing friendly found, try to munge the raw qname into
                //  something we can show to a user...
                String raw = column.getLocalName();
                raw = raw.substring(0, 1).toUpperCase() + raw.substring(1);

                Matcher m = qnameMunger.matcher(raw);
                if (m.matches()) {
                    headings[i] = m.group(1) + " " + m.group(2);
                } else {
                    headings[i] = raw;
                }
            }
        }
    }

    // Build a list of just the properties
    List<QName> properties = new ArrayList<QName>(propertyDetails.size());
    for (Pair<QName, Boolean> p : propertyDetails) {
        QName qn = null;
        if (p != null) {
            qn = p.getFirst();
        }
        properties.add(qn);
    }

    // Output
    if ("csv".equals(format)) {
        StringWriter sw = new StringWriter();
        CSVPrinter csv = new CSVPrinter(sw, CSVStrategy.EXCEL_STRATEGY);
        csv.println(headings);

        populateBody(resource, csv, properties);

        model.put(MODEL_CSV, sw.toString());
    } else {
        Workbook wb;
        if ("xlsx".equals(format)) {
            wb = new XSSFWorkbook();
            // TODO Properties
        } else {
            wb = new HSSFWorkbook();
            // TODO Properties
        }

        // Add our header row
        Sheet sheet = wb.createSheet("Export");
        Row hr = sheet.createRow(0);
        try {
            sheet.createFreezePane(0, 1);
        } catch (IndexOutOfBoundsException e) {
            //https://issues.apache.org/bugzilla/show_bug.cgi?id=51431 & http://stackoverflow.com/questions/6469693/apache-poi-clearing-freeze-split-panes
        }
        Font fb = wb.createFont();
        fb.setBoldweight(Font.BOLDWEIGHT_BOLD);
        Font fi = wb.createFont();
        fi.setBoldweight(Font.BOLDWEIGHT_BOLD);
        fi.setItalic(true);

        CellStyle csReq = wb.createCellStyle();
        csReq.setFont(fb);
        CellStyle csOpt = wb.createCellStyle();
        csOpt.setFont(fi);

        // Populate the header
        Drawing draw = null;
        for (int i = 0; i < headings.length; i++) {
            Cell c = hr.createCell(i);
            c.setCellValue(headings[i]);

            if (required[i]) {
                c.setCellStyle(csReq);
            } else {
                c.setCellStyle(csOpt);
            }

            if (headings[i].length() == 0) {
                sheet.setColumnWidth(i, 3 * 250);
            } else {
                sheet.setColumnWidth(i, 18 * 250);
            }

            if (descriptions[i] != null && descriptions[i].length() > 0) {
                // Add a description for it too
                if (draw == null) {
                    draw = sheet.createDrawingPatriarch();
                }
                ClientAnchor ca = wb.getCreationHelper().createClientAnchor();
                ca.setCol1(c.getColumnIndex());
                ca.setCol2(c.getColumnIndex() + 1);
                ca.setRow1(hr.getRowNum());
                ca.setRow2(hr.getRowNum() + 2);

                Comment cmt = draw.createCellComment(ca);
                cmt.setAuthor("");
                cmt.setString(wb.getCreationHelper().createRichTextString(descriptions[i]));
                cmt.setVisible(false);
                c.setCellComment(cmt);
            }
        }

        // Have the contents populated
        populateBody(resource, wb, sheet, properties);

        // Save it for the template
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        wb.write(baos);
        model.put(MODEL_EXCEL, baos.toByteArray());
    }
}

From source file:de.iteratec.iteraplan.businesslogic.exchange.elasticExcel.export.template.AbstractIntroSheetGenerator.java

License:Open Source License

private void insertIteraplanLogo(int colIndex, int rowIndex) {
    CreationHelper helper = getWorkbook().getCreationHelper();

    byte[] bytes;
    try {//from  w w  w  .  j  a  v a 2  s . co m
        InputStream is = logoImage.getInputStream();
        bytes = IOUtils.toByteArray(is);
    } catch (IOException e) {
        LOGGER.error("Could not read the excel template!", e);
        throw new IteraplanTechnicalException(IteraplanErrorMessages.INVALID_EXCEL_TEMPLATE);
    }
    int pictureIdx = getWorkbook().addPicture(bytes, Workbook.PICTURE_TYPE_PNG);

    Sheet sheet = getIntroductionSheet();
    Drawing drawing = sheet.createDrawingPatriarch();
    ClientAnchor anchor = helper.createClientAnchor();
    //set top-left corner of the picture
    anchor.setCol1(colIndex);
    anchor.setRow1(rowIndex);
    Picture pict = drawing.createPicture(anchor, pictureIdx);

    pict.resize();
}

From source file:de.iteratec.iteraplan.businesslogic.exchange.legacyExcel.exporter.ExportWorkbook.java

License:Open Source License

/**
 * Adds headers stored in <code>headers</code> to the current sheet. If required, a special width
 * for the corresponding column can be set by providing a value in <code>headersWidth</code> using
 * header as key.<br/>/*w w  w . j  a va  2  s.c  om*/
 * <b>IMPORTANT</b>: Headers are added in the order provided in <code>headers</code>.
 * 
 * @param headers
 *          headers to be added
 */
public void addHeaders(int sheetId, List<ExcelSheet.Header> headers) {
    Sheet sheet = getSheetById(sheetId);
    Drawing drawing = sheet.createDrawingPatriarch();
    CreationHelper factory = sheet.getWorkbook().getCreationHelper();
    Row row = sheet.createRow(this.getCurrentRowOfSheet(sheet, 3));
    int columnIndex = 0;
    for (ExcelSheet.Header header : headers) {
        int currColumnIndex = columnIndex;
        Cell cell = row.createCell(columnIndex);
        if (header.getDescription() != null) {
            ClientAnchor commentAnchor = factory.createClientAnchor();
            //Sizing the comment 1x3 cells
            commentAnchor.setCol1(cell.getColumnIndex());
            commentAnchor.setCol2(cell.getColumnIndex() + 1);
            commentAnchor.setRow1(row.getRowNum());
            commentAnchor.setRow2(row.getRowNum() + 3);

            Comment comment = drawing.createCellComment(commentAnchor);
            RichTextString str = factory.createRichTextString(header.getDescription());
            comment.setString(str);
            comment.setAuthor("");
            cell.setCellComment(comment);
        }

        setCellValue(cell, header.getLabel(), getHeaderTableStyle());
        Integer width = header.getWidth();
        if (width != null) {
            sheet.setColumnWidth(currColumnIndex, width.intValue());
        }
        columnIndex++;
    }

    LOGGER.debug("Added headers.");
}

From source file:de.jlo.talendcomp.excel.SpreadsheetOutput.java

License:Apache License

private void setCellComment(Cell cell, String comment) {
    if (comment == null || comment.trim().isEmpty()) {
        cell.removeCellComment();/*from  w  w w.ja  v a2  s  .c om*/
    } else {
        Comment c = cell.getCellComment();
        if (c == null) {
            ClientAnchor anchor = creationHelper.createClientAnchor();
            anchor.setRow1(cell.getRowIndex());
            anchor.setRow2(cell.getRowIndex() + commentHeight);
            anchor.setCol1(cell.getColumnIndex() + 1);
            anchor.setCol2(cell.getColumnIndex() + commentWidth + 1);
            anchor.setAnchorType(AnchorType.MOVE_AND_RESIZE);
            c = getDrawing().createCellComment(anchor);
            c.setVisible(false);
            if (commentAuthor != null) {
                c.setAuthor(commentAuthor);
            }
            cell.setCellComment(c);
        }
        RichTextString rts = creationHelper.createRichTextString(comment);
        c.setString(rts);
    }
}

From source file:de.viaboxx.nlstools.formats.MBExcelPersistencer.java

License:Apache License

private Comment comment(HSSFCell cell, String text) {
    CreationHelper factory = wb.getCreationHelper();
    Drawing drawing = sheet.createDrawingPatriarch();
    // 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(cell.getRow().getRowNum());
    anchor.setRow2(cell.getRow().getRowNum() + 3);
    Comment comment = drawing.createCellComment(anchor);
    RichTextString str = factory.createRichTextString(text);
    comment.setString(str);//ww  w  .ja  v  a 2 s.c o m
    cell.setCellComment(comment);
    return comment;
}

From source file:demo.poi.AddDimensionedImage.java

License:Apache License

/**
 * Add an image to a worksheet.// w w w .ja v a 2s  . com
 *
 * @param colNumber
 *            A primitive int that contains the index number of a column on
 *            the worksheet; POI column indices are zero based. Together
 *            with the rowNumber parameter's value, this parameter
 *            identifies a cell on the worksheet. The images top left hand
 *            corner will be aligned with the top left hand corner of this
 *            cell.
 * @param rowNumber
 *            A primitive int that contains the index number of a row on the
 *            worksheet; POI row indices are zero based. Together with the
 *            rowNumber parameter's value, this parameter identifies a cell
 *            on the worksheet. The images top left hand corner will be
 *            aligned with the top left hand corner of this cell.
 * @param sheet
 *            A reference to the sheet that contains the cell identified by
 *            the two parameters above.
 * @param drawing
 *            An instance of the DrawingPatriarch class. This is now passed
 *            into the method where it was, previously, recovered from the
 *            sheet in order to allow multiple pictures be inserted. If the
 *            patriarch was not 'cached in this manner each time it was
 *            created any previously positioned images would be simply
 *            over-written.
 * @param imageFile
 *            An instance of the URL class that encapsulates the name of and
 *            path to the image that is to be 'inserted into' the sheet.
 * @param reqImageWidthMM
 *            A primitive double that contains the required width of the
 *            image in millimetres.
 * @param reqImageHeightMM
 *            A primitive double that contains the required height of the
 *            image in millimetres.
 * @param resizeBehaviour
 *            A primitive int whose value will determine how the code should
 *            react if the image is larger than the cell referenced by the
 *            colNumber and rowNumber parameters. Four constants are
 *            provided to determine what should happen;
 *            AddDimensionedImage.EXPAND_ROW
 *            AddDimensionedImage.EXPAND_COLUMN
 *            AddDimensionedImage.EXPAND_ROW_AND_COLUMN
 *            AddDimensionedImage.OVERLAY_ROW_AND_COLUMN
 * @throws java.io.FileNotFoundException
 *             If the file containing the image cannot be located.
 * @throws java.io.IOException
 *             If a problem occurs whilst reading the file of image data.
 * @throws java.lang.IllegalArgumentException
 *             If an invalid value is passed to the resizeBehaviour
 *             parameter or if the extension of the image file indicates
 *             that it is of a type that cannot currently be added to the
 *             worksheet.
 */
public void addImageToSheet(int colNumber, int rowNumber, Sheet sheet, Drawing drawing, URL imageFile,
        double reqImageWidthMM, double reqImageHeightMM, int resizeBehaviour)
        throws IOException, IllegalArgumentException {
    ClientAnchor anchor = null;
    ClientAnchorDetail rowClientAnchorDetail = null;
    ClientAnchorDetail colClientAnchorDetail = null;
    int imageType = 0;

    // Validate the resizeBehaviour parameter.
    if ((resizeBehaviour != AddDimensionedImage.EXPAND_COLUMN)
            && (resizeBehaviour != AddDimensionedImage.EXPAND_ROW)
            && (resizeBehaviour != AddDimensionedImage.EXPAND_ROW_AND_COLUMN)
            && (resizeBehaviour != AddDimensionedImage.OVERLAY_ROW_AND_COLUMN)) {
        throw new IllegalArgumentException("Invalid value passed to the "
                + "resizeBehaviour parameter of AddDimensionedImage.addImageToSheet()");
    }

    // Call methods to calculate how the image and sheet should be
    // manipulated to accomodate the image; columns and then rows.
    colClientAnchorDetail = this.fitImageToColumns(sheet, colNumber, reqImageWidthMM, resizeBehaviour);
    rowClientAnchorDetail = this.fitImageToRows(sheet, rowNumber, reqImageHeightMM, resizeBehaviour);

    // Having determined if and how to resize the rows, columns and/or the
    // image, create the ClientAnchor object to position the image on
    // the worksheet. Note how the two ClientAnchorDetail records are
    // interrogated to recover the row/column co-ordinates and any insets.
    // The first two parameters are not used currently but could be if the
    // need arose to extend the functionality of this code by adding the
    // ability to specify that a clear 'border' be placed around the image.
    anchor = sheet.getWorkbook().getCreationHelper().createClientAnchor();

    anchor.setDx1(0);
    anchor.setDy1(0);
    anchor.setDx2(colClientAnchorDetail.getInset());
    anchor.setDy2(rowClientAnchorDetail.getInset());
    anchor.setCol1(colClientAnchorDetail.getFromIndex());
    anchor.setRow1(rowClientAnchorDetail.getFromIndex());
    anchor.setCol2(colClientAnchorDetail.getToIndex());
    anchor.setRow2(rowClientAnchorDetail.getToIndex());

    // For now, set the anchor type to do not move or resize the
    // image as the size of the row/column is adjusted. This could easilly
    // become another parameter passed to the method. Please read the note
    // above regarding the behaviour of image resizing.
    anchor.setAnchorType(ClientAnchor.MOVE_AND_RESIZE);

    // Now, add the picture to the workbook. Note that unlike the similar
    // method in the HSSF Examples section, the image type is checked.
    // First,
    // the image files location is identified by interrogating the URL
    // passed
    // to the method, the images type is identified before it is added to
    // the
    // sheet.
    String sURL = imageFile.toString().toLowerCase();
    if (sURL.endsWith(".png")) {
        imageType = Workbook.PICTURE_TYPE_PNG;
    } else if (sURL.endsWith("jpg") || sURL.endsWith(".jpeg")) {
        imageType = Workbook.PICTURE_TYPE_JPEG;
    } else {
        throw new IllegalArgumentException("Invalid Image file : " + sURL);
    }
    int index = sheet.getWorkbook().addPicture(IOUtils.toByteArray(imageFile.openStream()), imageType);
    drawing.createPicture(anchor, index);
}

From source file:domain.Excel.java

private void cargarLogo1(Integer fila, Integer columna, Double resize) {
        InputStream inputStream;//  www .j  av  a  2  s.  c o  m
        try {

            inputStream = getClass().getResourceAsStream("/resources/logo tru-test.png");//Tru-Test.jpg");

            byte[] bytes = IOUtils.toByteArray(inputStream);
            int pictureIdx = wb.addPicture(bytes, wb.PICTURE_TYPE_PNG);
            inputStream.close();
            CreationHelper helper = wb.getCreationHelper();
            Drawing drawing = sheet.createDrawingPatriarch();
            ClientAnchor anchor = helper.createClientAnchor();
            //set top-left corner for the image
            anchor.setCol1(columna);
            anchor.setRow1(fila);
            Picture pict = drawing.createPicture(anchor, pictureIdx);
            //Reset the image to the original size
            pict.resize(resize);

        } catch (FileNotFoundException ex) {
            Logger.getLogger(Excel.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IOException ex) {
            Logger.getLogger(Excel.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

From source file:domain.Excel.java

private void cargarLogo2(Integer fila, Integer columna, Double resize) {
        InputStream inputStream;/*from   w ww  . jav  a 2  s  .  c o m*/
        try {

            inputStream = getClass().getResourceAsStream("/resources/LOGO.png");//Tru-Test.jpg");

            byte[] bytes = IOUtils.toByteArray(inputStream);
            int pictureIdx = wb.addPicture(bytes, wb.PICTURE_TYPE_PNG);
            inputStream.close();
            CreationHelper helper = wb.getCreationHelper();
            Drawing drawing = sheet.createDrawingPatriarch();
            ClientAnchor anchor = helper.createClientAnchor();
            //set top-left corner for the image
            anchor.setCol1(columna);
            anchor.setRow1(fila);
            Picture pict = drawing.createPicture(anchor, pictureIdx);
            //Reset the image to the original size
            pict.resize(resize);

        } catch (FileNotFoundException ex) {
            Logger.getLogger(Excel.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IOException ex) {
            Logger.getLogger(Excel.class.getName()).log(Level.SEVERE, null, ex);
        }
    }