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

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

Introduction

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

Prototype

public void setRow2(int row2);

Source Link

Document

Returns the row (0 based) of the first cell.

Usage

From source file:org.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. j a  v  a  2 s . c om
 */
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].*)");
    String delimiterParam = req.getParameter(PARAM_REQ_DELIMITER);
    CSVStrategy reqCSVstrategy = null;
    if (delimiterParam != null && !delimiterParam.isEmpty()) {
        reqCSVstrategy = new CSVStrategy(delimiterParam.charAt(0), '"', CSVStrategy.COMMENTS_DISABLED);
    }
    // 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(dictionaryService) != null) {
                // Use the friendly titles, which may even be localised!
                headings[i] = pd.getTitle(dictionaryService);
                descriptions[i] = pd.getDescription(dictionaryService);
            } 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, reqCSVstrategy != null ? reqCSVstrategy : getCsvStrategy());
        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);
        sheet.createFreezePane(0, 1);

        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:org.bbreak.excella.reports.tag.ImageParamParser.java

License:Open Source License

/**
 * ??/* w  w  w . java  2s  .  c  o m*/
 * 
 * @param sheet ?
 * @param cell 
 * @param filePath ?
 * @param dx1 ??
 * @param dy1 ???
 * @param scale ???
 * @throws ParseException
 */
public void replaceImageValue(Sheet sheet, Cell cell, String filePath, Integer dx1, Integer dy1, Double scale)
        throws ParseException {

    Workbook workbook = sheet.getWorkbook();

    int format = -1;
    if (filePath.toLowerCase().endsWith(JPEG_SUFFIX) || filePath.toLowerCase().endsWith(JPG_SUFFIX)) {
        format = Workbook.PICTURE_TYPE_JPEG;
    } else if (filePath.toLowerCase().endsWith(PNG_SUFFIX)) {
        format = Workbook.PICTURE_TYPE_PNG;
    }
    if (format == -1) {
        throw new ParseException(cell,
                "????????" + filePath);
    }

    byte[] bytes = null;
    InputStream is = null;
    try {
        is = new FileInputStream(filePath);
        bytes = IOUtils.toByteArray(is);
    } catch (Exception e) {
        throw new ParseException(cell, e);
    } finally {
        try {
            is.close();
        } catch (IOException e) {
            throw new ParseException(cell, e);
        }
    }

    int pictureIdx = workbook.addPicture(bytes, format);

    CreationHelper helper = workbook.getCreationHelper();

    @SuppressWarnings("rawtypes")
    Drawing drawing = drawingCash.get(sheet);
    if (drawing == null) {
        drawing = sheet.createDrawingPatriarch();
        drawingCash.put(sheet, drawing);
    }

    ClientAnchor anchor = helper.createClientAnchor();

    anchor.setRow1(cell.getRowIndex());
    anchor.setCol1(cell.getColumnIndex());
    anchor.setRow2(cell.getRowIndex() + 1);
    anchor.setCol2(cell.getColumnIndex() + 1);
    if (dx1 != null) {
        anchor.setDx1(dx1);
    }
    if (dy1 != null) {
        anchor.setDy1(dy1);
    }

    Picture picture = drawing.createPicture(anchor, pictureIdx);
    picture.resize(scale);

}

From source file:org.eclipse.emfforms.internal.spreadsheet.core.renderer.EMFFormsSpreadsheetControlRenderer.java

License:Open Source License

private Comment createComment(Workbook workbook, Sheet sheet, VDomainModelReference domainModelReference,
        int row, int column) throws IOException {
    final CreationHelper factory = workbook.getCreationHelper();

    // When the comment box is visible, have it show in a 1x3 space
    final ClientAnchor anchor = factory.createClientAnchor();
    anchor.setCol1(column);//from  www. ja v  a2 s . c o m
    anchor.setCol2(column + 1);
    anchor.setRow1(row);
    anchor.setRow2(row + 1);

    final Drawing drawing = sheet.createDrawingPatriarch();
    final Comment comment = drawing.createCellComment(anchor);

    comment.setAuthor("EMFForms Spreadsheet Renderer"); //$NON-NLS-1$
    comment.setVisible(false);
    comment.setString(factory.createRichTextString(getSerializedDMR(domainModelReference)));
    return comment;
}

From source file:org.eclipse.emfforms.spreadsheet.integrationtest.ImportErrors_ITest.java

License:Open Source License

private Comment createComment(Workbook workbook, Sheet sheet, int row, int column) throws IOException {
    final CreationHelper factory = workbook.getCreationHelper();

    // When the comment box is visible, have it show in a 1x3 space
    final ClientAnchor anchor = factory.createClientAnchor();
    anchor.setCol1(column);//www  . j a v  a 2 s  .  c  om
    anchor.setCol2(column + 1);
    anchor.setRow1(row);
    anchor.setRow2(row + 1);

    final Drawing drawing = sheet.createDrawingPatriarch();
    final Comment comment = drawing.createCellComment(anchor);

    comment.setAuthor("EMFForms Spreadsheet Renderer"); //$NON-NLS-1$
    comment.setVisible(false);
    comment.setString(factory.createRichTextString("Ignore Sheet")); //$NON-NLS-1$
    return comment;
}

From source file:org.eclipse.emfforms.spreadsheet.integrationtest.ImportErrors_ITest.java

License:Open Source License

@Test
public void testNoObjectIdColumn() throws IOException {
    /* setup *//*from www. j a  v  a 2s.  c o  m*/
    final Workbook workbook = new HSSFWorkbook();
    final Sheet sheet = workbook.createSheet("root"); //$NON-NLS-1$
    final Row rowLabel = sheet.createRow(0);
    rowLabel.createCell(0).setCellValue("My feature"); //$NON-NLS-1$

    final CreationHelper factory = workbook.getCreationHelper();

    // When the comment box is visible, have it show in a 1x3 space
    final ClientAnchor anchor = factory.createClientAnchor();
    anchor.setCol1(0);
    anchor.setCol2(1);
    anchor.setRow1(0);
    anchor.setRow2(1);

    final Drawing drawing = sheet.createDrawingPatriarch();
    final Comment comment = drawing.createCellComment(anchor);
    comment.setString(factory.createRichTextString(
            "<?xml version=\"1.0\" encoding=\"UTF-8\"?><org.eclipse.emf.ecp.view.model:FeaturePathDomainModelReference xmi:version=\"2.0\" xmlns:xmi=\"http://www.omg.org/XMI\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:ecore=\"http://www.eclipse.org/emf/2002/Ecore\" xmlns:org.eclipse.emf.ecp.view.model=\"http://org/eclipse/emf/ecp/view/model/170\"><domainModelEFeature xsi:type=\"ecore:EAttribute\" href=\"http://eclipse/org/emf/ecp/makeithappen/model/task#//User/lastName\"/></org.eclipse.emf.ecp.view.model:FeaturePathDomainModelReference>")); //$NON-NLS-1$

    final Row rowDescription = sheet.createRow(1);
    rowDescription.createCell(0).setCellValue("My feature description"); //$NON-NLS-1$

    final Row rowMeta = sheet.createRow(2);
    rowMeta.createCell(0).setCellValue("Enter Numbers"); //$NON-NLS-1$

    final Row rowData = sheet.createRow(3);
    rowData.createCell(0).setCellValue("My Feature Value"); //$NON-NLS-1$
    /* act */
    final SpreadsheetImportResult result = EMFFormsSpreadsheetImporter.INSTANCE.importSpreadsheet(workbook,
            eClass);
    assertEquals(1, result.getErrorReports().size());
}

From source file:org.isisaddons.module.excel.dom.CellMarshaller.java

License:Apache License

private static void setCellComment(final Cell cell, final String commentText) {
    Sheet sheet = cell.getSheet();/*  w ww  .j a  va 2 s  . co  m*/
    Row row = cell.getRow();
    Workbook workbook = sheet.getWorkbook();
    CreationHelper creationHelper = workbook.getCreationHelper();
    ClientAnchor anchor = creationHelper.createClientAnchor();
    anchor.setCol1(cell.getColumnIndex());
    anchor.setCol2(cell.getColumnIndex() + 1);
    anchor.setRow1(row.getRowNum());
    anchor.setRow2(row.getRowNum() + 3);

    Drawing drawing = sheet.createDrawingPatriarch();
    Comment comment1 = drawing.createCellComment(anchor);

    RichTextString commentRtf = creationHelper.createRichTextString(commentText);

    comment1.setString(commentRtf);
    Comment comment = comment1;
    cell.setCellComment(comment);
}

From source file:org.nuxeo.ecm.platform.groups.audit.service.acl.excel.ExcelBuilder.java

License:Open Source License

/**
 * Return a Comment. Comments are supported only on XLS file (HSSF framework).
 *
 * @param row//from w  ww  .j  a  va2  s . c  om
 * @param col
 * @param colWidth
 * @param rowHeight
 * @return
 */
public Comment buildComment(String text, int row, int col, int colWidth, int rowHeight) {
    ClientAnchor anchor = create.createClientAnchor();
    anchor.setCol1(col);
    anchor.setCol2(col + colWidth);
    anchor.setRow1(row);
    anchor.setRow2(row + rowHeight);

    // Create the comment and set the text+author
    Comment comment = null;
    if (drawing instanceof HSSFPatriarch) {
        HSSFPatriarch p = (HSSFPatriarch) drawing;
        comment = p.createComment((HSSFAnchor) anchor);
    } else if (drawing instanceof XSSFDrawing) {
        log.error("comments not supported on XSSFDrawing, i.e. XLSX files");
        // XSSFDrawing p = (XSSFDrawing)drawing;
        // comment = p.createComment((XSSFAnchor)anchor);
    }
    if (comment != null) {
        RichTextString str = create.createRichTextString(text);
        comment.setString(str);
        comment.setAuthor("");
        // Assign the comment to the cell
        return comment;
    } else
        return null;
}

From source file:org.pentaho.reporting.engine.classic.core.modules.output.table.xls.helper.ExcelImageHandler.java

License:Open Source License

protected ClientAnchor computeExcel97ClientAnchor(final SlimSheetLayout currentLayout,
        final TableRectangle rectangle, final StrictBounds cb) {
    final int cell1x = rectangle.getX1();
    final int cell1y = rectangle.getY1();
    final int cell2x = Math.max(cell1x, rectangle.getX2() - 1);
    final int cell2y = Math.max(cell1y, rectangle.getY2() - 1);

    final long cell1width = currentLayout.getCellWidth(cell1x);
    final long cell1height = currentLayout.getRowHeight(cell1y);
    final long cell2width = currentLayout.getCellWidth(cell2x);
    final long cell2height = currentLayout.getRowHeight(cell2y);

    final long cell1xPos = currentLayout.getXPosition(cell1x);
    final long cell1yPos = currentLayout.getYPosition(cell1y);
    final long cell2xPos = currentLayout.getXPosition(cell2x);
    final long cell2yPos = currentLayout.getYPosition(cell2y);

    final int dx1 = (int) (1023 * ((cb.getX() - cell1xPos) / (double) cell1width));
    final int dy1 = (int) (255 * ((cb.getY() - cell1yPos) / (double) cell1height));
    final int dx2 = (int) (1023 * ((cb.getX() + cb.getWidth() - cell2xPos) / (double) cell2width));
    final int dy2 = (int) (255 * ((cb.getY() + cb.getHeight() - cell2yPos) / (double) cell2height));

    final ClientAnchor anchor = printerBase.getWorkbook().getCreationHelper().createClientAnchor();
    anchor.setDx1(dx1);/* w  ww  . jav a2  s .  c o m*/
    anchor.setDy1(dy1);
    anchor.setDx2(dx2);
    anchor.setDy2(dy2);
    anchor.setCol1(cell1x);
    anchor.setRow1(cell1y);
    anchor.setCol2(cell2x);
    anchor.setRow2(cell2y);
    anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_DONT_RESIZE);
    return anchor;
}

From source file:org.pentaho.reporting.engine.classic.core.modules.output.table.xls.helper.ExcelImageHandler.java

License:Open Source License

protected ClientAnchor computeExcel2003ClientAnchor(final SlimSheetLayout currentLayout,
        final TableRectangle rectangle, final StrictBounds cb) {
    final int cell1x = rectangle.getX1();
    final int cell1y = rectangle.getY1();
    final int cell2x = Math.max(cell1x, rectangle.getX2() - 1);
    final int cell2y = Math.max(cell1y, rectangle.getY2() - 1);

    final long cell1xPos = currentLayout.getXPosition(cell1x);
    final long cell1yPos = currentLayout.getYPosition(cell1y);
    final long cell2xPos = currentLayout.getXPosition(cell2x);
    final long cell2yPos = currentLayout.getYPosition(cell2y);

    final int dx1 = (int) StrictGeomUtility.toExternalValue((cb.getX() - cell1xPos) * XSSFShape.EMU_PER_POINT);
    final int dy1 = (int) StrictGeomUtility.toExternalValue((cb.getY() - cell1yPos) * XSSFShape.EMU_PER_POINT);
    final int dx2 = (int) Math.max(0, StrictGeomUtility
            .toExternalValue((cb.getX() + cb.getWidth() - cell2xPos) * XSSFShape.EMU_PER_POINT));
    final int dy2 = (int) Math.max(0, StrictGeomUtility
            .toExternalValue((cb.getY() + cb.getHeight() - cell2yPos) * XSSFShape.EMU_PER_POINT));

    final ClientAnchor anchor = printerBase.getWorkbook().getCreationHelper().createClientAnchor();
    anchor.setDx1(dx1);//from   w  w  w .  j a  va2 s .co  m
    anchor.setDy1(dy1);
    anchor.setDx2(dx2);
    anchor.setDy2(dy2);
    anchor.setCol1(cell1x);
    anchor.setRow1(cell1y);
    anchor.setCol2(cell2x);
    anchor.setRow2(cell2y);
    anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_DONT_RESIZE);
    return anchor;
}

From source file:org.tiefaces.components.websheet.utility.CellUtility.java

License:MIT License

/**
 * Creates the comment anchor./* w  w  w  .  j  a va 2 s. c  om*/
 *
 * @param newCell the new cell
 * @param factory the factory
 * @return the client anchor
 */
private static ClientAnchor createCommentAnchor(final Cell newCell, CreationHelper factory) {
    ClientAnchor anchor = factory.createClientAnchor();
    anchor.setCol1(newCell.getColumnIndex());
    anchor.setCol2(newCell.getColumnIndex() + 1);
    anchor.setRow1(newCell.getRowIndex());
    anchor.setRow2(newCell.getRowIndex() + 3);
    return anchor;
}