java模板导出，一个sheet两张表

^超级超级无“敌”苏苏苏苏苏^

已于 2024-10-09 14:19:19 修改

阅读量637

点赞数 8

文章标签： intellij-idea java

于 2024-10-09 10:16:59 首次发布

本文链接：https://blog.youkuaiyun.com/qq_68633809/article/details/142617093

版权

1.创建excel模板

2.导入依赖

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

3.编写代码

service中：

    /**
     * 店面信息
     *
     * @param param
     * @return
     */
    List<Map<String, Object>> storefronts(HashMap<String, Object> param);

    /**
     * 设备信息
     *
     * @param param
     * @return
     */
    public List<Map<String, Object>> deviceCleans(HashMap<String, Object> param);

impl中：

   /**
     * 店面信息
     *
     * @param param
     * @return
     */
    @Override
    public List<Map<String, Object>> storefronts(HashMap<String, Object> param) {
        List<HashMap<String, Object>> list = selectMapList(param);
        List<Map<String, Object>> result = new ArrayList<>();
        list.stream().forEach(x -> {
            HashMap<String, Object> map = new HashMap<>();
            String storefrontName = x.get("storefrontName") == null ? "" : x.get("storefrontName").toString();
            String cateringTypeName = x.get("cateringTypeName") == null ? "" : x.get("cateringTypeName").toString();
            String cateringTypeCycle = x.get("cateringTypeCycle") == null ? "" : x.get("cateringTypeCycle").toString();
            String directorName = x.get("directorName") == null ? "" : x.get("directorName").toString();
            String directorPhone = x.get("directorPhone") == null ? "" : x.get("directorPhone").toString();
            String detailAddress = x.get("detailAddress") == null ? "" : x.get("detailAddress").toString();
            String storefrontStatus = x.get("storefrontStatus") == null ? "" : x.get("storefrontStatus").toString();
            String storefrontState = x.get("storefrontState") == null ? "" : x.get("storefrontState").toString();

            map.put("storefrontName", storefrontName);
            map.put("cateringTypeName", cateringTypeName);
            map.put("cateringTypeCycle", cateringTypeCycle);
            map.put("directorName", directorName);
            map.put("directorPhone", directorPhone);
            map.put("detailAddress", detailAddress);
            if (Constants.APP_STATUS_ENABLE.equals(storefrontStatus)) {
                storefrontStatus = "启用";
            } else {
                storefrontStatus = "禁用";
            }
            map.put("storefrontStatus", storefrontStatus);
            if (Constants.STOREFRONT_CHANGESTATE_ZERO.equals(storefrontState)) {
                storefrontState = "待审核";
            } else if (Constants.STOREFRONT_CHANGESTATE_ONE.equals(storefrontState)) {
                storefrontState = "审核通过";
            } else if (Constants.STOREFRONT_CHANGESTATE_TWO.equals(storefrontState)) {
                storefrontState = "审核拒绝";
            } else if (Constants.STOREFRONT_CHANGESTATE_THREE.equals(storefrontState)) {
                storefrontState = "待注销";
            } else {
                storefrontState = "已注销";
            }
            map.put("storefrontState", storefrontState);
            if (result.size() != 1) {
                result.add(map);
            }
        });
        return result;
    }

    /**
     * 设备信息
     *
     * @param param
     * @return
     */
    @Override
    public List<Map<String, Object>> deviceCleans(HashMap<String, Object> param) {
        List<HashMap<String, Object>> list = selectMapList(param);
        List<Map<String, Object>> result = new ArrayList<>();
        list.stream().forEach(y -> {
            HashMap<String, Object> map = new HashMap<>();
            String name = y.get("name") == null ? "" : y.get("name").toString();
            String code = y.get("code") == null ? "" : y.get("code").toString();
            String installTime = y.get("installTime") == null ? "" : y.get("installTime").toString();
            String thisCleanTime = y.get("thisCleanTime") == null ? "" : y.get("thisCleanTime").toString();
            String nextCleanTime = y.get("nextCleanTime") == null ? "" : y.get("nextCleanTime").toString();
            String purifierInfo = y.get("purifierInfo") == null ? "" : y.get("purifierInfo").toString();
            String brand = y.get("brand") == null ? "" : y.get("brand").toString();
            String frequency = y.get("frequency") == null ? "" : y.get("frequency").toString();

            map.put("name", name);
            map.put("code", code);
            map.put("installTime", installTime);
            map.put("thisCleanTime", thisCleanTime);
            map.put("nextCleanTime", nextCleanTime);
            map.put("purifierInfo", purifierInfo);
            map.put("brand", brand);
            map.put("frequency", frequency);

            result.add(map);
        });
        return result;
    }

controller中（偷懒业务层代码写在了controller中）：（借鉴了Java根据模板生成excel文件【Java】【EasyExcel】【xls】-优快云博客）

  /**
     * 设备信息导出
     *
     * @param response
     * @param param
     * @throws Exception
     */
    @PostMapping("export")
    public void export(HttpServletResponse response, @RequestBody HashMap<String, Object> param) throws Exception {
        // 获取resource/template下的模板文件
        try {
            InputStream templateStream = DeviceCleanController.class.getResourceAsStream("/templates/设备信息2.xlsx");
            if (templateStream == null) {
                throw new FileNotFoundException("未找到模板文件");
            }

            Object storefrontId = param.get("storefrontId");

            Storefront storefront = storefrontService.selectStorefrontById(storefrontId.toString());
            String resultFileName = storefront.getStorefrontName() + "设备信息表" + LocalDate.now() + ".xlsx";

            response.setHeader("Content-Transfer-Encoding", "binary'");
            response.setHeader("'Cache-Control", "must-revalidate,post-check=0,pre-check=0");
            response.setHeader("Pragma", "public");
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset-UTF-8");
            resultFileName = URLEncoder.encode(resultFileName, "UTF-8");//解决中文乱码
            String header_body = "inline; filename=" + resultFileName + "; filename*=utf-8" + resultFileName;
            response.setHeader("Content-Disposition", header_body);

            // 生成目标文件
            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream())
                    .withTemplate(templateStream).build();
            WriteSheet writeSheet = EasyExcel.writerSheet().build();
            // 每次都会重新生成新的一行，而不是使用下面的空行
            FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
            // 替换占位符
            Object pageNum = param.get("pageNum");
            Object pageSize = param.get("pageSize");
            if (pageNum != null && !"".equals(pageNum.toString()) && pageSize != null && !"".equals(pageSize.toString())) {
                param.put("pageNum", pageNum);
                param.put("pageSize", pageSize);
                startPage(Integer.valueOf(pageNum.toString()), Integer.valueOf(pageSize.toString()));
            }
            param.put("storefrontId", param.get("storefrontId") == null ? "" : param.get("storefrontId").toString());
            param.put("name", param.get("name") == null ? "" : param.get("name").toString());
            param.put("code", param.get("code") == null ? "" : param.get("code").toString());
            param.put("purifierInfo", param.get("purifierInfo") == null ? "" : param.get("purifierInfo").toString());
            param.put("brand", param.get("brand") == null ? "" : param.get("brand").toString());
            param.put("frequency", param.get("frequency") == null ? "" : param.get("frequency").toString());
            excelWriter.fill(new FillWrapper("storefront", deviceCleanService.storefronts(param)), fillConfig, writeSheet);
            excelWriter.fill(new FillWrapper("deviceClean", deviceCleanService.deviceCleans(param)), fillConfig, writeSheet);
            excelWriter.finish();
            // 关闭模板流
            templateStream.close();
        } catch (FileNotFoundException e) {
            // 处理文件未找到异常
            response.setStatus(HttpServletResponse.SC_NOT_FOUND);
            // 返回适当的错误消息
            response.getWriter().write("未找到模板文件");
        } catch (Exception e) {
            // 处理其他异常
            response.setStatus(HttpServletResponse.SC_INTERNAL_SERVER_ERROR);
            // 返回适当的错误消息
            response.getWriter().write("内部服务器错误");
        }
    }

4.结果