java通过模板导出到Excel

最新推荐文章于 2024-05-17 11:59:56 发布

菜鸟ZhAng~

最新推荐文章于 2024-05-17 11:59:56 发布

阅读量628

点赞数 8

文章标签： java excel 开发语言

本文链接：https://blog.csdn.net/weixin_56227932/article/details/135774155

版权

依赖

依赖,该项目是gradle管理依赖，需要maven的可以去maven仓库找一下对应的依赖

implementation 'com.alibaba:easyexcel:2.2.10'

控制层

    @Operation(summary = "导出excel")
    @GetMapping("/export")
    public void exportExcel(@RequestParam String startTime, @RequestParam String endTime, HttpServletResponse response) {
        userDownloadStatisticsService.exportExcel(startTime, endTime, response);
    }

业务层

public void exportExcel(String startTime, String endTime, HttpServletResponse response) {
        //查询需要导出的数据
        LambdaQueryWrapper<UserDownloadStatistics> wrapper = new LambdaQueryWrapper<>();
        wrapper.between(UserDownloadStatistics::getDownloadTime, startTime, endTime);
        wrapper.orderBy(true, true, UserDownloadStatistics::getDownloadTime);
        List<UserDownloadStatistics> statisticsList = list(wrapper);

        //封装成导出模板
        List<UserStatisticsExcel> excelList = new ArrayList<>();
        statisticsList.forEach(date -> excelList.add(new UserStatisticsExcel(date.getDayDownloadNum(), date.getDownloadTime().toString())));
        String fileName = startTime + "~" + endTime + ".新增注册用户数.xlsx";

        //设置要下载的文件的名称
        ClassPathResource classPathResource = new ClassPathResource("/template/新增注册用户数.xlsx");
        InputStream inputStream = classPathResource.getStream();
        try {
            ServletOutputStream out = response.getOutputStream();
            //设置文件类型
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            //设置编码格式
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xlsx");
            //创建excel
            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate(inputStream).build();
            // 创建sheet
            WriteSheet writeSheet = EasyExcel.writerSheet(0).build();
            FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
            excelWriter.fill(excelList, fillConfig, writeSheet);
            //填充完成
            excelWriter.finish();
            out.flush();
        } catch (IOException e) {
            e.printStackTrace();
        }


    }