Example usage for org.apache.poi.ss.util CellRangeAddress CellRangeAddress

Introduction

In this page you can find the example usage for org.apache.poi.ss.util CellRangeAddress CellRangeAddress.

Prototype

public CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol) 

Source Link

Document

Creates new cell range.

Usage

From source file:org.corpus_tools.peppermodules.spreadsheet.tests.Spreadsheet2SaltMapperTest.java

License:Apache License

private FileOutputStream createFourthXlsxSample() throws IOException {

    xlsxWb = new XSSFWorkbook();
    Sheet xlsxSheet = xlsxWb.createSheet("fourthXlsxSample");
    Row xlsxRow1 = xlsxSheet.createRow(0);
    Cell xlsxCell1 = xlsxRow1.createCell(0);
    xlsxCell1.setCellValue("tok");
    Cell xlsxCell2 = xlsxRow1.createCell(1);
    xlsxCell2.setCellValue("anno1");
    Cell xlsxCell3 = xlsxRow1.createCell(2);
    xlsxCell3.setCellValue("tok2");
    Cell xlsxCell4 = xlsxRow1.createCell(3);
    xlsxCell4.setCellValue("anno2");
    Cell xlsxCell5 = xlsxRow1.createCell(4);
    xlsxCell5.setCellValue("lb");

    Row xlsxRow2 = xlsxSheet.createRow(1);
    Cell xlsxCell21 = xlsxRow2.createCell(0);
    xlsxCell21.setCellValue("This");
    Cell xlsxCell22 = xlsxRow2.createCell(1);
    xlsxCell22.setCellValue("pron1");
    Cell xlsxCell23 = xlsxRow2.createCell(2);
    xlsxCell23.setCellValue("This");
    Cell xlsxCell24 = xlsxRow2.createCell(3);
    xlsxCell24.setCellValue("pron2");
    Cell xlsxCell25 = xlsxRow2.createCell(4);
    xlsxCell25.setCellValue("lb");

    Row xlsxRow3 = xlsxSheet.createRow(2);
    Cell xlsxCell31 = xlsxRow3.createCell(0);
    xlsxCell31.setCellValue("is");
    Cell xlsxCell32 = xlsxRow3.createCell(1);
    xlsxCell32.setCellValue("verb1");
    Cell xlsxCell33 = xlsxRow3.createCell(2);
    xlsxCell33.setCellValue("is");
    Cell xlsxCell34 = xlsxRow3.createCell(3);
    xlsxCell34.setCellValue("verb2");
    xlsxRow3.createCell(4);/*from w w w  . j  a  v a 2  s .c  o m*/

    Row xlsxRow4 = xlsxSheet.createRow(3);
    Cell xlsxCell41 = xlsxRow4.createCell(0);
    xlsxCell41.setCellValue("an");
    Cell xlsxCell42 = xlsxRow4.createCell(1);
    xlsxCell42.setCellValue("art1");
    Cell xlsxCell43 = xlsxRow4.createCell(2);
    xlsxCell43.setCellValue("an");
    Cell xlsxCell44 = xlsxRow4.createCell(3);
    xlsxCell44.setCellValue("art2");
    xlsxRow4.createCell(4);

    Row xlsRow5 = xlsxSheet.createRow(4);
    Cell xlsCell51 = xlsRow5.createCell(0);
    xlsCell51.setCellValue("example");
    Cell xlsCell52 = xlsRow5.createCell(1);
    xlsCell52.setCellValue("noun1");
    Cell xlsCell53 = xlsRow5.createCell(2);
    xlsCell53.setCellValue("ex-");
    Cell xlsCell54 = xlsRow5.createCell(3);
    xlsCell54.setCellValue("noun2");
    xlsRow5.createCell(4);

    Row xlsxRow6 = xlsxSheet.createRow(5);
    xlsxRow6.createCell(0);
    xlsxRow6.createCell(1);
    Cell xlsxCell63 = xlsxRow6.createCell(2);
    xlsxCell63.setCellValue("ample");
    xlsxRow6.createCell(3);
    CellRangeAddress mergedPrim = new CellRangeAddress(4, 5, 0, 0);
    xlsxSheet.addMergedRegion(mergedPrim);
    CellRangeAddress mergedAnno1 = new CellRangeAddress(4, 5, 1, 1);
    xlsxSheet.addMergedRegion(mergedAnno1);
    CellRangeAddress mergedAnno2 = new CellRangeAddress(4, 5, 3, 3);
    xlsxSheet.addMergedRegion(mergedAnno2);
    xlsxRow6.createCell(4);

    Row xlsxRow7 = xlsxSheet.createRow(6);
    Cell xlsxCell71 = xlsxRow7.createCell(0);
    xlsxCell71.setCellValue(".");
    Cell xlsxCell72 = xlsxRow7.createCell(1);
    xlsxCell72.setCellValue("punct1");
    Cell xlsxCell73 = xlsxRow7.createCell(2);
    xlsxCell73.setCellValue(".");
    Cell xlsxCell74 = xlsxRow7.createCell(3);
    xlsxCell74.setCellValue("punct2");
    xlsxRow7.createCell(4);
    CellRangeAddress mergedAnnoLb = new CellRangeAddress(1, 6, 4, 4);
    xlsxSheet.addMergedRegion(mergedAnnoLb);

    xlsxWb.write(outStream);

    return outStream;
}

---

From source file:org.displaytag.render.HssfTableWriter.java

License:Open Source License

/**
 * Obtain the region over which to merge a cell.
 * @param first Column number of first cell from which to merge.
 * @param last Column number of last cell over which to merge.
 * @return The region over which to merge a cell.
 *//* ww  w .  j  a  v  a 2  s.  c  om*/
private CellRangeAddress getMergeCellsRegion(int first, int last) {
    return new CellRangeAddress(this.currentRow.getRowNum(), this.currentRow.getRowNum(), first, last);
}

---

From source file:org.displaytag.render.XssfTableWriter.java

License:Artistic License

/**
 * Obtain the region over which to merge a cell.
 * @param first Column number of first cell from which to merge.
 * @param last Column number of last cell over which to merge.
 * @return The region over which to merge a cell.
 *//*  ww  w  .jav  a  2  s .  c om*/
private CellRangeAddress getMergeCellsRegion(int first, int last) {
    return new CellRangeAddress(this.currentRow.getRowNum(), first, this.currentRow.getRowNum(), last);
}

---

From source file:org.drools.decisiontable.parser.xls.ExcelParserTest.java

License:Apache License

/**
 * This should test to see if a cell is in a certain range or not. 
 * If it is in a merged range, then it should return the top left cell.
 * @throws Exception/*w  ww  .  j  a  va2s .c om*/
 */
@Test
public void testCellMerge() throws Exception {
    ExcelParser parser = new ExcelParser((Map<String, List<DataListener>>) null);

    CellRangeAddress[] ranges = new CellRangeAddress[1];

    Workbook workbook = new XSSFWorkbook();
    Sheet sheet = workbook.createSheet();
    Cell cell = sheet.createRow(2).createCell(2);
    ranges[0] = new CellRangeAddress(2, 7, 2, 5);
    cell.setCellValue(FIRST_CELL_CONTENT);

    cell = sheet.createRow(7).createCell(5);
    cell.setCellValue(LAST_CELL_VALUE);

    cell = sheet.createRow(1).createCell(1);
    assertNull(parser.getRangeIfMerged(cell, ranges));

    cell = sheet.getRow(2).createCell(5);
    cell.setCellValue("wrong");

    CellRangeAddress rangeIfMerged = parser.getRangeIfMerged(cell, ranges);
    assertEquals(FIRST_CELL_CONTENT, sheet.getRow(rangeIfMerged.getFirstRow())
            .getCell(rangeIfMerged.getFirstColumn()).getStringCellValue());
}

---

From source file:org.drools.workbench.screens.guided.dtable.backend.server.conversion.PatternRowBuilder.java

License:Apache License

public void build() {

    final List<BaseColumn> expandedColumns = dtable.getExpandedColumns();

    for (int sourceIndex = 0; sourceIndex < expandedColumns.size(); sourceIndex++) {

        BaseColumn baseColumn = expandedColumns.get(sourceIndex);

        if (baseColumn instanceof BRLConditionVariableColumn || baseColumn instanceof BRLConditionColumn
                || baseColumn instanceof BRLActionVariableColumn || baseColumn instanceof BRLActionColumn) {
            throw new UnsupportedOperationException("Conversion of the BRL column is not supported.");
        } else if (baseColumn instanceof ConditionCol52) {

            final ConditionCol52 col = (ConditionCol52) baseColumn;
            final Pattern52 pattern = dtable.getPattern(col);
            int columnWidth = dtable.getConditionPattern(pattern.getBoundName()).getChildColumns().size();

            final int endIndex = columnIndex + columnWidth - 1;

            for (int i = columnIndex; i <= endIndex; i++) {
                patternRow.createCell(i)
                        .setCellValue(String.format("%s : %s", pattern.getBoundName(), pattern.getFactType()));
            }//from  ww w. j ava  2 s. co m

            if (columnWidth > 1) {
                sheet.addMergedRegion(new CellRangeAddress(PATTERN_ROW, PATTERN_ROW, columnIndex, endIndex));
                sheet.validateMergedRegions();
                columnIndex = endIndex;
                sourceIndex = sourceIndex + columnWidth - 1;
            }
        } else if (baseColumn instanceof RowNumberCol52) {
            // Ignore row column and do not up the columnIndex
            continue;
        }
        columnIndex++;
    }
}

---

From source file:org.eclipse.nebula.widgets.nattable.extension.poi.PoiExcelExporter.java

License:Open Source License

@Override
public void exportCell(OutputStream outputStream, Object exportDisplayValue, ILayerCell cell,
        IConfigRegistry configRegistry) throws IOException {
    int columnPosition = cell.getColumnPosition();
    int rowPosition = cell.getRowPosition();

    if (columnPosition != cell.getOriginColumnPosition() || rowPosition != cell.getOriginRowPosition()) {
        return;// w w w  .  j a va2 s .  c om
    }

    Cell xlCell = xlRow.createCell(columnPosition);

    int columnSpan = cell.getColumnSpan();
    int rowSpan = cell.getRowSpan();
    if (columnSpan > 1 || rowSpan > 1) {
        int lastRow = rowPosition + rowSpan - 1;
        int lastColumn = columnPosition + columnSpan - 1;
        xlSheet.addMergedRegion(new CellRangeAddress(rowPosition, lastRow, columnPosition, lastColumn));
    }

    CellStyleProxy cellStyle = new CellStyleProxy(configRegistry, DisplayMode.NORMAL,
            cell.getConfigLabels().getLabels());
    Color fg = cellStyle.getAttributeValue(CellStyleAttributes.FOREGROUND_COLOR);
    Color bg = cellStyle.getAttributeValue(CellStyleAttributes.BACKGROUND_COLOR);
    org.eclipse.swt.graphics.Font font = cellStyle.getAttributeValue(CellStyleAttributes.FONT);
    FontData fontData = font.getFontData()[0];
    String dataFormat = null;

    int hAlign = HorizontalAlignmentEnum.getSWTStyle(cellStyle);
    int vAlign = VerticalAlignmentEnum.getSWTStyle(cellStyle);

    boolean vertical = this.applyVerticalTextConfiguration
            ? isVertical(configRegistry.getConfigAttribute(CellConfigAttributes.CELL_PAINTER,
                    DisplayMode.NORMAL, cell.getConfigLabels().getLabels()))
            : false;

    if (exportDisplayValue == null)
        exportDisplayValue = ""; //$NON-NLS-1$

    if (exportDisplayValue instanceof Boolean) {
        xlCell.setCellValue((Boolean) exportDisplayValue);
    } else if (exportDisplayValue instanceof Calendar) {
        dataFormat = getDataFormatString(cell, configRegistry);
        xlCell.setCellValue((Calendar) exportDisplayValue);
    } else if (exportDisplayValue instanceof Date) {
        dataFormat = getDataFormatString(cell, configRegistry);
        xlCell.setCellValue((Date) exportDisplayValue);
    } else if (exportDisplayValue instanceof Number) {
        xlCell.setCellValue(((Number) exportDisplayValue).doubleValue());
    } else {
        xlCell.setCellValue(exportDisplayValue.toString());
    }

    CellStyle xlCellStyle = getExcelCellStyle(fg, bg, fontData, dataFormat, hAlign, vAlign, vertical);
    xlCell.setCellStyle(xlCellStyle);
}

---

From source file:org.eclipse.scada.ae.ui.views.export.excel.impl.ExportEventsImpl.java

License:Open Source License

private IStatus storeExcel(final File file, final List<Event> events, final List<Field> columns,
        final IProgressMonitor monitor) throws IOException {
    final HSSFWorkbook workbook = new HSSFWorkbook();

    final HSSFDataFormat dateFormat = workbook.createDataFormat();
    final HSSFCellStyle dateCellStyle = workbook.createCellStyle();
    dateCellStyle.setDataFormat(dateFormat.getFormat("YYYY-MM-DD hh:mm:ss.000"));

    try {// w w  w.  j  a va 2 s .  c o  m
        monitor.beginTask(Messages.ExportImpl_Progress_ExportingEvents, events.size() + 3 + columns.size());

        try {
            monitor.subTask(Messages.ExportImpl_Progress_CreateWorkbook);
            monitor.worked(1);

            final HSSFSheet sheet = createSheet(events, workbook, columns);
            monitor.worked(1);

            monitor.setTaskName(Messages.ExportImpl_Progress_ExportEvents);

            for (int i = 0; i < events.size(); i++) {
                final HSSFRow row = sheet.createRow(i + 1);

                final Event e = events.get(i);
                for (int j = 0; j < columns.size(); j++) {
                    final Field field = columns.get(j);
                    final ExcelCell cell = new ExcelCell(row, j, dateCellStyle);
                    field.render(e, cell);
                }
                monitor.worked(1);
                if (monitor.isCanceled()) {
                    return Status.CANCEL_STATUS;
                }
            }

            sheet.setRepeatingRows(new CellRangeAddress(0, 1, -1, -1));

            monitor.setTaskName("Auto sizing");
            for (int i = 0; i < columns.size(); i++) {
                monitor.subTask(String.format("Auto sizing column: %s", columns.get(i).getHeader()));
                sheet.autoSizeColumn(i);
                monitor.worked(1);

                if (monitor.isCanceled()) {
                    return Status.CANCEL_STATUS;
                }
            }

        } finally {
            monitor.subTask(Messages.ExportImpl_Progress_CloseFile);
            if (workbook != null) {
                makeDocInfo(workbook);

                final FileOutputStream stream = new FileOutputStream(file);
                workbook.write(stream);
                stream.close();
            }
            monitor.worked(1);
        }
    } finally {
        monitor.done();
    }

    return Status.OK_STATUS;
}

---

From source file:org.eclipse.scada.ae.ui.views.export.excel.impl.ExportEventsImpl.java

License:Open Source License

private HSSFSheet createSheet(final List<Event> events, final HSSFWorkbook workbook,
        final List<Field> columns) {
    final HSSFSheet sheet = workbook.createSheet(Messages.ExportImpl_ExcelSheet_Name);

    final HSSFHeader header = sheet.getHeader();
    header.setLeft(Messages.ExportImpl_ExcelSheet_Header);
    header.setRight(HeaderFooter.date() + " " + HeaderFooter.time());//$NON-NLS-1$

    final HSSFFooter footer = sheet.getFooter();
    footer.setLeft(String.format(Messages.ExportImpl_ExcelSheet_Footer_1, events.size()));

    footer.setRight(Messages.ExportImpl_ExcelSheet_Footer_2 + HeaderFooter.page()
            + Messages.ExportImpl_ExcelSheet_Footer_3 + HeaderFooter.numPages());

    makeHeader(columns, sheet);/*  w w w.  j ava  2 s .  c  om*/

    final HSSFPrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);
    printSetup.setFitWidth((short) 1);
    printSetup.setFitHeight((short) 0);
    printSetup.setPaperSize(PrintSetup.A4_PAPERSIZE);

    sheet.setAutoFilter(new CellRangeAddress(0, 0, 0, columns.size() - 1));
    sheet.createFreezePane(0, 1);
    sheet.setFitToPage(true);
    sheet.setAutobreaks(true);

    printSetup.setFooterMargin(0.25);

    sheet.setMargin(Sheet.LeftMargin, 0.25);
    sheet.setMargin(Sheet.RightMargin, 0.25);
    sheet.setMargin(Sheet.TopMargin, 0.25);
    sheet.setMargin(Sheet.BottomMargin, 0.5);

    return sheet;
}

---

From source file:org.efaps.esjp.common.file.FileUtil_Base.java

License:Apache License

/**
 * Copy row./*from w  ww.  j av a2s . c  o  m*/
 *
 * @param _srcSheet the src sheet
 * @param _destSheet the dest sheet
 * @param _srcRow the src row
 * @param _destRow the dest row
 * @param _styleMap the style map
 */
protected void copyRow(final Sheet _srcSheet, final Sheet _destSheet, final Row _srcRow, final Row _destRow,
        final Map<Integer, CellStyle> _styleMap) {
    final Set<CellRangeAddressWrapper> mergedRegions = new TreeSet<>();
    _destRow.setHeight(_srcRow.getHeight());
    final int deltaRows = _destRow.getRowNum() - _srcRow.getRowNum();
    for (int j = _srcRow.getFirstCellNum(); j <= _srcRow.getLastCellNum(); j++) {
        final Cell oldCell = _srcRow.getCell(j); // ancienne cell
        Cell newCell = _destRow.getCell(j); // new cell
        if (oldCell != null) {
            if (newCell == null) {
                newCell = _destRow.createCell(j);
            }
            copyCell(oldCell, newCell, _styleMap);
            final CellRangeAddress mergedRegion = getMergedRegion(_srcSheet, _srcRow.getRowNum(),
                    (short) oldCell.getColumnIndex());

            if (mergedRegion != null) {
                final CellRangeAddress newMergedRegion = new CellRangeAddress(
                        mergedRegion.getFirstRow() + deltaRows, mergedRegion.getLastRow() + deltaRows,
                        mergedRegion.getFirstColumn(), mergedRegion.getLastColumn());
                final CellRangeAddressWrapper wrapper = new CellRangeAddressWrapper(newMergedRegion);
                if (isNewMergedRegion(wrapper, mergedRegions)) {
                    mergedRegions.add(wrapper);
                    _destSheet.addMergedRegion(wrapper.range);
                }
            }
        }
    }
}

---

From source file:org.fenixedu.academic.ui.struts.action.candidacy.degreeChange.DegreeChangeCandidacyProcessDA.java

License:Open Source License

private void createHeader(final StyledExcelSpreadsheet spreadsheet, final Degree degree) {
    // title/*from   w  ww.  j  a va2 s. c  om*/
    spreadsheet.newHeaderRow();
    spreadsheet.addCell(degree.getName(), spreadsheet.getExcelStyle().getTitleStyle());

    // empty row
    spreadsheet.newHeaderRow();

    // table header
    spreadsheet.newHeaderRow();
    spreadsheet.addHeader(getString("label.candidacy.identification"));
    spreadsheet.addHeader(2, getString("label.candidacy.degree.and.school"));
    spreadsheet.addHeader(getString("label.candidacy.affinity"));
    spreadsheet.addHeader(getString("label.candidacy.degreeNature"));
    spreadsheet.addHeader(getString("label.candidacy.concludedUCs"));
    spreadsheet.addHeader(8, "");
    spreadsheet.addHeader(getString("label.candidacy.approvedEctsRate"));
    spreadsheet.addHeader(getString("label.candidacy.gradeRate"));
    spreadsheet.addHeader(getString("label.candidacy.degreeChange.seriesCandidacyGrade"));
    spreadsheet.addHeader(getString("label.candidacy.result"));

    spreadsheet.newHeaderRow();
    spreadsheet.addHeader(getString("label.number"));
    spreadsheet.addHeader(getString("label.name"));
    spreadsheet.addHeader(5, getString("label.number"));
    spreadsheet.addHeader(getString("label.candidacy.gradeSum.abbr"));
    spreadsheet.addHeader(getString("label.candidacy.approvedEcts"));
    spreadsheet.addHeader(getString("label.candidacy.enroledEcts"));

    // Id + N + Nome merge
    spreadsheet.getSheet().addMergedRegion(new CellRangeAddress(2, (short) 0, 2, (short) 1));
    // Degree name merge
    spreadsheet.getSheet().addMergedRegion(new CellRangeAddress(2, (short) 2, 3, (short) 2));
    // affinity merge
    spreadsheet.getSheet().addMergedRegion(new CellRangeAddress(2, (short) 3, 3, (short) 3));
    // degreeNature merge
    spreadsheet.getSheet().addMergedRegion(new CellRangeAddress(2, (short) 4, 3, (short) 4));
    // UCs merge
    spreadsheet.getSheet().addMergedRegion(new CellRangeAddress(2, (short) 5, 2, (short) 7));
    // A merge
    spreadsheet.getSheet().addMergedRegion(new CellRangeAddress(2, (short) 9, 3, (short) 9));
    // B merge
    spreadsheet.getSheet().addMergedRegion(new CellRangeAddress(2, (short) 10, 3, (short) 10));
    // C merge
    spreadsheet.getSheet().addMergedRegion(new CellRangeAddress(2, (short) 11, 3, (short) 11));
    // result merge
    spreadsheet.getSheet().addMergedRegion(new CellRangeAddress(2, (short) 12, 3, (short) 12));
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »