POI工具类（兼容2003和2007）

package com.booway.util;

import java.io.File;
import java.io.IOException;
import java.util.HashMap;
import java.util.Map;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;

/**
 *
    * @ClassName: ExcelUtil
    * @Description: 对Excel操作的工具类
    *
    * @date 2019年1月30日
    *
 */
public class ExcelUtil
{
    private static Logger logger = Logger.getLogger(ExcelUtil.class);

    /**
     * @throws IOException
     * @throws FileNotFoundException
     *
        * @Title: getWorkBook
        * @Description: 得到工作蒲
        * @param @param in
        * @param @return
        * @param @throws IOException    参数
        * @return Workbook    返回类型
        * @throws
     */
    public static Workbook getWorkBook(File file)
    {
        try
        {
            return WorkbookFactory.create(file);
        } catch (InvalidFormatException | IOException e)
        {
            logger.error("", e);
        }
        return null;

    }

    /**
    *
       * @Title: CopySheet
       * @Description: 复制sheet文件
       * @param @param srcSheet
       * @param @param destSheet    参数
       * @return void    返回类型
       * @throws
    */
    public static void CopySheet(Sheet srcSheet, Sheet destSheet)
    {
        int maxColumnNum = 0;
        Map<Integer, CellStyle> styleMap = new HashMap<Integer, CellStyle>();
        for (int i = srcSheet.getFirstRowNum(); i <= srcSheet.getLastRowNum(); i++)
        {
            Row srcRow = srcSheet.getRow(i);
            Row destRow = destSheet.createRow(i);
            if (srcRow != null)
            {
                ExcelUtil.copyRow(srcSheet, srcSheet, srcRow, destRow, styleMap);
                if (srcRow.getLastCellNum() > maxColumnNum)
                {
                    maxColumnNum = srcRow.getLastCellNum();
                }
            }
        }
        // 合并单元格
        mergerRegion(destSheet, srcSheet);

        for (int i = 0; i <= maxColumnNum; i++)
        {
            //设置列宽  
            destSheet.setColumnWidth(i, srcSheet.getColumnWidth(i));
        }

    }

    /**
     *
        * @Title: copyRow
        * @Description: 复制行
        * @param @param srcSheet
        * @param @param destSheet
        * @param @param srcRow
        * @param @param destRow
        * @param @param styleMap    参数
        * @return void    返回类型
        * @throws
     */
    public static void copyRow(Sheet srcSheet, Sheet destSheet, Row srcRow, Row destRow,
            Map<Integer, CellStyle> styleMap)
    {
        // 设置行高
        destRow.setHeight(srcRow.getHeight());
        for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++)
        {
            Cell srcCell = srcRow.getCell(j);
            Cell destCell = destRow.getCell(j);
            if (srcCell != null)
            {
                if (destCell == null)
                {
                    destCell = destRow.createCell(j);
                }
                ExcelUtil.copyCell(srcCell, destCell, styleMap);

            }
        }

    }

    /**
     *
        * @Title: copyCell
        * @Description: 复制列
        * @param @param srcCell
        * @param @param destCell
        * @param @param styleMap    参数
        * @return void    返回类型
        * @throws
     */
    public static void copyCell(Cell srcCell, Cell destCell, Map<Integer, CellStyle> styleMap)
    {
        if (styleMap != null)
        {
            if (srcCell.getSheet().getWorkbook() == destCell.getSheet().getWorkbook())
            {
                destCell.setCellStyle(srcCell.getCellStyle());
            } else
            {
                if (srcCell.getCellStyle().getDataFormatString() != null)
                {
                    int stHashCode = srcCell.getCellStyle().hashCode();
                    CellStyle newCellStyle = styleMap.get(stHashCode);
                    if (newCellStyle == null)
                    {
                        if ((srcCell instanceof HSSFCell && destCell instanceof HSSFCell)
                                || (srcCell instanceof XSSFCell && destCell instanceof XSSFCell))
                        {
                            newCellStyle = destCell.getSheet().getWorkbook().createCellStyle();
                            newCellStyle.cloneStyleFrom(srcCell.getCellStyle());
                        } else
                        {
                            newCellStyle = cloneStyleFrom(srcCell, destCell);
                        }
                        styleMap.put(stHashCode, newCellStyle);

                    }
                    destCell.setCellStyle(newCellStyle);
                }

            }
        }
        switch (srcCell.getCellType())
        {
        case Cell.CELL_TYPE_STRING:
            destCell.setCellValue(srcCell.getStringCellValue());
            break;
        case Cell.CELL_TYPE_NUMERIC:
            destCell.setCellValue(srcCell.getNumericCellValue());
            break;
        case Cell.CELL_TYPE_BLANK:
            destCell.setCellType(Cell.CELL_TYPE_BLANK);
            break;
        case Cell.CELL_TYPE_BOOLEAN:
            destCell.setCellValue(srcCell.getBooleanCellValue());
            break;
        case Cell.CELL_TYPE_ERROR:
            destCell.setCellErrorValue(srcCell.getErrorCellValue());
            break;
        case Cell.CELL_TYPE_FORMULA:
            destCell.setCellFormula(srcCell.getCellFormula());
            break;
        default:
            break;
        }

    }

    /**
     *
        * @Title: createCell
        * @Description: 创建下一列
        * @param @param row
        * @param @return    参数
        * @return Cell    返回类型
        * @throws
     */
    public static Cell createNextCell(Row row)
    {
        int num = row.getLastCellNum();
        if (num == -1)
        {
            num = 0;
        }
        return row.createCell(num);

    }

    /**
     *
        * @Title: createNextRow
        * @Description: 创建下一行
        * @param @param sheet
        * @param @return    参数
        * @return Row    返回类型
        * @throws
     */
    public static Row createNextRow(Sheet sheet)
    {
        return sheet.createRow(sheet.getLastRowNum() + 1);
    }

    /**
     *
        * @Title: mergerRegion
        * @Description: 合并单元格
        * @param @param targetSheet
        * @param @param sourceSheet    参数
        * @return void    返回类型
        * @throws
     */
    public static void mergerRegion(Sheet targetSheet, Sheet 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);
        }
    }
    
    /**
     *
        * @Title: cloneStyleFrom
        * @Description: 不同文件类型用来拷贝格式
        * @param @param srcCell
        * @param @param destCell
        * @param @return    参数
        * @return CellStyle    返回类型
        * @throws
     */
    public static CellStyle cloneStyleFrom(Cell srcCell, Cell destCell)
    {
        Workbook srcwb=  srcCell.getSheet().getWorkbook();
        Workbook destwb = destCell.getSheet().getWorkbook();
        CellStyle srcStyle = srcCell.getCellStyle();
        CellStyle destStyle = destwb.createCellStyle();
        Font destFont = destCell.getSheet().getWorkbook().createFont();

        if (srcStyle instanceof XSSFCellStyle)
        {
            destStyle.setFont(cloneFont(destFont, ((XSSFCellStyle) srcStyle).getFont()));
        } else
        {
            destStyle.setFont(cloneFont(destFont, ((HSSFCellStyle) srcStyle).getFont(srcwb)));

        }

        destStyle.setAlignment(srcStyle.getAlignment());
        destStyle.setBorderBottom(srcStyle.getBorderBottom());
        destStyle.setBorderLeft(srcStyle.getBorderLeft());
        destStyle.setBorderRight(srcStyle.getBorderRight());
        destStyle.setBorderTop(srcStyle.getBorderTop());
        destStyle.setDataFormat(cloneData( srcwb, destwb,srcStyle.getDataFormat()));
        destStyle.setFillBackgroundColor(srcStyle.getFillBackgroundColor());
        destStyle.setFillForegroundColor(srcStyle.getFillForegroundColor());
        destStyle.setFillPattern(srcStyle.getFillPattern());
        destStyle.setHidden(srcStyle.getHidden());
        destStyle.setIndention(srcStyle.getIndention());
        destStyle.setLocked(srcStyle.getLocked());
        destStyle.setVerticalAlignment(srcStyle.getVerticalAlignment());
        destStyle.setWrapText(srcStyle.getWrapText());

        return destStyle;
    }

    /**
     *
        * @Title: cloneFont
        * @Description: 复制字体
        * @param @param destFont
        * @param @param srcFont
        * @param @return    参数
        * @return Font    返回类型
        * @throws
     */
    public static Font cloneFont(Font destFont, Font srcFont)
    {
        destFont.setBoldweight(srcFont.getBoldweight());
        destFont.setCharSet(srcFont.getCharSet());
        destFont.setColor(srcFont.getColor());
        destFont.setFontName(srcFont.getFontName());
        destFont.setFontHeight(srcFont.getFontHeight());
        destFont.setItalic(srcFont.getItalic());
        destFont.setStrikeout(srcFont.getStrikeout());
        destFont.setTypeOffset(srcFont.getTypeOffset());
        destFont.setUnderline(srcFont.getUnderline());
        return destFont;

    }
    
    /**
     *
        * @Title: cloneData
        * @Description: 复制时间
        * @param @param srcwb
        * @param @param destwb
        * @param @param index
        * @param @return    参数
        * @return short    返回类型
        * @throws
     */
    public static short cloneData( Workbook srcwb,Workbook destwb,short index) {
        DataFormat srcFormat = srcwb.createDataFormat();
        DataFormat destFormat = destwb.createDataFormat();
        return destFormat.getFormat(srcFormat.getFormat(index));
    }
}