基于Hutool封装的Excel导出工具类

官网地址：https://www.hutool.cn/docs/#/poi/Excel%E7%94%9F%E6%88%90-ExcelWriter

 以前导出用的是poi，但是我发现hutool已经对poi进行了很好的封装，那么我们也可以在他的基础上进行二次封装，来达到我们的需求。下面是效果图

这是是只有一级表头

 这个是二级表头

 那么接下来直接上我们自己封装的工具类：

import cn.hutool.core.io.IoUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @description: Excel 工具
 * @author: Lxq
 * @date: 2020/7/16 10:06
 */
public class ExcelUtils {

    /**
     * 导出Excel
     *
     * @param response
     * @param list        数据
     * @param headerAlias 表头
     */
    public static void exportExcel(HttpServletResponse response, List<?> list, Map<String, String> headerAlias) {
        // 通过工具类创建writer，默认创建xls格式
        ExcelWriter writer = ExcelUtil.getWriter();
        //自定义标题别名
        headerAlias.forEach(
                (k, v) -> {
                    writer.addHeaderAlias(k, v);
                }
        );
        // 一次性写出内容，使用默认样式，强制输出标题
        writer.write(list, true);
        //out为OutputStream，需要写出到的目标流
        //response为HttpServletResponse对象
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        //test.xls是弹出下载对话框的文件名，不能为中文，中文请自行编码
        response.setHeader("Content-Disposition", "attachment;filename=" + "xxx" + ".xls");
        ServletOutputStream out = null;
        getServletOutputStream(response, writer, out);
    }

    /**
     * @param response
     * @param list        数据
     * @param headerAlias 表头
     */
    public static void exportExcel(HttpServletResponse response, List<?> list, List<Map<String, Object>> headerAlias) {
        ExcelWriter writer = ExcelUtil.getWriter();
        // 这个方法使用二级表头，那么直接先添加一行
        writer.passCurrentRow();
        int[] firstColumn = {0};
        int[] lastColumn = {0};
        Boolean[] flag = {true};
        List<Map<String, String>> sHead = new ArrayList<>();
        headerAlias.stream().forEach(
                l -> {
                    String headNmae = (String) l.get("headName");
                    Map<String, Object> content = (Map<String, Object>) l.get("children");
                    content.forEach(
                            (k, v) -> {
                                Map<String, String> head = new HashMap<>();
                                head.put(k, (String) v);
                                sHead.add(head);
                            }
                    );
                    lastColumn[0] += content.size();
                    if (flag[0] == true) {
                        // 开始时候下标从零开始
                        lastColumn[0] -= 1;
                        flag[0] = false;
                    }
                    writer.merge(0, 0, firstColumn[0], lastColumn[0], headNmae, true);
                    firstColumn[0] += content.size();
                }
        );
        sHead.stream().forEach(
                h -> {
                    h.forEach(
                            (k, v) -> writer.addHeaderAlias(k, v)
                    );
                }
        );
        // 一次性写出内容，使用默认样式，强制输出标题
        writer.write(list, true);
        //response为HttpServletResponse对象
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        //test.xls是弹出下载对话框的文件名，不能为中文，中文请自行编码
        response.setHeader("Content-Disposition", "attachment;filename=" + "xxx" + ".xls");
        ServletOutputStream out = null;
        getServletOutputStream(response, writer, out);

    }



    private static void getServletOutputStream(HttpServletResponse response, ExcelWriter writer, ServletOutputStream out) {
        try {
            out = response.getOutputStream();
            writer.flush(out, true);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            // 关闭writer，释放内存
            writer.close();
            //此处记得关闭输出Servlet流
            IoUtil.close(out);
        }

    }


}

那么我们应该传什么数据，才能生成这样的效果呢？

/**
 * @description:
 * @author: Lxq
 * @date: 2020/7/16 11:03
 */
@RestController
@RequestMapping("/test")
public class LocalVariable {

    @GetMapping("/export")
    public void testExport(HttpServletResponse response) {
        // ======================数据 ===============================
        List<Map<String, Object>> list = new ArrayList<>();
        Map<String, Object> map = new HashMap<>();
        map.put("name", "xiaoming");
        map.put("age", "11");
        map.put("score", "60");
        map.put("isPass", "是");
        map.put("examDate", "2020-8-1");
        list.add(map);

        // ===================== 二级表头================================
        List<Map<String, Object>> headerAlias = new ArrayList<>();
        Map<String, Object> head1 = new LinkedHashMap<>();
        head1.put("headName", "我是一级表头head1");
        Map<String, Object> son1 = new LinkedHashMap<>();
        son1.put("name", "姓名");
        son1.put("age", "年龄");
        head1.put("children", son1);
        headerAlias.add(head1);

        Map<String, Object> head2 = new LinkedHashMap<>();
        head2.put("headName", "我是一级表头head2");
        Map<String, Object> son2 = new LinkedHashMap<>();
        son2.put("score", "分数");
        son2.put("isPass", "是否通过");
        son2.put("examDate", "考试时间");
        head2.put("children", son2);
        headerAlias.add(head2);
        ExcelUtils.exportExcel(response, list, headerAlias);


    }

    @GetMapping("/export2")
    public void test2Export(HttpServletResponse response) {
        // ======================数据 ===============================
        List<Map<String, Object>> list = new ArrayList<>();
        Map<String, Object> map = new HashMap<>();
        map.put("name", "xiaoming");
        map.put("age", "11");
        map.put("score", "60");
        map.put("isPass", "是");
        map.put("examDate", "2020-8-1");
        list.add(map);
        // ===================== 一级表头================================
        Map<String, String> head = new LinkedHashMap<>();
        head.put("name", "姓名");
        head.put("age", "年龄");
        head.put("score", "分数");
        head.put("isPass", "是否通过");
        head.put("examDate", "考试时间");
        ExcelUtils.exportExcel(response, list, head);
    }

这是测试数据，需要的小伙伴们用起来，好用记得帮忙点赞，谢谢你们。