easypoi通过模板导出多sheet的excel表格

A慧眼如炬

已于 2024-04-16 09:26:34 修改

阅读量472

点赞数 1

分类专栏：文件操作 excel Java 文章标签： excel java 服务器 easypoi 导出多sheet

于 2023-10-23 17:57:49 首次发布

本文链接：https://blog.csdn.net/zc_huiyanruju/article/details/133994148

版权

Java 同时被 3 个专栏收录

16 篇文章 0 订阅

订阅专栏

excel

4 篇文章 0 订阅

订阅专栏

文件操作

3 篇文章 0 订阅

订阅专栏

本文介绍了如何使用Easypoi-web库在SpringMVC中创建一个HTTP接口，通过TemplateExportParams和ExcelExportUtil将数据导出到包含两个sheet（普通字段和列表）的Excel文件。

摘要由CSDN通过智能技术生成

需要引入的依赖

<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-web</artifactId>
    <version>4.4.0</version>
</dependency>

excel模板

第一个sheet

第二个sheet(列表)

代码

/**
  * 导出多sheet的excel
  */
@PostMapping("/exportMultipleSheetExcel")
public void exportMultipleSheetExcel(HttpServletRequest request, HttpServletResponse response) throws IOException {
    Map<Integer, Map<String, Object>> map = getExportMap();
    TemplateExportParams template = new TemplateExportParams("C:\\Users\\Admin\\Desktop\\test.xlsx", map.keySet().toArray(new Integer[]{}));
    Workbook workbook = ExcelExportUtil.exportExcel(map, template);
    workbook.write(response.getOutputStream());
}

获取数据

private Map<Integer, Map<String, Object>> getExportMap() {
        Map<Integer, Map<String, Object>> map = new HashMap<>(3);
        // 普通字段sheet
        Map<String, Object> oneMap = new HashMap<>(15);
        oneMap.put("olTotal", "118");
        oneMap.put("ofTotal", "2");
        oneMap.put("totalFraction", "2000");
        oneMap.put("maxFraction", "100");
        oneMap.put("minFraction", "68");
        oneMap.put("olRatio", "98%");
        oneMap.put("ofRatio", "2%");
        oneMap.put("esCount", "120");
        oneMap.put("esRatio", "100%");
        oneMap.put("passingRate", "100%");
        oneMap.put("date", DateUtil.getCurrentDate(DateUtil.DEFAULT_DATE_PATTERN));
        map.put(0, oneMap);
        // 列表sheet
        Map<String, Object> twoMap = new HashMap<>(2);
        List<Map<String, Object>> twoList = new ArrayList<>(3);
        Map<String, Object> twoSubMap = new HashMap<>(4);
        twoSubMap.put("num", "1");
        twoSubMap.put("number", "20230001");
        twoSubMap.put("name", "张三");
        twoList.add(twoSubMap);
        Map<String, Object> twoSubMap2 = new HashMap<>(4);
        twoSubMap2.put("num", "2");
        twoSubMap2.put("number", "20230002");
        twoSubMap2.put("name", "李四");
        twoList.add(twoSubMap2);
        Map<String, Object> twoSubMap3 = new HashMap<>(4);
        twoSubMap3.put("num", "3");
        twoSubMap3.put("number", "20230001");
        twoSubMap3.put("name", "王五");
        twoList.add(twoSubMap3);
        twoMap.put("list", twoList);
        map.put(1, twoMap);
        return map;
    }