前言

想自己写一个可以很方便的在工作中使用的配置多sheet页，复杂表头之类的导出导入工具类，第一版比较简单，还得受限制于实体类的配置，将来在第二第三版，或者更多版本中考虑实现一下全配置全自动导入导出。如果电脑屏幕前的你恰好能看到这篇文章，又恰好有些想法，欢迎评论区留言~

PS：请结合EasyExcel文档食用哦

导出

public static void writeExcel(HttpServletResponse response, List<List> dataList, List<Class> clazzList, List<String> sheetNames, String fileName) {
        //检查数据是否为空
        if (dataList.isEmpty() || sheetNames.isEmpty() || clazzList.isEmpty()) {
            throw new RuntimeException("数据为空,请检查");
        }
        //检查数据页是否一致
        if (dataList.size() != sheetNames.size() || dataList.size() != clazzList.size() || sheetNames.size() != clazzList.size()) {
            throw new RuntimeException("数据个数不一致,请检查");
        }
        ExcelWriter excelWriter = null;
        try {
            response.setHeader("content-type", "application/octet-stream");
            response.setContentType("application/octet-stream");
            response.setHeader("Content-Disposition", "attachment; filename=" + fileName);
            ServletOutputStream os = response.getOutputStream();
            excelWriter = EasyExcel.write(os).build();
            for (int i = 0; i < dataList.size(); i++) {
                String sheetName = sheetNames.get(i);
                //导出的实体类型
                Class clazz = clazzList.get(i);
                //数据集合
                List data = dataList.get(i);
                WriteSheet writeSheet = EasyExcel.writerSheet(i, sheetName).head(clazz).build();
                excelWriter.write(data, writeSheet);
            }
        } catch (Exception e) {
            log.error("导出excel异常", e);
        }finally{
            // 千万别忘记finish 会帮忙关闭流
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
    }

导入

public static Map<String, Object> readExcel(MultipartFile file, List<Class> clazzList) {
        ExcelReader excelReader = null;
        Map<String, Object> resultMap = new HashMap<>();
        try {
            excelReader = EasyExcel.read(file.getInputStream()).build();
            //获取sheet页
            List<ReadSheet> sheetList = excelReader.excelExecutor().sheetList();
            if (clazzList.size() != sheetList.size()) {
                //其实就是throw new RuntimeException,下同
                throw new KYException("sheet页数量与数据类型个数不一致,请检查");
            }
            List<ReadSheet> readSheetList = new ArrayList<>();
            //循环读取每页的数据
            for (int i = 0; i < sheetList.size(); i++) {
                Class clazz = clazzList.get(i);
                ReadSheet readSheet = EasyExcel.readSheet(i).head(clazz).registerReadListener(new ReadListener() {
                    List list = new ArrayList();

                    @Override
                    public void onException(Exception exception, AnalysisContext analysisContext) {
                        if (exception instanceof ExcelDataConvertException) {
                            exception.printStackTrace();
                            ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException)exception;
                            //StrUtil.format 此处用的是HuTool工具包里的格式化字符串
                            throw new KYException(StrUtil.format("第{}行，第{}列解析异常，数据为:{}", excelDataConvertException.getRowIndex(),
                                    excelDataConvertException.getColumnIndex(), excelDataConvertException.getCellData()));
                        }
                    }

                    @Override
                    public void invoke(Object o, AnalysisContext analysisContext) {
                        list.add(JSONObject.parseObject(JSONObject.toJSONString(o), clazz));
                        resultMap.put(clazz.getSimpleName(), list);
                    }

                    @Override
                    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                        //这里可以直接做存入数据库操作
                    }

                    @Override
                    public boolean hasNext(AnalysisContext analysisContext) {
                        return true;
                    }

                    @Override
                    public void invokeHead(Map map, AnalysisContext analysisContext) {
                        //这里可以获取表头信息
                    }
                }).build();
                readSheetList.add(readSheet);
            }
            excelReader.read(readSheetList);
        } catch (IOException e) {
            log.error("读取excel异常", e);
        }
        finally{
            if (excelReader != null) {
                excelReader.finish();
            }
        }
        return resultMap;
    }