JAVA 读取excel文件成List＜Entity＞

package com.fsinfo.common.utils;

import com.fsinfo.modules.enterprise.entity.EnterpriseRecordEntity;
import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;

import java.util.Date;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

    /**
     * @Author leijiaxuan　　　* @Email 897953910@qq.com
     * @Date 9:50 2020/7/22
     **/
    public class ExcelInsert {

// private static Logger logger = Logger.getLogger(ExcelReader.class.getName()); // 日志打印类

        private static final String XLS = "xls";
        private static final String XLSX = "xlsx";

        /**
         * 根据文件后缀名类型获取对应的工作簿对象
         * @param inputStream 读取文件的输入流
         * @param fileType 文件后缀名类型（xls或xlsx）
         * @return 包含文件数据的工作簿对象
         * @throws IOException
         */
        public static Workbook getWorkbook(InputStream inputStream, String fileType) throws IOException {
            Workbook workbook = null;
            if (fileType.equalsIgnoreCase(XLS)) {
                workbook = new HSSFWorkbook(inputStream);
            } else if (fileType.equalsIgnoreCase(XLSX)) {
                workbook = new XSSFWorkbook(inputStream);
            }
            return workbook;
        }

        /**
         * 读取Excel文件内容
         * @param myfile 要读取的Excel文件流
         * @return 读取结果列表，读取失败时返回null
         */
        public static List<EnterpriseRecordEntity> readExcel(MultipartFile myfile) {

            Workbook workbook = null;
            FileInputStream inputStream = null;

            try {
                // 获取Excel后缀名
                String fileName=myfile.getOriginalFilename();
                String fileType = fileName.substring(fileName.lastIndexOf(".") + 1, fileName.length());
                // // 获取Excel文件
                // File excelFile = new File(fileName);
                // if (!excelFile.exists()) {
                 logger.warning("指定的Excel文件不存在！");
                // return null;
                // }

                File file = new File(myfile.getOriginalFilename());
                FileUtils.copyInputStreamToFile(myfile.getInputStream(), file);
                // 获取Excel工作簿
                inputStream = new FileInputStream(file);
                workbook = getWorkbook(inputStream, fileType);
                // 会在本地产生临时文件，用完后需要删除
                if (file.exists()) {
                    file.delete();
                }

                // 读取excel中的数据
                List<EnterpriseRecordEntity> resultDataList = parseExcel(workbook);

                return resultDataList;
            } catch (Exception e) {
                // logger.warning("解析Excel失败，文件名：" + fileName + " 错误信息：" + e.getMessage());
                return null;
            } finally {
                try {
                    if (null != workbook) {
                        workbook.close();
                    }
                    if (null != inputStream) {
                        inputStream.close();
                    }
                } catch (Exception e) {
                    // logger.warning("关闭数据流出错！错误信息：" + e.getMessage());
                    return null;
                }
            }
        }

        /**
         * 解析Excel数据
         * @param workbook Excel工作簿对象
         * @return 解析结果
         */
        private static List<EnterpriseRecordEntity> parseExcel(Workbook workbook) {
            List<EnterpriseRecordEntity> resultDataList = new ArrayList<>();
            // 解析sheet
            for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
                Sheet sheet = workbook.getSheetAt(sheetNum);

                // 校验sheet是否合法
                if (sheet == null) {
                    continue;
                }
                
                // 获取第一行数据
                int firstRowNum = sheet.getFirstRowNum();
                Row firstRow = sheet.getRow(firstRowNum);
                if (null == firstRow) {
                // logger.warning("解析Excel失败，在第一行没有读取到任何数据！");
                }

                // 解析每一行的数据，构造数据对象
                int rowStart = firstRowNum + 1;
                int rowEnd = sheet.getPhysicalNumberOfRows();
                for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
                    Row row = sheet.getRow(rowNum);

                    if (null == row) {
                        continue;
                    }

                    EnterpriseRecordEntity resultData = convertRowToData(row);
                    if (null == resultData) {
                    // logger.warning("第 " + row.getRowNum() + "行数据不合法，已忽略！");
                        continue;
                    }
                    resultDataList.add(resultData);
                }
            }

            return resultDataList;
        }

        /**
         * 将单元格内容转换为字符串
         * @param cell
         * @return
         */
        private static String convertCellValueToString(Cell cell) {
            if(cell==null){
                return null;
            }
            String returnValue = null;
            switch (cell.getCellType()) {
                case 0: //数字
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
                        SimpleDateFormat sdf = null;
                        if (cell.getCellStyle().getDataFormat() == HSSFDataFormat
                                .getBuiltinFormat("h:mm")) {
                            sdf = new SimpleDateFormat("HH:mm");
                        } else {// 日期
                            sdf = new SimpleDateFormat("yyyy-MM-dd");
                        }
                        Date date = cell.getDateCellValue();
                        returnValue = sdf.format(date);
                    } else if (cell.getCellStyle().getDataFormat() == 58) {
                        // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                        double value = cell.getNumericCellValue();
                        Date date = org.apache.poi.ss.usermodel.DateUtil
                                .getJavaDate(value);
                        returnValue = sdf.format(date);
                    } else {
                        double value = cell.getNumericCellValue();
                        CellStyle style = cell.getCellStyle();
                        DecimalFormat format = new DecimalFormat();
                        String temp = style.getDataFormatString();
                        // 单元格设置成常规
                        if (temp.equals("General")) {
                            format.applyPattern("#");
                        }
                        returnValue = format.format(value);
                    }
                    break;
                case 1: //字符串
                    returnValue = cell.getStringCellValue();
                    break;
                case 4: //布尔
                    Boolean booleanValue = cell.getBooleanCellValue();
                    returnValue = booleanValue.toString();
                    break;
                case 2: // 空值
                    break;
                case 7: // 公式
                    returnValue = cell.getCellFormula();
                    break;
                case 64: // 故障
                    break;
                default:
                    break;
            }
            return returnValue;
        }

        /**
         * 提取每一行中需要的数据，构造成为一个结果数据对象
         *
         * 当该行中有单元格的数据为空或不合法时，忽略该行的数据
         *
         * @param row 行数据
         * @return 解析后的行数据对象，行数据错误时返回null
         */
        private static EnterpriseRecordEntity convertRowToData(Row row) {
            EnterpriseRecordEntity resultData = new EnterpriseRecordEntity();

            Cell cell;
            int cellNum = 0;
            // 获取记录日期
            cell = row.getCell(cellNum++);
            String recorddate = convertCellValueToString(cell);
            resultData.setRecorddate(java.sql.Date.valueOf(recorddate));
            // 获取企业名称
            cell = row.getCell(cellNum++);
            String enterpriseuuid = convertCellValueToString(cell);
            resultData.setEnterpriseuuid(enterpriseuuid);
                // 获取企业反映问题
            cell = row.getCell(cellNum++);
            String recordcontext = convertCellValueToString(cell);
            resultData.setRecordcontext(recordcontext);
            // 获取解决情况
            cell = row.getCell(cellNum++);
            String handlinfo = convertCellValueToString(cell);
            resultData.setHandlinfo(handlinfo);
            // 获取备注
            cell = row.getCell(cellNum++);
            String remarks = convertCellValueToString(cell);
            resultData.setRemarks(remarks);

            return resultData;
        }
    }