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;
	}
}