import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.PushbackInputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
import java.util.List;
import org.apache.poi.POIXMLDocument;
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.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.xmlbeans.impl.piccolo.io.FileFormatException;
/**
 *yanbiao 2016.10.25 
 */
public class ExcelUtil {
 
  private static final String EXTENSION_XLS = "xls";
  private static final String EXTENSION_XLSX = "xlsx";

/**
* 文件檢查
*/
private void preReadCheck(String filePath) throws FileNotFoundException, FileFormatException {

File file = new File(filePath);
if (!file.exists()) {
throw new FileNotFoundException("導入的文件不存在：" + filePath);
}
if (!(filePath.endsWith(EXTENSION_XLS) || filePath.endsWith(EXTENSION_XLSX))) {
throw new FileFormatException("傳入的文件不是excel");
}
} 
 /**
   * 取得WorkBook對象
   * xls:HSSFWorkbook,03版
   * xlsx:XSSFWorkbook,07版
  */
 private Workbook getWorkbook(String filePath) throws IOException, InvalidFormatException { 
    //直接判斷后綴來返回相應的Workbook對象多數情況沒問題，但是這個更保險，第3條已經說明  
    Workbook wb = null;
    InputStream is = new FileInputStream(filePath);
    if (!is.markSupported()) {
      is = new PushbackInputStream(is, 8);
    }
    if (POIFSFileSystem.hasPOIFSHeader(is)) {
       return new HSSFWorkbook(is);
    }
    if (POIXMLDocument.hasOOXMLHeader(is)) {
      return new XSSFWorkbook(OPCPackage.open(is));
    }
    throw new IllegalArgumentException("您的excel版本目前不支持poi解析");
  }
 
  /**
   * 讀取excel文件內容
   */
  public Map<Integer, List<String>> readExcel(String filePath) throws FileNotFoundException, FileFormatException {
    // 檢查和獲取workbook對象
    this.preReadCheck(filePath);
    Workbook wb = null;
    Map<Integer,List<String>> map = new HashMap<Integer, List<String>>();
    try {
      wb = this.getWorkbook(filePath);
      // 默認只讀取第一個sheet 
      Sheet sheet = wb.getSheetAt(0);
      int rowcount = sheet.getLastRowNum();//邏輯行，包括空行
      int cellcount = sheet.getRow(0).getLastCellNum();//第一行（將來作為字段的行）有多少個單元格
      for (int i=0;i<rowcount;i++) {          //這里用最原始的for循環來保證每行都會被讀取
         List<String> list = new ArrayList<String>();
         Row row = sheet.getRow(i);
         if(null!=row){
            for (int j=0;j<cellcount;j++) {
             list.add(getCellValue(row.getCell(j)));  //這里也是用for循環，用Cell c:row這樣的遍歷，空單元格就被拋棄了  
            }
            System.out.println("第"+(row.getRowNum()+1)+"行數據："+list.toString());
            map.put(row.getRowNum(), list); 
          }else{
            for (int j=0;j<cellcount;j++) {
              list.add("無數據");   
            }
            System.out.println("第"+(i+1)+"行數據："+list.toString());
            map.put(i, list);
        }     
      }    
    } catch (Exception e) {
        System.out.println("讀取Excel異常："+e.getMessage());
        e.printStackTrace();
      } finally {
         if (wb != null) {
           try {
               wb.close();
            } catch (IOException e) {
               e.printStackTrace();
             }
         }
       }
    return map;   
  }
  /**
   * 取單元格的值
   */
  private String getCellValue(Cell c) {
    if (c == null) {
      return "無數據";
    }
    String value = "";
    switch (c.getCellType()){
    case HSSFCell.CELL_TYPE_NUMERIC://數字
       value = c.getNumericCellValue()+"";
    break;
    case HSSFCell.CELL_TYPE_STRING://字符串
      value = c.getStringCellValue();
    break;
    case HSSFCell.CELL_TYPE_BOOLEAN://boolean
      value = c.getBooleanCellValue()+"";
    break;
    case HSSFCell.CELL_TYPE_FORMULA://公式
      value = c.getCellFormula()+"";
    break;
    case HSSFCell.CELL_TYPE_BLANK://空值
      value= "無數據";
     break;
    case HSSFCell.CELL_TYPE_ERROR:
      value = "非法字符";
     break;
    default:
      value= "未知類型";
     break;    
    }
    return value;
  }
}