util.java

一个java生成自动生成Excel

            newStyle.setDataFormat( style.getDataFormat() );
            newStyle.setFillBackgroundColor( style.getFillBackgroundColor() );
            newStyle.setFillForegroundColor( style.getFillForegroundColor() );
            newStyle.setFillPattern( style.getFillPattern() );
            newStyle.setFont( workbook.getFontAt( style.getFontIndex() ) );
            newStyle.setHidden( style.getHidden() );
            newStyle.setIndention( style.getIndention() );
            newStyle.setLeftBorderColor( style.getLeftBorderColor() );
            newStyle.setLocked( style.getLocked() );
            newStyle.setRightBorderColor( style.getRightBorderColor() );
            newStyle.setTopBorderColor( style.getTopBorderColor() );
            newStyle.setVerticalAlignment( style.getVerticalAlignment() );
            newStyle.setWrapText( style.getWrapText() );
            return newStyle;
    }

    public static String escapeAttributes(String tag) {
        if( tag == null ){
            return tag;
        }
        int i = 0;
        StringBuffer sb = new StringBuffer("");
        StringBuffer attrValue = new StringBuffer("");
        final char expressionClosingSymbol = '}';
        final char expressionStartSymbol = '{';
        boolean isAttrValue = false;
        int exprCount = 0;
        while( i<tag.length() ){
            if( !isAttrValue ){
                sb.append( tag.charAt( i ) );
                if( tag.charAt(i) == '\"' ){
                    isAttrValue = true;
                    attrValue = new StringBuffer("");
                }
            }else{
                if( tag.charAt( i ) == '\"'){
                    if( exprCount != 0 ){
                        attrValue.append( tag.charAt( i ) );
                    }else{
                        sb.append( escapeXml( attrValue.toString() ));
                        sb.append( tag.charAt( i ) );
                        isAttrValue = false;
                    }
                }else{
                    attrValue.append( tag.charAt( i ) );
                    if( tag.charAt( i ) == expressionClosingSymbol ){
                        exprCount--;
                    }else if( tag.charAt( i ) == expressionStartSymbol ){
                        exprCount++;
                    }
                }
            }
            i++;
        }
        if( isAttrValue ){
            log.warn("Can't parse ambiguous quot in " + tag);
        }
        return sb.toString();
    }


    /**
     * <p>Escapes XML entities in a <code>String</code>.</p>
     *
     * @param str The <code>String</code> to escape.
     * @return A new escaped <code>String</code>.
     */
    private static String escapeXml(String str) {
        if( str == null ){
            return str;
        }
        StringBuffer buf = new StringBuffer(str.length() * 2);
        int i;
        for (i = 0; i < str.length(); ++i) {
            char ch = str.charAt(i);
            String entityName = getEntityName(ch);
            if (entityName == null) {
                if (ch > 0x7F) {
                    buf.append("&#");
                    buf.append((int)ch);
                    buf.append(';');
                } else {
                    buf.append(ch);
                }
            } else {
                buf.append('&');
                buf.append(entityName);
                buf.append(';');
            }
        }
        return buf.toString();
    }

    private static String getEntityName(char ch) {
        return (String) xmlEntities.get( Integer.toString(ch) );
    }

    public static void shiftCellsLeft(HSSFSheet sheet, int startRow, short startCol, int endRow, short endCol, short shiftNumber){
        for(int i = startRow; i <= endRow; i++){
            boolean doSetWidth = true;
            HSSFRow row = sheet.getRow( i );
            if( row!=null ){
                for(short j = startCol; j<=endCol; j++){
                    HSSFCell cell = row.getCell( j );
                    if( cell==null ){
                        cell = row.createCell( j );
                        doSetWidth = false;
                    }
                    HSSFCell destCell = row.getCell( (short) (j - shiftNumber) );
                    if( destCell == null ){
                        destCell = row.createCell( (short) (j - shiftNumber) );
                    }
                    copyCell( cell, destCell, true );
                    if( doSetWidth ){
                        sheet.setColumnWidth( destCell.getCellNum(), getWidth( sheet, cell.getCellNum() ) );
                    }
                }
            }
        }
    }

    static short getWidth(HSSFSheet sheet, short col){
        short width = sheet.getColumnWidth( col );
        if( width == sheet.getDefaultColumnWidth() ){
            width = (short) (width * 256);
        }
        return width;
    }


    public static void shiftCellsRight(HSSFSheet sheet, int startRow, int endRow, short startCol, short shiftNumber){
        for(int i = startRow; i <= endRow; i++){
            HSSFRow row = sheet.getRow( i );
            if( row!=null ){
                short lastCellNum = row.getLastCellNum();
                for(short j = lastCellNum; j>=startCol; j--){
                    HSSFCell destCell = row.getCell( (short) (j + shiftNumber) );
                    if( destCell == null ){
                        destCell = row.createCell( (short) (j + shiftNumber) );
                    }
                    HSSFCell cell = row.getCell( j );
                    if( cell==null ){
                        cell = row.createCell( j );
                    }
                    copyCell( cell, destCell, true );
                }
            }
        }
    }

    public static void updateCellValue( HSSFSheet sheet, int rowNum, short colNum, String cellValue){
        HSSFRow hssfRow  = sheet.getRow( rowNum );
        HSSFCell hssfCell = hssfRow.getCell( colNum );
        hssfCell.setCellValue( cellValue );
    }

    public static void copyPageSetup(HSSFSheet destSheet, HSSFSheet srcSheet) {
        HSSFHeader header = srcSheet.getHeader();
        HSSFFooter footer = srcSheet.getFooter();
        if (footer != null) {
            destSheet.getFooter().setLeft(footer.getLeft());
            destSheet.getFooter().setCenter(footer.getCenter());
            destSheet.getFooter().setRight(footer.getRight());
        }
        if (header != null) {
            destSheet.getHeader().setLeft(header.getLeft());
            destSheet.getHeader().setCenter(header.getCenter());
            destSheet.getHeader().setRight(header.getRight());
        }
    }

    public static void copyPrintSetup(HSSFSheet destSheet, HSSFSheet  srcSheet) {
        HSSFPrintSetup setup = srcSheet.getPrintSetup();
        if (setup != null) {
            destSheet.getPrintSetup().setLandscape(setup.getLandscape());
            destSheet.getPrintSetup().setPaperSize(setup.getPaperSize());
            destSheet.getPrintSetup().setScale(setup.getScale());
            destSheet.getPrintSetup().setFitWidth( setup.getFitWidth() );
            destSheet.getPrintSetup().setFitHeight( setup.getFitHeight() );
            destSheet.getPrintSetup().setFooterMargin( setup.getFooterMargin() );
            destSheet.getPrintSetup().setHeaderMargin( setup.getHeaderMargin() );
            destSheet.getPrintSetup().setPaperSize( setup.getPaperSize() );
            destSheet.getPrintSetup().setPageStart( setup.getPageStart() );
        }
    }

    public static void setPrintArea(HSSFWorkbook resultWorkbook, int sheetNum) {
        int maxColumnNum = 0;
        for (int j = resultWorkbook.getSheetAt(sheetNum).getFirstRowNum(); j <= resultWorkbook.getSheetAt(sheetNum).getLastRowNum(); j++) {
            HSSFRow row = resultWorkbook.getSheetAt(sheetNum).getRow(j);
            if (row != null) {
                maxColumnNum = row.getLastCellNum();
            }
        }
        resultWorkbook.setPrintArea(sheetNum,
                0,
                maxColumnNum,
                0,
                resultWorkbook.getSheetAt(sheetNum).getLastRowNum()
        );
    }

    protected static final String regexCellRef = "[a-zA-Z]+[0-9]+";
    protected static final Pattern regexCellRefPattern = Pattern.compile( regexCellRef );
    protected static final String regexCellCharPart = "[0-9]+";
    protected static final String regexCellDigitPart = "[a-zA-Z]+";
    protected static  String cellRangeSeparator = ":";

    public static boolean isColumnRange(List cells) {
        String firstCell = (String) cells.get( 0 );
        boolean isColumnRange = true;
        if( firstCell != null && firstCell.length() > 0 ){
            String firstCellCharPart = firstCell.split(regexCellCharPart)[0];
            String firstCellDigitPart = firstCell.split(regexCellDigitPart)[1];
            int cellNumber = Integer.parseInt( firstCellDigitPart );
            String nextCell, cellCharPart, cellDigitPart;
            for (int i = 1; i < cells.size() && isColumnRange; i++) {
                nextCell = (String) cells.get(i);
                cellCharPart = nextCell.split( regexCellCharPart )[0];
                cellDigitPart = nextCell.split( regexCellDigitPart )[1];
                if( !firstCellCharPart.equalsIgnoreCase( cellCharPart ) || Integer.parseInt(cellDigitPart) != ++cellNumber ){
                    isColumnRange = false;
                }
            }
        }
        return isColumnRange;
    }

    public static boolean isRowRange(List cells) {
        String firstCell = (String) cells.get( 0 );
        boolean isRowRange = true;
        if( firstCell != null && firstCell.length() > 0 ){
            String firstCellDigitPart = firstCell.split(regexCellDigitPart)[1];
            String nextCell, cellDigitPart;
            CellReference cellRef = new CellReference( firstCell );
            int cellNumber = cellRef.getCol();
            for (int i = 1; i < cells.size() && isRowRange; i++) {
                nextCell = (String) cells.get(i);
                cellDigitPart = nextCell.split( regexCellDigitPart )[1];
                cellRef = new CellReference( nextCell );
                if( !firstCellDigitPart.equalsIgnoreCase( cellDigitPart ) || cellRef.getCol() != ++cellNumber ){
                    isRowRange = false;
                }
            }
        }
        return isRowRange;
    }

    public static String buildCommaSeparatedListOfCells(String refSheetName, List cells) {
        String listOfCells = "";
        for (int i = 0; i < cells.size() - 1; i++) {
            String cell = (String) cells.get(i);
            listOfCells += getRefCellName(refSheetName, cell) + ",";
        }
        listOfCells += getRefCellName( refSheetName, (String) cells.get( cells.size() - 1 ));
        return listOfCells;
    }

    public static String detectCellRange(String refSheetName, List cells) {
        if( cells == null || cells.isEmpty() ){
            return "";
        }
        String firstCell = (String) cells.get( 0 );
        String range = firstCell;
        if( firstCell != null && firstCell.length() > 0 ){
            if( isRowRange(cells) || isColumnRange(cells) ){
                String lastCell = (String) cells.get( cells.size() - 1 );
                range = getRefCellName(refSheetName, firstCell) + cellRangeSeparator + lastCell.toUpperCase();
            }else{
                range = buildCommaSeparatedListOfCells(refSheetName, cells );
            }
        }
        return range;
    }

    public static String getRefCellName(String refSheetName, String cellName){
        if( refSheetName == null ){
            return cellName.toUpperCase();
        }else{
            return refSheetName + "!" + cellName.toUpperCase();
        }
    }


}