org.apache.poi.ss.util.CellRangeAddress Java Examples

public String toString() {
	StringBuffer sb = new StringBuffer();
	sb.append("[DV]\n");
	sb.append(" options=").append(Integer.toHexString(_option_flags));
	sb.append(" title-prompt=").append(formatTextTitle(_promptTitle));
	sb.append(" title-error=").append(formatTextTitle(_errorTitle));
	sb.append(" text-prompt=").append(formatTextTitle(_promptText));
	sb.append(" text-error=").append(formatTextTitle(_errorText));
	sb.append("\n");
	appendFormula(sb, "Formula 1:",  _formula1);
	appendFormula(sb, "Formula 2:",  _formula2);
	sb.append("Regions: ");
	int nRegions = _regions.countRanges();
	for(int i=0; i<nRegions; i++) {
		if (i>0) {
			sb.append(", ");
		}
		CellRangeAddress addr = _regions.getCellRangeAddress(i);
		sb.append('(').append(addr.getFirstRow()).append(',').append(addr.getLastRow());
		sb.append(',').append(addr.getFirstColumn()).append(',').append(addr.getLastColumn()).append(')');
	}
	sb.append("\n");
	sb.append("[/DV]");

	return sb.toString();
}
 

/**
 * 判断单元格是否是合并的单格，如果是，获取其合并的行数。
 */
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;
}
 

/**
 * 判断合并了行
 * @param sheet
 * @param row
 * @param column
 * @return
 */
public static boolean isMergedRow(Sheet 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;
}
 

/**
 * 创建 表头
 * 
 * @param title
 * @param sheet
 * @param workbook
 * @param feildWidth
 */
private static int createHeaderRow(ExcelTitle entity, Sheet sheet,
		HSSFWorkbook workbook, int feildWidth) {
	Row row = sheet.createRow(0);
	row.setHeight((short) 900);
	createStringCell(row, 0, entity.getTitle(), getHeaderStyle(workbook,entity),null);
	sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, feildWidth));
	if(entity.getSecondTitle()!=null){
		row = sheet.createRow(1);
		HSSFCellStyle style = workbook.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
		createStringCell(row, 0, entity.getSecondTitle(), style,null);
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, feildWidth));
		return 2;
	}
	return 1;
}
 

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;
}
 

private void mergeCellRegion( final TableRectangle rectangle, final CellStyle spannedStyle ) {
  final int rowSpan = rectangle.getRowSpan();
  final int columnSpan = rectangle.getColumnSpan();
  if ( rowSpan <= 1 && columnSpan <= 1 ) {
    return;
  }

  int row = rectangle.getY1();
  int col = rectangle.getX1();

  sheet.addMergedRegion( new CellRangeAddress( row, ( row + rowSpan - 1 ), col, ( col + columnSpan - 1 ) ) );

  for ( int spannedRow = 0; spannedRow < rowSpan; spannedRow += 1 ) {
    for ( int spannedCol = 0; spannedCol < columnSpan; spannedCol += 1 ) {
      final Cell regionCell = getCellAt( ( col + spannedCol ), row + spannedRow );
      if ( spannedStyle != null ) {
        regionCell.setCellStyle( spannedStyle );
      }
    }
  }
}
 

/**
 * Allows to add a new Conditional Formatting set to the sheet.
 *
 * @param regions - list of rectangular regions to apply conditional formatting rules
 * @param cfRules - set of up to three conditional formatting rules
 *
 * @return index of the newly created Conditional Formatting object
 */
public int addConditionalFormatting(CellRangeAddress[] regions, HSSFConditionalFormattingRule[] cfRules) {
    if (regions == null) {
        throw new IllegalArgumentException("regions must not be null");
    }
    for(CellRangeAddress range : regions) range.validate(SpreadsheetVersion.EXCEL97);

    if (cfRules == null) {
        throw new IllegalArgumentException("cfRules must not be null");
    }
    if (cfRules.length == 0) {
        throw new IllegalArgumentException("cfRules must not be empty");
    }
    if (cfRules.length > 3) {
        throw new IllegalArgumentException("Number of rules must not exceed 3");
    }

    CFRuleBase[] rules = new CFRuleBase[cfRules.length];
    for (int i = 0; i != cfRules.length; i++) {
        rules[i] = cfRules[i].getCfRuleRecord();
    }
    CFRecordsAggregate cfra = new CFRecordsAggregate(regions, rules);
    return _conditionalFormattingTable.add(cfra);
}
 

/**
 * Highlight payments that are due in the next thirty days
 */
static void expiryInNext30Days(Sheet sheet) {
	CellStyle style = sheet.getWorkbook().createCellStyle();
	style.setDataFormat((short) BuiltinFormats.getBuiltinFormat("d-mmm"));
	sheet.createRow(0).createCell(0).setCellValue("Date");
	sheet.createRow(1).createCell(0).setCellFormula("TODAY()+29");
	sheet.createRow(2).createCell(0).setCellFormula("A2+1");
	sheet.createRow(3).createCell(0).setCellFormula("A3+1");
	for (int rownum = 1; rownum <= 3; rownum++)
		sheet.getRow(rownum).getCell(0).setCellStyle(style);
	SheetConditionalFormatting sheetCF = sheet
			.getSheetConditionalFormatting();
	// Condition 1: Formula Is =A2=A1 (White Font)
	ConditionalFormattingRule rule1 = sheetCF
			.createConditionalFormattingRule("AND(A2-TODAY()>=0,A2-TODAY()<=30)");
	FontFormatting font = rule1.createFontFormatting();
	font.setFontStyle(false, true);
	font.setFontColorIndex(IndexedColors.BLUE.index);
	CellRangeAddress[] regions = { CellRangeAddress.valueOf("A2:A4") };
	sheetCF.addConditionalFormatting(regions, rule1);
	sheet.getRow(0).createCell(1)
			.setCellValue("Dates within the next 30 days are highlighted");
}
 

public cfData execute( cfSession _session, List<cfData> parameters ) throws cfmRunTimeException {
	cfSpreadSheetData	spreadsheet = null;
	
	/*
	 * Collect up the parameters
	 */
spreadsheet		= (cfSpreadSheetData)parameters.get(4);
int startRow	= parameters.get(3).getInt()-1;
int endRow		= parameters.get(2).getInt()-1;
int startCol	= parameters.get(1).getInt()-1;
int endCol		= parameters.get(0).getInt()-1;

if ( startRow < 0 )
		throwException(_session, "startRow must be 1 or greater (" + startRow + ")");
	if ( endRow < 0 )
		throwException(_session, "endRow must be 1 or greater (" + endRow + ")");
if ( startCol < 0 )
		throwException(_session, "column must be 1 or greater (" + startCol + ")");
	if ( endCol < 0 )
		throwException(_session, "row must be 1 or greater (" + endCol + ")");

	if ( endRow < startRow )
		throwException(_session, "startrow must be smaller than endrow");
	if ( endCol < startCol )
		throwException(_session, "startcolumn must be smaller than endcolumn");
	
	//Perform the merge operation
 	spreadsheet.getActiveSheet().addMergedRegion( new CellRangeAddress( startRow, endRow, startCol, endCol ) );

	return cfBooleanData.TRUE;
}
 

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 );

    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;
}
 

public CellRangeAddress getMergedRegionAt(int index) {
    //safety checks
    MergedCellsTable mrt = getMergedRecords();
    if (index >=  mrt.getNumberOfMergedRegions()) {
        return null;
    }
    return mrt.get(index);
}
 

private static CFHeaderBase createHeader(CellRangeAddress[] regions, CFRuleBase[] rules) {
    final CFHeaderBase header;
    if (rules.length == 0 || rules[0] instanceof CFRuleRecord) {
        header = new CFHeaderRecord(regions, rules.length);
    } else {
        header = new CFHeader12Record(regions, rules.length);
    }

    // set the "needs recalculate" by default to avoid Excel handling conditional formatting incorrectly
    // see bug 52122 for details
    header.setNeedRecalculation(true);

    return header;
}
 

/**
 * Vertical merge
 *
 * @param autoMergeCallback AutoMergeCallback
 * @param row               Current row
 * @param field             Current field
 * @param excelField        ExcelField annotation on current field
 * @param index             Line index, index type according to isHead
 * @param dataSize          Excel head data size or body data size,
 * @param colIndex          Current col index
 * @param cellValue         Current cell value
 * @param obj               The object corresponding to the current row
 */
private void autoMergeY(ExcelAutoMergeCallback<?> autoMergeCallback, Row row, Field field, ExcelField excelField, int index, int colIndex,
                        Object cellValue, Object obj, int dataSize) {
    if (index == 0) {
        autoMergeCallback.mergeY(obj == null ? null : this.gson.fromJson(this.gson.toJson(obj), (Type) obj.getClass()), field, colIndex, index);
        this.oldRowModelMap.put(colIndex, new ExcelOldRowModel(cellValue, row.getRowNum()));
        return;
    }
    ExcelOldRowModel excelOldRowModel = this.oldRowModelMap.get(colIndex);
    if (autoMergeCallback.mergeY(obj == null ? null : this.gson.fromJson(this.gson.toJson(obj), (Type) obj.getClass()), field, colIndex, index)) {
        if (ParamUtils.equals(cellValue, excelOldRowModel.getOldRowCellValue(), excelField.autoMerge().empty())) {
            if (index == dataSize - 1) {
                this.context.getSheet().addMergedRegion(new CellRangeAddress(excelOldRowModel.getOldRowIndex(), row.getRowNum(), colIndex, colIndex));
            }
            return;
        }
        if (excelOldRowModel.getOldRowIndex() + 1 < row.getRowNum()) {
            this.context.getSheet().addMergedRegion(new CellRangeAddress(excelOldRowModel.getOldRowIndex(), row.getRowNum() - 1, colIndex, colIndex));
        }
        if (index != dataSize - 1) {
            this.oldRowModelMap.put(colIndex, new ExcelOldRowModel(cellValue, row.getRowNum()));
        }
        return;
    }
    if (excelOldRowModel.getOldRowIndex() + 1 < row.getRowNum()) {
        this.context.getSheet().addMergedRegion(new CellRangeAddress(excelOldRowModel.getOldRowIndex(), row.getRowNum() - 1, colIndex, colIndex));
        excelOldRowModel.setOldRowCellValue(cellValue);
        excelOldRowModel.setOldRowIndex(row.getRowNum());
        this.oldRowModelMap.put(colIndex, excelOldRowModel);
    }
}
 

public CellRangeAddress getArrayFormulaRange() {
	if (_sharedFormulaRecord != null) {
		throw new IllegalStateException("not an array formula cell.");
	}
	CellReference expRef = _formulaRecord.getFormula().getExpReference();
	if (expRef == null) {
		throw new IllegalStateException("not an array formula cell.");
	}
	ArrayRecord arec = _sharedValueManager.getArrayRecord(expRef.getRow(), expRef.getCol());
	if (arec == null) {
		throw new IllegalStateException("ArrayRecord was not found for the locator " + expRef.formatAsString());
	}
	CellRangeAddress8Bit a = arec.getRange();
	return new CellRangeAddress(a.getFirstRow(), a.getLastRow(), a.getFirstColumn(),a.getLastColumn());
}
 

private CellRangeAddress getMergedRegionBegunBy( Sheet sheet, int row, int col ) {
	for( int i = 0; i < sheet.getNumMergedRegions(); ++i ) {
		CellRangeAddress range = sheet.getMergedRegion(i);
		if( ( range.getFirstColumn() == col ) && ( range.getFirstRow() == row ) ) {
			return range;
		}
	}
	return null;
}
 

public FeatRecord(RecordInputStream in) {
	futureHeader = new FtrHeader(in);
	
	isf_sharedFeatureType = in.readShort();
	reserved1 = in.readByte();
	reserved2 = in.readInt();
	int cref = in.readUShort();
	cbFeatData = in.readInt();
	reserved3 = in.readShort();

	cellRefs = new CellRangeAddress[cref];
	for(int i=0; i<cellRefs.length; i++) {
		cellRefs[i] = new CellRangeAddress(in);
	}
	
	switch(isf_sharedFeatureType) {
	case FeatHdrRecord.SHAREDFEATURES_ISFPROTECTION:
		sharedFeature = new FeatProtection(in);
		break;
	case FeatHdrRecord.SHAREDFEATURES_ISFFEC2:
		sharedFeature = new FeatFormulaErr2(in);
		break;
	case FeatHdrRecord.SHAREDFEATURES_ISFFACTOID:
		sharedFeature = new FeatSmartTag(in);
		break;
	default:
		logger.log( POILogger.ERROR, "Unknown Shared Feature " + isf_sharedFeatureType + " found!");
	}
}
 

/**
 * Builds the rows' headers recursively with this order: |-----|-----|-----| | | | 3 | | | |-----| | | 2 | 4 | | | |-----| | 1 | | 5 | | |-----|-----| | | |
 * 7 | | | 6 |-----| | | | 8 | |-----|-----|-----| | | | 11 | | 9 | 10 |-----| | | | 12 | |-----|-----|-----|
 *
 * @param sheet
 *            The sheet of the XLS file
 * @param siblings
 *            The siblings nodes of the headers structure
 * @param rowNum
 *            The row number where the first sibling must be inserted
 * @param columnNum
 *            The column number where the siblings must be inserted
 * @param createHelper
 *            The file creation helper
 * @throws JSONException
 */
protected void buildRowsHeaders(Sheet sheet, CrossTab cs, List<Node> siblings, int rowNum, int columnNum, CreationHelper createHelper, Locale locale,
		CellStyle cellStyle) throws JSONException {
	int rowsCounter = rowNum;

	for (int i = 0; i < siblings.size(); i++) {
		Node aNode = siblings.get(i);
		List<Node> childs = aNode.getChilds();
		Row row = sheet.getRow(rowsCounter);
		Cell cell = row.createCell(columnNum);
		String text = aNode.getDescription();

		if (cs.isMeasureOnRow() && (childs == null || childs.size() <= 0)) {
			// apply the measure scale factor
			text = MeasureScaleFactorOption.getScaledName(text, cs.getMeasureScaleFactor(text), locale);
		}
		cell.setCellValue(createHelper.createRichTextString(text));
		cell.setCellType(this.getCellTypeString());

		cell.setCellStyle(cellStyle);

		int descendants = aNode.getLeafsNumber();
		if (descendants > 1) {
			sheet.addMergedRegion(new CellRangeAddress(rowsCounter, // first row (0-based)
					rowsCounter + descendants - 1, // last row (0-based)
					columnNum, // first column (0-based)
					columnNum // last column (0-based)
			));
		}

		if (childs != null && childs.size() > 0) {
			buildRowsHeaders(sheet, cs, childs, rowsCounter, columnNum + 1, createHelper, locale, cellStyle);
		}
		int increment = descendants > 1 ? descendants : 1;
		rowsCounter = rowsCounter + increment;
	}
}
 

private void setCellRangeAddressBorder(CellRangeAddress rangle, Sheet sheet) {
	int border = 1;
	Workbook wb = sheet.getWorkbook();
	RegionUtil.setBorderBottom(border, rangle, sheet, wb);
	RegionUtil.setBorderLeft(border, rangle, sheet, wb);
	RegionUtil.setBorderRight(border, rangle, sheet, wb);
	RegionUtil.setBorderTop(border, rangle, sheet, wb);
}
 

/**
 * @return the list of merged regions
 */
@Override
public List<CellRangeAddress> getMergedRegions() {
    List<CellRangeAddress> addresses = new ArrayList<CellRangeAddress>();
    int count = _sheet.getNumMergedRegions();
    for (int i=0; i < count; i++) {
        addresses.add(_sheet.getMergedRegionAt(i));
    }
    return addresses;
}
 

/**
 * 处理合并单元格
 * 
 * @param index
 * @param rowNum
 * @param text
 * @param mergeDataMap
 * @param sheet
 * @param cell
 * @param delys
 */
private static void hanlderMergeCells(Integer index, int rowNum, String text, Map<Integer, MergeEntity> mergeDataMap, Sheet sheet, Cell cell, int[] delys) {
	if (mergeDataMap.containsKey(index)) {
		if (checkIsEqualByCellContents(mergeDataMap.get(index), text, cell, delys, rowNum)) {
			mergeDataMap.get(index).setEndRow(rowNum);
		} else {
			sheet.addMergedRegion(new CellRangeAddress(mergeDataMap.get(index).getStartRow(), mergeDataMap.get(index).getEndRow(), index, index));
			mergeDataMap.put(index, createMergeEntity(text, rowNum, cell, delys));
		}
	} else {
		mergeDataMap.put(index, createMergeEntity(text, rowNum, cell, delys));
	}
}
 

void setCellArrayFormula(CellRangeAddress range) {
    int row = _record.getRow();
    short col = _record.getColumn();
    short styleIndex = _record.getXFIndex();
    setCellType(CellType.FORMULA, false, row, col, styleIndex);

    // Billet for formula in rec
    Ptg[] ptgsForCell = {new ExpPtg(range.getFirstRow(), range.getFirstColumn())};
    FormulaRecordAggregate agg = (FormulaRecordAggregate) _record;
    agg.setParsedExpression(ptgsForCell);
}
 

/**
 * 指定したセルのアドレスの結合情報を取得する。
 * @since 0.5
 * @param sheet シート情報
 * @param rowIdx 行番号
 * @param colIdx 列番号
 * @return 結合していない場合nullを返す。
 */
public static CellRangeAddress getMergedRegion(final Sheet sheet, final int rowIdx, final int colIdx) {
    ArgUtils.notNull(sheet, "sheet");

    final int num = sheet.getNumMergedRegions();
    for(int i=0; i < num; i ++) {
        final CellRangeAddress range = sheet.getMergedRegion(i);
        if(range.isInRange(rowIdx, colIdx)) {
            return range;
        }
    }

    return null;
}
 

@Override
public void processRecord(XlsReadContext xlsReadContext, Record record) {
    MergeCellsRecord mcr = (MergeCellsRecord)record;
    for (int i = 0; i < mcr.getNumAreas(); i++) {
        CellRangeAddress cellRangeAddress = mcr.getAreaAt(i);
        CellExtra cellExtra = new CellExtra(CellExtraTypeEnum.MERGE, null, cellRangeAddress.getFirstRow(),
            cellRangeAddress.getLastRow(), cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn());
        xlsReadContext.xlsReadSheetHolder().setCellExtra(cellExtra);
        xlsReadContext.analysisEventProcessor().extra(xlsReadContext);
    }
}
 

public void mergeCells(int firstRow, int lastRow, int firstCol, int lastCol, Object content, SpreadsheetCellStyle style) {
    setValue(firstRow, firstCol, content);
    for (int col = firstCol; col <= lastCol; col++) {
        for (int row = firstRow; row <= lastRow; row++) {
            setStyle(row, col, style);
        }
    }
    sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
}
 

@Override
public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
    Integer relativeRowIndex, Boolean isHead) {
    if (isHead) {
        return;
    }
    if (relativeRowIndex % eachRow == 0) {
        CellRangeAddress cellRangeAddress = new CellRangeAddress(row.getRowNum(), row.getRowNum() + eachRow - 1,
            columnIndex, columnIndex + columnExtend - 1);
        writeSheetHolder.getSheet().addMergedRegionUnsafe(cellRangeAddress);
    }
}
 

/**
 * セルの書式をコピーする。
 * <p>コピー先のセルの種類は、空セルとする。</p>
 * <p>結合情報も列方向の結合をコピーする。</p>
 *
 * @since 2.0
 * @param fromCell コピー元
 * @param toCell コピー先
 */
private void copyCellStyle(final Cell fromCell, final Cell toCell) {

    final CellStyle style = fromCell.getCellStyle();
    toCell.setCellStyle(style);
    toCell.setCellType(CellType.BLANK);

    // 横方向に結合されている場合、結合情報のコピーする。（XlsArrayColumns用）
    final Sheet sheet = fromCell.getSheet();
    final CellRangeAddress mergedRegion = POIUtils.getMergedRegion(sheet, fromCell.getRowIndex(), fromCell.getColumnIndex());
    final int mergedSize = POIUtils.getColumnSize(mergedRegion);

    if(POIUtils.getColumnSize(mergedRegion) >= 2) {
        CellRangeAddress newMergedRegion = POIUtils.getMergedRegion(sheet, toCell.getRowIndex(), toCell.getColumnIndex());
        if(newMergedRegion != null) {
            // 既に結合している場合 - 通常はありえない。
            return;
        }

        newMergedRegion = POIUtils.mergeCells(sheet,
                mergedRegion.getFirstColumn(), toCell.getRowIndex(), mergedRegion.getLastColumn(), toCell.getRowIndex());

        // 結合先のセルの書式も設定する
        // 中間のセルの設定
        for(int i=1; i < mergedSize; i++) {
            Cell mergedFromCell = POIUtils.getCell(sheet, toCell.getColumnIndex()+i, fromCell.getRowIndex());

            Cell mergedToCell = POIUtils.getCell(sheet, toCell.getColumnIndex()+i, toCell.getRowIndex());
            mergedToCell.setCellStyle(mergedFromCell.getCellStyle());
            mergedToCell.setCellType(CellType.BLANK);
        }

    }

}
 

private void addMergeCellsRecord(MergeCellsRecord mcr) {
	int nRegions = mcr.getNumAreas();
	for (int i = 0; i < nRegions; i++) {
		CellRangeAddress cra = mcr.getAreaAt(i);
		_mergedRegions.add(cra);
	}
}
 

@Test
public void givenCellRefString_whenAddMergeRegion_thenMergeRegionCreated() throws IOException {
    Workbook workbook = new XSSFWorkbook(fileLocation);
    Sheet sheet = workbook.getSheetAt(0);

    assertEquals(0, sheet.getNumMergedRegions());        
    sheet.addMergedRegion(CellRangeAddress.valueOf("A1:C1"));
    assertEquals(1, sheet.getNumMergedRegions());

    workbook.close();
}
 

@Override
public CellRange<HSSFCell> setArrayFormula(String formula, CellRangeAddress range) {
    // make sure the formula parses OK first
    int sheetIndex = _workbook.getSheetIndex(this);
    Ptg[] ptgs = HSSFFormulaParser.parse(formula, _workbook, FormulaType.ARRAY, sheetIndex);
    CellRange<HSSFCell> cells = getCellRange(range);

    for (HSSFCell c : cells) {
        c.setCellArrayFormula(range);
    }
    HSSFCell mainArrayFormulaCell = cells.getTopLeftCell();
    FormulaRecordAggregate agg = (FormulaRecordAggregate) mainArrayFormulaCell.getCellValueRecord();
    agg.setArrayFormula(range, ptgs);
    return cells;
}
 

public static void removeCellRange(Row row){
	Sheet sheet = row.getSheet();
	for(Cell cell : row){
		for(int i=0; i< sheet.getNumMergedRegions(); i++){
			CellRangeAddress cr = sheet.getMergedRegion(i);
			if(cr.getFirstRow()==row.getRowNum() && cr.getFirstColumn()==cell.getColumnIndex()){
				sheet.removeMergedRegion(i);
			}
		}
	}
}