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

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

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. ClientAnchor
  6. setRow1

Introduction

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

Prototype

public void setRow1(int row1);

Source Link

Document

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

Usage

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   w ww .j  a  va  2s. 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);/*w w w  .  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 ww  w.  jav  a 2s  .com
    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();// ww  w.  j  a  v a  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 w  w  .  j a  v  a 2 s.com
 * @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.nuxeo.ecm.platform.groups.audit.service.acl.excel.ExcelBuilder.java

License:Open Source License

@Override
public void setPicture(int pictureIdx, int col1, int row1, boolean resize) {
    ClientAnchor anchor = create.createClientAnchor();
    // set top-left corner of the picture,
    // subsequent call of Picture#resize() will operate relative to it
    anchor.setCol1(col1);/*from w w w  .  jav a  2 s.c  om*/
    anchor.setRow1(row1);
    Picture pict = drawing.createPicture(anchor, pictureIdx);

    // auto-size picture relative to its top-left corner
    if (resize)
        pict.resize();
}

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);//from w ww  .j  a va  2  s .  com
    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 ww  w .  j  a v  a 2  s . c om
    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.//from  w ww.  ja v  a  2s .co  m
 *
 * @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;
}

From source file:org.waterforpeople.mapping.dataexport.GraphicalSurveySummaryExporter.java

License:Open Source License

/**
 * Writes the report as an XLS document/*from w  ww. j  a  v  a2s .  c  o  m*/
 */
private void writeSummaryReport(Map<QuestionGroupDto, List<QuestionDto>> questionMap, SummaryModel summaryModel,
        String sector, Workbook wb) throws Exception {
    String title = sector == null ? SUMMARY_LABEL.get(locale) : sector;
    Sheet sheet = null;
    int sheetCount = 2;
    String curTitle = WorkbookUtil.createSafeSheetName(title);
    while (sheet == null) {
        sheet = wb.getSheet(curTitle);
        if (sheet == null) {
            sheet = wb.createSheet(WorkbookUtil.createSafeSheetName(curTitle));
        } else {
            sheet = null;
            curTitle = title + " " + sheetCount;
            sheetCount++;
        }
    }
    CreationHelper creationHelper = wb.getCreationHelper();
    Drawing patriarch = sheet.createDrawingPatriarch();
    int curRow = 0;
    Row row = getRow(curRow++, sheet);
    if (sector == null) {
        createCell(row, 0, REPORT_HEADER.get(locale), headerStyle);
    } else {
        createCell(row, 0, sector + " " + REPORT_HEADER.get(locale), headerStyle);
    }
    for (QuestionGroupDto group : orderedGroupList) {
        if (questionMap.get(group) != null) {
            for (QuestionDto question : questionMap.get(group)) {
                if (!(QuestionType.OPTION == question.getType() || QuestionType.NUMBER == question.getType())) {
                    continue;
                } else {
                    if (summaryModel.getResponseCountsForQuestion(question.getKeyId(), sector).size() == 0) {
                        // if there is no data, skip the question
                        continue;
                    }
                }
                // for both options and numeric, we want a pie chart and
                // data table for numeric, we also want descriptive
                // statistics
                int tableTopRow = curRow++;
                int tableBottomRow = curRow;
                row = getRow(tableTopRow, sheet);
                // span the question heading over the data table
                sheet.addMergedRegion(new CellRangeAddress(curRow - 1, curRow - 1, 0, 2));
                createCell(row, 0, getLocalizedText(question.getText(), question.getTranslationMap()),
                        headerStyle);
                DescriptiveStats stats = summaryModel.getDescriptiveStatsForQuestion(question.getKeyId(),
                        sector);
                if (stats != null && stats.getSampleCount() > 0) {
                    sheet.addMergedRegion(new CellRangeAddress(curRow - 1, curRow - 1, 4, 5));
                    createCell(row, 4, getLocalizedText(question.getText(), question.getTranslationMap()),
                            headerStyle);
                }
                row = getRow(curRow++, sheet);
                createCell(row, 1, FREQ_LABEL.get(locale), headerStyle);
                createCell(row, 2, PCT_LABEL.get(locale), headerStyle);

                // now create the data table for the option count
                Map<String, Long> counts = summaryModel.getResponseCountsForQuestion(question.getKeyId(),
                        sector);
                int sampleTotal = 0;
                List<String> labels = new ArrayList<String>();
                List<String> values = new ArrayList<String>();
                int firstOptRow = curRow;
                for (Entry<String, Long> count : counts.entrySet()) {
                    row = getRow(curRow++, sheet);
                    String labelText = count.getKey();
                    if (labelText == null) {
                        labelText = "";
                    }
                    StringBuilder builder = new StringBuilder();
                    if (QuestionType.OPTION == question.getType() && !DEFAULT_LOCALE.equals(locale)) {
                        String[] tokens = labelText.split("\\|");
                        // see if we have a translation for this option
                        for (int i = 0; i < tokens.length; i++) {
                            if (i > 0) {
                                builder.append("|");
                            }
                            if (question.getOptionContainerDto() != null
                                    && question.getOptionContainerDto().getOptionsList() != null) {
                                boolean found = false;
                                for (QuestionOptionDto opt : question.getOptionContainerDto()
                                        .getOptionsList()) {
                                    if (opt.getText() != null
                                            && opt.getText().trim().equalsIgnoreCase(tokens[i])) {
                                        builder.append(getLocalizedText(tokens[i], opt.getTranslationMap()));
                                        found = true;
                                        break;
                                    }
                                }
                                if (!found) {
                                    builder.append(tokens[i]);
                                }
                            }
                        }
                    } else {
                        builder.append(labelText);
                    }
                    createCell(row, 0, builder.toString(), null);
                    createCell(row, 1, count.getValue().toString(), null);

                    labels.add(builder.toString());
                    values.add(count.getValue().toString());
                    sampleTotal += count.getValue();
                }
                row = getRow(curRow++, sheet);
                createCell(row, 0, TOTAL_LABEL.get(locale), null);
                createCell(row, 1, sampleTotal + "", null);
                for (int i = 0; i < values.size(); i++) {
                    row = getRow(firstOptRow + i, sheet);
                    if (sampleTotal > 0) {
                        createCell(row, 2, PCT_FMT.format((Double.parseDouble(values.get(i)) / sampleTotal)),
                                null);
                    } else {
                        createCell(row, 2, PCT_FMT.format(0), null);
                    }
                }

                tableBottomRow = curRow;

                if (stats != null && stats.getSampleCount() > 0) {
                    int tempRow = tableTopRow + 1;
                    row = getRow(tempRow++, sheet);
                    createCell(row, 4, "N", null);
                    createCell(row, 5, sampleTotal + "", null);
                    row = getRow(tempRow++, sheet);
                    createCell(row, 4, MEAN_LABEL.get(locale), null);
                    createCell(row, 5, stats.getMean() + "", null);
                    row = getRow(tempRow++, sheet);
                    createCell(row, 4, STD_E_LABEL.get(locale), null);
                    createCell(row, 5, stats.getStandardError() + "", null);
                    row = getRow(tempRow++, sheet);
                    createCell(row, 4, MEDIAN_LABEL.get(locale), null);
                    createCell(row, 5, stats.getMedian() + "", null);
                    row = getRow(tempRow++, sheet);
                    createCell(row, 4, MODE_LABEL.get(locale), null);
                    createCell(row, 5, stats.getMode() + "", null);
                    row = getRow(tempRow++, sheet);
                    createCell(row, 4, STD_D_LABEL.get(locale), null);
                    createCell(row, 5, stats.getStandardDeviation() + "", null);
                    row = getRow(tempRow++, sheet);
                    createCell(row, 4, VAR_LABEL.get(locale), null);
                    createCell(row, 5, stats.getVariance() + "", null);
                    row = getRow(tempRow++, sheet);
                    createCell(row, 4, RANGE_LABEL.get(locale), null);
                    createCell(row, 5, stats.getRange() + "", null);
                    row = getRow(tempRow++, sheet);
                    createCell(row, 4, MIN_LABEL.get(locale), null);
                    createCell(row, 5, stats.getMin() + "", null);
                    row = getRow(tempRow++, sheet);
                    createCell(row, 4, MAX_LABEL.get(locale), null);
                    createCell(row, 5, stats.getMax() + "", null);
                    if (tableBottomRow < tempRow) {
                        tableBottomRow = tempRow;
                    }
                }
                curRow = tableBottomRow;
                if (labels.size() > 0) {
                    boolean hasVals = false;
                    if (values != null) {
                        for (String val : values) {
                            try {
                                if (val != null && new Double(val.trim()) > 0D) {
                                    hasVals = true;
                                    break;
                                }
                            } catch (Exception e) {
                                // no-op
                            }
                        }
                    }
                    // only insert the image if we have at least 1 non-zero
                    // value
                    if (hasVals && generateCharts) {
                        // now insert the graph
                        int indx = wb.addPicture(JFreechartChartUtil.getPieChart(labels, values,
                                getLocalizedText(question.getText(), question.getTranslationMap()), CHART_WIDTH,
                                CHART_HEIGHT), Workbook.PICTURE_TYPE_PNG);
                        ClientAnchor anchor = creationHelper.createClientAnchor();
                        anchor.setDx1(0);
                        anchor.setDy1(0);
                        anchor.setDx2(0);
                        anchor.setDy2(255);
                        anchor.setCol1(6);
                        anchor.setRow1(tableTopRow);
                        anchor.setCol2(6 + CHART_CELL_WIDTH);
                        anchor.setRow2(tableTopRow + CHART_CELL_HEIGHT);
                        anchor.setAnchorType(2);
                        patriarch.createPicture(anchor, indx);
                        if (tableTopRow + CHART_CELL_HEIGHT > tableBottomRow) {
                            curRow = tableTopRow + CHART_CELL_HEIGHT;
                        }
                    }
                }

                // add a blank row between questions
                getRow(curRow++, sheet);
                // flush the sheet so far to disk; we will not go back up
                ((SXSSFSheet) sheet).flushRows(0); // retain 0 last rows and
                // flush all others

            }
        }
    }
}