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:com.vaadin.addon.spreadsheet.ContextMenuManager.java

License:Open Source License

/**
 * Gets a list of available actions for the row at the given index.
 * /*from  ww w  .ja  v  a 2 s  .  c  om*/
 * @param rowIndex
 *            Index of the target row, 1-based
 * @return List of actions
 */
protected ArrayList<SpreadsheetActionDetails> createActionsListForRow(int rowIndex) {
    ArrayList<SpreadsheetActionDetails> actions = new ArrayList<SpreadsheetActionDetails>();
    final CellRangeAddress row = new CellRangeAddress(rowIndex - 1, rowIndex - 1, -1, -1);
    for (Handler handler : actionHandlers) {
        for (Action action : handler.getActions(row, spreadsheet)) {
            String key = actionMapper.key(action);
            spreadsheet.setResource(key, action.getIcon());
            SpreadsheetActionDetails spreadsheetActionDetails = new SpreadsheetActionDetails();
            spreadsheetActionDetails.caption = action.getCaption();
            spreadsheetActionDetails.key = key;
            spreadsheetActionDetails.type = 1;
            actions.add(spreadsheetActionDetails);
        }
    }
    return actions;
}

From source file:com.vaadin.addon.spreadsheet.Spreadsheet.java

License:Open Source License

/**
 * Creates a CellRangeAddress from the given cell address string. Also
 * checks that the range is valid within the currently active sheet. If it
 * is not, the resulting range will be truncated to fit the active sheet.
 * /* www .jav  a2s .co  m*/
 * @param addressString
 *            Cell address string, e.g. "B3:C5"
 * @return A CellRangeAddress based on the given coordinates.
 */
protected CellRangeAddress createCorrectCellRangeAddress(String addressString) {
    final String[] split = addressString.split(":");
    final CellReference cr1 = new CellReference(split[0]);
    final CellReference cr2 = new CellReference(split[1]);
    int r1 = cr1.getRow() > cr2.getRow() ? cr2.getRow() : cr1.getRow();
    int r2 = cr1.getRow() > cr2.getRow() ? cr1.getRow() : cr2.getRow();
    int c1 = cr1.getCol() > cr2.getCol() ? cr2.getCol() : cr1.getCol();
    int c2 = cr1.getCol() > cr2.getCol() ? cr1.getCol() : cr2.getCol();
    if (r1 >= getState().rows) {
        r1 = getState().rows - 1;
    }
    if (r2 >= getState().rows) {
        r2 = getState().rows - 1;
    }
    if (c1 >= getState().cols) {
        c1 = getState().cols - 1;
    }
    if (c2 >= getState().cols) {
        c2 = getState().cols - 1;
    }
    return new CellRangeAddress(r1, r2, c1, c2);
}

From source file:com.vaadin.addon.spreadsheet.Spreadsheet.java

License:Open Source License

/**
 * Creates a CellRangeAddress from the given start and end coordinates. Also
 * checks that the range is valid within the currently active sheet. If it
 * is not, the resulting range will be truncated to fit the active sheet.
 * //from www . j  a va2 s. c o m
 * @param row1
 *            Index of the starting row, 1-based
 * @param col1
 *            Index of the starting column, 1-based
 * @param row2
 *            Index of the ending row, 1-based
 * @param col2
 *            Index of the ending column, 1-based
 * 
 * @return A CellRangeAddress based on the given coordinates.
 */
protected CellRangeAddress createCorrectCellRangeAddress(int row1, int col1, int row2, int col2) {
    int r1 = row1 > row2 ? row2 : row1;
    int r2 = row1 > row2 ? row1 : row2;
    int c1 = col1 > col2 ? col2 : col1;
    int c2 = col1 > col2 ? col1 : col2;
    if (r1 >= getState().rows) {
        r1 = getState().rows;
    }
    if (r2 >= getState().rows) {
        r2 = getState().rows;
    }
    if (c1 >= getState().cols) {
        c1 = getState().cols;
    }
    if (c2 >= getState().cols) {
        c2 = getState().cols;
    }
    return new CellRangeAddress(r1 - 1, r2 - 1, c1 - 1, c2 - 1);
}

From source file:com.vaadin.addon.spreadsheet.Spreadsheet.java

License:Open Source License

/**
 * Merge cells. See {@link Sheet#addMergedRegion(CellRangeAddress)}.
 * /*from  w  w  w. j a va  2 s  .c om*/
 * @param row1
 *            Index of the starting row of the merged region, 0-based
 * @param col1
 *            Index of the starting column of the merged region, 0-based
 * @param row2
 *            Index of the ending row of the merged region, 0-based
 * @param col2
 *            Index of the ending column of the merged region, 0-based
 */
public void addMergedRegion(int row1, int col1, int row2, int col2) {
    addMergedRegion(new CellRangeAddress(row1, row2, col1, col2));
}

From source file:com.vaadin.addon.spreadsheet.Spreadsheet.java

License:Open Source License

/**
 * Selects the given range, using the cell at row1 and col1 as an anchor.
 * /*from   ww  w .  ja va 2s.c o m*/
 * @param row1
 *            Index of the first row of the area, 0-based
 * @param col1
 *            Index of the first column of the area, 0-based
 * @param row2
 *            Index of the last row of the area, 0-based
 * @param col2
 *            Index of the last column of the area, 0-based
 */
public void setSelectionRange(int row1, int col1, int row2, int col2) {
    CellReference ref = new CellReference(row1, col1);
    CellRangeAddress cra = new CellRangeAddress(row1, row2, col1, col2);
    selectionManager.handleCellRangeSelection(ref, cra, true);
}

From source file:com.vaadin.addon.spreadsheet.SpreadsheetFilterTable.java

License:Open Source License

/**
 * Creates a new filter table for the given spreadsheet component, sheet and
 * region. If the component is currently displaying the sheet that the table
 * belongs to, pop-up buttons and content (filters) are created.
 * /*from w ww  .jav  a2s .  c  o  m*/
 * @param spreadsheet
 *            Target Spreadsheet
 * @param sheet
 *            Target sheet within the Spreadsheet
 * @param fullTableRegion
 *            Cell range to include in the table
 */
public SpreadsheetFilterTable(Spreadsheet spreadsheet, Sheet sheet, CellRangeAddress fullTableRegion) {
    super(spreadsheet, sheet, fullTableRegion);

    popupButtonToFiltersMap = new HashMap<PopupButton, HashSet<SpreadsheetFilter>>();
    popupButtonToClearButtonMap = new HashMap<PopupButton, Button>();
    filteringRegion = new CellRangeAddress(fullTableRegion.getFirstRow() + 1, fullTableRegion.getLastRow(),
            fullTableRegion.getFirstColumn(), fullTableRegion.getLastColumn());

    if (isTableSheetCurrentlyActive()) {
        initFilters();
        initClearAllButtons();
    }
}

From source file:com.vaadin.addon.spreadsheet.SpreadsheetFilterTable.java

License:Open Source License

/**
 * Creates item filters for this table.//from   ww w.  j av  a  2 s.c  o m
 */
protected void initItemFilters() {
    int firstRow = filteringRegion.getFirstRow();
    int lastRow = filteringRegion.getLastRow();
    for (PopupButton popupButton : getPopupButtons()) {
        int column = popupButton.getColumn();
        ItemFilter itemFilter = new ItemFilter(new CellRangeAddress(firstRow, lastRow, column, column),
                getSpreadsheet(), popupButton, this);
        addComponentToPopup(popupButton, itemFilter);
        registerFilter(popupButton, itemFilter);
    }
}

From source file:com.vaadin.addon.spreadsheet.SpreadsheetHandlerImpl.java

@Override
public void onPaste(String text) {
    Workbook workbook = spreadsheet.getWorkbook();
    Sheet activesheet = workbook.getSheetAt(workbook.getActiveSheetIndex());

    CellReference selectedCellReference = spreadsheet.getSelectedCellReference();

    String[] lines;//from  w  w w. j  a v a  2s .  c  o  m
    if (text.indexOf("\r\n") > -1) {
        lines = text.split("\r\n");
    } else if (text.indexOf("\n") > -1) {
        lines = text.split("\n");
    } else {
        lines = text.split("\r");
    }

    int pasteHeight = lines.length;
    int pasteWidth = 1;
    for (String line : lines) {
        String[] tokens = splitOnTab(line);
        pasteWidth = Math.max(pasteWidth, tokens.length);
    }

    int rowIndex = selectedCellReference.getRow();
    int colIndex = selectedCellReference.getCol();

    // Check for protected cells at target
    for (int i = 0; i < pasteHeight; i++) {
        Row row = activesheet.getRow(rowIndex + i);
        if (row != null) {
            for (int j = 0; j < pasteWidth; j++) {
                Cell cell = row.getCell(colIndex + j);
                if (spreadsheet.isCellLocked(cell)) {
                    protectedCellWriteAttempted();
                    return;
                }
            }
        }
    }

    CellValueCommand command = new CellValueCommand(spreadsheet);
    CellRangeAddress affectedRange = new CellRangeAddress(rowIndex, rowIndex + pasteHeight - 1, colIndex,
            colIndex + pasteWidth - 1);
    command.captureCellRangeValues(affectedRange);

    for (int i = 0; i < pasteHeight; i++) {
        String line = lines[i];
        Row row = activesheet.getRow(rowIndex + i);
        if (row == null) {
            row = activesheet.createRow(rowIndex + i);
        }
        String[] tokens = splitOnTab(line);
        for (int j = 0; j < pasteWidth; j++) {
            Cell cell = row.getCell(colIndex + j);
            if (cell == null) {
                cell = row.createCell(colIndex + j);
            }
            if (j < tokens.length) {
                String cellContent = tokens[j];
                Double numVal = SpreadsheetUtil.parseNumber(cell, cellContent, spreadsheet.getLocale());
                if (numVal != null) {
                    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                    cell.setCellValue(numVal);
                } else {
                    cell.setCellValue(cellContent);
                }
            } else {
                cell.setCellType(Cell.CELL_TYPE_BLANK);
                spreadsheet.markCellAsDeleted(cell, true);
            }

            spreadsheet.getCellValueManager().markCellForUpdate(cell);
            spreadsheet.getCellValueManager().getFormulaEvaluator().notifyUpdateCell(cell);
        }
    }

    spreadsheet.getSpreadsheetHistoryManager().addCommand(command);
    spreadsheet.updateMarkedCells();
    // re-set selection to copied area
    spreadsheet.setSelectionRange(rowIndex, colIndex, rowIndex + pasteHeight - 1, colIndex + pasteWidth - 1);

    fireCellValueChangeEvent(affectedRange);
}

From source file:com.wabacus.system.component.application.report.abstractreport.AbsListReportType.java

License:Open Source License

protected void showSubRowDataInPlainExcelForWholeReport(Workbook workbook, CellStyle dataCellStyle,
        int position) {
    AbsListReportSubDisplayBean subDisplayBean = this.alrbean.getSubdisplaybean();
    if (subDisplayBean == null)
        return;/*from   w  w  w.  j  ava  2 s.  c  o m*/
    List<AbsListReportSubDisplayRowBean> lstStatiDisplayRowBeans = subDisplayBean.getLstSubDisplayRowBeans();
    if (lstStatiDisplayRowBeans == null || lstStatiDisplayRowBeans.size() == 0)
        return;

    List<AbsListReportSubDisplayColBean> lstStatiColBeans = null;
    for (AbsListReportSubDisplayRowBean sRowBeanTmp : lstStatiDisplayRowBeans) {
        /****if(sRowBeanTmp.getDisplaytype()==AbsListReportStatiBean.STATIROW_DISPLAYTYPE_PAGE)
        {
        if(rrequest.getShowtype()!=Consts.DISPLAY_ON_PAGE&&!this.cacheDataBean.isExportPrintPartData()) continue;
        if(rrequest.getShowtype()==Consts.DISPLAY_ON_PAGE&&cacheDataBean.getPagesize()<0) continue;//???
        }else if(sRowBeanTmp.getDisplaytype()==AbsListReportStatiBean.STATIROW_DISPLAYTYPE_REPORT)
        {
        if(rrequest.getShowtype()!=Consts.DISPLAY_ON_PAGE&&this.cacheDataBean.isExportPrintPartData()
                ||rrequest.getShowtype()==Consts.DISPLAY_ON_PAGE&&cacheDataBean.getPagesize()>0)
        {
            if(cacheDataBean.getFinalPageno()!=cacheDataBean.getPagecount()) continue;
        }
        }*/
        if (sRowBeanTmp.getDisplayposition() != AbsListReportSubDisplayBean.SUBROW_POSITION_BOTH
                && sRowBeanTmp.getDisplayposition() != position)
            continue;
        lstStatiColBeans = sRowBeanTmp.getLstSubColBeans();
        if (lstStatiColBeans == null || lstStatiColBeans.size() == 0)
            continue;
        String stativalue;
        int startcolidx = 0;
        int endcolidx = -1;
        CellRangeAddress region;
        for (AbsListReportSubDisplayColBean scbean : lstStatiColBeans) {
            stativalue = getSubColDisplayValue(this.subDisplayDataObj, scbean);
            stativalue = Tools.replaceAll(stativalue, "&nbsp;", " ");
            stativalue = stativalue.replaceAll("<.*?\\>", "");//?html
            if (rbean.getDbean().isDataexportColselect() || lstStatiColBeans.size() == 1
                    || (cacheDataBean.getAttributes().get("authroize_col_display") != null
                            && String.valueOf(cacheDataBean.getAttributes().get("authroize_col_display")).trim()
                                    .equals("false"))
                    || alrbean.hasControllCol()) {
                startcolidx = 0;
                endcolidx = cacheDataBean.getTotalColCount() - 1;
                int deltaCount = 0;
                if (alrdbean.getRowGroupColsNum() > 0 && alrdbean.getRowgrouptype() == 2) {
                    deltaCount = alrdbean.getRowGroupColsNum() - 1;
                }
                endcolidx = endcolidx + deltaCount;
            } else {
                startcolidx = endcolidx + 1;
                endcolidx = startcolidx + scbean.getPlainexcel_colspan() - 1;
            }
            region = new CellRangeAddress(excelRowIdx, excelRowIdx, startcolidx, endcolidx);
            StandardExcelAssistant.getInstance().setRegionCellStringValue(workbook, excelSheet, region,
                    dataCellStyle, stativalue);
        }
        excelRowIdx++;
    }
}

From source file:com.wabacus.system.component.application.report.CrossListReportType.java

License:Open Source License

protected void showSubRowDataInPlainExcelForWholeReport(Workbook workbook, CellStyle dataCellStyle,
        int position) {
    if (position == AbsListReportSubDisplayBean.SUBROW_POSITION_BOTTOM
            && this.lstVerticalStatiColBeansAndValues != null
            && this.lstVerticalStatiColBeansAndValues.size() > 0) {
        String stativalue;/*from  w ww .  j  a va2 s .  com*/
        int startcolidx = 0;
        int endcolidx = -1;
        CellRangeAddress region;
        for (VerticalCrossStatisticColData vcDataTmp : this.lstVerticalStatiColBeansAndValues) {
            stativalue = vcDataTmp.getColValue();
            stativalue = Tools.replaceAll(stativalue, "&nbsp;", " ");
            stativalue = stativalue.replaceAll("<.*?\\>", "");
            startcolidx = endcolidx + 1;
            endcolidx = startcolidx + vcDataTmp.getColspan() - 1;
            region = new CellRangeAddress(excelRowIdx, excelRowIdx, startcolidx, endcolidx);
            StandardExcelAssistant.getInstance().setRegionCellStringValue(workbook, excelSheet, region,
                    dataCellStyle, stativalue);
        }
        excelRowIdx++;
    }
    super.showSubRowDataInPlainExcelForWholeReport(workbook, dataCellStyle, position);
}