详解java实现Excel 单元格取值工具类

在工作中经常遇到通过excel获取数据的需求，比如通过excel将数据提交到数据库等。现针对excel单元格的取值方法提取出来作为一个工具类。
具体代码如下：

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

/**
 *  Excel 单元格取值工具类
 *  所需jar包（poi-3.9.jar）
 */
public class ExcelValueUtils {
    /**
     *   获取表格的值（合并单元格与普通单元格）
     * @param sheet
     * @param rowNum
     * @param colNum
     * @return
     */
    public static String getCellValueInfo(Sheet sheet, int rowNum, int colNum) {
        String cellValue = "";
        //判断是否是合并单元格
        boolean isMergedRegion = isMergedRegion(sheet,rowNum ,colNum);
        if(isMergedRegion){
            cellValue = getMergedRegionValue(sheet,rowNum ,colNum);
        }else{
            Row row = sheet.getRow(rowNum);
            cellValue = getCellValue(row.getCell(colNum));
        }
        return cellValue;
    }

    /**
     *   获取普通单元格的值
     * @param cell
     * @return
     */
    public static String getCellValue(Cell cell){
        if(cell == null) return "";
        if(cell.getCellType() == Cell.CELL_TYPE_STRING){
            return cell.getStringCellValue();
        }else if(cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){
            return String.valueOf(cell.getBooleanCellValue());
        }else if(cell.getCellType() == Cell.CELL_TYPE_FORMULA){
            return cell.getCellFormula() ;
        }else if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
            String strCell;
            if (DateUtil.isCellDateFormatted(cell)) {
                double d = cell.getNumericCellValue();
                strCell = new SimpleDateFormat("yyyy/MM/dd").format(DateUtil.getJavaDate(d));
            } else {
                cell.setCellType(Cell.CELL_TYPE_STRING);
                strCell = cell.getStringCellValue();
            }
            return strCell;
        }
        return "";
    }

    /**
     *   获取合并单元格的值
     * @param sheet
     * @param row
     * @param column
     * @return
     */
    public static String getMergedRegionValue(Sheet 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){
                    Row fRow = sheet.getRow(firstRow);
                    Cell fCell = fRow.getCell(firstColumn);
                    return getCellValue(fCell) ;
                }
            }
        }
        return null ;
    }

    /**
     *  判断指定的单元格是否是合并单元格
     * @param sheet
     * @param row 行下标
     * @param column 列下标
     * @return
     */
    public static boolean isMergedRegion(Sheet 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 sheet
     * @return List<CellRangeAddress>
     */
    public static List<CellRangeAddress> getCombineCell(Sheet sheet){
        List<CellRangeAddress> list = new ArrayList<CellRangeAddress>();
        //获得一个 sheet 中合并单元格的数量
        int sheetmergerCount = sheet.getNumMergedRegions();
        //遍历所有的合并单元格
        for(int i = 0; i<sheetmergerCount;i++){
            //获得合并单元格保存进list中
            CellRangeAddress ca = sheet.getMergedRegion(i);
            list.add(ca);
        }
        return list;
    }
}