Java Code Examples for org.apache.poi.ss.util.CellRangeAddress#getLastColumn()

/**
 * Also creates cells if they don't exist
 */
private CellRange<HSSFCell> getCellRange(CellRangeAddress range) {
    int firstRow = range.getFirstRow();
    int firstColumn = range.getFirstColumn();
    int lastRow = range.getLastRow();
    int lastColumn = range.getLastColumn();
    int height = lastRow - firstRow + 1;
    int width = lastColumn - firstColumn + 1;
    List<HSSFCell> temp = new ArrayList<HSSFCell>(height * width);
    for (int rowIn = firstRow; rowIn <= lastRow; rowIn++) {
        for (int colIn = firstColumn; colIn <= lastColumn; colIn++) {
            HSSFRow row = getRow(rowIn);
            if (row == null) {
                row = createRow(rowIn);
            }
            HSSFCell cell = row.getCell(colIn);
            if (cell == null) {
                cell = row.createCell(colIn);
            }
            temp.add(cell);
        }
    }
    return SSCellRange.create(firstRow, firstColumn, height, width, temp, HSSFCell.class);
}
 

private static boolean isMergedRegion(Sheet sheet, int rowNum, int columnNum) {
    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 (rowNum >= firstRow && rowNum <= lastRow) {
            if (columnNum >= firstColumn && columnNum <= lastColumn) {
                return true;
            }
        }
    }
    return false;
}
 

/**
 * 判断单元格在不在合并单元格范围内，如果是，获取其合并的列数。
 */
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;
}
 

private static CellRangeAddress shiftRange(FormulaShifter shifter, CellRangeAddress cra, int currentExternSheetIx) {
    // FormulaShifter works well in terms of Ptgs - so convert CellRangeAddress to AreaPtg (and back) here
    AreaPtg aptg = new AreaPtg(cra.getFirstRow(), cra.getLastRow(), cra.getFirstColumn(), cra.getLastColumn(), false, false, false, false);
    Ptg[] ptgs = { aptg, };

    if (!shifter.adjustFormula(ptgs, currentExternSheetIx)) {
        return cra;
    }
    Ptg ptg0 = ptgs[0];
    if (ptg0 instanceof AreaPtg) {
        AreaPtg bptg = (AreaPtg) ptg0;
        return new CellRangeAddress(bptg.getFirstRow(), bptg.getLastRow(), bptg.getFirstColumn(), bptg.getLastColumn());
    }
    if (ptg0 instanceof AreaErrPtg) {
        return null;
    }
    throw new IllegalStateException("Unexpected shifted ptg class (" + ptg0.getClass().getName() + ")");
}
 

private void fixRightBorder(HSSFCellStyle cellStyle, HSSFSheet sheet, int columnIndex, HSSFCell resultCell) {
    fixRightCell(sheet, resultCell.getRowIndex(), columnIndex + 1, cellStyle);
    // fix merged right border
    for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
        CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
        if (mergedRegion.isInRange(resultCell.getRowIndex(), resultCell.getColumnIndex())) {
            int firstRow = mergedRegion.getFirstRow();
            int lastRow = mergedRegion.getLastRow();
            int regionWidth = mergedRegion.getLastColumn() - mergedRegion.getFirstColumn() + 1;

            for (int rightIndex = firstRow; rightIndex <= lastRow; rightIndex++) {
                fixRightCell(sheet, rightIndex, columnIndex + regionWidth, cellStyle);
            }
            break;
        }
    }
}
 

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

protected boolean isUsed(int colIndex , int rowIndex){
    boolean result = false;
    Sheet sheet = excel.getSheet();
    int num = sheet.getNumMergedRegions();
    for (int i = 0; i < num; i++) {
        CellRangeAddress range = sheet.getMergedRegion(i);
        int firstRow = range.getFirstRow();
        int lastRow = range.getLastRow();
        int firstColumn = range.getFirstColumn();
        int lastColumn = range.getLastColumn();
        if (firstRow < rowIndex && lastRow >= rowIndex) {
            if(firstColumn <= colIndex && lastColumn >= colIndex){
                result = true;
            }
        }
    }
    return result;
}
 

private static Cell getMergedRegionCell(Sheet sheet, int rowNum, int columnNum) {
    int sheetMergeCount = sheet.getNumMergedRegions();
    for (int i = 0; i < sheetMergeCount; i++) {
        CellRangeAddress ca = sheet.getMergedRegion(i);
        int firstColumn = ca.getFirstColumn();
        int lastColumn = ca.getLastColumn();
        int firstRow = ca.getFirstRow();
        int lastRow = ca.getLastRow();
        if (rowNum >= firstRow && rowNum <= lastRow) {
            if (columnNum >= firstColumn && columnNum <= lastColumn) {
                Row fRow = sheet.getRow(firstRow);
                return fRow.getCell(firstColumn);
            }
        }
    }
    return null;
}
 

/**
 * 判断指定的单元格是否是合并单元格
 * @param sheet
 * @param row 行下标
 * @param column 列下标
 * @return
 */
public static boolean isMergedRegion(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;
}
 

private void fixDownBorder(HSSFCellStyle cellStyle, HSSFSheet sheet, int columnIndex, int rowIndex, HSSFCell resultCell) {
    // fix simple down border
    fixDownCell(sheet, rowIndex + 1, columnIndex, cellStyle);
    // fix merged down border
    for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
        CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
        if (mergedRegion.isInRange(resultCell.getRowIndex(), resultCell.getColumnIndex())) {
            int firstColumn = mergedRegion.getFirstColumn();
            int lastColumn = mergedRegion.getLastColumn();
            int regionHeight = mergedRegion.getLastRow() - mergedRegion.getFirstRow() + 1;

            for (int downIndex = firstColumn; downIndex <= lastColumn; downIndex++) {
                fixDownCell(sheet, rowIndex + regionHeight, downIndex, cellStyle);
            }
            break;
        }
    }
}
 

public boolean mergedRegion( Sheet sheet, int top, int left, int bottom, int right ) {
	for( int i = 0; i < sheet.getNumMergedRegions(); ++i ) {
		CellRangeAddress curRegion = sheet.getMergedRegion(i);
		if( ( curRegion.getFirstRow() == top )
				&& ( curRegion.getFirstColumn() == left )
				&& ( curRegion.getLastRow() == bottom )
				&& ( curRegion.getLastColumn() == right ) ) {
			return true;
		}
	}
	return false;
}
 

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

/**
 * @param source
 *            the sheet to copy.
 * @param destSheet
 *            the sheet to create.
 * @param srcRow
 *            the row to copy.
 * @param destRow
 *            the row to create.
 * @param styleMap
 *
 */
private static void copyRow(HSSFSheet source, XSSFSheet destSheet, HSSFRow srcRow, XSSFRow destRow, List<CellStyle> styleMap) {

	Set<CellRangeAddressWrapper> mergedRegions = new TreeSet<CellRangeAddressWrapper>();
	short dh = source.getDefaultRowHeight();
	if (srcRow.getHeight() != dh) {
		destRow.setHeight(srcRow.getHeight());
	}
	int j = srcRow.getFirstCellNum();
	if (j < 0) {
		j = 0;
	}
	for (; j <= srcRow.getLastCellNum(); j++) {
		HSSFCell oldCell = srcRow.getCell(j);
		XSSFCell newCell = destRow.getCell(j);
		if (oldCell != null) {
			if (newCell == null) {
				newCell = destRow.createCell(j);
			}
			copyCell(oldCell, newCell, styleMap);
			CellRangeAddress mergedRegion = getMergedRegion(source, srcRow.getRowNum(), (short) oldCell.getColumnIndex());

			if (mergedRegion != null) {

				CellRangeAddress newMergedRegion = new CellRangeAddress(mergedRegion.getFirstRow(), mergedRegion.getLastRow(),
						mergedRegion.getFirstColumn(), mergedRegion.getLastColumn());

				CellRangeAddressWrapper wrapper = new CellRangeAddressWrapper(newMergedRegion);
				if (isNewMergedRegion(wrapper, mergedRegions)) {
					mergedRegions.add(wrapper);
					destSheet.addMergedRegion(wrapper.range);
				}
			}
		}
	}

}
 

/**
 * 結合を考慮したセルのコメントを取得する。
 * @param cell 元となるセル。
 * @return コメント。コメントが設定されていなければ、nullを返す。
 */
private Comment getMergedCellComment(final Cell cell) {
    Comment comment = cell.getCellComment();
    if(comment != null) {
        return comment;
    }
    
    final Sheet sheet = cell.getSheet();
    final int size = sheet.getNumMergedRegions();
    
    for(int i=0; i < size; i++) {
        final CellRangeAddress range = sheet.getMergedRegion(i);
        if(!range.isInRange(cell)) {
            continue;
        }
        
        // nullでないセルを取得する。
        for(int rowIdx=range.getFirstRow(); rowIdx <= range.getLastRow(); rowIdx++) {
            final Row row = sheet.getRow(rowIdx);
            if(row == null) {
                continue;
            }

            for(int colIdx=range.getFirstColumn(); colIdx <= range.getLastColumn(); colIdx++) {
                final Cell valueCell = row.getCell(colIdx);
                if(valueCell == null) {
                    continue;
                }

                comment = valueCell.getCellComment();
                if(comment != null) {
                    return comment;
                }
            }
        }
    }
    
    return null;
    
}
 

/**
 * <p>
 * Process a CellImage from the images list and place the image on the sheet.
 * </p><p>
 * This involves changing the row height as necesssary and determining the column spread of the image.
 * </p>
 * @param cellImage
 * The image to be placed on the sheet.
 */
private void processCellImage( HandlerState state, Drawing drawing, CellImage cellImage ) {
	Coordinate location = cellImage.location;
	
	Cell cell = state.currentSheet.getRow( location.getRow() ).getCell( location.getCol() );

	IImageContent image = cellImage.image;		
	
	StyleManagerUtils smu = state.getSmu();
	float ptHeight = cell.getRow().getHeightInPoints();
	if( image.getHeight() != null ) {
		ptHeight = smu.fontSizeInPoints( image.getHeight().toString() );
	}

	// Get image width
	int endCol = cell.getColumnIndex();
       double lastColWidth = ClientAnchorConversions.widthUnits2Millimetres( (short)state.currentSheet.getColumnWidth( endCol ) )
       		+ 2.0;
       int dx = smu.anchorDxFromMM( lastColWidth, lastColWidth );
       double mmWidth = 0.0;
       if( smu.isAbsolute(image.getWidth())) {
           mmWidth = image.getWidth().convertTo(DimensionType.UNITS_MM);
       } else if(smu.isPixels(image.getWidth())) {
           mmWidth = ClientAnchorConversions.pixels2Millimetres( image.getWidth().getMeasure() );
       }
	// Allow image to span multiple columns
	CellRangeAddress mergedRegion = getMergedRegionBegunBy( state.currentSheet, location.getRow(), location.getCol() );
	if( (cellImage.spanColumns) || ( mergedRegion != null ) ) {
        log.debug( "Image size: ", image.getWidth(), " translates as mmWidth = ", mmWidth );
        if( mmWidth > 0) {
            double mmAccumulatedWidth = 0;
            int endColLimit = cellImage.spanColumns ? 256 : mergedRegion.getLastColumn();
            for( endCol = cell.getColumnIndex(); mmAccumulatedWidth < mmWidth && endCol < endColLimit; ++ endCol ) {
                lastColWidth = ClientAnchorConversions.widthUnits2Millimetres( (short)state.currentSheet.getColumnWidth( endCol ) )
                		+ 2.0;
                mmAccumulatedWidth += lastColWidth;
                log.debug( "lastColWidth = ", lastColWidth, "; mmAccumulatedWidth = ", mmAccumulatedWidth);
            }
            if( mmAccumulatedWidth > mmWidth ) {
                mmAccumulatedWidth -= lastColWidth;
                --endCol;
                double mmShort = mmWidth - mmAccumulatedWidth;
                dx = smu.anchorDxFromMM( mmShort, lastColWidth );
            }
        }
	} else {
		float widthRatio = (float)(mmWidth / lastColWidth);
		ptHeight = ptHeight / widthRatio;
	}

	int rowsSpanned = state.findRowsSpanned( cell.getRowIndex(), cell.getColumnIndex() );
	float neededRowHeightPoints = ptHeight;
	
	for( int i = 0; i < rowsSpanned; ++i ) {
		int rowIndex = cell.getRowIndex() + 1 + i;
		neededRowHeightPoints -= state.currentSheet.getRow(rowIndex).getHeightInPoints();
	}
	
	if( neededRowHeightPoints > cell.getRow().getHeightInPoints()) {
		cell.getRow().setHeightInPoints( neededRowHeightPoints );
	}
	
	// ClientAnchor anchor = wb.getCreationHelper().createClientAnchor();
	ClientAnchor anchor = state.getWb().getCreationHelper().createClientAnchor();
       anchor.setCol1(cell.getColumnIndex());
       anchor.setRow1(cell.getRowIndex());
       anchor.setCol2(endCol);
       anchor.setRow2(cell.getRowIndex() + rowsSpanned);
       anchor.setDx2(dx);
       anchor.setDy2( smu.anchorDyFromPoints( ptHeight, cell.getRow().getHeightInPoints() ) );
       anchor.setAnchorType(ClientAnchor.MOVE_DONT_RESIZE);
    drawing.createPicture(anchor, cellImage.imageIdx);
}
 

private void loadMapColumns(final Sheet sheet, final List<RecordHeader> headers, final List<MergedRecord> mergedRecords,
        final CellPosition beginPosition, final Class<?> recordClass, final Object record, final Configuration config, final LoadingWorkObject work) throws XlsMapperException {

    final List<FieldAccessor> properties = FieldAccessorUtils.getPropertiesWithAnnotation(
            recordClass, work.getAnnoReader(), XlsMapColumns.class)
            .stream()
            .filter(p -> p.isReadable())
            .collect(Collectors.toList());

    for(FieldAccessor property : properties) {
        final XlsMapColumns mapAnno = property.getAnnotationNullable(XlsMapColumns.class);

        if(!Utils.isLoadCase(mapAnno.cases())) {
            continue;
        }

        Class<?> valueClass = mapAnno.valueClass();
        if(valueClass == Object.class) {
            valueClass = property.getComponentType();
        }

        // get converter (map key class)
        final CellConverter<?> converter = getCellConverter(valueClass, property, config);
        if(converter instanceof FieldFormatter) {
            work.getErrors().registerFieldFormatter(property.getName(), valueClass, (FieldFormatter<?>)converter, true);
        }

        boolean foundPreviousColumn = false;
        final Map<String, Object> map = new LinkedHashMap<>();
        for(RecordHeader headerInfo : headers){
            int hRow = beginPosition.getRow() + headerInfo.getInterval();
            if(Utils.matches(headerInfo.getLabel(), mapAnno.previousColumnName(), config)){
                foundPreviousColumn = true;
                hRow++;
                continue;
            }

            if(Utils.isNotEmpty(mapAnno.nextColumnName()) && Utils.matches(headerInfo.getLabel(), mapAnno.nextColumnName(), config)) {
                break;
            }

            if(foundPreviousColumn){
                final Cell cell = POIUtils.getCell(sheet, beginPosition.getColumn(), hRow);
                property.setMapPosition(record, CellPosition.of(cell), headerInfo.getLabel());
                property.setMapLabel(record, headerInfo.getLabel(), headerInfo.getLabel());

                property.getMapCommentSetter().ifPresent(setter -> 
                config.getCommentOperator().loadMapCellComment(setter, cell, record, headerInfo.getLabel(), property, config));
                
                CellRangeAddress mergedRange = POIUtils.getMergedRegion(sheet, cell.getRowIndex(), cell.getColumnIndex());
                if(mergedRange != null) {
                    int mergedSize =  mergedRange.getLastColumn() - mergedRange.getFirstColumn() + 1;
                    mergedRecords.add(new MergedRecord(headerInfo, mergedRange, mergedSize));
                } else {
                    mergedRecords.add(new MergedRecord(headerInfo, CellRangeAddress.valueOf(POIUtils.formatCellAddress(cell)), 1));
                }

                try {
                    final Object value = converter.toObject(cell);
                    map.put(headerInfo.getLabel(), value);
                } catch(TypeBindException e) {
                    e.setBindClass(valueClass);  // マップの項目のタイプに変更
                    work.addTypeBindError(e, cell, String.format("%s[%s]", property.getName(), headerInfo.getLabel()), headerInfo.getLabel());
                    if(!config.isContinueTypeBindFailure()) {
                        throw e;
                    }
                }
            }
        }

        if(foundPreviousColumn) {
            property.setValue(record, map);
        }
    }
}
 

public static void copyRow(Sheet worksheet, Row newRow, Row sourceRow) {
Workbook workbook = worksheet.getWorkbook();
      for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
          Cell oldCell = sourceRow.getCell(i);
          Cell newCell = newRow.createCell(i);

          if (oldCell == null) {
              newCell = null;
              continue;
          }

          CellStyle newCellStyle = workbook.createCellStyle();
          newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
          newCell.setCellStyle(newCellStyle);

          if (oldCell.getCellComment() != null) {
              newCell.setCellComment(oldCell.getCellComment());
          }

          if (oldCell.getHyperlink() != null) {
              newCell.setHyperlink(oldCell.getHyperlink());
          }

          newCell.setCellType(oldCell.getCellType());

          switch (oldCell.getCellType()) {
              case Cell.CELL_TYPE_BLANK:
                  newCell.setCellValue(oldCell.getStringCellValue());
                  break;
              case Cell.CELL_TYPE_BOOLEAN:
                  newCell.setCellValue(oldCell.getBooleanCellValue());
                  break;
              case Cell.CELL_TYPE_ERROR:
                  newCell.setCellErrorValue(oldCell.getErrorCellValue());
                  break;
              case Cell.CELL_TYPE_FORMULA:
                  newCell.setCellFormula(oldCell.getCellFormula());
                  break;
              case Cell.CELL_TYPE_NUMERIC:
                  newCell.setCellValue(oldCell.getNumericCellValue());
                  break;
              case Cell.CELL_TYPE_STRING:
                  newCell.setCellValue(oldCell.getRichStringCellValue());
                  break;
          }
      }

      for (int i = 0; i < worksheet.getNumMergedRegions(); i++) {
          CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i);
          if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) {
              CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(),
                      (newRow.getRowNum() +
                              (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow()
                                      )),
                      cellRangeAddress.getFirstColumn(),
                      cellRangeAddress.getLastColumn());
              worksheet.addMergedRegion(newCellRangeAddress);
          }
      }
  }
 

/**
 * @param range either the range of the entire block to be read, or just the
 *              top row of the cells, in which case the method will stop when
 *              the first empty cell is reached in the first column
 * @param columnTypes An array of data types expected at each column.
 *                 If this array is shorter than the number of column, then the last
 *                 data type is used until the end. So if only one value is given,
 *                 then that is used for the entire block.
 */
public Object[][] readBlock(final String range, final Class<?>... columnTypes) {

    if (columnTypes == null || columnTypes.length == 0) {
        throw new ExcelException("columnTypes cannot be null / empty");
    }

    final CellRangeAddress cra = CellRangeAddress.valueOf(range);
    final AreaReference ar = new AreaReference(range, null);
    final Sheet sheet = workbook.getSheet(ar.getFirstCell().getSheetName());

    final int firstColumn = cra.getFirstColumn();
    final int firstRow = cra.getFirstRow();
    final int lastRow = cra.getLastRow();
    final int height = lastRow - firstRow + 1;
    final int width = cra.getLastColumn() - firstColumn + 1;

    List<Object> result;
    if (height == 1) {
        result = new LinkedList<>();
    } else {
        result = new ArrayList<>(height);
    }

    for (int rowNum = 0; moreDataToRead(sheet, firstColumn, firstRow, lastRow, rowNum); rowNum++) {
        final Row row = sheet.getRow(firstRow + rowNum);
        final Object[] resultRow = new Object[width];
        result.add(resultRow);
        for (int colNum = 0; colNum < width; colNum++) {

            Class<?> colType;
            if (colNum < columnTypes.length - 1) {
                colType = columnTypes[colNum];
            } else {
                colType = columnTypes[columnTypes.length - 1];
            }

            final Cell cell = row.getCell(firstColumn + colNum);
            resultRow[colNum] = readCell(cell, colType);
        }

    }

    return result.toArray(new Object[][] {});
}
 

public static void copyMergedRegion(final HSSFSheet sheet, final List<CellRangeAddress> regionList, final int rowNum) {
    for (final CellRangeAddress region : regionList) {
        final CellRangeAddress address = new CellRangeAddress(rowNum, rowNum, region.getFirstColumn(), region.getLastColumn());
        sheet.addMergedRegion(address);
    }
}
 

public void setArrayFormula(CellRangeAddress r, Ptg[] ptgs) {

		ArrayRecord arr = new ArrayRecord(Formula.create(ptgs), new CellRangeAddress8Bit(r.getFirstRow(), r.getLastRow(), r.getFirstColumn(), r.getLastColumn()));
		_sharedValueManager.addArrayRecord(arr);
	}