Apache POI封装工具类: 操作Excel表格

Apache POI封装工具类: 操作Excel表格

import org.apache.poi.hssf.usermodel.*;import org.apache.poi.hssf.util.HSSFColor;import org.apache.poi.ss.usermodel.BorderStyle;import org.apache.poi.ss.usermodel.CellType;import org.apache.poi.ss.util.CellRangeAddress;import java.util.HashMap;import java.util.Map;import java.util.regex.Pattern;public class POIUtil {    /**     * 功能：拷贝sheet     * 实际调用     copySheet(targetSheet, sourceSheet, targetWork, sourceWork, true)     * @param targetSheet     * @param sourceSheet     * @param targetWork     * @param sourceWork                                                                        */    public static void copySheet(HSSFSheet targetSheet, HSSFSheet sourceSheet,            HSSFWorkbook targetWork, HSSFWorkbook sourceWork) throws Exception{        if(targetSheet == null || sourceSheet == null || targetWork == null || sourceWork == null){            throw new IllegalArgumentException("调用PoiUtil.copySheet()方法时，targetSheet、sourceSheet、targetWork、sourceWork都不能为空，故抛出该异常！");        }        copySheet(targetSheet, sourceSheet, targetWork, sourceWork, true);    }    /**     * 功能：拷贝sheet     * @param targetSheet     * @param sourceSheet     * @param targetWork     * @param sourceWork     * @param copyStyle                 boolean 是否拷贝样式     */    public static void copySheet(HSSFSheet targetSheet, HSSFSheet sourceSheet,            HSSFWorkbook targetWork, HSSFWorkbook sourceWork, boolean copyStyle)throws Exception {        if(targetSheet == null || sourceSheet == null || targetWork == null || sourceWork == null){            throw new IllegalArgumentException("调用PoiUtil.copySheet()方法时，targetSheet、sourceSheet、targetWork、sourceWork都不能为空，故抛出该异常！");        }        //复制源表中的行        int maxColumnNum = 0;        Map styleMap = (copyStyle) ? new HashMap() : null;        HSSFPatriarch patriarch = targetSheet.createDrawingPatriarch(); //用于复制注释        for (int i = sourceSheet.getFirstRowNum(); i <= sourceSheet.getLastRowNum(); i++) {            HSSFRow sourceRow = sourceSheet.getRow(i);            HSSFRow targetRow = targetSheet.createRow(i);            if (sourceRow != null) {                copyRow(targetRow, sourceRow,                        targetWork, sourceWork,patriarch, styleMap);                if (sourceRow.getLastCellNum() > maxColumnNum) {                    maxColumnNum = sourceRow.getLastCellNum();                }            }        }        //复制源表中的合并单元格        mergerRegion(targetSheet, sourceSheet);        //设置目标sheet的列宽        for (int i = 0; i <= maxColumnNum; i++) {            targetSheet.setColumnWidth(i, sourceSheet.getColumnWidth(i));        }    }    /**     * 功能：拷贝row     * @param targetRow     * @param sourceRow     * @param styleMap     * @param targetWork     * @param sourceWork     * @param targetPatriarch     */    public static void copyRow(HSSFRow targetRow, HSSFRow sourceRow,            HSSFWorkbook targetWork, HSSFWorkbook sourceWork,HSSFPatriarch targetPatriarch, Map styleMap) throws Exception {        if(targetRow == null || sourceRow == null || targetWork == null || sourceWork == null || targetPatriarch == null){            throw new IllegalArgumentException("调用PoiUtil.copyRow()方法时，targetRow、sourceRow、targetWork、sourceWork、targetPatriarch都不能为空，故抛出该异常！");        }        //设置行高        targetRow.setHeight(sourceRow.getHeight());        for (int i = sourceRow.getFirstCellNum(); i <= sourceRow.getLastCellNum(); i++) {            HSSFCell sourceCell = sourceRow.getCell(i);            HSSFCell targetCell = targetRow.getCell(i);            if (sourceCell != null) {                if (targetCell == null) {                    targetCell = targetRow.createCell(i);                }                //拷贝单元格，包括内容和样式                copyCell(targetCell, sourceCell, targetWork, sourceWork, styleMap);                //拷贝单元格注释                copyComment(targetCell,sourceCell,targetPatriarch);            }        }    }    /**     * 功能：拷贝cell，依据styleMap是否为空判断是否拷贝单元格样式     * @param targetCell            不能为空     * @param sourceCell            不能为空     * @param targetWork            不能为空     * @param sourceWork            不能为空     * @param styleMap              可以为空                     */    public static void copyCell(HSSFCell targetCell, HSSFCell sourceCell, HSSFWorkbook targetWork, HSSFWorkbook sourceWork,Map styleMap) {        if(targetCell == null || sourceCell == null || targetWork == null || sourceWork == null ){            throw new IllegalArgumentException("调用PoiUtil.copyCell()方法时，targetCell、sourceCell、targetWork、sourceWork都不能为空，故抛出该异常！");        }        //处理单元格样式        if(styleMap != null){            if (targetWork == sourceWork) {                targetCell.setCellStyle(sourceCell.getCellStyle());            } else {                String stHashCode = "" + sourceCell.getCellStyle().hashCode();                HSSFCellStyle targetCellStyle = (HSSFCellStyle) styleMap                        .get(stHashCode);                if (targetCellStyle == null) {                    targetCellStyle = targetWork.createCellStyle();                    targetCellStyle.cloneStyleFrom(sourceCell.getCellStyle());                    styleMap.put(stHashCode, targetCellStyle);                }                targetCell.setCellStyle(targetCellStyle);            }        }        //处理单元格内容        switch (sourceCell.getCellTypeEnum()) {        case STRING:            targetCell.setCellValue(sourceCell.getRichStringCellValue());            break;        case NUMERIC:            targetCell.setCellValue(sourceCell.getNumericCellValue());            break;        case BLANK:            targetCell.setCellType(HSSFCell.CELL_TYPE_BLANK);            break;        case BOOLEAN:            targetCell.setCellValue(sourceCell.getBooleanCellValue());            break;        case ERROR:            targetCell.setCellErrorValue(sourceCell.getErrorCellValue());            break;        case FORMULA:            targetCell.setCellFormula(sourceCell.getCellFormula());            break;        default:            break;        }    }    /**     * 功能：拷贝comment     * @param targetCell     * @param sourceCell     * @param targetPatriarch     */    public static void copyComment(HSSFCell targetCell,HSSFCell sourceCell,HSSFPatriarch targetPatriarch)throws Exception{        if(targetCell == null || sourceCell == null || targetPatriarch == null){            throw new IllegalArgumentException("调用PoiUtil.copyCommentr()方法时，targetCell、sourceCell、targetPatriarch都不能为空，故抛出该异常！");        }        //处理单元格注释        HSSFComment comment = sourceCell.getCellComment();        if(comment != null){            HSSFComment newComment = targetPatriarch.createComment(new HSSFClientAnchor());            newComment.setAuthor(comment.getAuthor());            newComment.setColumn(comment.getColumn());            newComment.setFillColor(comment.getFillColor());            newComment.setHorizontalAlignment(comment.getHorizontalAlignment());            newComment.setLineStyle(comment.getLineStyle());            newComment.setLineStyleColor(comment.getLineStyleColor());            newComment.setLineWidth(comment.getLineWidth());            newComment.setMarginBottom(comment.getMarginBottom());            newComment.setMarginLeft(comment.getMarginLeft());            newComment.setMarginTop(comment.getMarginTop());            newComment.setMarginRight(comment.getMarginRight());            newComment.setNoFill(comment.isNoFill());            newComment.setRow(comment.getRow());            newComment.setShapeType(comment.getShapeType());            newComment.setString(comment.getString());            newComment.setVerticalAlignment(comment.getVerticalAlignment());            newComment.setVisible(comment.isVisible());            targetCell.setCellComment(newComment);        }    }    /**     * 功能：复制原有sheet的合并单元格到新创建的sheet     *      * @param sheetCreat     * @param sourceSheet     */    public static void mergerRegion(HSSFSheet targetSheet, HSSFSheet sourceSheet)throws Exception {        if(targetSheet == null || sourceSheet == null){            throw new IllegalArgumentException("调用PoiUtil.mergerRegion()方法时，targetSheet或者sourceSheet不能为空，故抛出该异常！");        }        for (int i = 0; i < sourceSheet.getNumMergedRegions(); i++) {            CellRangeAddress oldRange = sourceSheet.getMergedRegion(i);            CellRangeAddress newRange = new CellRangeAddress(                    oldRange.getFirstRow(), oldRange.getLastRow(),                    oldRange.getFirstColumn(), oldRange.getLastColumn());            targetSheet.addMergedRegion(newRange);        }    }    /**     * 功能：重新定义HSSFColor.YELLOW的色值     *      * @param workbook     * @return     */    /*public static HSSFColor setMForeColor(HSSFWorkbook workbook) {        HSSFPalette palette = workbook.getCustomPalette();        HSSFColor hssfColor = null;        // byte[] rgb = { (byte) 221, (byte) 241, (byte) 255 };        // try {            // hssfColor = palette.findColor(rgb[0], rgb[1], rgb[2]);            // if (hssfColor == null) {                // palette.setColorAtIndex(HSSFColor.YELLOW.index, rgb[0], rgb[1],                        // rgb[2]);                // hssfColor = palette.getColor(HSSFColor.YELLOW.index);            // }        // } catch (Exception e) {            // e.printStackTrace();        // }        // return hssfColor;    // }*/  /**     * 功能：重新定义HSSFColor.PINK的色值     *      * @param workbook     * @return     */    public static HSSFColor setMBorderColor(HSSFWorkbook workbook) {        HSSFPalette palette = workbook.getCustomPalette();        HSSFColor hssfColor = null;        byte[] rgb = { (byte) 0, (byte) 128, (byte) 192 };        try {            hssfColor = palette.findColor(rgb[0], rgb[1], rgb[2]);            if (hssfColor == null) {                palette.setColorAtIndex(HSSFColor.PINK.index, rgb[0], rgb[1],                        rgb[2]);                hssfColor = palette.getColor(HSSFColor.PINK.index);            }        } catch (Exception e) {            e.printStackTrace();        }        return hssfColor;    }    /**     * 功能：设置单元格自动调整行高列宽自动     * @param wb     * @param cell     */    public static void setCellStyle(HSSFSheet sheet, HSSFCell cell) {        HSSFCellStyle style = cell.getCellStyle();        sheet.autoSizeColumn(1);        cell.setCellStyle(style);    }    /**     * 设置单元格边框与换行     * @param cell     */    public static void setCellBorderWrapStyle(HSSFWorkbook wb, HSSFRow row, int rowMaxColumn) {        Map<String, HSSFCellStyle> styleMap = new HashMap<String, HSSFCellStyle>();        HSSFCell cell = null;        for(int i=0; i<=rowMaxColumn; i++) {            if(row.getCell(i) == null) {                cell = row.createCell(i);            }else {                cell = row.getCell(i);            }            cell.setCellType(CellType.STRING);            if("".equals(cell.getStringCellValue()) || cell.getStringCellValue() == null) {                cell.setCellValue(" ");            }            HSSFCellStyle style = cell.getCellStyle();            String stHashCode = "" + style.hashCode();            HSSFCellStyle newStyle = (HSSFCellStyle) styleMap                    .get(stHashCode);            if (newStyle == null) {                //字体                HSSFFont font = style.getFont(wb);                font.setFontHeightInPoints((short)9);                //表格样式                style.setBorderBottom(BorderStyle.THIN);//下边框类型                style.setBorderLeft(BorderStyle.THIN);//左边框类型                style.setBorderRight(BorderStyle.THIN);//右边框类型                style.setBorderTop(BorderStyle.THIN);//上边框类型                //自动换行                style.setWrapText(true);                styleMap.put(stHashCode, style);            }            cell.setCellStyle(style);        }    }    public static void setA3LandscapePrintSetup(HSSFSheet sheet) {        HSSFPrintSetup ps = sheet.getPrintSetup();        ps.setLandscape(true); // 打印方向，true：横向，false：纵向(默认)          ps.setPaperSize(HSSFPrintSetup.A3_PAPERSIZE); //纸张类型    }    /**       * 获取合并单元格的值       * @param sheet       * @param row       * @param column       * @return       */        public static String getMergedRegionValue(HSSFSheet sheet ,int row , int column){         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(row >= firstRow && row <= lastRow){                    if(column >= firstColumn && column <= lastColumn){                        HSSFRow fRow = sheet.getRow(firstRow);                        HSSFCell fCell = fRow.getCell(firstColumn);                      return getCellValue(fCell);                    }                }            }            return null ;        }      /**     * 获取合并单元格最大的行号     * @param sheet     * @param row     * @param column     * @return     */    public static Integer getMergedRegionMaxRow(HSSFSheet sheet ,int row , int column){            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(row >= firstRow && row <= lastRow){                    if(column >= firstColumn && column <= lastColumn){                        return lastRow;                    }                }            }            return -1 ;        }      /**      * 判断指定的单元格是否是合并单元格      * @param sheet       * @param row 行下标      * @param column 列下标      * @return      */      public static 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;    }    /**       * 获取单元格的值       * @param cell       * @return       */        @SuppressWarnings("deprecation")    public static String getCellValue(HSSFCell cell) {        if (cell == null)            return "";        if (cell.getCellTypeEnum() == CellType.STRING) {            return cell.getStringCellValue();        } else if (cell.getCellTypeEnum() == CellType.BOOLEAN) {            return String.valueOf(cell.getBooleanCellValue());        } else if (cell.getCellTypeEnum() == CellType.FORMULA) {            return cell.getCellFormula();        } else if (cell.getCellTypeEnum() == CellType.NUMERIC) {            return String.valueOf(cell.getNumericCellValue());        }        return "";    }    /**     * 获取单元格自动高度     * @param str     * @param fontCountInline     * @return     */    public static float getExcelCellAutoHeight(HSSFRow row, float fontCountInline) {        String str = getMaxStr(row);        float defaultRowHeight = 15.00f;//每一行的高度指定        float defaultCount = 0.00f;        for (int i = 0; i < str.length(); i++) {            float ff = getregex(str.substring(i, i + 1));            defaultCount = defaultCount + ff;        }        return ((int) (defaultCount / fontCountInline) + 1) * defaultRowHeight + 1.0f;//计算    }    public static String getMaxStr(HSSFRow row) {        String maxStr = "";        for(int i=row.getFirstCellNum(); i<=row.getLastCellNum(); i++ ) {            if(row.getCell(i) != null) {                row.getCell(i).setCellType(CellType.STRING);                if(row.getCell(i).getStringCellValue().length() > maxStr.length()) {                    maxStr = row.getCell(i).getStringCellValue();                }            }        }        return maxStr;    }    public static float getregex(String charStr) {        if(charStr=="")        {            return 0.5f;        }        // 判断是否为字母或字符        if (Pattern.compile("^[A-Za-z0-9]+$").matcher(charStr).matches()) {            return 0.5f;        }        // 判断是否为全角        if (Pattern.compile("[\u4e00-\u9fa5]+$").matcher(charStr).matches()) {            return 1.00f;        }        //全角符号 及中文        if (Pattern.compile("[^x00-xff]").matcher(charStr).matches()) {            return 1.00f;        }        return 0.5f;    }}

以上代码封装为POIUtils，可以很方便的进行Excel的复制等操作。
当然参考了网上的一些博客内容