EasyExcel下载模板，excel导入数据库，数据库导出到excel

在做电子存销系统的时候，有时需要将数据导出供用户参考，也需要把数据存入数据库做持久化存储，也许后续能做数据可视化。

依赖

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

文档模板下载（就是后续数据导入导出的表格，这里做个模板）

//文件模板下载接口
    @GetMapping("download")
    public ResponseEntity<byte[]> downloadFile(String path) throws Exception {
        //加载模板文件
        ClassPathResource classPathResource = new ClassPathResource(path);
        InputStream inputStream = classPathResource.getInputStream();

        //设置响应头
        HttpHeaders httpHeaders = new HttpHeaders();
        httpHeaders.setContentType(MediaType.APPLICATION_OCTET_STREAM);
        httpHeaders.setContentDispositionFormData("attachment", URLEncoder.encode(classPathResource.getFilename(), "UTF-8"));

        //将Excel文件内容写入到ByteArrayOutputStream
        ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
        EasyExcel.write(byteArrayOutputStream).withTemplate(inputStream).sheet().doFill(null);

        //获取ByteArrayOutputStream的字节数组
        byte[] bytes = byteArrayOutputStream.toByteArray();

        //返回文件内容和响应头
        return new ResponseEntity<>(bytes, httpHeaders, HttpStatus.OK);
    }

数据导出

//数据导出接口
    @GetMapping("/export")
    public ResponseEntity<byte[]> doExport(HttpServletResponse response) throws IOException {
        //从数据库获取数据
        List<Product> products = productService.list();

        //设置响应头
        HttpHeaders httpHeaders = new HttpHeaders();
        httpHeaders.setContentType(MediaType.APPLICATION_OCTET_STREAM);
        httpHeaders.setContentDispositionFormData("attchment", URLEncoder.encode("产品列表.xlsx", "UTF-8"));

        //创建一个ByteArrayOutputStream用于写入Excel数据
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();

        //排除不需要导出的字段
        List<String> excludeFields = List.of("id", "createTime", "updateTime", "errorReason");

        //将数据写入Excel
        EasyExcel.write(outputStream, Product.class)
                .sheet("产品列表")
                .excludeColumnFieldNames(excludeFields)
                .doWrite(products);

        //获取ByteArrayOutputStream的字节数组
        byte[] bytes = outputStream.toByteArray();

        //设置响应头
        response.setHeader("Content-Disposition", "attchment;filename=" + URLEncoder.encode("产品列表.xlsx", "UTF-8"));
        response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
        response.setContentLength(bytes.length);

        //将Excel数据写入HttpServletResponse输出流
        response.getOutputStream().write(bytes);

        //关闭输出流
        response.getOutputStream().flush();
        response.getOutputStream().close();

        //返回空的响应体，因为文件已经通过HttpServletResponse输出了
        return new ResponseEntity<>(null, httpHeaders, HttpStatus.OK);
    }

数据导入

 //数据导入接口
    @PostMapping("uploadExcel")
    public ResponseEntity<Map<String, Object>> doUpload(MultipartFile excel) throws IOException {
        if (excel == null){
            throw new IllegalArgumentException("文件为空");
        }
        InputStream inputStream = excel.getInputStream();

        ArrayList<Product> errors = new ArrayList<>(); //校验错误产品集合
        List<String> allCodes = productService.getAllCode();//那搭配现有的产品编码

        final int[] allNums = {0}; // 记录总行数
        final int[] sucNums = {0}; // 成功导入行数

        //使用EasyExcel读取Excel文件内容
        EasyExcel.read(inputStream, Product.class, new ReadListener<Product>() {
            public static final int BATCH_COUNT = 100;
            private List<Product> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);

            @Override
            public void invoke(Product product, AnalysisContext analysisContext) {
                allNums[0]++;
                if (StringUtils.isEmpty(product.getCoding())) {
                    product.setErrorReason("产品编码为空");
                    errors.add(product);
                    return;//如果产品编码为空，将错误信息添加到集合中，并终止本次处理
                }
                if (StringUtils.isEmpty(product.getBrand())) {
                    product.setErrorReason("产品品牌为空");
                    errors.add(product);
                    return;
                }
                if (StringUtils.isEmpty(product.getClassify())) {
                    product.setErrorReason("产品分类为空");
                    errors.add(product);
                    return;
                }
                if (product.getColor() == null) {
                    product.setErrorReason("产品颜色为空");
                    errors.add(product);
                    return;
                }
                boolean contains = allCodes.contains(product.getCoding());
                if (contains) {
                    product.setErrorReason("产品编码重复");
                    errors.add(product);
                    return;
                }

                allCodes.add(product.getCoding());
                cachedDataList.add(product);

                if (cachedDataList.size() > BATCH_COUNT) {
                    save();//达到批量处理的数量，执行保存逻辑
                    cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
                }
            }

            private void save(){
                boolean b = productService.saveBatch(cachedDataList);
                if (!b) {
                    errors.addAll(cachedDataList);//批量保存数据失败，将数据添加到错误集合中
                    return;
                }
                sucNums[0] = sucNums[0] + cachedDataList.size();//计算成功保存的数量
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                save();//处理剩余数据的保存逻辑
            }
        }).sheet().doRead();

        String fileName = "error.xlsx";
        if (errors.size() > 0) {
            //生成包含错误信息的Excel文件
            URL resource = this.getClass().getResource("/");
            String path = resource.getPath();
            File file = new File(path + " /excels/errors");
            if (!file.exists()) {
                file.mkdirs();
            }

            ArrayList<String> strings = new ArrayList<>();
            strings.add("id");
            strings.add("createTime");
            strings.add("updateTime");
            EasyExcel.write(file.getPath() + "/" + fileName, Product.class)
                    .sheet("失败记录")
                    .excludeColumnFieldNames(strings)
                    .doWrite(() -> errors);
        } else {
            fileName = "";//如果没有错误信息，则文件名为空字符串
        }

        HashMap<String, Object> map = new HashMap<>();
        map.put("allNums", allNums[0]);
        map.put("sucNums", sucNums[0]);//成功列表
        map.put("errorFile", fileName);
        return new ResponseEntity<>(map, HttpStatus.OK);
    }