合并指定sheet指定区域的单元格

@param sheetName sheet名字
@param firstRow  要合并的第一行
@param lastRow   要合并的最后一行
@param firstCol  要合并的第一列
@param lastCol   要合并的最后一列
173913
public void mergedRegion(String sheetName, int firstRow, int lastRow, int firstCol,
                             int lastCol) {
        mergedRegion(workbook.getSheet(sheetName), firstRow, lastRow, firstCol, lastCol);
    }
929517
private Cell findTemplateCell(ExcelRows excelRowsAnn) {
        val fromRef = excelRowsAnn.fromRef();
        if (PoiUtil.isFullCellReference(fromRef)) {
            val cellRef = new CellReference(fromRef);
            return sheet.getRow(cellRef.getRow()).getCell(cellRef.getCol());
        }

        val cellRef = new CellReference(fromRef + "1");
        val col = cellRef.getCol();
        val searchKey = excelRowsAnn.searchKey();
        for (int i = cellRef.getRow(), ii = sheet.getLastRowNum(); i <= ii; ++i) {
            val row = sheet.getRow(i);
            if (row == null) continue;
            val cell = row.getCell(col);
            if (cell == null) continue;

            val cellValue = PoiUtil.getCellStringValue(cell);
            if (StringUtils.contains(cellValue, searchKey)) return cell;
        }

        return null;
    }
221942
public static Cell getCell(final Sheet sheet, final int column, final int row) {
        ArgUtils.notNull(sheet, "sheet");

        Row rows = sheet.getRow(row);
        if(rows == null) {
            rows = sheet.createRow(row);
        }

        Cell cell = rows.getCell(column);
        if(cell == null) {
            cell = rows.createCell(column, CellType.BLANK);
        }

        return cell;
    }
956387
public static void removeOtherSheets(Sheet sheet) {
        val wb = sheet.getWorkbook();

        List<String> deletedNames = Lists.newArrayList();
        for (int i = 0; i < wb.getNumberOfSheets(); ++i) {
            val sheetName = wb.getSheetName(i);
            if (!sheetName.equals(sheet.getSheetName())) {
                deletedNames.add(sheetName);
            }
        }

        deletedNames.forEach(x -> wb.removeSheetAt(wb.getSheetIndex(x)));
    }
633275
private void parse(Workbook ws) throws ParseException {
    LOG.info("Parsing XLS file...");
    Sheet sheet = ws.getSheet(SHEET_NAME);
    if (sheet == null) {
      sheet = ws.getSheetAt(0);
    }
    if (sheet == null) {
      throw new ParseException("No sheet found");
    }

    int firstRow = sheet.getFirstRowNum();
    int lastRow = sheet.getLastRowNum();
    LOG.info("first row is:" + firstRow + ", last row is:" + lastRow);

    // first row is expected to be the header.
    analyzeHeader(sheet.getRow(firstRow));

    for (int i = firstRow + 1; i <= lastRow; i++) {
      Row r = sheet.getRow(i);
      extractRowData(r, cellMap);
    }
    LOG.info("Parsing of XLS file finished.");
  }
1190950
@Nonnull
  public Sheet createNewSheet (@Nullable final String sName)
  {
    m_aLastSheet = sName == null ? m_aWB.createSheet () : m_aWB.createSheet (WorkbookUtil.createSafeSheetName (sName));
    m_nLastSheetRowIndex = 0;
    m_aLastRow = null;
    m_nLastRowCellIndex = 0;
    m_aLastCell = null;
    m_nMaxCellIndex = 0;
    return m_aLastSheet;
  }
258446
public static int findMaxCol(Sheet sheet) {
        int maxCol = 0;
        for (int i = 0, ii = sheet.getLastRowNum(); i <= ii; ++i) {
            val row = sheet.getRow(i);
            if (row == null) continue;

            for (int j = row.getLastCellNum() - 1; j > maxCol; --j) {
                val cell = row.getCell(j);
                if (cell == null) continue;

                val value = getCellStringValue(cell);
                if (StringUtils.isNotEmpty(value)) {
                    maxCol = j;
                    break;
                }
            }
        }

        return maxCol;
    }
70041
public XceliteSheet getSheet(String sheetName) {
    Sheet sheet = workbook.getSheet(sheetName);
    if (sheet == null) {
      throw new XceliteException(String.format("Could not find sheet named \"%s\"", sheetName));
    }
    return new XceliteSheetImpl(sheet, file);
  }
1298093
private static void firstFirstLastLast(Statement st,
                                           TableLocation tableName,
                                           String pkCol,
                                           String geomCol,
                                           double tolerance) throws SQLException {
        LOGGER.info("Selecting the first coordinate of the first geometry and " +
                "the last coordinate of the last geometry...");
        final String numGeoms = "ST_NumGeometries(" + geomCol + ")";
        final String firstGeom = "ST_GeometryN(" + geomCol + ", 1)";
        final String firstPointFirstGeom = "ST_PointN(" + firstGeom + ", 1)";
        final String lastGeom = "ST_GeometryN(" + geomCol + ", " + numGeoms + ")";
        final String lastPointLastGeom = "ST_PointN(" + lastGeom + ", ST_NumPoints(" + lastGeom + "))";
        st.execute("drop TABLE if exists "+ COORDS_TABLE);
        if (tolerance > 0) {
            st.execute("CREATE TABLE "+ COORDS_TABLE+" AS "
                    + "SELECT " + pkCol + " EDGE_ID, "
                    + firstPointFirstGeom + " START_POINT, "
                    + expand(firstPointFirstGeom, tolerance) + " START_POINT_EXP, "
                    + lastPointLastGeom + " END_POINT, "
                    + expand(lastPointLastGeom, tolerance) + " END_POINT_EXP "
                    + "FROM " + tableName);
        } else {
            // If the tolerance is zero, there is no need to call ST_Expand.
            st.execute("CREATE  TABLE "+ COORDS_TABLE+" AS "
                    + "SELECT " + pkCol + " EDGE_ID, "
                    + firstPointFirstGeom + " START_POINT, "
                    + lastPointLastGeom + " END_POINT "
                    + "FROM " + tableName);
        }
    }
114878
protected void formatCell (HTMLTable.CellFormatter formatter, int row, int col, int limit)
    {
        formatter.setHorizontalAlignment(row, col, _cellHorizAlign);
        formatter.setVerticalAlignment(row, col, _cellVertAlign);
        formatter.setStyleName(row, col, "Cell");
        if (row == (limit-1)/_cols) {
            formatter.addStyleName(row, col, "BottomCell");
        } else if (row == 0) {
            formatter.addStyleName(row, col, "TopCell");
        } else {
            formatter.addStyleName(row, col, "MiddleCell");
        }
    }
206351
public static Row insertRow(final Sheet sheet, final int rowIndex) {

        ArgUtils.notNull(sheet, "cell");
        ArgUtils.notMin(rowIndex, 0, "rowIndex");

        // 最終行を取得する
        int lastRow = sheet.getLastRowNum();
        if(lastRow < rowIndex) {
            // データが定義されている範囲害の場合は、行を新たに作成して返す。
            return sheet.createRow(rowIndex);
        }

        sheet.shiftRows(rowIndex, lastRow+1, 1);
        return sheet.createRow(rowIndex);
    }
340975
private <P> SheetBindingErrors<P> loadSheet(final Sheet sheet, final Class<P> clazz, final AnnotationReader annoReader)
            throws XlsMapperException {

        // 値の読み込み対象のJavaBeanオブジェクトの作成
        final P beanObj = configuration.createBean(clazz);

        final SheetBindingErrors<P> errors =  configuration.getBindingErrorsFactory().create(beanObj);
        errors.setSheetName(sheet.getSheetName());
        errors.setSheetIndex(sheet.getWorkbook().getSheetIndex(sheet));

        final LoadingWorkObject work = new LoadingWorkObject();
        work.setAnnoReader(annoReader);
        work.setErrors(errors);

        // セルのキャッシュ情報の初期化
        configuration.getCellFormatter().init(configuration.isCacheCellValueOnLoad());

        final FieldAccessorFactory adpterFactory = new FieldAccessorFactory(annoReader);

        // リスナークラスの@PreLoad用メソッドの実行
        final XlsListener listenerAnno = annoReader.getAnnotation(beanObj.getClass(), XlsListener.class);
        if(listenerAnno != null) {
            for(Class<?> listenerClass : listenerAnno.value()) {
                final Object listenerObj = configuration.createBean(listenerClass);

                for(Method method : listenerObj.getClass().getMethods()) {
                    if(annoReader.hasAnnotation(method, XlsPreLoad.class)) {
                        Utils.invokeNeedProcessMethod(listenerObj, method, beanObj, sheet, configuration, work.getErrors(), ProcessCase.Load);
                    }
                }
            }

        }

        // @PreLoad用のメソッドの実行
        for(Method method : clazz.getMethods()) {

            if(annoReader.hasAnnotation(method, XlsPreLoad.class)) {
                Utils.invokeNeedProcessMethod(beanObj, method, beanObj, sheet, configuration, work.getErrors(), ProcessCase.Load);
            }
        }

        final List<FieldAccessorProxy> accessorProxies = new ArrayList<>();

        // public メソッドの処理
        for(Method method : clazz.getMethods()) {
            method.setAccessible(true);
            for(Annotation anno : annoReader.getAnnotations(method)) {
                final XlsFieldProcessor annoFieldProcessor = anno.annotationType().getAnnotation(XlsFieldProcessor.class);
                if(ClassUtils.isAccessorMethod(method) && annoFieldProcessor != null) {
                    // 登録済みのFieldProcessorの取得
                    FieldProcessor<?> processor = configuration.getFieldProcessorRegistry().getProcessor(anno.annotationType());

                    // アノテーションに指定されているFieldProcessorの場合
                    if(processor == null && annoFieldProcessor.value().length > 0) {
                        processor = configuration.createBean(annoFieldProcessor.value()[0]);

                    }

                    if(processor != null) {
                        final FieldAccessor accessor = adpterFactory.create(method);
                        final FieldAccessorProxy accessorProxy = new FieldAccessorProxy(anno, processor, accessor);
                        if(!accessorProxies.contains(accessorProxy)) {
                            accessorProxies.add(accessorProxy);
                        }

                    } else {
                        // FieldProcessorが見つからない場合
                        throw new AnnotationInvalidException(anno, MessageBuilder.create("anno.XlsFieldProcessor.notResolve")
                                .varWithAnno("anno", anno.annotationType())
                                .format());
                    }

                }

                if(anno instanceof XlsPostLoad) {
                    work.addNeedPostProcess(new NeedProcess(beanObj, beanObj, method));
                }
            }

        }

        // フィールドの処理
        for(Field field : clazz.getDeclaredFields()) {

            field.setAccessible(true);
            for(Annotation anno : annoReader.getAnnotations(field)) {

                final XlsFieldProcessor annoFieldProcessor = anno.annotationType().getAnnotation(XlsFieldProcessor.class);
                if(annoFieldProcessor != null) {

                    // 登録済みのFieldProcessorの取得
                    FieldProcessor<?> processor = configuration.getFieldProcessorRegistry().getProcessor(anno.annotationType());

                    // アノテーションに指定されているFieldProcessorの場合
                    if(processor == null && annoFieldProcessor.value().length > 0) {
                        processor = configuration.createBean(annoFieldProcessor.value()[0]);

                    }

                    if(processor != null) {
                        final FieldAccessor accessor = adpterFactory.create(field);
                        final FieldAccessorProxy accessorProxy = new FieldAccessorProxy(anno, processor, accessor);
                        if(!accessorProxies.contains(accessorProxy)) {
                            accessorProxies.add(accessorProxy);
                        }

                    } else {
                        // FieldProcessorが見つからない場合
                        throw new AnnotationInvalidException(anno, MessageBuilder.create("anno.XlsFieldProcessor.notResolve")
                                .varWithAnno("anno", anno.annotationType())
                                .format());
                    }

                }


            }
        }

        // 順番を並び替えて保存処理を実行する
        Collections.sort(accessorProxies, new FieldAccessorProxyComparator());
        for(FieldAccessorProxy accessorProxy : accessorProxies) {
            accessorProxy.loadProcess(sheet, beanObj, configuration, work);
        }

        // リスナークラスの@PostLoadの取得
        if(listenerAnno != null) {
            for(Class<?> listenerClass : listenerAnno.value()) {
                Object listenerObj = configuration.createBean(listenerClass);
                for(Method method : listenerObj.getClass().getMethods()) {
                    if(annoReader.hasAnnotation(method, XlsPostLoad.class)) {
                        work.addNeedPostProcess(new NeedProcess(beanObj, listenerObj, method));
                    }
                }
            }

        }

        //@PostLoadが付与されているメソッドの実行
        for(NeedProcess need : work.getNeedPostProcesses()) {
            Utils.invokeNeedProcessMethod(need.getProcess(), need.getMethod(), need.getTarget(), sheet, configuration, work.getErrors(), ProcessCase.Load);
        }

        // セルのキャッシュ情報の初期化
        configuration.getCellFormatter().init(configuration.isCacheCellValueOnLoad());

        return errors;
    }
598849
private void processLastCell(LastCellOfRowDummyRecord lastCell) {
		// 每行结束时， 调用handle() 方法
		this.rowHandler.handle(curSheetIndex, lastCell.getRow(), this.rowCellList);
		// 清空行Cache
		this.rowCellList.clear();
	}
221908
public static int getColumns(final Sheet sheet) {
        ArgUtils.notNull(sheet, "sheet");

        int minRowIndex = sheet.getFirstRowNum();
        int maxRowIndex = sheet.getLastRowNum();
        int maxColumnsIndex = 0;
        for(int i = minRowIndex; i <= maxRowIndex; i++) {
            final Row row = sheet.getRow(i);
            if(row == null) {
                continue;
            }

            final int column = row.getLastCellNum();
            if(column > maxColumnsIndex) {
                maxColumnsIndex = column;
            }
        }

        return maxColumnsIndex;
    }
194958
public static Cell[] getRow(final Sheet sheet, final int row) {
        ArgUtils.notNull(sheet, "sheet");

        Row rows = sheet.getRow(row);
        if(rows == null) {
            rows = sheet.createRow(row);
        }
        int maxColumn = getColumns(sheet);
        Cell[] cells = new Cell[maxColumn];
        for(int i=0; i < maxColumn; i++) {
            Cell cell = rows.getCell(i);
            if(cell == null) {
                cell = rows.createCell(i, CellType.BLANK);
            }
            cells[i] = cell;
        }

        return cells;
    }
1019877
public void newCell(final int row,
            final int column,
            final String value,
            int mergedColStart) {
        if ( isCellValueEmpty( value ) ) {
            return;
        }
        if ( _isInRuleTable && row == this._ruleStartRow ) {
            return;
        }
        if ( this._isInRuleTable ) {
            processRuleCell( row, column, value, mergedColStart );
        } else {
            processNonRuleCell( row, column, value );
        }
    }
204053
public Optional<SheetBindingErrors<P>> getBySheetIndex(final int sheetIndex) {
        return list.stream()
                .filter(b -> b.getSheetIndex() == sheetIndex)
                .findFirst();
    }