Easypoi + Spring Boot 自定义模板导出Ecxel

一个什么都不会de程序员

已于 2023-02-14 18:21:24 修改

阅读量1.5k

点赞数

分类专栏： EasyPOI 文章标签： spring boot java

于 2022-08-19 14:50:24 首次发布

本文链接：https://blog.csdn.net/m0_66525523/article/details/126424076

版权

EasyPOI 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

1.加入Easypoi依赖

        
        <!--poi依赖 -->
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>4.1.0</version>
        </dependency>

        <!--导出依赖 -->
         <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>4.1.2</version>
        </dependency>

2.Controller

这里传输类要与实体类字段保持一致

/**
     * 模板导出
     */
    @ApiOperation("导出")
    @GetMapping(value = "/exportByTem", produces = "application/octet-stream")
    public void exportJg(HttpServletResponse response, AcVO currentAccountEntity, SysSiteEntity siteEntity) throws Exception {
        //拿到模板
        TemplateExportParams paramss = new TemplateExportParams("模板存放的位置");
        Map<String, Object> map = new HashMap<String, Object>();
        //对时间进行格式化
        if (null != currentAccountEntity.getEndTime() && currentAccountEntity.getBeginTime() != null) {
            SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
            map.put("time", format.format(currentAccountEntity.getBeginTime()) + "-" + format.format(currentAccountEntity.getEndTime()));
        }
        //添加到模板{{site}}
        map.put("site", currentAccountEntity.getSite());
        if (null != currentAccountEntity) {
            //导出的数据是传输类,自定义查询所需要的数据
            List<AcVO> page = currentAccountService.selectList(currentAccountEntity);
            List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
            //通过循环遍历把查到的数据放到map中
            for (int i = 0; i < page.size(); i++) {
                Map<String, Object> lm = new HashMap<>();
                //put的键要跟excel模板中的名称一致
                lm.put("id", i + 1 + "");
                lm.put("name", page.get(i).getName());
                lm.put("specification", page.get(i).getSpecification());
                lm.put("unit", page.get(i).getUnit());
                lm.put("quantity", String.valueOf(page.get(i).getQuantity()));
                lm.put("note", page.get(i).getNote());
                list.add(lm);
            }
            //这是第二个遍历的对象
            List<Map<String, Object>> list1 = new ArrayList<Map<String, Object>>();
            for (int i = 0; i < page.size(); i++) {
                Map<String, Object> lms = new HashMap<>();
                //照片是阿里云的url,处理以图片的形式导出
                ImageEntity image = new ImageEntity();
                image.setColspan(3);
                image.setUrl(page.get(i).getPicture());
                lms.put("info", page.get(i).getInfo());
                lms.put("picture", image);
                list1.add(lms);
            }
            //把map数据放到对象里
            map.put("maplist", list);
            map.put("list", list1);
            //把数据添加到模板导出
            Workbook workbook = ExcelExportUtil.exportExcel(paramss, map);
            //下面是对合并单元格得到处理(每个人导出的结果会不一样,自己修改就好)
            int row = 0;
            int rows = 0;
            for (int i = 0; i < list.size(); i++) {
                row = i + 6;
                //这里的getSheetAT,第一个和第二个参数是从第几行开始到第几行结束,第三个参数是第从几个单元格合并(从0开始),第四的参数的到第几个单元格结束合并
                PoiMergeCellUtil.addMergedRegion(workbook.getSheetAt(0), row, row, 1, 2);
                PoiMergeCellUtil.addMergedRegion(workbook.getSheetAt(0), row, row, 3, 5);
                PoiMergeCellUtil.addMergedRegion(workbook.getSheetAt(0), row, row, 7, 8);
                for (int j = 0; j <list1.size(); j++) {
                    rows = 10 + list.size()-1 + j;
                    PoiMergeCellUtil.addMergedRegion(workbook.getSheetAt(0), rows, rows, 0, 3);

                }
            }
            ServletOutputStream out = null;
            try {
                //流的形式传输数据
                response.setHeader("content-type", "application/octet-stream");
                //防止中文乱码
                response.setHeader("content-disposition", "attachment;filename=" + URLEncoder.encode("导出模板的名称", "UTF-8"));
                out = response.getOutputStream();
                workbook.write(out);
            } catch (
                    IOException e) {
                e.printStackTrace();
            } finally {
                if (null != out) {
                    try {
                        out.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
            }
        }
    }