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

List of usage examples for org.apache.poi.ss.util CellRangeAddress CellRangeAddress

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.util.*
  5. CellRangeAddress
  6. 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.crm.webapp.util.ExcelCustomExporter.java

License:Apache License

protected void tableFacet(FacesContext context, Sheet sheet, SubTable table, int columnCount,
        String facetType) {/* w w  w .j  a v  a  2  s .c om*/
    Map<String, UIComponent> map = table.getFacets();
    UIComponent component = map.get(facetType);
    if (component != null) {
        String headerValue = null;
        if (component instanceof HtmlCommandButton) {
            headerValue = exportValue(context, component);
        } else if (component instanceof HtmlCommandLink) {
            headerValue = exportValue(context, component);
        } else if (component instanceof UIPanel) {
            String header = "";
            for (UIComponent child : component.getChildren()) {
                headerValue = exportValue(context, child);
                header = header + headerValue;
            }
            headerValue = header;
        } else {
            headerValue = exportFacetValue(context, component);
        }

        int sheetRowIndex = sheet.getLastRowNum() + 1;
        Row row = sheet.createRow(sheetRowIndex);
        Cell cell = row.createCell((short) 0);
        cell.setCellValue(headerValue);
        cell.setCellStyle(facetStyle);

        sheet.addMergedRegion(new CellRangeAddress(sheetRowIndex, //first row (0-based)
                sheetRowIndex, //last row  (0-based)
                0, //first column (0-based)
                columnCount //last column  (0-based)
        ));

    }
}

From source file:com.crm.webapp.util.ExcelCustomExporter.java

License:Apache License

protected void tableFacet(FacesContext context, Sheet sheet, DataList list, String facetType) {
    Map<String, UIComponent> map = list.getFacets();
    UIComponent component = map.get(facetType);
    if (component != null) {
        String headerValue = null;
        if (component instanceof HtmlCommandButton) {
            headerValue = exportValue(context, component);
        } else if (component instanceof HtmlCommandLink) {
            headerValue = exportValue(context, component);
        } else {//w  ww.  j  a va2s .  c om
            headerValue = exportFacetValue(context, component);
        }

        int sheetRowIndex = sheet.getLastRowNum() + 1;
        Row row = sheet.createRow(sheetRowIndex);
        Cell cell = row.createCell((short) 0);
        cell.setCellValue(headerValue);
        cell.setCellStyle(facetStyle);

        sheet.addMergedRegion(new CellRangeAddress(sheetRowIndex, //first row (0-based)
                sheetRowIndex, //last row  (0-based)
                0, //first column (0-based)
                1 //last column  (0-based)
        ));

    }
}

From source file:com.crm.webapp.util.ExcelCustomExporter.java

License:Apache License

protected void tableColumnGroup(Sheet sheet, DataTable table, String facetType) {
    ColumnGroup cg = table.getColumnGroup(facetType);
    List<UIComponent> headerComponentList = null;
    if (cg != null) {
        headerComponentList = cg.getChildren();
    }/*from  w ww .j a  v  a  2s .  c  o  m*/
    if (headerComponentList != null) {
        for (UIComponent component : headerComponentList) {
            if (component instanceof org.primefaces.component.row.Row) {
                org.primefaces.component.row.Row row = (org.primefaces.component.row.Row) component;
                int sheetRowIndex = sheet.getLastRowNum() + 1;
                Row xlRow = sheet.createRow(sheetRowIndex);
                int i = 0;
                for (UIComponent rowComponent : row.getChildren()) {
                    UIColumn column = (UIColumn) rowComponent;
                    String value = null;
                    if (facetType.equalsIgnoreCase("header")) {
                        value = column.getHeaderText();
                    } else {
                        value = column.getFooterText();
                    }
                    int rowSpan = column.getRowspan();
                    int colSpan = column.getColspan();

                    Cell cell = xlRow.getCell(i);

                    if (rowSpan > 1 || colSpan > 1) {
                        if (rowSpan > 1) {
                            cell = xlRow.createCell((short) i);
                            Boolean rowSpanFlag = false;
                            for (int j = 0; j < sheet.getNumMergedRegions(); j++) {
                                CellRangeAddress merged = sheet.getMergedRegion(j);
                                if (merged.isInRange(sheetRowIndex, i)) {
                                    rowSpanFlag = true;
                                }

                            }
                            if (!rowSpanFlag) {
                                cell.setCellValue(value);
                                cell.setCellStyle(facetStyle);
                                sheet.addMergedRegion(new CellRangeAddress(sheetRowIndex, //first row (0-based)
                                        sheetRowIndex + (rowSpan - 1), //last row  (0-based)
                                        i, //first column (0-based)
                                        i //last column  (0-based)
                                ));
                            }
                        }
                        if (colSpan > 1) {
                            cell = xlRow.createCell((short) i);

                            for (int j = 0; j < sheet.getNumMergedRegions(); j++) {
                                CellRangeAddress merged = sheet.getMergedRegion(j);
                                if (merged.isInRange(sheetRowIndex, i)) {
                                    cell = xlRow.createCell((short) ++i);
                                }
                            }
                            cell.setCellValue(value);
                            cell.setCellStyle(facetStyle);
                            sheet.addMergedRegion(new CellRangeAddress(sheetRowIndex, //first row (0-based)
                                    sheetRowIndex, //last row  (0-based)
                                    i, //first column (0-based)
                                    i + (colSpan - 1) //last column  (0-based)
                            ));
                            i = i + colSpan - 1;
                        }
                    } else {
                        cell = xlRow.createCell((short) i);
                        for (int j = 0; j < sheet.getNumMergedRegions(); j++) {
                            CellRangeAddress merged = sheet.getMergedRegion(j);
                            if (merged.isInRange(sheetRowIndex, i)) {
                                cell = xlRow.createCell((short) ++i);
                            }
                        }
                        cell.setCellValue(value);
                        cell.setCellStyle(facetStyle);
                    }

                    i++;
                }
            }

        }

    }
}

From source file:com.crm.webapp.util.ExcelCustomExporter.java

License:Apache License

protected void tableColumnGroup(Sheet sheet, SubTable table, String facetType) {
    ColumnGroup cg = table.getColumnGroup(facetType);
    List<UIComponent> headerComponentList = null;
    if (cg != null) {
        headerComponentList = cg.getChildren();
    }/*from   w w  w  .  j a  va 2 s.c  o m*/
    if (headerComponentList != null) {
        for (UIComponent component : headerComponentList) {
            if (component instanceof org.primefaces.component.row.Row) {
                org.primefaces.component.row.Row row = (org.primefaces.component.row.Row) component;
                int sheetRowIndex = sheet.getLastRowNum() + 1;
                Row xlRow = sheet.createRow(sheetRowIndex);
                int i = 0;
                for (UIComponent rowComponent : row.getChildren()) {
                    UIColumn column = (UIColumn) rowComponent;
                    String value = null;
                    if (facetType.equalsIgnoreCase("header")) {
                        value = column.getHeaderText();
                    } else {
                        value = column.getFooterText();
                    }
                    int rowSpan = column.getRowspan();
                    int colSpan = column.getColspan();

                    Cell cell = xlRow.getCell(i);

                    if (rowSpan > 1 || colSpan > 1) {

                        if (rowSpan > 1) {
                            cell = xlRow.createCell((short) i);
                            Boolean rowSpanFlag = false;
                            for (int j = 0; j < sheet.getNumMergedRegions(); j++) {
                                CellRangeAddress merged = sheet.getMergedRegion(j);
                                if (merged.isInRange(sheetRowIndex, i)) {
                                    rowSpanFlag = true;
                                }

                            }
                            if (!rowSpanFlag) {
                                cell.setCellStyle(cellStyle);
                                cell.setCellValue(value);
                                sheet.addMergedRegion(new CellRangeAddress(sheetRowIndex, //first row (0-based)
                                        sheetRowIndex + rowSpan - 1, //last row  (0-based)
                                        i, //first column (0-based)
                                        i //last column  (0-based)
                                ));
                            }
                        }
                        if (colSpan > 1) {
                            cell = xlRow.createCell((short) i);
                            for (int j = 0; j < sheet.getNumMergedRegions(); j++) {
                                CellRangeAddress merged = sheet.getMergedRegion(j);
                                if (merged.isInRange(sheetRowIndex, i)) {
                                    cell = xlRow.createCell((short) ++i);
                                }
                            }
                            cell.setCellStyle(cellStyle);
                            cell.setCellValue(value);
                            sheet.addMergedRegion(new CellRangeAddress(sheetRowIndex, //first row (0-based)
                                    sheetRowIndex, //last row  (0-based)
                                    i, //first column (0-based)
                                    i + colSpan - 1 //last column  (0-based)
                            ));
                            i = i + colSpan - 1;
                        }
                    } else {
                        cell = xlRow.createCell((short) i);
                        for (int j = 0; j < sheet.getNumMergedRegions(); j++) {
                            CellRangeAddress merged = sheet.getMergedRegion(j);
                            if (merged.isInRange(sheetRowIndex, i)) {
                                cell = xlRow.createCell((short) ++i);
                            }
                        }
                        cell.setCellValue(value);
                        cell.setCellStyle(facetStyle);

                    }
                    i++;
                }
            }

        }
    }

}

From source file:com.dituiba.excel.BaseExcelService.java

License:Apache License

/**
 * sheet ?//  ww w  . j av  a  2  s.c  om
 * @param sheet
 * @param row
 * @param length
 * @param data
 */
public static void addTitle(Sheet sheet, int row, int length, String data) {
    if (data == null || data.equals("") || data.equals("null")) {
        return;
    }
    Row sheetRow = sheet.createRow(row);
    for (int i = 0; i < length; i++) {
        sheetRow.createCell(i);
    }
    CellStyle style = sheet.getWorkbook().createCellStyle(); // ?
    style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 
    CellRangeAddress cellRangeAddress = new CellRangeAddress(row, row, 0, length - 1);
    sheet.addMergedRegion(cellRangeAddress);
    Cell cell = sheetRow.getCell(0);
    cell.setCellStyle(style);
    cell.setCellValue(data);
}

From source file:com.dituiba.excel.ExportTableService.java

License:Apache License

public void doExport() {
    Collection<CellBean> cellBeans = tableBean.getCellBeans();
    if (ObjectHelper.isNotEmpty(cellBeans)) {
        for (CellBean cellBean : cellBeans) {
            if (cellBean.getXSize() > 1 || cellBean.getYSize() > 1) {
                log.debug("??{}", JsonUtil.toJSON(cellBean));
                CellRangeAddress range = new CellRangeAddress(cellBean.getRowIndex(),
                        cellBean.getRowIndex() + cellBean.getYSize() - 1, cellBean.getColumnIndex(),
                        cellBean.getColumnIndex() + cellBean.getXSize() - 1);
                sheet.addMergedRegion(range);
            }//from   w w  w  .  j ava  2 s. c o  m
            log.debug("set row:{},column:{},content:{}", cellBean.getRowIndex(), cellBean.getColumnIndex(),
                    cellBean.getContent());
            Cell cell = sheet.getRow(cellBean.getRowIndex()).getCell(cellBean.getColumnIndex());
            cell.setCellValue(cellBean.getContent());
            CellStyle cellStyle = cell.getCellStyle();
            if (cellStyle == null) {
                cellStyle = sheet.getWorkbook().createCellStyle();
            }
            if (cellBean.isAlignCenter()) {
                cellStyle.setAlignment(CellStyle.ALIGN_CENTER);//
            }
            if (cellBean.isVerticalCenter()) {
                cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//
            }
            cellStyle.setWrapText(cellBean.isWrapText());
            cell.setCellStyle(cellStyle);
        }
    }
}

From source file:com.dua3.meja.model.poi.PoiSheet.java

License:Apache License

private void setAutoFilterForPoiRow(org.apache.poi.ss.usermodel.Row poiRow) {
    if (poiRow != null) {
        int rowNumber = poiRow.getRowNum();
        short col1 = poiRow.getFirstCellNum();
        short coln = poiRow.getLastCellNum();
        poiSheet.setAutoFilter(new CellRangeAddress(rowNumber, rowNumber, col1, coln));
    }/*  w ww .  j  a v a  2s  .c  om*/
}

From source file:com.dua3.meja.model.poi.PoiSheet.java

License:Apache License

@Override
public void addMergedRegion(RectangularRegion cells) {
    CellRangeAddress cra = new CellRangeAddress(cells.getFirstRow(), cells.getLastRow(), cells.getFirstColumn(),
            cells.getLastColumn());/*from ww w  .  j  a v a  2s  .  c  om*/
    poiSheet.addMergedRegion(cra);
    mergedRegions.add(cells);

    // update cell data
    int spanX = cells.getLastColumn() - cells.getFirstColumn() + 1;
    int spanY = cells.getLastRow() - cells.getFirstRow() + 1;
    PoiCell topLeftCell = getCell(cells.getFirstRow(), cells.getFirstColumn());
    for (int i = 0; i < spanY; i++) {
        for (int j = 0; j < spanX; j++) {
            PoiCell cell = getCell(cells.getFirstRow() + i, cells.getFirstColumn() + j);
            cell.addedToMergedRegion(topLeftCell, spanX, spanY);
        }
    }
}

From source file:com.eryansky.core.excelTools.JsGridReportBase.java

License:Apache License

/**
 * //  w  ww. j  ava  2s. c o  m
 * @param
 * @return void
 */
private void stopGrouping(HSSFSheet sheet, HashMap<Integer, String> word, HashMap<Integer, Integer> counter,
        int i, int size, int rownum, HSSFCellStyle style) {
    String w = word.get(i);
    if (w != null) {
        int len = counter.get(i);
        CellRangeAddress address = new CellRangeAddress(rownum - len, rownum - 1, i, i);
        sheet.addMergedRegion(address);
        fillMergedRegion(sheet, address, style);
        word.remove(i);
        counter.remove(i);
    }
    if (i + 1 < size) {
        stopGrouping(sheet, word, counter, i + 1, size, rownum, style);
    }
}

From source file:com.eryansky.core.excelTools.JsGridReportBase.java

License:Apache License

/**
 * /*from  w  ww .  j a  va 2  s. c  o m*/
 * @param
 * @return void
 */
private void generateColumn(HSSFSheet sheet, TableColumn tc, int maxlevel, int rownum, int colnum,
        HSSFCellStyle headerstyle) {
    HSSFRow row = sheet.getRow(rownum);
    if (row == null)
        row = sheet.createRow(rownum);

    HSSFCell cell = row.createCell(colnum);
    cell.setCellValue(tc.getDisplay());

    if (headerstyle != null)
        cell.setCellStyle(headerstyle);
    if (tc.isComplex()) {
        CellRangeAddress address = new CellRangeAddress(rownum, rownum, colnum, colnum + tc.getLength() - 1);
        sheet.addMergedRegion(address);
        fillMergedRegion(sheet, address, headerstyle);

        int cn = colnum;
        for (int i = 0; i < tc.getChildren().size(); i++) {
            if (i != 0) {
                cn = cn + tc.getChildren().get(i - 1).getLength();
            }
            generateColumn(sheet, tc.getChildren().get(i), maxlevel, rownum + 1, cn, headerstyle);
        }
    } else {
        CellRangeAddress address = new CellRangeAddress(rownum, rownum + maxlevel - tc.level, colnum, colnum);
        sheet.addMergedRegion(address);
        fillMergedRegion(sheet, address, headerstyle);
    }
    sheet.autoSizeColumn(colnum, true);
}