Java CellRangeAddress类代码示例

import org.apache.poi.ss.util.CellRangeAddress; //导入依赖的package包/类
protected void setupTotalCell(Cell cell, final String propId, final int currentRow, final int startRow, int col) {
    cell.setCellStyle(getCellStyle(propId, currentRow, startRow, col, true));
    final HorizontalAlignment poiAlignment = getGridHolder().getCellAlignment(propId);
    CellUtil.setAlignment(cell, poiAlignment);
    Class<?> propType = getGridHolder().getPropertyType(propId);
    if (isNumeric(propType)) {
        CellRangeAddress cra = new CellRangeAddress(startRow, currentRow - 1, col, col);
        if (isHierarchical()) {
            // 9 & 109 are for sum. 9 means include hidden cells, 109 means exclude.
            // this will show the wrong value if the user expands an outlined category, so
            // we will range value it first
            cell.setCellFormula("SUM(" + cra.formatAsString(hierarchicalTotalsSheet.getSheetName(),
                    true) + ")");
        } else {
            cell.setCellFormula("SUM(" + cra.formatAsString() + ")");
        }
    } else {
        if (0 == col) {
            cell.setCellValue(createHelper.createRichTextString("Total"));
        }
    }
}
 

import org.apache.poi.ss.util.CellRangeAddress; //导入依赖的package包/类
private boolean isMergedRegion(XSSFSheet sheet, int row, int column) {
	int sheetMergeCount = sheet.getNumMergedRegions();
	for (int i = 0; i < sheetMergeCount; i++) {
		CellRangeAddress range = sheet.getMergedRegion(i);
		int firstColumn = range.getFirstColumn();
		int lastColumn = range.getLastColumn();
		int firstRow = range.getFirstRow();
		int lastRow = range.getLastRow();
		if (row > firstRow && row < lastRow) {
			if (column > firstColumn && column < lastColumn) {
				return true;
			}
		}
	}
	return false;
}
 

import org.apache.poi.ss.util.CellRangeAddress; //导入依赖的package包/类
/**
 * 判断单元格是否是合并的单格，如果是，获取其合并的行数。
 */
private static int getMergerCellRegionRow(HSSFSheet sheet, int cellRow, int cellCol) throws Throwable {
	int retVal = 0;
	int sheetMergerCount = sheet.getNumMergedRegions();
	for (int i = 0; i < sheetMergerCount; i++) {
		CellRangeAddress cra = sheet.getMergedRegion(i);
		int firstRow = cra.getFirstRow(); // 合并单元格CELL起始行
		int firstCol = cra.getFirstColumn(); // 合并单元格CELL起始列
		int lastRow = cra.getLastRow(); // 合并单元格CELL结束行
		int lastCol = cra.getLastColumn(); // 合并单元格CELL结束列
		if (cellRow >= firstRow && cellRow <= lastRow) { // 判断该单元格是否是在合并单元格中
			if (cellCol >= firstCol && cellCol <= lastCol) {
				retVal = lastRow - firstRow + 1; // 得到合并的行数
				break;
			}
		}
	}
	return retVal;
}
 

import org.apache.poi.ss.util.CellRangeAddress; //导入依赖的package包/类
/**
 * 获取合并单元格式
 *
 * @param sheet
 * @param row
 * @param columnFrom
 * @param columnTo
 * @return
 */
private static void setRegionBorder(HSSFSheet sheet, int row, int columnFrom, int columnTo) {
    CellRangeAddress region = new CellRangeAddress(row, row, columnFrom, columnTo);
    sheet.addMergedRegion(region);
    final short border = CellStyle.BORDER_THIN;
    HSSFWorkbook wb = sheet.getWorkbook();
    RegionUtil.setBorderBottom(border, region, sheet, wb);
    RegionUtil.setBorderTop(border, region, sheet, wb);
    RegionUtil.setBorderLeft(border, region, sheet, wb);
    RegionUtil.setBorderRight(border, region, sheet, wb);
    RegionUtil.setBottomBorderColor(HSSFColor.BLACK.index, region, sheet, wb);
    RegionUtil.setTopBorderColor(HSSFColor.BLACK.index, region, sheet, wb);
    RegionUtil.setLeftBorderColor(HSSFColor.BLACK.index, region, sheet, wb);
    RegionUtil.setRightBorderColor(HSSFColor.BLACK.index, region, sheet, wb);
}
 

import org.apache.poi.ss.util.CellRangeAddress; //导入依赖的package包/类
/**
 * 合并行
 */
//TODO 暂时支持两行表头
private void mergeRows(Sheet sheet, CellStyle cellStyle, ExcelMeta excelMeta) {

    Row row = null;
    Cell cell = null;
    String[] lastRowVals = new String[excelMeta.getTitleColumnNum()];
    for (int r = 0; r < excelMeta.getTitleRowNum(); r++) {
        for (int c = 0; c < excelMeta.getTitleColumnNum(); c++) {
            row = sheet.getRow(r);
            cell = row.getCell(c);
            if (r == 0) {
                lastRowVals[c] = cell.getStringCellValue();
            } else {
                if (StringUtils.equals(lastRowVals[c], cell.getStringCellValue())) {
                    cell.setCellValue("");
                    sheet.addMergedRegion(new CellRangeAddress(0, r, c, c));
                    Cell nowCell = sheet.getRow(0).getCell(c);
                    nowCell.setCellStyle(cellStyle);
                }
            }

        }
    }

}
 

import org.apache.poi.ss.util.CellRangeAddress; //导入依赖的package包/类
private Span getSpan(XSSFSheet sheet,int row ,int column){
	int sheetMergeCount = sheet.getNumMergedRegions(); 
	for (int i = 0; i < sheetMergeCount; i++) {
		CellRangeAddress range = sheet.getMergedRegion(i);
		int firstColumn = range.getFirstColumn();
		int lastColumn = range.getLastColumn();
		int firstRow = range.getFirstRow();
		if(row == firstRow && column==firstColumn){  
			int lastRow = range.getLastRow();
			int rowSpan=lastRow-firstRow;
			if(rowSpan>0){
				rowSpan++;
			}
			int colSpan=lastColumn-firstColumn;
			if(colSpan>0){
				colSpan++;
			}
			return new Span(rowSpan,colSpan);
		}
	}
	return new Span(0,0);
}
 

import org.apache.poi.ss.util.CellRangeAddress; //导入依赖的package包/类
public String getStringRangeValue(Cell cell) {
    int rowIndex = cell.getRowIndex();
    int columnIndex = cell.getColumnIndex();

    Sheet sheet = cell.getSheet();
    int size = sheet.getNumMergedRegions();
    for (int i = 0; i < size; i++) {
        CellRangeAddress range = sheet.getMergedRegion(i);
        if (range.isInRange(rowIndex, columnIndex)) {
            Cell firstCell = getCell(sheet, range.getFirstRow(),
                    range.getFirstColumn()); // 左上のセルを取得
            return getStringValue(firstCell);
        }
    }
    return null;
}
 

import org.apache.poi.ss.util.CellRangeAddress; //导入依赖的package包/类
/**
 * 判断单元格在不在合并单元格范围内，如果是，获取其合并的列数。
 */
private static int getMergerCellRegionCol(HSSFSheet sheet, int cellRow, int cellCol) throws Throwable {
	int retVal = 0;
	int sheetMergerCount = sheet.getNumMergedRegions();
	for (int i = 0; i < sheetMergerCount; i++) {
		CellRangeAddress cra = sheet.getMergedRegion(i);
		int firstRow = cra.getFirstRow(); // 合并单元格CELL起始行
		int firstCol = cra.getFirstColumn(); // 合并单元格CELL起始列
		int lastRow = cra.getLastRow(); // 合并单元格CELL结束行
		int lastCol = cra.getLastColumn(); // 合并单元格CELL结束列
		if (cellRow >= firstRow && cellRow <= lastRow) { // 判断该单元格是否是在合并单元格中
			if (cellCol >= firstCol && cellCol <= lastCol) {
				retVal = lastCol - firstCol + 1; // 得到合并的列数
				break;
			}
		}
	}
	return retVal;
}
 

import org.apache.poi.ss.util.CellRangeAddress; //导入依赖的package包/类
protected void formatCellStatus(Sheet sheet, Cell cell) {
	cell.setCellStyle(styles.get("status"));
	SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
	ConditionalFormattingRule ruleGreen = sheetCF.createConditionalFormattingRule(ComparisonOperator.EQUAL, "1");
	PatternFormatting fill1 = ruleGreen.createPatternFormatting();
	fill1.setFillBackgroundColor(IndexedColors.GREEN.index);
	fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
	//
	ConditionalFormattingRule ruleRed = sheetCF.createConditionalFormattingRule(ComparisonOperator.EQUAL, "0");
	PatternFormatting fill2 = ruleRed.createPatternFormatting();
	fill2.setFillBackgroundColor(IndexedColors.RED.index);
	fill2.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
	//
	ConditionalFormattingRule ruleOrange = sheetCF.createConditionalFormattingRule(ComparisonOperator.EQUAL, "2");
	PatternFormatting fill3 = ruleOrange.createPatternFormatting();
	fill3.setFillBackgroundColor(IndexedColors.ORANGE.index);
	fill3.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
	//
	String name = CellReference.convertNumToColString(cell.getColumnIndex());
	String location = "$" + name + "$" + cell.getRowIndex() + ":$" + name + "$" + (cell.getRowIndex() + 1);

	CellRangeAddress[] regions = { CellRangeAddress.valueOf(location) };
	ConditionalFormattingRule[] cfRules = new ConditionalFormattingRule[] { ruleGreen, ruleRed, ruleOrange };
	sheetCF.addConditionalFormatting(regions, cfRules);
}
 

import org.apache.poi.ss.util.CellRangeAddress; //导入依赖的package包/类
private void createFilter(Sheet sheet) {
	int startRow = ROW_INDEX_COLUMNS_DESCRIPTIONS;
	int lastRow = ROW_INDEX_COLUMNS_DESCRIPTIONS + 1;
	int startCol = 0;
	int lastCol = sheet.getRow(startRow).getLastCellNum() - 1;
	sheet.setAutoFilter(new CellRangeAddress(startRow, lastRow, startCol, lastCol));

}
 

import org.apache.poi.ss.util.CellRangeAddress; //导入依赖的package包/类
/**
 * 合并单元格
 * 
 * @author      ZhengWei(HY)
 * @createDate  2017-06-21
 * @version     v1.0
 *
 * @param i_Sheet        工作表
 * @param i_FirstRow     首行
 * @param i_LastRow      尾行
 * @param i_FirstColumn  首列
 * @param i_LastColumn   尾列
 * @param i_IsSafe       是要安全？还是要性能
 */
public final static void addMergedRegions(Sheet i_Sheet ,int i_FirstRow ,int i_LastRow ,int i_FirstColumn ,int i_LastColumn ,boolean i_IsSafe)
{
    if ( i_FirstRow == i_LastRow && i_FirstColumn == i_LastColumn )
    {
        return;
    }
    
    CellRangeAddress v_CellRA = new CellRangeAddress(i_FirstRow 
                                                    ,i_LastRow 
                                                    ,i_FirstColumn
                                                    ,i_LastColumn);
    
    if ( i_IsSafe )
    {
        i_Sheet.addMergedRegion(v_CellRA);
    }
    else
    {
        i_Sheet.addMergedRegionUnsafe(v_CellRA);
    }
}
 

import org.apache.poi.ss.util.CellRangeAddress; //导入依赖的package包/类
private Span getSpan(HSSFSheet sheet,int row ,int column){
	int sheetMergeCount = sheet.getNumMergedRegions(); 
	for (int i = 0; i < sheetMergeCount; i++) {
		CellRangeAddress range = sheet.getMergedRegion(i);
		int firstColumn = range.getFirstColumn();
		int lastColumn = range.getLastColumn();
		int firstRow = range.getFirstRow();
		if(row == firstRow && column==firstColumn){  
			int lastRow = range.getLastRow();
			int rowSpan=lastRow-firstRow;
			if(rowSpan>0){
				rowSpan++;
			}
			int colSpan=lastColumn-firstColumn;
			if(colSpan>0){
				colSpan++;
			}
			return new Span(rowSpan,colSpan);
		}
	}
	return new Span(0,0);
}
 

import org.apache.poi.ss.util.CellRangeAddress; //导入依赖的package包/类
private boolean isMergedRegion(HSSFSheet sheet, int row, int column) {
	int sheetMergeCount = sheet.getNumMergedRegions();
	for (int i = 0; i < sheetMergeCount; i++) {
		CellRangeAddress range = sheet.getMergedRegion(i);
		int firstColumn = range.getFirstColumn();
		int lastColumn = range.getLastColumn();
		int firstRow = range.getFirstRow();
		int lastRow = range.getLastRow();
		if (row > firstRow && row < lastRow) {
			if (column > firstColumn && column < lastColumn) {
				return true;
			}
		}
	}
	return false;
}
 

import org.apache.poi.ss.util.CellRangeAddress; //导入依赖的package包/类
public int addTitleToSheet(ReportTitle reportTitle, Sheet sheet, int row, int firstCol, int lastCol) {
	if (!reportTitle.isShowTitle()) {
		return row;
	}
	Row titleRow = sheet.createRow(row);
	Cell titleCell = titleRow.createCell(firstCol);
	titleCell.setCellType(Cell.CELL_TYPE_STRING);
	titleCell.setCellValue(reportTitle.getTitle());
	CellStyle titleStyle = new TitleStyleBuilder().builder(reportTitle, sheet.getWorkbook());
	titleCell.setCellStyle(titleStyle);
	CellRangeAddress rangle = new CellRangeAddress(row, row, firstCol, lastCol);
	sheet.addMergedRegion(rangle);
	this.setCellRangeAddressBorder(rangle, sheet);
	return row + 1;
}
 

import org.apache.poi.ss.util.CellRangeAddress; //导入依赖的package包/类
/**
 * @param srcSheet the sheet to copy.
 * @param destSheet the sheet to create.
 * @param srcRow the row to copy.
 * @param destRow the row to create.
 * @param styleMap -
 */
public static void copyRow(XSSFSheet srcSheet, XSSFSheet destSheet, XSSFRow srcRow, XSSFRow destRow, Map<Integer, XSSFCellStyle> styleMap) {
    // manage a list of merged zone in order to not insert two times a merged zone  
    Set<CellRangeAddressWrapper> mergedRegions = new TreeSet<CellRangeAddressWrapper>();
    destRow.setHeight(srcRow.getHeight());
    // pour chaque row  
    for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) {
        if(j<0){
        }else{
            XSSFCell oldCell = srcRow.getCell(j);   // ancienne cell  
        XSSFCell newCell = destRow.getCell(j);  // new cell   
        if (oldCell != null) {
            if (newCell == null) {
                newCell = destRow.createCell(j);
            }
            // copy chaque cell  
            copyCell(oldCell, newCell, styleMap);
                  // copy les informations de fusion entre les cellules  
            //System.out.println("row num: " + srcRow.getRowNum() + " , col: " + (short)oldCell.getColumnIndex());  
            CellRangeAddress mergedRegion = getMergedRegion(srcSheet, srcRow.getRowNum(), (short) oldCell.getColumnIndex());

            if (mergedRegion != null) {
                //System.out.println("Selected merged region: " + mergedRegion.toString());  
                CellRangeAddress newMergedRegion = new CellRangeAddress(mergedRegion.getFirstRow(), mergedRegion.getLastRow(), mergedRegion.getFirstColumn(), mergedRegion.getLastColumn());
                //System.out.println("New merged region: " + newMergedRegion.toString());  
                CellRangeAddressWrapper wrapper = new CellRangeAddressWrapper(newMergedRegion);
                if (isNewMergedRegion(wrapper, mergedRegions)) {
                    mergedRegions.add(wrapper);
                    destSheet.addMergedRegion(wrapper.range);
                }
            }
        }
        }
        
    }

}