读取Excel文档的内容工具类

import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
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.XSSFWorkbook;
public class Excel2ObjectMappingUtil {

	public static Map<String,List<CZZbMapping>> para(Context context,String fileName,byte[] data) throws Exception{
		if(fileName.endsWith(".xls")|| fileName.endsWith(".xlsx")) {
			try {
				Map<String,List<CZZbMapping>> map=new HashMap<String, List<CZZbMapping>>();
				ByteArrayInputStream datas=new ByteArrayInputStream(data);
				boolean isExcel2007 = getExcelVersion(fileName);
				Workbook wb = (!isExcel2007)?new XSSFWorkbook(datas):new HSSFWorkbook(datas);
			    int count = wb.getNumberOfSheets();//sheet个数
			   
			    for(int i = 0; i < count; i++){
			    	List<CZZbMapping> list=new ArrayList<CZZbMapping>();
			    	Sheet sheet = wb.getSheetAt(i);
			    	int rowCount = sheet.getLastRowNum()+1;//行数
					 //计算列数
					int colCount = getColCount(sheet, rowCount);
					if(rowCount < 2 || colCount <5) {
						continue;
					}
			    	String[][] sheetData = oneSheetProcess(sheet, rowCount, colCount);
			    	for(int j=1 ;j < rowCount ;j++) {
			    		CZZbMapping zm=new CZZbMapping();
			    		zm.setRecid(GUID.randomID());//主键
			    		String key = sheetData[j][0];
			    		System.out.println(key);
			    		String value = sheetData[j][0];
			    		System.out.println("第"+j+"行第1列"+value);
			    		zm.setJqrcode(sheetData[j][0]);//jqrcode
			    		zm.setCzcode(sheetData[j][1]);//czcode
			    		zm.setMapid(GUID.tryValueOf(sheetData[j][2]));//mappingid
			    		zm.setReportname(sheetData[j][3]);//报表名称
			    		zm.setTabName(sheetData[j][4]);//项目标识名称
			    		list.add(zm);
			    	}
			    	String sheetName = sheet.getSheetName();//报表名称
			    	map.put(sheetName, list);
			    }
			    return map;
			} catch (IOException e) {
				e.printStackTrace();
			}
		}else{
			System.out.println("文件名不对");
		}
		return null;
	}
	/**
	 * 将sheet中的数据按二阶数组获取
	 * @param sheet
	 * @param rowCount
	 * @param colCount
	 * @return
	 */
	private static String[][] oneSheetProcess(Sheet sheet, int rowCount, int colCount) {
		String showTexts[][];
		showTexts = new String[rowCount][colCount];
		for(int r = 0;r<rowCount;r++){
			for(int c=0;c<colCount;c++){
				showTexts[r][c] = doGetShowText(sheet, r, c);
			}
		}	
		return showTexts;
	}
	/**
	 * 获取单元格显示的值
	 * @param sheet
	 * @param row
	 * @param col
	 * @return
	 */
	public static String doGetShowText(Sheet sheet, int row, int col) {
		if(row>=sheet.getFirstRowNum() && row<=sheet.getLastRowNum()){
			Row hssfRow = sheet.getRow(row);
			if(hssfRow!=null && col>=hssfRow.getFirstCellNum() && col<=hssfRow.getLastCellNum()){
				Cell cell = getSheetCell(sheet,row,col);
				return getStringCellValue(cell);
			}
		}
		return "";
	}
	/**
	 * 获取sheet的单元格
	 * @param sheet
	 * @param row
	 * @param col
	 * @return
	 */
	private static Cell getSheetCell(Sheet sheet, int row, int col) {
		int theRow = row;
		int theCol = col;
		int num = sheet.getNumMergedRegions();
		for(int i=0;i<num;i++){
			CellRangeAddress region = sheet.getMergedRegion(i);
			if(col>=region.getFirstColumn() && col<=region.getLastColumn() &&
					row>=region.getFirstRow() && row<=region.getLastRow()){
				theRow = region.getFirstRow();
				theCol = region.getFirstColumn();
				break;
			}
		}
		Row sheetRow = sheet.getRow(theRow);
		Cell cell = sheetRow.getCell(theCol);
		return cell;
	}
	/**
	 * 获取单元格字符值
	 * @param cell
	 * @return
	 */
	private static String getStringCellValue(final Cell cell) {
		if(cell==null)
			return "";
		switch(cell.getCellType()){
		case HSSFCell.CELL_TYPE_STRING:
			return cell.getStringCellValue();
		case HSSFCell.CELL_TYPE_NUMERIC:
			if (HSSFDateUtil.isCellDateFormatted(cell)) {        
				Date d = cell.getDateCellValue();        
				DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");        
				return formater.format(d);   
			} 
			return handleNumericStr(cell.getNumericCellValue());
		case HSSFCell.CELL_TYPE_BLANK:
			return "";
		}
		return "";
	}	
	/**
	 * 将数字转换成字符
	 * @param numericCellValue
	 * @return
	 */
	private static String handleNumericStr(double numericCellValue) {
		BigDecimal bd = new BigDecimal(numericCellValue);
		return bd.toString();
	}
	
	/**
	 * 获取列数
	 * @param sheet
	 * @param rowCount
	 * @return
	 */
	private static int getColCount(Sheet sheet, int rowCount) {
		int colCount = 0;
		for(int r=0;r< rowCount;r++){
			Row hssfRow = sheet.getRow(r);
			if(hssfRow!=null){
				if(colCount<hssfRow.getLastCellNum()){
					colCount = hssfRow.getLastCellNum();
				}
			}
		}
		return colCount;
	}
	
	public static boolean getExcelVersion(String fileName) {
		if(fileName.matches("^.+\\.(?i)(xlsx)$"))
			return true;
		return false;
	}
}