util.java

一个java生成自动生成Excel

        while( sheet.getRow(i).getCell( cellNum ) == null && i >= startRow ){
            i--;
        }
        for(int j = 0; j < shiftNumber && i>=startRow; j++, i--){
            if( sheet.getRow(i).getCell(cellNum) != null ){
                sheet.getRow(i).removeCell( sheet.getRow(i).getCell(cellNum) );
            }
        }
    }

    private static void moveCell(HSSFCell srcCell, HSSFCell destCell) {
        destCell.setCellStyle(srcCell.getCellStyle());
        switch (srcCell.getCellType()) {
            case HSSFCell.CELL_TYPE_STRING:
                destCell.setCellValue(srcCell.getStringCellValue());
                break;
            case HSSFCell.CELL_TYPE_NUMERIC:
                destCell.setCellValue(srcCell.getNumericCellValue());
                break;
            case HSSFCell.CELL_TYPE_BLANK:
                destCell.setCellType(HSSFCell.CELL_TYPE_BLANK);
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN:
                destCell.setCellValue(srcCell.getBooleanCellValue());
                break;
            case HSSFCell.CELL_TYPE_ERROR:
                destCell.setCellErrorValue(srcCell.getErrorCellValue());
                break;
            case HSSFCell.CELL_TYPE_FORMULA:
                break;
            default:
                break;
        }
        srcCell.setCellType( HSSFCell.CELL_TYPE_BLANK );
    }

    private static void duplicateStyle(RowCollection rowCollection, int rowToCopy, int startRow, int num) {
        HSSFSheet sheet = rowCollection.getParentRow().getSheet().getHssfSheet();
        Set mergedRegions = new TreeSet();
        HSSFRow srcRow = sheet.getRow(rowToCopy);
        for (int i = startRow; i < startRow + num; i++) {
            HSSFRow destRow = sheet.getRow(i);
            if (destRow == null) {
                destRow = sheet.createRow(i);
            }
            destRow.setHeight(srcRow.getHeight());
            for (int j = 0; j < rowCollection.getCells().size(); j++) {
                Cell cell = (Cell) rowCollection.getCells().get(j);
                HSSFCell hssfCell = cell.getHssfCell();
                if (hssfCell != null) {
                    HSSFCell newCell = destRow.createCell( hssfCell.getCellNum() );
                    copyCell(hssfCell, newCell, true);
                    Region mergedRegion = getMergedRegion( sheet, rowToCopy, hssfCell.getCellNum() );
                    if( mergedRegion != null ){
                        Region newMergedRegion = new Region( i, mergedRegion.getColumnFrom(),
                                i + mergedRegion.getRowTo() - mergedRegion.getRowFrom(), mergedRegion.getColumnTo() );
                        if( isNewMergedRegion( newMergedRegion, mergedRegions ) ){
                            mergedRegions.add( newMergedRegion );
                            sheet.addMergedRegion( newMergedRegion );
                        }
                    }
                }
            }
        }
    }

    public static void copyRow( HSSFSheet sheet, HSSFRow oldRow, HSSFRow newRow ){
        Set mergedRegions = new TreeSet();
        newRow.setHeight( oldRow.getHeight() );
        for( short j = oldRow.getFirstCellNum(); j <= oldRow.getLastCellNum(); j++){
            HSSFCell oldCell = oldRow.getCell( j );
            HSSFCell newCell = newRow.getCell( j );
            if( oldCell != null ){
                if( newCell == null ){
                    newCell = newRow.createCell( j );
                }
                copyCell( oldCell, newCell, true );
                Region mergedRegion = getMergedRegion( sheet, oldRow.getRowNum(), oldCell.getCellNum() );
                if( mergedRegion != null ){
                    Region newMergedRegion = new Region( newRow.getRowNum(), mergedRegion.getColumnFrom(),
                            newRow.getRowNum() + mergedRegion.getRowTo() - mergedRegion.getRowFrom(), mergedRegion.getColumnTo() );
                    if( isNewMergedRegion( newMergedRegion, mergedRegions ) ){
                        mergedRegions.add( newMergedRegion );
                        sheet.addMergedRegion( newMergedRegion );
                    }
                }
            }
        }
    }

    public static void copyRow( HSSFSheet srcSheet, HSSFSheet destSheet, HSSFRow srcRow, HSSFRow destRow ){
        Set mergedRegions = new TreeSet();
        destRow.setHeight( srcRow.getHeight() );
        for( short j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++){
            HSSFCell oldCell = srcRow.getCell( j );
            HSSFCell newCell = destRow.getCell( j );
            if( oldCell != null ){
                if( newCell == null ){
                    newCell = destRow.createCell( j );
                }
                copyCell( oldCell, newCell, true );
                Region mergedRegion = getMergedRegion( srcSheet, srcRow.getRowNum(), oldCell.getCellNum() );
                if( mergedRegion != null ){
//                    Region newMergedRegion = new Region( destRow.getRowNum(), mergedRegion.getColumnFrom(),
//                            destRow.getRowNum() + mergedRegion.getRowTo() - mergedRegion.getRowFrom(), mergedRegion.getColumnTo() );
                    Region newMergedRegion = new Region( mergedRegion.getRowFrom(), mergedRegion.getColumnFrom(),
                            mergedRegion.getRowTo(), mergedRegion.getColumnTo() );
                    if( isNewMergedRegion( newMergedRegion, mergedRegions ) ){
                        mergedRegions.add( newMergedRegion );
                        destSheet.addMergedRegion( newMergedRegion );
                    }
                }
            }
        }
    }

    public static void copyRow( HSSFSheet srcSheet, HSSFSheet destSheet, HSSFRow srcRow, HSSFRow destRow, String expressionToReplace, String expressionReplacement ){
        Set mergedRegions = new TreeSet();
        destRow.setHeight( srcRow.getHeight() );
        for( short j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++){
            HSSFCell oldCell = srcRow.getCell( j );
            HSSFCell newCell = destRow.getCell( j );
            if( oldCell != null ){
                if( newCell == null ){
                    newCell = destRow.createCell( j );
                }
                copyCell( oldCell, newCell, true, expressionToReplace, expressionReplacement );
                Region mergedRegion = getMergedRegion( srcSheet, srcRow.getRowNum(), oldCell.getCellNum() );
                if( mergedRegion != null ){
//                    Region newMergedRegion = new Region( destRow.getRowNum(), mergedRegion.getColumnFrom(),
//                            destRow.getRowNum() + mergedRegion.getRowTo() - mergedRegion.getRowFrom(), mergedRegion.getColumnTo() );
                    Region newMergedRegion = new Region( mergedRegion.getRowFrom(), mergedRegion.getColumnFrom(),
                            mergedRegion.getRowTo(), mergedRegion.getColumnTo() );
                    if( isNewMergedRegion( newMergedRegion, mergedRegions ) ){
                        mergedRegions.add( newMergedRegion );
                        destSheet.addMergedRegion( newMergedRegion );
                    }
                }
            }
        }
    }

    public static void copySheets(HSSFSheet newSheet, HSSFSheet sheet) {
        int maxColumnNum = 0;
        for(int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++){
            HSSFRow srcRow = sheet.getRow( i );
            HSSFRow destRow = newSheet.createRow( i );
            if( srcRow != null ){
                Util.copyRow( sheet, newSheet, srcRow, destRow);
                if( srcRow.getLastCellNum() > maxColumnNum ){
                    maxColumnNum = srcRow.getLastCellNum();
                }
            }
        }
        for(short i = 0; i <= maxColumnNum; i++){
            newSheet.setColumnWidth( i, sheet.getColumnWidth( i ) );
        }
    }

    public static void copySheets(HSSFSheet newSheet, HSSFSheet sheet, String expressionToReplace, String expressionReplacement) {
        int maxColumnNum = 0;
        for(int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++){
            HSSFRow srcRow = sheet.getRow( i );
            HSSFRow destRow = newSheet.createRow( i );
            if( srcRow != null ){
                Util.copyRow( sheet, newSheet, srcRow, destRow, expressionToReplace, expressionReplacement);
                if( srcRow.getLastCellNum() > maxColumnNum ){
                    maxColumnNum = srcRow.getLastCellNum();
                }
            }
        }
        for(short i = 0; i <= maxColumnNum; i++){
            newSheet.setColumnWidth( i, sheet.getColumnWidth( i ) );
        }
    }

    public static void copyCell(HSSFCell oldCell, HSSFCell newCell, boolean copyStyle) {
        if( copyStyle ){
            newCell.setCellStyle(oldCell.getCellStyle());
        }
        newCell.setEncoding( oldCell.getEncoding() );
        switch (oldCell.getCellType()) {
            case HSSFCell.CELL_TYPE_STRING:
                newCell.setCellValue(oldCell.getStringCellValue());
                break;
            case HSSFCell.CELL_TYPE_NUMERIC:
                newCell.setCellValue(oldCell.getNumericCellValue());
                break;
            case HSSFCell.CELL_TYPE_BLANK:
                newCell.setCellType(HSSFCell.CELL_TYPE_BLANK);
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN:
                newCell.setCellValue(oldCell.getBooleanCellValue());
                break;
            case HSSFCell.CELL_TYPE_ERROR:
                newCell.setCellErrorValue(oldCell.getErrorCellValue());
                break;
            case HSSFCell.CELL_TYPE_FORMULA:
                newCell.setCellFormula( oldCell.getCellFormula() );
                break;
            default:
                break;
        }
    }

    public static void copyCell(HSSFCell oldCell, HSSFCell newCell, boolean copyStyle, String expressionToReplace, String expressionReplacement) {
        if( copyStyle ){
            newCell.setCellStyle(oldCell.getCellStyle());
        }
        newCell.setEncoding( oldCell.getEncoding() );
        switch (oldCell.getCellType()) {
            case HSSFCell.CELL_TYPE_STRING:
                String oldValue = oldCell.getStringCellValue();
                newCell.setCellValue(oldValue!=null?oldValue.replaceAll(expressionToReplace, expressionReplacement):null);
                break;
            case HSSFCell.CELL_TYPE_NUMERIC:
                newCell.setCellValue(oldCell.getNumericCellValue());
                break;
            case HSSFCell.CELL_TYPE_BLANK:
                newCell.setCellType(HSSFCell.CELL_TYPE_BLANK);
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN:
                newCell.setCellValue(oldCell.getBooleanCellValue());
                break;
            case HSSFCell.CELL_TYPE_ERROR:
                newCell.setCellErrorValue(oldCell.getErrorCellValue());
                break;
            case HSSFCell.CELL_TYPE_FORMULA:
                newCell.setCellFormula( oldCell.getCellFormula() );
                break;
            default:
                break;
        }
    }


    public static Object getProperty(Object bean, String propertyName) {
        Object value = null;
        try {
            if( log.isDebugEnabled() ){
                log.debug("getting property=" + propertyName + " for bean=" + bean.getClass().getName());
            }
            value = PropertyUtils.getProperty(bean, propertyName);
        } catch (IllegalAccessException e) {
            log.warn("Can't get property " + propertyName + " in the bean " + bean, e);
        } catch (InvocationTargetException e) {
            log.warn("Can't get property " + propertyName + " in the bean " + bean, e);
        } catch (NoSuchMethodException e) {
            log.warn("Can't get property " + propertyName + " in the bean " + bean, e);
        }
        return value;

    }


    /**
     * Saves workbook to file
     * @param fileName - File name to save workbook
     * @param workbook - Workbook to save
     */
    public static void writeToFile(String fileName, HSSFWorkbook workbook) {
        OutputStream os;
        try {
            os = new BufferedOutputStream(new FileOutputStream(fileName));
            workbook.write(os);
            os.flush();
            os.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * Duplicates given HSSFCellStyle object
     * @param workbook - source HSSFWorkbook object
     * @param style - HSSFCellStyle object to duplicate
     * @return HSSFCellStyle
     */
    public static HSSFCellStyle duplicateStyle(HSSFWorkbook workbook, HSSFCellStyle style){
            HSSFCellStyle newStyle = workbook.createCellStyle();
            newStyle.setAlignment( style.getAlignment() );
            newStyle.setBorderBottom( style.getBorderBottom() );
            newStyle.setBorderLeft( style.getBorderLeft() );
            newStyle.setBorderRight( style.getBorderRight() );
            newStyle.setBorderTop( style.getBorderTop() );
            newStyle.setBottomBorderColor( style.getBottomBorderColor() );