Excel 导出

1. pom相关

        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-spring-boot-starter</artifactId>
            <version>4.1.0</version>
        </dependency>
        <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>

2.  导出方法

    void test2(HttpServletRequest request, HttpServletResponse response) {
        List<Map<String, Object>> data = new ArrayList<>();

        Map<String, Object> sheet1 = new HashMap<>();
        ExportParams exportParams = new ExportParams(null,"sheet1");
        sheet1.put("title", exportParams);
        sheet1.put("entity", ZsResultT2.class);
        List<ZsResultT2> sheet1Data = new ArrayList<>();
        ZsResultT2 item1 = new ZsResultT2();
        item1.setCode("1111");
        item1.setNo(1);
        item1.setShusheng("河北省");
        sheet1Data.add(item1);
        sheet1.put("data", sheet1Data);
        data.add(sheet1);

        Map<String, Object> sheet12 = new HashMap<>();
        ExportParams exportParams2 = new ExportParams(null,"sheet12");
        sheet12.put("title", exportParams2);
        sheet12.put("entity", ZsResultT2.class);
        List<ZsResultT2> sheet1Data2 = new ArrayList<>();
        ZsResultT2 item12 = new ZsResultT2();
        item12.setCode("1111");
        item12.setNo(1);
        item12.setShusheng("河北省");
        sheet1Data2.add(item12);
        sheet12.put("data", sheet1Data2);
        data.add(sheet12);
        easypoiUtil.exportExcelMoreSheet(request, response, "test.xls", null, data);
    }

 

3. 相关类

package com.diit.dwyxbd.utils;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.stereotype.Service;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.*;

@Service("easypoiUtil")
public class EasypoiUtil {

    /**
     * excel单个sheet导出
     * @param request
     * @param response
     * @param excelName excel名称
     * @param head head名称 第一行,不需要传null
     * @param title 列
     * @param data 数据
     * @return 成功标识
     */
    public boolean exportExcel(HttpServletRequest request, HttpServletResponse response, String excelName, String head, List<Map<String, Object>> title, List<Map<String, Object>> data) {
        //构建标题
        List<ExcelExportEntity> titleList = new ArrayList<ExcelExportEntity>();//存放标题
        for (Map<String, Object> map : title) {
            if (map.get("title") != null) {
                ExcelExportEntity colEntity = new ExcelExportEntity(map.get("title").toString(), map.get("field").toString());
                colEntity.setNeedMerge(true);
                titleList.add(colEntity);
            }
        }
        // 创建sheet
        ExportParams ep = new ExportParams(head, "sheet1");
        // 设置样式
        ep.setStyle(ExcelStyleUtil.class);
        Workbook workbook = ExcelExportUtil.exportExcel(ep, titleList, data);
        //下载生成的excel
        try {
            responseExcelFile(request, response, excelName, workbook);
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }


    public boolean exportExcelMoreSheet(HttpServletRequest request, HttpServletResponse response, String excelName, List<Map<String, Object>> title, List<Map<String, Object>> data) {
        try {
            Workbook workbook = ExcelExportUtil.exportExcel(data, ExcelType.HSSF);
            responseExcelFile(request, response, excelName, workbook);
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    void responseExcelFile(HttpServletRequest request, HttpServletResponse response,String fileName,Workbook workbook) throws Exception {
        String userAgent = request.getHeader("user-agent").toLowerCase();
        if (userAgent.contains("msie") || userAgent.contains("like gecko")) {
            fileName = URLEncoder.encode(fileName, "UTF-8");
        } else {
            fileName = new String(fileName.getBytes("utf-8"), "ISO-8859-1");
        }
        response.setCharacterEncoding("UTF-8");
        response.setHeader("content-Type", "application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
        OutputStream out = response.getOutputStream();
        workbook.write(out);
        out.close();
    }
}

 

 

 

参考 https://blog.csdn.net/weixin_45624150/article/details/107180646