POI读取Excel

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
public class ExcelReader {
    private static final Logger logger= LoggerFactory.getLogger(ExcelReader.class);
	 /*************************2015/3/24 用POI读取EXCEL************************/
	/**
	 * @param path 文件路径
	 * @param fileType 文件扩展名
	 * @return map-success 是否成功 msg-错误信息 dataList 数据列表
	 * */
    public static HashMap<String,Object> read(String path,String fileType) throws IOException  
    {  
    	Boolean success = true;  //是否正确标志 1-正确
    	String msg = "";
    	HashMap<String, Object> result = new HashMap<String, Object>();
    	
    	List<List<Object>> dataList = new ArrayList<List<Object>>();
    	try{
            File file = new File(path);  
         
            if (fileType.equals("xls")) {  
            	dataList = read2003Excel(file);  
            }  
            else if (fileType.equals("xlsx")) {  
            	dataList = read2007Excel(file);  
            }  
            else {  
        	      success = false;
        	      msg = "您输入的Excel格式不正确";
                  logger.info("您输入的Excel格式不正确");
            }  
           
    	 }catch(Exception e){
    		success = false;
    		msg = "读取EXCEL的过程中出现了异常";
    		logger.info("捕捉到了异常： ");
    		logger.info(""+e);
    	}
    	
    	result.put("success", success);
    	result.put("msg", msg);
    	result.put("dataList", dataList);
    	
        //输出读取的内容
    	/*for(int i=0; i<dataList.size(); i++){
    		logger.info("row " + i + ":");
    		List<Object> temp = dataList.get(i);
    		for(int j=0; j<temp.size(); j++){
    			logger.info(temp.get(j));
    		}
    	}*/
    	return result;
    }  
    
    /**
	 * 读取 office 2003 excel
	 * 
	 * @throws IOException
	 * @throws FileNotFoundException
	 */
	private static List<List<Object>> read2003Excel(File file)
			throws IOException {
		List<List<Object>> list = new ArrayList<List<Object>>();
		HSSFWorkbook hwb = new HSSFWorkbook(new FileInputStream(file));
		HSSFSheet sheet = hwb.getSheetAt(0);
		Object value = null;
		HSSFRow row = null;
		
		HSSFCell cell = null;

		Short firstCellNumIndex = null;
		Short lastCellNumIndex = null;
		
		logger.info("sheet rows : " + sheet.getFirstRowNum() + " - " + sheet.getPhysicalNumberOfRows());
		
		for (int i = sheet.getFirstRowNum(); i < sheet
				.getPhysicalNumberOfRows(); i++) {
			row = sheet.getRow(i);
			if (row == null) {
				continue;
			}
		
			List<Object> linked = new ArrayList<Object>();
			
			//20160429 为空的个数
			Integer emptyNum = 0;
			//logger.info("row " + i + " cells : " + row.getFirstCellNum() + " - " + row.getLastCellNum());
			
			if(row.getFirstCellNum()<0 || row.getLastCellNum()<0){
				continue;
			}

			//20180115 统一用第一行标题行的row.getFirstCellNum()，row.getLastCellNum()，因为getFirstCellNum获取的是
			//某行第一个实际单元格的下标 会自动忽略掉为null的单元格
			if(firstCellNumIndex == null){
				firstCellNumIndex = row.getFirstCellNum();
			}
			if(lastCellNumIndex == null){
				lastCellNumIndex = row.getLastCellNum();
			}
			//logger.info("firstCellNumIndex: " + firstCellNumIndex + "; lastCellNumIndex: " + lastCellNumIndex);
			
			for (int j = firstCellNumIndex; j < lastCellNumIndex; j++) {
				cell = row.getCell(j);
				if (cell == null) {
					linked.add("");
					emptyNum++;
					continue;
				}
				DecimalFormat df = new DecimalFormat("0");// 格式化 number String
															// 字符
				SimpleDateFormat sdf = new SimpleDateFormat(
						"yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
				DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字
				switch (cell.getCellType()) {
					case XSSFCell.CELL_TYPE_STRING:
						//logger.info(i + "行" + j + " 列 is String type");
						value = cell.getStringCellValue();
						break;
					case XSSFCell.CELL_TYPE_NUMERIC:
						//logger.info(i + "行" + j + " 列 is Number type ; DateFormt:" + cell.getCellStyle().getDataFormatString());

						if(DateUtil.isCellDateFormatted(cell)){
							   value = sdf.format(HSSFDateUtil.getJavaDate(cell
									.getNumericCellValue()));
						 }else{
								cell.setCellType(Cell.CELL_TYPE_STRING);
								String temp = cell.getStringCellValue();
								// 判断是否包含小数点，如果不含小数点，则以字符串读取，如果含小数点，则转换为Double类型的字符串
								if (temp.indexOf(".") > -1) {
									value = nf.format(new Double(temp.trim()));
								} else {
									value = temp.trim();
								}
						 }
						break;
					case XSSFCell.CELL_TYPE_BOOLEAN:
						//logger.info(i + "行" + j + " 列 is Boolean type");
						value = cell.getBooleanCellValue();
						break;
					case XSSFCell.CELL_TYPE_BLANK:
						//logger.info(i + "行" + j + " 列 is Blank type");
						value = "";
						break;
					default:
						//logger.info(i + "行" + j + " 列 is default type");
						value = cell.toString();
				}
				if (value == null || "".equals(value)) {
					linked.add("");
					emptyNum++;
					continue;
				}
				linked.add(value);
			}// for cells
			
			//20160429
			if(emptyNum.equals(row.getLastCellNum() - row.getFirstCellNum())){//一行全为空
				logger.info("第" + i  + "行为空！");
				continue;
			}

			//logger.info("row i[" + i + "] size: " + linked.size());
			
			list.add(linked);
		}
		return list;
	}

	/**
	 * 读取Office 2007 excel
	 * */
	private static List<List<Object>> read2007Excel(File file)
			throws IOException {
		List<List<Object>> list = new ArrayList<List<Object>>();
		// 构造 XSSFWorkbook 对象，strPath 传入文件路径
		XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(file));
		// 读取第一章表格内容
		XSSFSheet sheet = xwb.getSheetAt(0);
		Object value = null;
		XSSFRow row = null;
		XSSFCell cell = null;
		
		logger.info("sheet rows : " + sheet.getFirstRowNum() + " - " + sheet.getPhysicalNumberOfRows());
		Short firstCellNumIndex = null;
		Short lastCellNumIndex = null;

		for (int i = sheet.getFirstRowNum(); i < sheet
				.getPhysicalNumberOfRows(); i++) {
			row = sheet.getRow(i);
			if (row == null) {
				continue;
			}
			
			List<Object> linked = new ArrayList<Object>();
			
			//20160429 为空的个数
			Integer emptyNum = 0;
			//logger.info("row " + i + " : " + row.getFirstCellNum() + " - " + row.getLastCellNum());
			
			if(row.getFirstCellNum()<0 || row.getLastCellNum()<0){
				continue;
			}

			//20180115 统一用第一行标题行的row.getFirstCellNum()，row.getLastCellNum()，因为getFirstCellNum获取的是
			//某行第一个实际单元格的下标 会自动忽略掉为null的单元格
			if(firstCellNumIndex == null){
				firstCellNumIndex = row.getFirstCellNum();
			}
			if(lastCellNumIndex == null){
				lastCellNumIndex = row.getLastCellNum();
			}
			//logger.info("firstCellNumIndex: " + firstCellNumIndex + "; lastCellNumIndex: " + lastCellNumIndex);
			
			for (int j = firstCellNumIndex; j < lastCellNumIndex; j++) {
				
				cell = row.getCell(j);

				if (cell == null) {
					linked.add("");
					emptyNum++;
					continue;
				}
				DecimalFormat df = new DecimalFormat("0");// 格式化 number String
															// 字符
				SimpleDateFormat sdf = new SimpleDateFormat(
						"yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
				DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字
				switch (cell.getCellType()) {
				case XSSFCell.CELL_TYPE_STRING:
					//logger.info(i + "行" + j + " 列 is String type");
					value = cell.getStringCellValue();
					break;
				case XSSFCell.CELL_TYPE_NUMERIC:
//					logger.info(i + "行" + j + " 列 is Number type ; DateFormt:" + cell.getCellStyle().getDataFormatString());
					if(DateUtil.isCellDateFormatted(cell)){  
						   value = sdf.format(HSSFDateUtil.getJavaDate(cell
								.getNumericCellValue())); 
		             }else{   
		                  
		            	    cell.setCellType(Cell.CELL_TYPE_STRING);
							String temp = cell.getStringCellValue();
							// 判断是否包含小数点，如果不含小数点，则以字符串读取，如果含小数点，则转换为Double类型的字符串
							if (temp.indexOf(".") > -1) {
								value = nf.format(new Double(temp.trim()));		
							} else {
								value = temp.trim();
							}
		             }  
					break;
				case XSSFCell.CELL_TYPE_BOOLEAN:
					//logger.info(i + "行" + j + " 列 is Boolean type");
					value = cell.getBooleanCellValue();
					break;
				case XSSFCell.CELL_TYPE_BLANK:
					//logger.info(i + "行" + j + " 列 is Blank type");
					value = "";
					break;
				default:
					//logger.info(i + "行" + j + " 列 is default type");
					cell.setCellType(Cell.CELL_TYPE_STRING);
	                String temp = cell.getStringCellValue();  
	                value = temp.trim();  
	                 
				}
				if (value == null || "".equals(value)) {
					linked.add("");
					emptyNum++;
					continue;
				}
				linked.add(value);
			}
			
			if(emptyNum.equals(row.getLastCellNum() - row.getFirstCellNum())){//一行全为空
				logger.info("第" + i  + "行为空！");
				continue;
			}
			
			list.add(linked);
		}
		return list;
	}
	
	//20160517 Excel 读取
	/*public static void main(String[] args) throws IOException{
		HashMap<String, Object> result  = read("D:/CITY_LIST.xls", "xls");
		if(result != null){
			List<List<Object>> dataList = (List<List<Object>>)result.get("dataList");
			
			//logger.info("dataList.size: " + dataList.size());
			System.out.println(("dataList.size: " + dataList.size()));
			
			for(int i=1; i<dataList.size(); i++){
				List<Object> tempList = dataList.get(i);
				String insertSql = "INSERT INTO STATISTIC_CITY(ID,CITY_NAME) VALUES(";
				for(int j=0; j<tempList.size(); j++){
					//System.out.println("i - " + i + ", j- " + j + " : " + tempList.get(j));

					if(j>0){
						insertSql += ",'" + tempList.get(j) + "'";
					}else{
						insertSql += tempList.get(j);
					}
				}
				insertSql += ");";
				System.out.println(insertSql);
			}
		}
	}*/
}