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

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

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. ClientAnchor
  6. 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;
}