poi导入文件

package com.foriseland.fas.member.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.io.FilenameUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
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;
 
/**
 * Excel组件
 * 
 * @author zxy
 * @version 1.0
 * @since 1.0
 */ 
public abstract class ImportExcelUtil { 
 
    /**
     * Excel 2003
     */ 
    private final static String XLS = "xls"; 
    /**
     * Excel 2007
     */ 
    private final static String XLSX = "xlsx"; 
    /**
     * 分隔符
     */ 
    private final static String SEPARATOR = "|"; 
 
    /**
     * 由Excel文件的Sheet导出至List
     * 
     * @param file
     * @param sheetNum
     * @return
     */ 
    public static List<String> exportListFromExcel(File file, int sheetNum) 
            throws IOException { 
        return exportListFromExcel(new FileInputStream(file), 
                FilenameUtils.getExtension(file.getName()), sheetNum); 
    } 
 
    /**
     * 由Excel流的Sheet导出至List
     * 
     * @param is
     * @param extensionName
     * @param sheetNum
     * @return
     * @throws IOException
     */ 
    public static List<String> exportListFromExcel(InputStream is, 
            String extensionName, int sheetNum) throws IOException { 
 
        Workbook workbook = null; 
 
        if (extensionName.toLowerCase().equals(XLS)) { 
            workbook = new HSSFWorkbook(is); 
        } else if (extensionName.toLowerCase().equals(XLSX)) { 
            workbook = new XSSFWorkbook(is); 
        } 
 
        return exportListFromExcel(workbook, sheetNum); 
    } 
 
    /**
     * 由指定的Sheet导出至List
     * 
     * @param workbook
     * @param sheetNum
     * @return
     * @throws IOException
     */ 
    private static List<String> exportListFromExcel(Workbook workbook, 
            int sheetNum) { 
 
        Sheet sheet = workbook.getSheetAt(sheetNum); 
 
        // 解析公式结果 
        FormulaEvaluator evaluator = workbook.getCreationHelper() 
                .createFormulaEvaluator(); 
 
        List<String> list = new ArrayList<String>(); 
 
        SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
       
        int minRowIx = sheet.getFirstRowNum(); 
        int maxRowIx = sheet.getLastRowNum(); 
        for (int rowIx = minRowIx; rowIx <= maxRowIx; rowIx++) { 
            Row row = sheet.getRow(rowIx); 
            StringBuilder sb = new StringBuilder(); 
 
            short minColIx = row.getFirstCellNum(); 
            short maxColIx = row.getLastCellNum(); 
            for (short colIx = minColIx; colIx <= maxColIx; colIx++) { 
                Cell cell = row.getCell(new Integer(colIx)); 
                CellValue cellValue = evaluator.evaluate(cell); 
                if (cellValue == null) { 
                 sb.append(SEPARATOR); 
                    continue; 
                } 
                System.out.println(cellValue.getCellType());
                // 经过公式解析，最后只存在Boolean、Numeric和String三种数据类型，此外就是Error了 
                // 其余数据类型，根据官方文档，完全可以忽略http://poi.apache.org/spreadsheet/eval.html 
                switch (cellValue.getCellType()) { 
                case Cell.CELL_TYPE_BOOLEAN: 
                    sb.append(SEPARATOR + cellValue.getBooleanValue()); 
                    break; 
                case Cell.CELL_TYPE_NUMERIC: 
                    // 这里的日期类型会被转换为数字类型，需要判别后区分处理 
                    if (DateUtil.isCellDateFormatted(cell)) { 
                        sb.append(SEPARATOR + sdf.format(cell.getDateCellValue())); 
                    } else { 
                        sb.append(SEPARATOR + cellValue.getNumberValue()); 
                    } 
                    break; 
                case Cell.CELL_TYPE_STRING: 
                    sb.append(SEPARATOR + cellValue.getStringValue()); 
                    break; 
                case Cell.CELL_TYPE_FORMULA: 
                    break; 
                case Cell.CELL_TYPE_BLANK: 
//                 sb.append(SEPARATOR); 
                    break; 
                case Cell.CELL_TYPE_ERROR: 
                    break; 
                default: 
                    break; 
                } 
            } 
            sb.deleteCharAt(sb.length()-1);
            list.add(sb.toString()); 
        } 
        return list; 
    } 
   
   
    /**
     * 由Excel文件的Sheet导出至List (特殊：自定义起始行)
     * 
     * @param file
     * @param sheetNum
     * @return
     */ 
    public static List<String> exportListFromExcelCustom(File file, int sheetNum,int startNum) 
            throws IOException { 
        return exportListFromExcelCustom(new FileInputStream(file), 
                FilenameUtils.getExtension(file.getName()), sheetNum,startNum); 
    } 
 
    /**
     * 由Excel流的Sheet导出至List (特殊：自定义起始行)
     * 
     * @param is
     * @param extensionName
     * @param sheetNum
     * @return
     * @throws IOException
     */ 
    public static List<String> exportListFromExcelCustom(InputStream is, 
            String extensionName, int sheetNum,int startNum) throws IOException { 
        Workbook workbook = null; 
        if (extensionName.toLowerCase().equals(XLS)) { 
            workbook = new HSSFWorkbook(is); 
        } else if (extensionName.toLowerCase().equals(XLSX)) { 
            workbook = new XSSFWorkbook(is); 
        } 
 
        return exportListFromExcelCustom(workbook, sheetNum, startNum); 
    } 
 
    /**
     * 由指定的Sheet导出至List (特殊：自定义起始行)
     * 
     * @param workbook
     * @param sheetNum
     * @return
     * @throws IOException
     */ 
    private static List<String> exportListFromExcelCustom(Workbook workbook, 
            int sheetNum, int startNum) { 
 
        Sheet sheet = workbook.getSheetAt(sheetNum); 
   
        // 解析公式结果 
        FormulaEvaluator evaluator = workbook.getCreationHelper() 
                .createFormulaEvaluator(); 
 
        List<String> list = new ArrayList<String>(); 
 
        SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
       
//        int minRowIx = sheet.getFirstRowNum();         
        int minRowIx = startNum;
//        int PhysicalNumberOfRows = sheet.getPhysicalNumberOfRows();
        int maxRowIx = sheet.getLastRowNum(); 
        for (int rowIx = minRowIx; rowIx <= maxRowIx; rowIx++) {       
            Row row = sheet.getRow(rowIx); 
            if ((row == null) || isBlankRow(row)) {
                continue;
            }
            StringBuilder sb = new StringBuilder();  
           
            short minColIx = row.getFirstCellNum(); 
            short maxColIx = row.getLastCellNum(); 
            for (short colIx = minColIx; colIx <= maxColIx; colIx++) { 
                Cell cell = row.getCell(new Integer(colIx)); 
                CellValue cellValue = evaluator.evaluate(cell); 
                if (cellValue == null) { 
                 sb.append(SEPARATOR); 
                    continue; 
                } 
//                 System.out.println(cellValue.getCellType());
                // 经过公式解析，最后只存在Boolean、Numeric和String三种数据类型，此外就是Error了 
                // 其余数据类型，根据官方文档，完全可以忽略http://poi.apache.org/spreadsheet/eval.html 
                switch (cellValue.getCellType()) { 
                case Cell.CELL_TYPE_BOOLEAN: 
                    sb.append(SEPARATOR + cellValue.getBooleanValue()); 
                    break; 
                case Cell.CELL_TYPE_NUMERIC: 
                    // 这里的日期类型会被转换为数字类型，需要判别后区分处理 
                    if (DateUtil.isCellDateFormatted(cell)) { 
                        sb.append(SEPARATOR + sdf.format(cell.getDateCellValue())); 
                    } else {
                     DecimalFormat df = new DecimalFormat("0");
                     String value = df.format(cellValue.getNumberValue());
                        sb.append(SEPARATOR + value);
                    } 
                    break; 
                case Cell.CELL_TYPE_STRING: 
                    sb.append(SEPARATOR + cellValue.getStringValue()); 
                    break; 
                case Cell.CELL_TYPE_FORMULA: 
                    break; 
                case Cell.CELL_TYPE_BLANK: 
//                 sb.append(SEPARATOR); 
                    break; 
                case Cell.CELL_TYPE_ERROR: 
                    break; 
                default: 
                    break; 
                } 
            }
            sb.deleteCharAt(sb.length()-1);
            list.add(sb.toString());
        }          

        return list; 
    } 
   
    /**
     * 由Excel文件的Sheet导出至List (特殊：自定义起始行)
     * 
     * @param file
     * @param sheetNum
     * @return
     */ 
    public static List<String> exportListFromExcelZQ(File file, int sheetNum,int startNum) 
            throws IOException { 
        return exportListFromExcelZQ(new FileInputStream(file), 
                FilenameUtils.getExtension(file.getName()), sheetNum,startNum); 
    } 
   
    /**
     * 由Excel流的Sheet导出至List (特殊：自定义起始行)
     * 
     * @param is
     * @param extensionName
     * @param sheetNum
     * @return
     * @throws IOException
     */ 
    public static List<String> exportListFromExcelZQ(InputStream is, 
            String extensionName, int sheetNum,int startNum) throws IOException { 
        Workbook workbook = null; 
        if (extensionName.toLowerCase().equals(XLS)) { 
            workbook = new HSSFWorkbook(is); 
        } else if (extensionName.toLowerCase().equals(XLSX)) { 
            workbook = new XSSFWorkbook(is); 
        } 
 
        return exportListFromExcelZQ(workbook, sheetNum, startNum); 
    } 
   
    /**
     * 由指定的Sheet导出至List (特殊：自定义起始行)
     * 
     * @param workbook
     * @param sheetNum
     * @return
     * @throws IOException
     */ 
    private static List<String> exportListFromExcelZQ(Workbook workbook, 
            int sheetNum, int startNum) { 
 
        Sheet sheet = workbook.getSheetAt(sheetNum); 
   
        // 解析公式结果 
        FormulaEvaluator evaluator = workbook.getCreationHelper() 
                .createFormulaEvaluator(); 
 
        List<String> list = new ArrayList<String>(); 
 
        SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
       
//        int minRowIx = sheet.getFirstRowNum();         
        int minRowIx = startNum;
//        int PhysicalNumberOfRows = sheet.getPhysicalNumberOfRows();
        int maxRowIx = sheet.getLastRowNum(); 
        for (int rowIx = minRowIx; rowIx <= maxRowIx; rowIx++) {       
            Row row = sheet.getRow(rowIx); 
            if ((row == null) || isBlankRow(row)) {
                continue;
            }
            StringBuilder sb = new StringBuilder();  
           
            short minColIx = row.getFirstCellNum(); 
            short maxColIx = row.getLastCellNum(); 
            for (short colIx = minColIx; colIx <= maxColIx; colIx++) { 
                Cell cell = row.getCell(new Integer(colIx)); 
                CellValue cellValue = evaluator.evaluate(cell); 
                if (cellValue == null) { 
                 sb.append(SEPARATOR); 
                    continue; 
                } 
//                 System.out.println(cellValue.getCellType());
                // 经过公式解析，最后只存在Boolean、Numeric和String三种数据类型，此外就是Error了 
                // 其余数据类型，根据官方文档，完全可以忽略http://poi.apache.org/spreadsheet/eval.html 
                switch (cellValue.getCellType()) { 
                case Cell.CELL_TYPE_BOOLEAN: 
                    sb.append(SEPARATOR + cellValue.getBooleanValue()); 
                    break; 
                case Cell.CELL_TYPE_NUMERIC: 
                    // 这里的日期类型会被转换为数字类型，需要判别后区分处理 
                    if (DateUtil.isCellDateFormatted(cell)) { 
                        sb.append(SEPARATOR + sdf.format(cell.getDateCellValue())); 
                    } else {
                     DecimalFormat df = new DecimalFormat("0");
                     String value = df.format(cellValue.getNumberValue());
                     if (cellValue.getNumberValue() > Double.parseDouble(value)){
                         value = String.valueOf(cellValue.getNumberValue());
                     }
                        sb.append(SEPARATOR + value);
                    } 
                    break; 
                case Cell.CELL_TYPE_STRING: 
                    sb.append(SEPARATOR + cellValue.getStringValue()); 
                    break; 
                case Cell.CELL_TYPE_FORMULA: 
                    break; 
                case Cell.CELL_TYPE_BLANK: 
//                 sb.append(SEPARATOR); 
                    break; 
                case Cell.CELL_TYPE_ERROR: 
                    break; 
                default: 
                    break; 
                } 
            }
            sb.deleteCharAt(sb.length()-1);
            list.add(sb.toString());
        }          

        return list; 
    } 
   
    /**
     * 根据不同的标识找到合适自己的文件导入上传
     * @param file   文件路劲
     * @param sheetNum  excel的第几个tab
     * @param startNum  开始行数
     * @param sign   用户自定义标识根据不同的需要满足自己的需求
     * @return
     * @throws IOException
     */
    public static List<String> exportListFromExcelNew(File file, int sheetNum, int startNum, String sign) throws IOException{
     
      String extensionName = FilenameUtils.getExtension(file.getName());
      InputStream is =  new FileInputStream(file);
      Workbook workbook = null; 
         if (extensionName.toLowerCase().equals(XLS)) { 
             workbook = new HSSFWorkbook(is); 
         } else if (extensionName.toLowerCase().equals(XLSX)) { 
             workbook = new XSSFWorkbook(is); 
         } 
          Sheet sheet = workbook.getSheetAt(sheetNum); 
          // 解析公式结果 
          FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); 
   
          List<String> list = new ArrayList<String>(); 
   
          SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
         
          int minRowIx = startNum;
          int maxRowIx = sheet.getLastRowNum(); 
          for (int rowIx = minRowIx; rowIx <= maxRowIx; rowIx++) {       
              Row row = sheet.getRow(rowIx); 
              if ((row == null) || isBlankRow(row)) {
                  continue;
              }
              StringBuilder sb = new StringBuilder();  
             
              short minColIx = row.getFirstCellNum(); 
              short maxColIx = row.getLastCellNum(); 
              for (short colIx = minColIx; colIx <= maxColIx; colIx++) { 
                  Cell cell = row.getCell(new Integer(colIx)); 
                  CellValue cellValue = evaluator.evaluate(cell);
                  if (cellValue == null) { 
                   sb.append(SEPARATOR); 
                      continue; 
                  } 
                  // 经过公式解析，最后只存在Boolean、Numeric和String三种数据类型，此外就是Error了 
                  // 其余数据类型，根据官方文档，完全可以忽略http://poi.apache.org/spreadsheet/eval.html 
                  switch (cellValue.getCellType()) { 
                  case Cell.CELL_TYPE_BOOLEAN: 
                      sb.append(SEPARATOR + cellValue.getBooleanValue()); 
                      break; 
                  case Cell.CELL_TYPE_NUMERIC: 
                      // 这里的日期类型会被转换为数字类型，需要判别后区分处理 
                      if (DateUtil.isCellDateFormatted(cell)) { 
                          sb.append(SEPARATOR + sdf.format(cell.getDateCellValue())); 
                      } else {
                  if ("contractUpload".equals(sign)) {
                   Row row0 = sheet.getRow(0);
                   Cell cell0 = row0.getCell(new Integer(colIx)); 
                   if(String.valueOf(cell0).contains("合同")){
                    sb.append(SEPARATOR + cellValue.getNumberValue());
                   }else {
                    DecimalFormat df = new DecimalFormat("0");
                          String value = df.format(cellValue.getNumberValue());
                          sb.append(SEPARATOR + value);
       }
      }else {
       DecimalFormat df = new DecimalFormat("0");
                      String value = df.format(cellValue.getNumberValue());
                      sb.append(SEPARATOR + value);
      }
                      } 
                      break; 
                  case Cell.CELL_TYPE_STRING: 
                      sb.append(SEPARATOR + cellValue.getStringValue()); 
                      break; 
                  case Cell.CELL_TYPE_FORMULA: 
                      break; 
                  case Cell.CELL_TYPE_BLANK: 
                      break; 
                  case Cell.CELL_TYPE_ERROR: 
                      break; 
                  default: 
                      break; 
                  } 
              }
              sb.deleteCharAt(sb.length()-1);
              list.add(sb.toString());
          }          

          return list;
 }
   
   
   
   
    /**
     * 判断excel中是否存在有空格的无效数据
     * @param row
     * @return
     */
    public static boolean isBlankRow(Row row){
        if(row == null) {
         return true;
        }
        boolean result = true;
        for(int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++){
            Cell cell = row.getCell(i, Row.RETURN_BLANK_AS_NULL);
            String value = "";
            if(cell != null){
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    value = cell.getStringCellValue();
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    value = String.valueOf((int) cell.getNumericCellValue());
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    value = String.valueOf(cell.getBooleanCellValue());
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    value = String.valueOf(cell.getCellFormula());
                    break;
                //case Cell.CELL_TYPE_BLANK:
                //    break;
                default:
                    break;
                }
                
                if(!value.trim().equals("")){
                    result = false;
                    break;
                }
            }
        }      
        return result;
    }
   
    public static void main(String[] args) {
     String path = "D:/2016_02_29_15_19_20.xls"; 
        List<String> list = null; 
        try { 
            list = exportListFromExcel(new File(path), 0);
            for(int i=0;i<list.size();i++){
             System.out.println(list.get(i));
            }
        } catch (IOException e) {
         e.printStackTrace();
        } 
 }
   
}