将Excel文件一键解析为list对象集合

调用代码：

@Override
public List<VirtualSettleAccountExcelData> importExcel(String accountNo, MultipartFile file) {
    List<VirtualSettleAccountExcelData> virtualSettleAccountExcelDataList=null;
    try {
        String name = file.getOriginalFilename();
        if (!name.contains(".xls") && !name.contains(".xlsx")) {
            throw new Exception("文件格式不正确");
        }
        //导入的字段以及对应的值
        Map<String, String> titleMap = new HashMap();
        titleMap.put("订单编号", "virtualAccountAppId");
        titleMap.put("新网客户虚户", "virtualAccountNo");
        //要校验必填的数据
        Map<String, String> checkMap = new HashMap();
        checkMap.put("virtualAccountAppId","订单编号");
        checkMap.put("virtualAccountNo","新网客户虚户");

        virtualSettleAccountExcelDataList= ExcelUtil.excelToDataList
                (file.getInputStream(),name,VirtualSettleAccountExcelData.class,titleMap,checkMap);
    } catch (Exception ex) {
        logger.warn("解析虚户数据 error" ,ex.getMessage(), ex);
    }
    return virtualSettleAccountExcelDataList;
}

ExcelUtil封装类代码：

package geex.fin.acc.utils;

import com.google.common.collect.Lists;
import jxl.read.biff.BiffException;
import net.sf.json.JSONObject;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;


import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.*;


public class ExcelUtil {

    private static final Logger LOGGER = LoggerFactory.getLogger(ExcelUtil.class);

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

    /**
     *
     * @param row          excel中的某一行
     * @param list         excel一行数据对应的MerchantImportReq属性值顺序存放在list中
     * @param firstCellNum 开始列
     * @param lastCellNum  结束列
     * @return
     * @throws Exception
     */
    private static <T> T excelTOData(Row row, List<String> list, Integer firstCellNum, Integer lastCellNum,T t) throws Exception {
        Class clazz = t.getClass();
        int i = 0;
        for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) {
            if(i<list.size()) {
                Cell cell = row.getCell(cellNum);
                Field field = clazz.getDeclaredField(list.get(i++));
                field.setAccessible(true);
                String cellValue = getCellValue(cell,cellNum);
                field.set(t, cellValue);
            }
        }
        return t;
    }



    /**
     * 获取excel单元格的值
     *
     * @param cell 单元格
     * @return value值
     * @throws Exception
     */
    public static String getCellValue(Cell cell,int cellNum) throws Exception {
        String cellValue = null;
        if (cell == null) {
            return cellValue;
        }
        //把数字当成String来读，避免出现1读成1.0的情况
        if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            if (cell.getCellStyle().getDataFormatString().indexOf("%") != -1) {
                Double numValue=cell.getNumericCellValue()*100;
                String dValue = String.format("%.2f", numValue);
                cellValue = dValue+ "%";
                cell.setCellValue(cellValue);//此处因为我要将错误数据导出，所以把值写回到列中。
            } else if(!HSSFDateUtil.isCellDateFormatted(cell)){
                cell.setCellType(Cell.CELL_TYPE_STRING);
            }else {
                cellValue = cell.toString();
            }
        }
        if(cell.getCellType()==Cell.CELL_TYPE_FORMULA){
            int cellNu=cellNum+1;
            throw new Exception("第"+cellNu+"列格式不正确，不支持公式数据读取");
        }
        //判断数据的类型
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC: //数字
                if (HSSFDateUtil.isCellDateFormatted(cell)){
                    Date date = cell.getDateCellValue();
                    //格式转换
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    String format = sdf.format(date);
                    System.out.println(format);
                    return format;
                }
                cellValue = String.valueOf(cell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_STRING: //字符串
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case Cell.CELL_TYPE_BOOLEAN: //Boolean
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA: //公式
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            case Cell.CELL_TYPE_BLANK: //空值
                break;
            default:
                throw new Exception("存在非法格式的单元格"); //其他类型报错
        }
        return cellValue;

    }


    public static String getCellValue(Cell cell) throws Exception {
        String cellValue = null;
        if (cell == null) {
            return cellValue;
        }
        //把数字当成String来读，避免出现1读成1.0的情况
        if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            cell.setCellType(Cell.CELL_TYPE_STRING);
        }
        //判断数据的类型
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC: //数字
                cellValue = String.valueOf(cell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_STRING: //字符串
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case Cell.CELL_TYPE_BOOLEAN: //Boolean
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA: //公式
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            case Cell.CELL_TYPE_BLANK: //空值
                break;
            default:
                throw new Exception("存在非法格式的单元格"); //其他类型报错
        }
        return cellValue;
    }


    /**
     * 根据文件后缀名获取 workbook
     *
     * @param is       文件流
     * @param fileName 文件名称
     * @return workbook
     * @throws IOException
     * @throws BiffException
     */
    public static Workbook getWorkBook(InputStream is, String fileName) throws Exception {
        Workbook workbook = null;
        try {
            //根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
            if (fileName.endsWith(xls)) {
                workbook = new HSSFWorkbook(is);
            } else if (fileName.endsWith(xlsx)) {
                workbook = new XSSFWorkbook(is);
            }
            return workbook;
        }catch (IOException ex){
            throw new Exception("生成文件异常IO"+ex.getLocalizedMessage());
        }catch (Exception ex){
            throw new Exception("生成文件异常");
        }finally {
            if(null!=workbook) {
                workbook.close();
            }
        }
    }


    /**
     *
     * @param inputStream 文件
     * @param name 文件名称
     * @param t 传入得对象
     * @param titleMap 文件读取的数据
     * @param checkMap 必填值校验
     * @param <T> 返回的对象
     * @return
     * @throws Exception
     */
    public static <T> List<T> excelToDataList(InputStream inputStream,
                                                          String name,
                                                          Class<T> t,
                                                          Map<String, String> titleMap,
                                                          Map<String, String> checkMap) throws Exception {
        List<T> lists = new ArrayList<>();
        Workbook wb = null;
        try {
            //根据文件后缀,获取worebook
            wb = getWorkBook(inputStream, name);
            if (wb == null) {
                throw new Exception("创建工作簿失败");
            }
            Sheet sheet = wb.getSheetAt(0);
            //获得当前sheet的开始行
            int firstRowNum = sheet.getFirstRowNum();
            //获得当前sheet的结束行
            int lastRowNum = sheet.getLastRowNum();
            //第一行 title标题行
            Row fristRow = sheet.getRow(firstRowNum);

            //获得第一行的开始列
            int firstCellNum = fristRow.getFirstCellNum();
            //获得第一行的列数
            int lastCellNum = fristRow.getPhysicalNumberOfCells();

            //excel中每列数据在MerchantImportReq属性的顺序
            List<String> keyLists = new ArrayList<>();
            for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) {
                Cell cell = fristRow.getCell(cellNum);
                if (titleMap.size() == 0) {
                    break;
                }
                for (Map.Entry<String, String> entry : titleMap.entrySet()) {
                    String key = entry.getKey();
                    String cellValue = getCellValue(cell);
                    if (null != cellValue) {
                        if (cellValue.trim().equals(key)) {
                            keyLists.add(titleMap.get(key));
                            titleMap.remove(key);
                            break;
                        }
                    }
                }
            }
            //判断是否缺少信息
            if (lastCellNum < keyLists.size()) {
                throw new Exception("文件信息不全，缺少列");
            }
            //循环除了第一行的所有行  除了title行剩下的都为需要处理的数据
            for (int rowNum = firstRowNum + 1; rowNum <= lastRowNum; rowNum++) {
                //获得当前行
                Row row = sheet.getRow(rowNum);
                if (row == null) {
                    continue;
                }
                T newT = t.newInstance();
                //获取FundCostInfoPageResponse对象并放进list中
                newT = excelTOData(row, keyLists, firstCellNum, lastCellNum, newT);
                boolean rowNumFlag=false;
                for (Field f : newT.getClass().getDeclaredFields()) {
                    f.setAccessible(true);
                    if (f.get(newT) != null && StringUtils.isNotBlank((f.get(newT).toString()))) {
                        rowNumFlag=true;
                        break;
                    }

                }
                if (null != newT&&rowNumFlag) {
                    JSONObject jsonObject = JSONObject.fromObject(newT);
                    if (!checkObjAllFieldsIsNull(jsonObject, checkMap)) {
                        lists.add(newT);
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception("资金成本汇总数据解析异常 :" + e.getMessage());
        }
        return lists;
    }



    /**
     * 判断对象为空 或者 属性是否全部为空
     *
     * @param
     * @return
     */
    public static boolean checkObjAllFieldsIsNull(JSONObject jsonObject, Map<String, String> checkMap) throws Exception{
        boolean flag = false;
        if (null == jsonObject) {
            return true;
        }
        for (Map.Entry<String, String> entry : checkMap.entrySet()) {
            String key = entry.getKey();
            String value = entry.getValue();
            String jsonValue=jsonObject.getString(key);
            if (StringUtils.isEmpty(jsonValue)) {
                throw new Exception("必填项【" + value + "】字段不能为空！");
            }
            boolean dateFlag=true;
            if(key.equals("startDate")||key.equals("endDate")){
                dateFlag=DateUtils.isLegalDate(jsonValue.length(),jsonValue,"yyyy-MM-dd");
            }
            if(!dateFlag){
                throw new Exception("【" + value + "】字段格式错误！");
            }

        }
        return flag;
    }



}