poi读取Excel文件的数据的工具类

import org.apache.commons.lang.time.DateFormatUtils;
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.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * poi读取Excel文件的数据
 */
public class ReadExcelData {


    private static final String EXCEL_XLS = "xls";
    private static final String EXCEL_XLSX = "xlsx";

    /**
     * 判断版本获取Wordboook
     * @param in
     * @param fileName
     * @return
     * @throws IOException
     */
    private static Workbook getWorkbook(InputStream in, String fileName) throws IOException {
        Workbook wbook = null;
        if (fileName.endsWith(EXCEL_XLS)) {
            wbook = new HSSFWorkbook(in);
        } else if (fileName.endsWith(EXCEL_XLSX)) {
            wbook = new XSSFWorkbook(in);
        }
        return wbook;
    }

    /**
     * 解析Excel，获取文件数据
     * @param mfile
     * @param fileName
     * @return Excel第一个Sheet的数据以List<List<String>>形式返回
     */
    public static List<List<String>> getExcelData(MultipartFile mfile, String fileName) {
        List<List<String>> lists = new ArrayList<List<String>>();
        try {
            Workbook workbook = getWorkbook(mfile.getInputStream(), fileName);
            //获取Sheet的数量
            int sheetCount = workbook.getNumberOfSheets();
            // 第一个Sheet
            Sheet sheet = workbook.getSheetAt(0);
            //表头
            Row rowHead = sheet.getRow(0);
            //总列数
            int columns = rowHead.getPhysicalNumberOfCells();
            //总行数
            int lines = sheet.getPhysicalNumberOfRows();
            //循环获取每行数据
            for (int i = 0; i < lines; i++) {
                //循环获取每列
                List<String> list = new ArrayList<String>();
                for (int j = 0; j < columns; j++) {
                    if (sheet.getRow(i).getCell(j) != null){
                        Cell cell = sheet.getRow(i).getCell(j);
                        Object obj = getValue(cell);
                        list.add(String.valueOf(obj));
                    }else {
                        list.add(null);
                    }
                }
                lists.add(list);

            }
        }catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }


        return lists;
    }

    /**
     * 根据对应的单元格的数据类型设定值（这里写成了除了日期就是字符串的形式）
     * @param cell
     * @return
     */
    private static Object getValue(Cell cell) {
        Object obj = null;
//        cell.setCellType(1);
        switch (cell.getCellTypeEnum()) {
            case BOOLEAN:
                obj = cell.getBooleanCellValue();
                break;
            case ERROR:
                obj = cell.getErrorCellValue();
                break;
            case NUMERIC:
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    Date date = cell.getDateCellValue();
                    obj = DateFormatUtils.format(date, "yyyy-MM-dd");
                }else{
                    cell.setCellType(XSSFCell.CELL_TYPE_STRING);
                    obj = cell.getStringCellValue();
                }
                break;
            case STRING:
                obj = cell.getStringCellValue();
                break;
            default:
                break;
        }
        return obj;
    }

}