新手封装的POI操作Excel

最新推荐文章于 2022-06-02 15:39:08 发布

weixin_34241036

最新推荐文章于 2022-06-02 15:39:08 发布

阅读量110

点赞数

文章标签： java

原文链接：https://my.oschina.net/lxping/blog/39770

版权

为什么80%的码农都做不了架构师？>>>

public interface ExcelWorkBook {

/**
    * 读取excel文件
    * @param path
    * @throws Exception
    */
    public void read(String path)throws Exception;

    /**
    * 赋值指定单元格文本值
    * @param row
    * @param col
    * @param text
    * @throws Exception
    */
    void setText(int row, int col, String text) throws Exception;
    /**
    * 获取指定单元格文本值
    * @param row
    * @param col
    * @return
    * @throws Exception
    */
    String getText(int row, int col) throws Exception;

    /**
    * 保存excel文件
    * @param newpath
    * @throws Exception
    */
    void write(String newpath) throws Exception;
    /**
    * 复制sheet一部分范围到指定的另一部分
    * @param srcRow1
    * @param srcCol1
    * @param srcRow2
    * @param srcCol2
    * @param dstRow1
    * @param dstCol1
    * @throws Exception
    */
    void copyRange(int srcRow1, int srcCol1, int srcRow2, int srcCol2,
            int dstRow1, int dstCol1) throws Exception;
    /**
    * 删除指定范围单元格
    * @param row1
    * @param col1
    * @param row2
    * @param col2
    * @param shift
    * @throws Exception
    */
    void deleteRange(int row1, int col1, int row2, int col2, short shift) throws Exception;
    /**
    * 添加指定范围单元格
    * @param row1
    * @param col1
    * @param row2
    * @param col2
    * @param shift
    * @throws Exception
    */
    void insertRange(int row1, int col1, int row2, int col2, short shift) throws Exception;

    /**
    * 获取当前sheet索引
    * @return
    * @throws Exception
    */
    int getSheet() throws Exception;
    /**
    * 获取当前sheet名称
    * @return
    * @throws Exception
    */
    String getSheetName() throws Exception;
    /**
    * 获取指定sheet名称
    * @param sheetIndex
    * @return
    * @throws Exception
    */
    String getSheetName(int sheetIndex) throws Exception;
    /**
    * 设置当前sheet名称
    * @param sheetName
    * @throws Exception
    */
    void setSheetName(String sheetName) throws Exception;
    /**
    * 设置指定sheet名称
    * @param sheetIndex
    * @param sheetName
    * @throws Exception
    */
    void setSheetName(int sheetIndex, String sheetName) throws Exception;
    /**
    * 跳转到指定sheet
    * @param sheetIndex
    * @throws Exception
    */
    void setSheet(int sheetIndex) throws Exception;
    /**
    * 设置指定单元格数值
    * @param row
    * @param col
    * @param number
    * @throws Exception
    */
    void setNumber(int row, int col, double number)throws Exception;
    /**
    * 设置指定Sheet设置指定单元格数值
    * @param sheet
    * @param row
    * @param col
    * @param number
    * @throws Exception
    */
    void setNumber(int sheet, int row, int col, double number)throws Exception;
    /**
    * 指定位置添加图片
    * @param left
    * @param top
    * @param right
    * @param bottom
    * @param imagepath
    * @throws Exception
    */
    void addPicture(int left, int top, int right, int bottom,String imagepath) throws Exception;
    /**
    * 指定范围设置样式
    * @param rangeStyle
    * @param row1
    * @param col1
    * @throws Exception
    */
    void setRangeStyle(CellStyle rangeStyle,int row1, int col1) throws Exception;

    CellStyle getRangeStyle(int row1, int col1) throws Exception;
    /**
    * 指定范围设置样式
    * @param rangeStyle
    * @param row1
    * @param col1
    * @throws Exception
    */
    void setRangeStyle(CellStyle rangestyle, int row1, int col1,
            int row2, int col2) throws Exception;
    /**
    * 复制指定sheet到一个新的sheet
    * @param insertAt
    * @throws Exception
    */
    void copySheet(int insertAt) throws Exception;
    /**
    * 复制指定sheet到一个现有的sheet 并且修改名称
    * @param insertAt
    * @throws Exception
    */
    void copySheet(int sheetIndex, int insertAt, String newname) throws Exception;
    /**
    * 设置行的高度
    * @param row
    * @param height
    * @throws Exception
    */
    void setRowHeight(int row, int height) throws Exception;

    void setSelection(int row1, int col1, int row2, int col2) throws Exception;

    void setSelection(String range) throws Exception;

    void editCopyRight() throws Exception;

    void autoFilter() throws Exception;
    /**
    * 隐藏指定的sheet
    * @param indexs
    * @throws Exception
    */
    void hiddenSheets(int[] indexs) throws Exception;
    /**
    * 添加指定的sheet
    * @param sheets
    * @param sheetNames
    * @throws Exception
    */
    void insertSheets(int sheets,String[] sheetNames) throws Exception;
    /**
    * 获取当前sheet最后一行
    * @return
    */
    public int getLastRow();
    /**
    * 获取当前sheet最后一列
    * @return
    */
    public int getLastCol();

    public int getChartCount();
    /**
    * 获取当前excel文件有多少个sheet
    * @return
    */
    public int getNumSheets();
    /**
    * 判断是否是合并单元格
    * @param sheet
    * @param intCellRow
    * @param intCellCol
    * @return
    * @throws IOException
    */
    public boolean isInMergerCellRegion(Sheet sheet, int intCellRow,int intCellCol) throws IOException;
    /**
    * 隐藏指定列
    * @param col
    * @param ishidden
    * @throws Exception
    */
    void setColHidden(int col,boolean ishidden) throws Exception;
    /**
    * 隐藏指定行
    * @param row
    * @param ishidden
    * @throws Exception
    */
    void setRowHidden(int row,boolean ishidden) throws Exception;
    /**
    * 隐藏指定列
    * @param cols
    * @param ishidden
    * @throws Exception
    */
    void setColHidden(int[] cols,boolean ishidden) throws Exception;
    /**
    * 隐藏指定行
    * @param row
    * @param ishidden
    * @throws Exception
    */
    void setRowHidden(int[] rows,boolean ishidden) throws Exception;
    /**
    * 复制指定范围
    * @param dstRow1
    * @param dstCol1
    * @param dstRow2
    * @param dstCol2
    * @param srcRow1
    * @param srcCol1
    * @param srcRow2
    * @param srcCol2
    * @throws Exception
    */
    void copyRange(int dstRow1,int dstCol1, int dstRow2, int dstCol2,int srcRow1,int srcCol1, int srcRow2,int srcCol2) throws Exception;
    /**
    * 写入公式
    * @param row
    * @param col
    * @param text
    * @throws Exception
    */
    void setFormula(int row,int col,String text) throws Exception;
    /**
    * 删除指定 sheet
    * @param sheet
    * @param sheets
    * @throws Exception
    */
    void deleteSheets(int sheet,int sheets) throws Exception;
    /**
    * 获取单元格背景颜色
    * @param row
    * @param col
    * @return
    */
    public short getBackgroundColor(int row,int col);
    /**
    * 获取单元格字体颜色
    * @param row
    * @param col
    * @return
    */
    public java.awt.Color getFontColor(int row,int col);
    /**
    * 获取单元格字体名称
    * @param row
    * @param col
    * @return
    */
    public String getFontName(int row,int col);
    /**
    * 获取指定单元格字体大小
    * @param row
    * @param col
    * @return
    */
    public int getFontSize(int row,int col);

｝

public enum ExcelType {
    XLSX, XLS;
    public static boolean exist(String value) {
        for (ExcelType element : ExcelType.values()) {
            if (element.toString().equals(value)) {
                return true;
            }
        }
        return false;
    }
}

实现类:

import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.PushbackInputStream;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.imageio.ImageIO;

import org.apache.poi.POIXMLDocument;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.Drawing;
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.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.lxping.platform.extendxls.ExcelWorkBook;

/**
* @name WorkBook
*
* @description CLASS_DESCRIPTION
*
* MORE_INFORMATION
*
* @author lxping
*
* @since 2012-2-01
*
* @version 1.0
*/

public class WorkBook implements ExcelWorkBook {

    private Workbook workBook;
    private Sheet w_sheet = null;
    private Row s_row = null;
    private Cell r_cell = null;
    private int sheetIndex;
    private ExcelType type;

    @Override
    public void addPicture(int left, int top, int right,int bottom, String imagepath) throws Exception {
        //先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArray
        ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();;
        BufferedImage bufferImg = ImageIO.read(new File(imagepath));;
        ImageIO.write(bufferImg,imagepath.substring(imagepath.lastIndexOf(".")),byteArrayOut);;
        Drawing patriarch = w_sheet.createDrawingPatriarch();;
        switch (type) {
        case XLS:
            HSSFClientAnchor anchor = new HSSFClientAnchor(0,0,1023,255,(short)0,0,(short)10,10);;
            patriarch.createPicture((ClientAnchor) anchor , workBook.addPicture(byteArrayOut.toByteArray(),Workbook.PICTURE_TYPE_JPEG));;
            break;
        case XLSX:
            XSSFClientAnchor anchorXSS = new XSSFClientAnchor(0,0,1023,255,(short)0,0,(short)10,10);;
            patriarch.createPicture((ClientAnchor) anchorXSS , workBook.addPicture(byteArrayOut.toByteArray(),Workbook.PICTURE_TYPE_JPEG));;
            break;
        default:
            break;
        }
    }

    @Override
    public void autoFilter() throws Exception {
        // TODO Auto-generated method stub

    }

    @Override
    public void copyRange(int srcRow1, int srcCol1, int srcRow2, int srcCol2,int dstRow1, int dstCol1) throws Exception {
        int dstRow2 = dstRow1 + (srcRow2 - srcRow1);
        int dstCol2 = dstCol1 + (srcCol2 - srcCol1);
        copyRange(dstRow1, dstCol1, dstRow2, dstCol2, srcRow1,srcCol1, srcRow2, srcCol2);
    }

    @Override
    public void copyRange(int dstRow1, int dstCol1, int dstRow2, int dstCol2,
            int srcRow1, int srcCol1, int srcRow2, int srcCol2)
            throws Exception {
        int col=srcCol1;
        for (int i = dstRow1; i < dstRow2; i++) {
            srcCol1=col;
            for (int j = dstCol1; j < dstCol2; j++) {
                CellStyle cellStyle = null;
                //获取源单元格
                Row row = w_sheet.getRow(i);
                Cell cell=row.getCell(j);
                if (srcCol1>srcCol2||srcRow1>srcRow2) {
                    break;
                }
                //获取目标单元格
                Row src_row = w_sheet.getRow(srcRow1);
                Cell src_cell=src_row.getCell(srcCol1);
                // new一个式样
                cellStyle = workBook.createCellStyle();
                // 设置边框线型
                cellStyle.setBorderTop(cell.getCellStyle().getBorderTop());
                cellStyle.setBorderBottom(cell.getCellStyle().getBorderBottom());
                cellStyle.setBorderLeft(cell.getCellStyle().getBorderLeft());
                cellStyle.setBorderRight(cell.getCellStyle().getBorderRight());
                // 设置内容位置:例水平居中,居右，居工
                cellStyle.setAlignment(cell.getCellStyle().getAlignment());
                // 设置内容位置:例垂直居中,居上，居下
                cellStyle.setVerticalAlignment(cell.getCellStyle().getVerticalAlignment());
                // 自动换行
                cellStyle.setWrapText(cell.getCellStyle().getWrapText());
                src_cell.setCellStyle(cellStyle);
                // 设置单元格高度
                src_cell.getRow().setHeight(cell.getRow().getHeight());
                // 单元格类型
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    String strVal = removeInternalBlank(cell.getStringCellValue());
                    src_cell.setCellValue(strVal);
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    src_cell.setCellValue(cell.getNumericCellValue());
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    try {
                        src_cell.setCellFormula(String.valueOf(cell.getNumericCellValue()));
                    } catch (IllegalStateException e) {
                        try {
                            src_cell.setCellValue(String.valueOf(cell.getRichStringCellValue()));
                        } catch (Exception ex) {
                            src_cell.setCellValue("公式出错");
                        }
                    }
                    break;
                }
                srcCol1++;
                srcRow1++;
            }
        }

    }

    @Override
    public void copySheet(int insertAt) throws Exception {
        int sheet = sheetIndex;
        sheet=sheet+1;
        copySheet(insertAt,sheet,"Sheet"+sheet);
    }

    @Override
    public void copySheet(int sheetIndex, int insertAt, String newname)
            throws Exception {
        Sheet sheetCreat = workBook.createSheet(newname);
        Sheet datasource = workBook.getSheetAt(sheetIndex);
        // 复制源表中的合并单元格
        MergerRegion(sheetCreat, datasource);
        int firstRow = datasource.getFirstRowNum();
        int lastRow = datasource.getLastRowNum();
        for (int i = firstRow; i <= lastRow; i++) {
            // 创建新建excel Sheet的行
            Row rowCreat = sheetCreat.createRow(i);
            // 取得源有excel Sheet的行
            Row row = datasource.getRow(i);
            // 单元格式样
            CellStyle cellStyle = null;
            int firstCell = row.getFirstCellNum();
            int lastCell = row.getLastCellNum();
            for (int j = firstCell; j < lastCell; j++) {
                // 自动适应列宽貌似不起作用
                sheetCreat.autoSizeColumn(j);
                // new一个式样
                cellStyle = workBook.createCellStyle();
                // 设置边框线型
                cellStyle.setBorderTop(row.getCell(j).getCellStyle().getBorderTop());
                cellStyle.setBorderBottom(row.getCell(j).getCellStyle().getBorderBottom());
                cellStyle.setBorderLeft(row.getCell(j).getCellStyle().getBorderLeft());
                cellStyle.setBorderRight(row.getCell(j).getCellStyle().getBorderRight());
                // 设置内容位置:例水平居中,居右，居工
                cellStyle.setAlignment(row.getCell(j).getCellStyle().getAlignment());
                // 设置内容位置:例垂直居中,居上，居下
                cellStyle.setVerticalAlignment(row.getCell(j).getCellStyle().getVerticalAlignment());
                // 自动换行
                cellStyle.setWrapText(row.getCell(j).getCellStyle().getWrapText());
                rowCreat.createCell(j).setCellStyle(cellStyle);
                // 设置单元格高度
                rowCreat.getCell(j).getRow().setHeight(row.getCell(j).getRow().getHeight());
                // 单元格类型
                switch (row.getCell(j).getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    String strVal = removeInternalBlank(row.getCell(j).getStringCellValue());
                    rowCreat.getCell(j).setCellValue(strVal);
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    rowCreat.getCell(j).setCellValue(row.getCell(j).getNumericCellValue());
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    try {
                        rowCreat.getCell(j).setCellValue(String.valueOf(row.getCell(j).getNumericCellValue()));
                    } catch (IllegalStateException e) {
                        try {
                            rowCreat.getCell(j).setCellFormula(String.valueOf(row.getCell(j).getRichStringCellValue()));
                        } catch (Exception ex) {
                            rowCreat.getCell(j).setCellValue("公式出错");
                        }
                    }
                    break;
                }
            }
        }
    }

    @Override
    public void deleteRange(int row1, int col1, int row2, int col2, short shift)
            throws Exception {

    }

    @Override
    public void deleteSheets(int sheet, int sheets) throws Exception {
        for (int i = 0; i <= sheets; i++) {
            workBook.removeSheetAt(sheet+i);
        }
    }

    @Override
    public void editCopyRight() throws Exception {

    }

    @Override
    public int getChartCount() {
        // TODO Auto-generated method stub
        return 0;
    }

    @Override
    public int getLastCol() {
        return w_sheet.getLeftCol();
    }

    @Override
    public int getLastRow() {
        return w_sheet.getLastRowNum();
    }

    @Override
    public int getNumSheets() {
        return workBook.getNumberOfSheets();
    }

    @Override
    public CellStyle getRangeStyle(int row1, int col1) throws Exception {
        CellStyle style = w_sheet.getRow(row1).getCell(col1).getCellStyle();
        return style;
    }

    @Override
    public int getSheet() throws Exception {
        return sheetIndex;
    }

    @Override
    public String getSheetName() throws Exception {
        return w_sheet.getSheetName();
    }

    @Override
    public String getSheetName(int sheetIndex) throws Exception {
        String sheetName=null;
        //临时打开一个sheet 读取sheet名称
        Sheet sheet=workBook.getSheetAt(sheetIndex);
        sheetName=sheet.getSheetName();
        sheet = null;
        return sheetName;
    }

    @Override
    public String getText(int row, int col) throws Exception {
        s_row = w_sheet.getRow(row);
        r_cell = s_row.getCell(col);
        return String.valueOf(getCellValue(r_cell));
    }

    @Override
    public void hiddenSheets(int[] indexs) throws Exception {
        for (int i = 0; i < indexs.length; i++) {
            workBook.setSheetHidden(i, true);
        }
    }

    @Override
    public void insertRange(int row1, int col1, int row2, int col2, short shift)
            throws Exception {
        // TODO Auto-generated method stub

    }

    @Override
    public void insertSheets(int sheets, String[] sheetNames)throws Exception {
        for (int j = 0; j < sheets; j++) {
            workBook.createSheet(sheetNames[j]);
        }
    }

    @Override
    public void read(String path) throws Exception {
        String value = path.substring(path.lastIndexOf(".")+1);
        if(!ExcelType.exist(value.toUpperCase())){
            throw new Exception("文件类型错误!");
        }
        type=ExcelType.valueOf(value.toUpperCase());
        InputStream stream=new FileInputStream(path);
        workBook = create(stream);
        //默认打开第一个sheet
        sheetIndex=0;
        w_sheet = workBook.getSheetAt(sheetIndex);
    }
    public static Workbook create(InputStream inp) throws IOException, InvalidFormatException {
        if(! inp.markSupported()) {
            inp = new PushbackInputStream(inp, 8);
        }
        if(POIFSFileSystem.hasPOIFSHeader(inp)) {
            return new HSSFWorkbook(inp);
        }
        if(POIXMLDocument.hasOOXMLHeader(inp)) {
            return new XSSFWorkbook(OPCPackage.open(inp));
        }
        throw new IllegalArgumentException("Your InputStream was neither an OLE2 stream, nor an OOXML stream");
    }
    @Override
    public void setColHidden(int col, boolean ishidden) throws Exception {
        w_sheet.getRow(0).getCell(col).getCellStyle().setHidden(ishidden);

    }

    @Override
    public void setColHidden(int[] cols, boolean ishidden) throws Exception {
        for (int i : cols) {
            w_sheet.getRow(0).getCell(i).getCellStyle().setHidden(ishidden);
        }
    }

    @Override
    public void setFormula(int row, int col, String text) throws Exception {
        s_row = w_sheet.getRow(row);
        r_cell = s_row.getCell(col);
        r_cell.setCellFormula(text);
    }

    @Override
    public void setNumber(int row, int col, double number) throws Exception {
        s_row = w_sheet.getRow(row);
        r_cell = s_row.getCell(col);
        r_cell.setCellValue(number);
    }

    @Override
    public void setNumber(int sheet, int row, int col, double number)
            throws Exception {
        sheetIndex=sheet;
        w_sheet=workBook.getSheetAt(sheetIndex);
        s_row = w_sheet.getRow(row);
        r_cell = s_row.getCell(col);
        r_cell.setCellValue(number);
    }

    @Override
    public void setRangeStyle(CellStyle rangeStyle,int row1, int col1) throws Exception {
        w_sheet.getRow(row1).getCell(col1).setCellStyle(rangeStyle);
    }

    @Override
    public void setRangeStyle(CellStyle rangestyle, int row1, int col1,
            int row2, int col2) throws Exception {
            for (int i = row1; i < row2; i++) {
                for (int j = col1; j < col2; j++) {
                    w_sheet.getRow(i).getCell(j).setCellStyle(rangestyle);
                }
            }
    }

    @Override
    public void setRowHeight(int row, int height) throws Exception {
        Row row2=w_sheet.getRow(row);
        row2.setHeight((short)height);
    }

    @Override
    public void setRowHidden(int row, boolean ishidden) throws Exception {
        Row row2=w_sheet.getRow(row);
        row2.getRowStyle().setHidden(ishidden);
    }

    @Override
    public void setRowHidden(int[] rows, boolean ishidden) throws Exception {
        for (int i : rows) {
            Row row2=w_sheet.getRow(i);
            row2.getRowStyle().setHidden(ishidden);
        }
    }

    @Override
    public void setSelection(int row1, int col1, int row2, int col2)
            throws Exception {

    }

    @Override
    public void setSelection(String range) throws Exception {

    }

    @Override
    public void setSheet(int sheetIndex) throws Exception {
        this.sheetIndex=sheetIndex;
        w_sheet=workBook.getSheetAt(sheetIndex);
    }

    @Override
    public void setSheetName(String sheetName) throws Exception {
        workBook.setSheetName(sheetIndex, sheetName);
    }

    @Override
    public void setSheetName(int sheetIndex, String sheetName) throws Exception {
        workBook.setSheetName(sheetIndex, sheetName);
    }

    @Override
    public void setText(int row, int col, String text) throws Exception {
        s_row = w_sheet.getRow(row);
        r_cell = s_row.getCell(col);
        r_cell.setCellValue(text);
    }

    @Override
    public void write(String newpath) throws Exception {
        OutputStream stream=new FileOutputStream(newpath);
        workBook.write(stream);
    }
    public Object getCellValue(Cell cell){
        Object value=null;
        if(cell == null){
            return value;
        }else if(cell.getCellType() == Cell.CELL_TYPE_BLANK){
            //System.out.println("cellType为：CELL_TYPE_BLANK");
        }else if(cell.getCellType() == Cell.CELL_TYPE_STRING){
            value=cell.getRichStringCellValue().getString();
        }else if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
            value=cell.getNumericCellValue();
        }else if(cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){
            value=cell.getBooleanCellValue();
        }else if(cell.getCellType() == Cell.CELL_TYPE_FORMULA){
            value=cell.getCellFormula();
        }
        return value;
    }
    /**
    * 复制原有sheet的合并单元格到新创建的sheet
    *
    * @param sheetCreat 新创建sheet
    * @param sheet      原有的sheet
    */
    private static void MergerRegion(Sheet sheetCreat, Sheet sheet) {
        int sheetMergerCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergerCount; i++) {
            CellRangeAddress mergedRegionAt = sheet.getMergedRegion(i);
            sheetCreat.addMergedRegion(mergedRegionAt);
        }
    }

    /**
    * 判断单元格在不在合并单元格范围内
    *
    * @param sheet
    * @param intCellRow
    *            被判断的单元格的行号
    * @param intCellCol
    *            被判断的单元格的列号
    * @return TRUE 表示在，反之不在
    * @throws IOException
    */
    public boolean isInMergerCellRegion(Sheet sheet, int intCellRow,int intCellCol) throws IOException {
        boolean retVal = false;

        int sheetMergerCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergerCount; i++) {
            CellRangeAddress cra = (CellRangeAddress) sheet.getMergedRegion(i);
            // 合并单元格CELL起始行
            int firstRow = cra.getFirstRow();
            // 合并单元格CELL起始列
            int firstCol = cra.getFirstColumn();

            // 合并单元格CELL结束行
            int lastRow = cra.getFirstColumn();
            // 合并单元格CELL结束列
            int lastCol = cra.getLastColumn();

            if (intCellRow >= firstRow && intCellRow <= lastRow) {
                if (intCellCol >= firstCol && intCellCol <= lastCol) {
                    retVal = true;
                    break;
                }
            }
        }
        return retVal;
    }

    /**
    * 去除字符串内部空格
    */
    public static String removeInternalBlank(String s) {
        // System.out.println("bb:" + s);
        Pattern p = Pattern.compile("\\s*|\t|\r|\n");
        Matcher m = p.matcher(s);
        char str[] = s.toCharArray();
        StringBuffer sb = new StringBuffer();
        for (int i = 0; i < str.length; i++) {
            if (str[i] == ' ') {
                sb.append(' ');
            } else {
                break;
            }
        }
        String after = m.replaceAll("");
        return sb.toString() + after;
    }


    public short getBackgroundColor(int row,int col) {
        CellStyle style = w_sheet.getRow(row).getCell(col).getCellStyle();
        short color = style.getFillBackgroundColor();
        return color;
    }

    public java.awt.Color getFontColor(int row,int col) {
        CellStyle style = w_sheet.getRow(row).getCell(col).getCellStyle();
        Font font = workBook.getFontAt(style.getFontIndex());
        java.awt.Color color = java.awt.Color.getColor(font.getFontName(),font.getColor());
        return color;
    }

    public String getFontName(int row,int col) {
        CellStyle style = w_sheet.getRow(row).getCell(col).getCellStyle();
        Font font = workBook.getFontAt(style.getFontIndex());
        return font.getFontName();
    }

    public int getFontSize(int row,int col) {
        CellStyle style = w_sheet.getRow(row).getCell(col).getCellStyle();
        Font font = workBook.getFontAt(style.getFontIndex());
        return font.getFontHeight();
    }

}