springboot中关于Excel的三种读取方式

昨天看到了alibaba的EasyExcel就想写一下我常用的三种方式做一个小小的对比

首先是之前项目中用到过的，我们公司自己写的工具类，

使用方式
controller层

/**
     * 导入exceli
     *
     * @param request 请求
     * @return 导入结果
     */
    @RequestMapping(value = "/importExcel", method = RequestMethod.POST)
    @RequiresPermissions("roadHorizontalAlignment:roadhorizontalalignment:importExcel")
    @ResponseBody
    public JSONObject importExcel(HttpServletRequest request, HttpServletResponse response) {
        //正常导入提示
        JSONObject json = new JSONObject();
        try{
            MultipartHttpServletRequest multipartHttpServletRequest = (MultipartHttpServletRequest) request;
            MultipartFile multipartFile = multipartHttpServletRequest.getFile("file");
            if (multipartFile.isEmpty()) {
                json.put("msg", "文件不存在");
            } else {
                json.putAll(monthlTrafficVolumeServiceImpl.analysisExcel(multipartFile));
            }
        } catch (Exception e){
            e.printStackTrace();
            throw new RRException(e.getMessage(),500);
        }
        return json;
    }

serviceImpl层的

/**
     * 分析导入的excel
     * @param multipartFile
     * @return
     */
    @Override
    @Transactional
    public JSONObject analysisExcel(MultipartFile multipartFile){
        JSONObject resObject = new JSONObject();
        //excel数据
        List<List<List<Object>>> allDataList = null;//list中list中list
        /**
         * 其中来说第一层是页，第二层数据航，第三层是具体的数据
         */
        try {
            InputStream inputStream = multipartFile.getInputStream();
            String xls = "xls";
            String xlsx = "xlsx";
            //excel2007以前版本
            ImportExcelForXlsUtil importXls = new ImportExcelForXlsUtil();
            //excel2007及以后版本
            ImportExcelForXlsxUtil importXlsx = new ImportExcelForXlsxUtil();
            // 判断导入的excel版本
            if (multipartFile.getOriginalFilename().endsWith(xls)) {
                allDataList = importXls.getListByExcel(inputStream, multipartFile.getOriginalFilename());
            } else if (multipartFile.getOriginalFilename().endsWith(xlsx)) {
                allDataList = importXlsx.getBankListByExcel(inputStream, multipartFile.getOriginalFilename());
            }


            //遍历每个sheet页
            for(int j = 0;j < allDataList.size();j++){
               List<List<Object>> dataList = allDataList.get(j);//每一个页面上的所有的数据
                StringBuilder headErrorMsg = new StringBuilder();//书写错误类型
                boolean headFlag = true;
                if (dataList.size() < 2) {
                    headErrorMsg.append("导入的数据不能为空");
                    headFlag = false;
                } else {
                    /*判断表头是否一一对应*/
                    //第一行
                    List header = dataList.get(0);
                    String[] tableHeader = {"年份","月份","观测站编号","观测站名称","站点桩号","起点桩号","止点桩号","观测里程","起点名称","止点名称","车道数量",
                            "路线业务编号","行驶方向","中小客流量","大客车流量","小货车流量",
                            "中货车流量","大货车流量","特大货流量","集装箱流量",
                            "客车流量","货车流量","汽车流量","客车当量","货车当量","汽车当量"};
                    //表头对应
                    String[] dataColumn = {"","","","","","","","","","","","routeBusinessNo","directionOfTravel",
                            "smallAndMediumPassengerFlow","busFlow","pickupTruckFlow","mediumTruckFlow","largeTruckFlow",
                            "largeCargoFlow","containerFlow","passengerFlow","truckFlow","vehicleFlow","busEquivalent","truckEquivalent","carEquivalent"};
                   //对于表头的文件的判断
                    if(headFlag){
                        if(header.size() == tableHeader.length){
                            for(int i = 0; i < tableHeader.length; i++){
                                if(!StringUtils.equals(tableHeader[i],header.get(i).toString())){
                                    headErrorMsg.append("第一行第" + i + "列与导入模板不一致;");
                                    headFlag = false;
                                }
                            }
                        } else {
                            headErrorMsg.append("第一行与导入模板不一致;");
                            headFlag = false;
                        }
                    }
                    String year = "";
                    String month = "";
                    //完成一个list存放我们的对象
                    List<MonthTrafficVolumeEntity> stationTrafficList = new ArrayList<>();

                    //如果表头对应, 开始对用户填入的数据进行校验
                    if(headFlag){
                        //拿到所有的观察点
                        List<StationsEntity> stationsEntitys = stationsDao.selectList(null);
                        for(int i = 1; i < dataList.size() ; i++){
                            List dataRow = dataList.get(i);//拿到数据
                ···}
                if(resObject.size() == 0){
                resObject.put("msg","导入成功");
                resObject.put("code",200);
            } else {
                throw new Exception(resObject.toString());
            }
            return resObject;
        } catch (Exception e){
            e.printStackTrace();
            throw new RRException(e.getMessage(),500);
        }

    }
                

具体的工具类

public class ImportExcelForXlsUtil {
    /**
     * 记录日志
     */
    public static final Logger logger = LoggerFactory.getLogger(ImportExcelForXlsUtil.class);
    /**
     * 验证是否是科学计数法
     */
    String pattern = "^(?!0\\d)\\d+(\\.\\d{1,})?E-\\d+$\n";
    /**
     * 运算方法
     */
    BigDecimal bd1 = null;

    /**
     * 解析excel为list的方法
     *
     * @param in       输入流
     * @param fileName 文件名
     * @return 解析后的list
     */
    public List<List<List<Object>>> getListByExcel(InputStream in, String fileName) {
        List<List<List<Object>>> reslist = new ArrayList<>();
        try {
            Workbook work = this.getWorkBook(in, fileName);
            FormulaEvaluator evaluator = work.getCreationHelper().createFormulaEvaluator();
            Sheet sheet = null;
            Row row = null;
            Cell cell = null;
            //标题长度初始化
            int headerLength = 0;
            for (int i = 0; i < work.getNumberOfSheets(); i++) {
                List<List<Object>> list = new ArrayList<List<Object>>();
                //EXCEL文件可能有多个sheet,但是存放数据的一般是sheet1,其他sheet例如码表可以不读去
//                if (i != 0) {
//                    break;
//                }
                sheet = work.getSheetAt(i);
                if (sheet == null ||  sheet.getSheetName().equals("hidden")) {
                    continue;
                }
                for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
                    row = sheet.getRow(j);
//                    if (j == 0)  {

//                    }
                    if (row == null) {
                        continue;
                    }
                    //headerLength = row.getPhysicalNumberOfCells();
                    headerLength = row.getLastCellNum();
                    List<Object> li = new ArrayList<Object>();
                    for (int y = 0; y < headerLength; y++) {
                        cell = row.getCell(y);
                        if (cell == null) {
                            li.add("");
                        } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                            evaluator.evaluateFormulaCell(cell);
                            String result = String.valueOf(cell.getNumericCellValue());
                            //如果是科学计数法
                            if (result.toString().toUpperCase().indexOf("E") > 0 && !result.toString().toUpperCase().contains("ESAL") && !checkChinese(result.toString())){
                                bd1 = new BigDecimal(result);
                                li.add(bd1.setScale(2, BigDecimal.ROUND_HALF_UP).toPlainString());
                            } else {
                                li.add(this.getCellValue(cell));
                            }
                        } else {
                            Object o = this.getCellValue(cell);
                            //如果是科学计数法
                            if (o.toString().toUpperCase().indexOf("E") > 0 && !o.toString().toUpperCase().contains("ESAL") && !checkChinese(o.toString())) {
                                bd1 = new BigDecimal(o.toString());
                                li.add(bd1.setScale(2, BigDecimal.ROUND_HALF_UP).toPlainString());
                            } else {
                                li.add(o);
                            }
                        }
                    }
                    //加判断
                    if(li.stream().filter(p->!(p == null || "".equals(p.toString()))).collect(Collectors.toList()).size() != 0){
                        list.add(li);
                    }
                }
                reslist.add(list);
            }
        } catch (IOException e) {
            e.printStackTrace();
            logger.error("创建excel为空" + e.getMessage());
        } catch (NullPointerException e) {
            e.printStackTrace();
            logger.error("创建excel为空" + e.getMessage());
        }
        return reslist;
    }
}

实话实说，在不考虑性能的情况下是真的好用，真的贼好用但是性能上直接gg，万条数据感觉就会很难受

第二种是公司在修改之后制作的

controller层

@PostMapping("/importExcel")
    @ApiOperation("excel导入")
    public Result<List<Map<String, Object>>> importExcel(MultipartFile uploadFile) {
        List<Map<String, Object>> result = new ArrayList<>();
        String flag = "1";
        try {
            InputStream inputStream = uploadFile.getInputStream();
            flag = "2";
            if (inputStream != null) {
                result = ImportExcel.importExcel(inputStream, ReimConstants.EXCEL_HEAD, ReimConstants.EXCEL_HEAD_ALIAS);
                System.out.println("result : " + result);
            } else {
                return null;
            }
            // InputStream inputStream=new FileInputStream(new File("C:\\Users\\strugglingjing\\Desktop\\新建 Microsoft Excel 工作表.xlsx"));
        } catch (FileNotFoundException e) {
            logger.error(getClass() + "文件不存在---{}",e);
            return Result.badRequest("文件不存在",500);
        } catch (IOException io) {
            logger.error(getClass() + "文件错误---{}",io);
            return Result.badRequest("文件错误",500);
        } catch (Exception e) {
            e.printStackTrace();
            logger.error(getClass() + "模板格式错误---{}",e);
            return Result.badRequest("模板格式错误",500);
        }
        return Result.ok(result);
    }

工具类

/**
         * 读取excel表格内容返回List<Map>
         * @param inputStream  excel文件流
         * @param head         表头数组
         * @param headerAlias  表头别名数组
         * @return
         */
        public static List<Map<String,Object>> importExcel(InputStream inputStream, String[] head, String[] headerAlias) throws Exception{
            ExcelReader reader = ExcelUtil.getReader(inputStream);
            List<Object> header=reader.readRow(0);
            //替换表头关键字
            if(ArrayUtils.isEmpty(head)|| ArrayUtils.isEmpty(headerAlias)||head.length!=headerAlias.length){
                return null;
            }else{
                for(int i=0;i<head.length;i++){
                    if(head[i].equals(header.get(i))){
                        reader.addHeaderAlias(head[i],headerAlias[i]);
                    }else{
                        return null;
                    }

                }
            }
            //读取指点行开始的表数据（以下介绍的三个参数也可以使用动态传入，根据个人业务情况修改）
            //1：表头所在行数  2：数据开始读取位置   Integer.MAX_VALUE:数据读取结束行位置
            List<Map<String,Object>> read = reader.read(0,1,Integer.MAX_VALUE);
            return read;
        }

实话实说简化确实是简化了性能也有了提升，但是没有办法对于传出的格式进行一个手动的处理，不是很方便，处理的话需要自己额外进行判断格式是否正确

第三种，alibaba的EasyExcel

首先需要导入依赖

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.7</version>
        </dependency>

其实是因为各种原因没有导进去，然后使用idea强制导的，idea真香，

然后在这里需要写一个监听

package com.qzsoft.reim.returndepositbill.dto;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.qzsoft.reim.returndepositbill.service.impl.ReimReturnDepositBillServiceImpl;

import java.util.ArrayList;
import java.util.List;

public class StudentListener extends AnalysisEventListener<ReimReturnDepositBillDetailDTO> {

    //最多一次写入多少条
    private static final int BATCH_COUNT = 100;
    // dto的集合
    private List<ReimReturnDepositBillDetailDTO> list = new ArrayList<>();

    private ReimReturnDepositBillServiceImpl studentBiz;
    
    // 构造器
    public StudentListener(){
        studentBiz = new ReimReturnDepositBillServiceImpl();
    }

    // 构造器
    public StudentListener(ReimReturnDepositBillServiceImpl studentBiz){
        this.studentBiz = studentBiz;
    }

    //读取数据的时候会执行invoke方法
    @Override
    public void invoke(ReimReturnDepositBillDetailDTO student, AnalysisContext analysisContext) {
        list.add(student);
        System.out.println(student);
        this.studentBiz.test();
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
//        if(list.size() >= BATCH_COUNT){
//            this.studentBiz.saveListStu(list);
//            list.clear();           //清空list
//        }
    }

    //所有的数据解析完成之后都会来调用
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
//        this.studentBiz.saveListStu(list); //确保最后遗留的数据保存在数据库中
        this.studentBiz.test();
    }


}

具体的dto视情况而定

controller层

    @PostMapping("/importExcel")
    @ApiOperation("excel导入")
    public Result<List<Map<String, Object>>> importExcel(MultipartFile uploadFile)  {
        try{
            InputStream is = uploadFile.getInputStream();
            EasyExcel.read(is, ReimReturnDepositBillDetailDTO.class, new StudentListener(reimReturnDepositBillServiceImpl)).sheet().doRead();
        }
        catch (Exception e){
            System.out.println(e);
        }
        return Result.ok(null);
    }

这是三种读取Excel的方式，总结一下就是是用的复杂就不需要读取的时候在使用其他的东西，简单需要自己写方法去处理数据，当然也是我们推荐的