使用easypoi根据表头信息动态导出excel

第一步添加依赖

 　　　　<dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-spring-boot-starter</artifactId>
            <version>4.1.3</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-scratchpad -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-scratchpad</artifactId>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
        </dependency>

　创建实体

DrillingDaily 注解@Excel表示需要导出那个字段，默认将其全部导出，不需要导出哪个列是，将注解

 注解@Excel的值改为空字符串，就可以根据传入的表头信息灵活导出excel

package com.shiwen.jdzx.model.daily;

import cn.afterturn.easypoi.excel.annotation.Excel;

/**
 * <p>Title:DrillingDaily </p>
 * <p>Description: 钻井日报</p>
 * <p>Company:西安石文软件有限公司 </p>
 *
 * @author liuguiyuan
 * @date 2019/9/4 14:04
 */
public class DrillingDaily {

    /**
     * 钻井日报
     * jh：井号；ktgs：勘探公司；jd：井队；rq：日期
     * sjjs：设计井深；drjs：当日井深；jc：进尺
     * ztcc：钻头尺寸；ztxh：钻头型号；cw：层位
     * gcjk：工程简况；zy：钻压；zs：转速；by2：泵压
     * pl：排量；md：密度；nd：粘度；ss：失水；cz：纯钻
     * gj：固井；sc：生产；fz：复杂；sg：事故
     * xl：修理；tg：停工；qt：其他；bz：备注
     */
    @Excel(name = "井号")
    private String jh;
    @Excel(name = "勘探公司")
    private String ktgs;
    @Excel(name = "井队")
    private String jd;
    @Excel(name = "日期")
    private String rq;
    @Excel(name = "设计井深")
    private Double sjjs;
    @Excel(name = "当日井深")
    private Double drjs;
    @Excel(name = "进尺")
    private Double jc;
    @Excel(name = "钻头尺寸")
    private Double ztcc;
    @Excel(name = "钻头型号")
    private String ztxh;
    @Excel(name = "层位")
    private String cw;
    @Excel(name = "工程简况")
    private String gcjk;
    @Excel(name = "钻压")
    private String zy;
    @Excel(name = "转速")
    private String zs;
    @Excel(name = "泵压")
    private String by2;
    @Excel(name = "排量")
    private String pl;
    @Excel(name = "密度")
    private String md;
    @Excel(name = "粘度")
    private String nd;
    @Excel(name = "失水")
    private String ss;
    @Excel(name = "纯钻")
    private Double cz;
    @Excel(name = "固井")
    private Double gj;
    @Excel(name = "生产")
    private Double sc;
    @Excel(name = "复杂")
    private Double fz;
    @Excel(name = "事故")
    private Double sg;
    @Excel(name = "修理")
    private Double xl;
    @Excel(name = "停工")
    private Double tg;
    @Excel(name = "其他")
    private String qt;
    @Excel(name = "备注")
    private String bz;

    public String getJh() {
        return jh;
    }

    public void setJh(String jh) {
        this.jh = jh;
    }

    public String getKtgs() {
        return ktgs;
    }

    public void setKtgs(String ktgs) {
        this.ktgs = ktgs;
    }

    public String getJd() {
        return jd;
    }

    public void setJd(String jd) {
        this.jd = jd;
    }

    public String getRq() {
        return rq;
    }

    public void setRq(String rq) {
        this.rq = rq;
    }

    public Double getSjjs() {
        return sjjs;
    }

    public void setSjjs(Double sjjs) {
        this.sjjs = sjjs;
    }

    public Double getDrjs() {
        return drjs;
    }

    public void setDrjs(Double drjs) {
        this.drjs = drjs;
    }

    public Double getJc() {
        return jc;
    }

    public void setJc(Double jc) {
        this.jc = jc;
    }

    public Double getZtcc() {
        return ztcc;
    }

    public void setZtcc(Double ztcc) {
        this.ztcc = ztcc;
    }

    public String getZtxh() {
        return ztxh;
    }

    public void setZtxh(String ztxh) {
        this.ztxh = ztxh;
    }

    public String getCw() {
        return cw;
    }

    public void setCw(String cw) {
        this.cw = cw;
    }

    public String getGcjk() {
        return gcjk;
    }

    public void setGcjk(String gcjk) {
        this.gcjk = gcjk;
    }

    public String getZy() {
        return zy;
    }

    public void setZy(String zy) {
        this.zy = zy;
    }

    public String getZs() {
        return zs;
    }

    public void setZs(String zs) {
        this.zs = zs;
    }

    public String getBy2() {
        return by2;
    }

    public void setBy2(String by2) {
        this.by2 = by2;
    }

    public String getPl() {
        return pl;
    }

    public void setPl(String pl) {
        this.pl = pl;
    }

    public String getMd() {
        return md;
    }

    public void setMd(String md) {
        this.md = md;
    }

    public String getNd() {
        return nd;
    }

    public void setNd(String nd) {
        this.nd = nd;
    }

    public String getSs() {
        return ss;
    }

    public void setSs(String ss) {
        this.ss = ss;
    }

    public Double getCz() {
        return cz;
    }

    public void setCz(Double cz) {
        this.cz = cz;
    }

    public Double getGj() {
        return gj;
    }

    public void setGj(Double gj) {
        this.gj = gj;
    }

    public Double getSc() {
        return sc;
    }

    public void setSc(Double sc) {
        this.sc = sc;
    }

    public Double getFz() {
        return fz;
    }

    public void setFz(Double fz) {
        this.fz = fz;
    }

    public Double getSg() {
        return sg;
    }

    public void setSg(Double sg) {
        this.sg = sg;
    }

    public Double getXl() {
        return xl;
    }

    public void setXl(Double xl) {
        this.xl = xl;
    }

    public Double getTg() {
        return tg;
    }

    public void setTg(Double tg) {
        this.tg = tg;
    }

    public String getQt() {
        return qt;
    }

    public void setQt(String qt) {
        this.qt = qt;
    }

    public String getBz() {
        return bz;
    }

    public void setBz(String bz) {
        this.bz = bz;
    }
}

　　编写修改@excel注解值的工具类 

　　ModifyAnnotationValues 

package com.shiwen.jdzx.server.util;

import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelCollection;
import cn.afterturn.easypoi.excel.annotation.ExcelEntity;
import lombok.extern.slf4j.Slf4j;

import java.lang.reflect.*;
import java.util.List;
import java.util.Map;

/**
 * @company: 石文软件有限公司
 * @description
 * @author: wangjie
 * @create: 2020-01-10 11:17
 **/
@Slf4j
public class ModifyAnnotationValues {

    /**
     *      * 修改fields上@Excel注解的name属性，不需要下载的列，name修改增加_ignore.
     *      * 保存原来的@Excel注解name属性值，本次生成后用来恢复
     *      * @Params
     *      *     headers：用户勾选，由前端传来的列名，列名的key必须和Model字段对应
     *      *     clazz：model实体类
     *      *     excelMap：用来记录原值的map，因为用到了递归，这里返回值作为参数传入
     *      * @return Map<String, String> 原实体类字段名和@Excel注解name属性值的映射关系<字段名，@Excel注解name属性值>
     *      
     */
    public static Map<String, String> dynamicChangeAndSaveSourceAnnotation(List<String> headers, Class clazz, Map<String, String> excelMap) {
        Field[] fields = clazz.getDeclaredFields();
        for (Field field : fields) {
            // @Excel注解
            if (field.isAnnotationPresent(Excel.class)) {
                boolean flag = true;
                for (int i = 0; i < headers.size(); i++) {
                    String header = headers.get(i);
                    if (field.getName().equals(header)) {
                        flag = false;
                        break;
                    }
                }
                // 下载列不包括该字段，进行隐藏，并记录原始值
                if (flag) {
                    Excel annotation = field.getAnnotation(Excel.class);
                    // 保存注解
                    excelMap.put(field.getName(), annotation.name());
                    InvocationHandler handler = Proxy.getInvocationHandler(annotation);
                    String value = annotation.name().toString();
                    changeAnnotationValue(handler, " ");
                }
                // @ExcelCollection注解
            } else if (field.isAnnotationPresent(ExcelCollection.class) && field.getType().isAssignableFrom(List.class)) {
                Type type = field.getGenericType();
                if (type instanceof ParameterizedType) {
                    ParameterizedType pt = (ParameterizedType) type;
                    Class collectionClazz = (Class) pt.getActualTypeArguments()[0];
                    // 解决@ExcelCollection如果没有需要下载列的异常，java.lang.IllegalArgumentException: The 'to' col (15) must not be less than the 'from' col (16)
                    // 如果没有需要下载列，将@ExcelCollection忽略
                    Field[] collectionFields = collectionClazz.getDeclaredFields();
                    boolean flag = false;
                    out:
                    for (Field temp : collectionFields) {
                        if (!temp.isAnnotationPresent(Excel.class)) {
                            continue;
                        }
                        for (int i = 0; i < headers.size(); i++) {
                            String header = headers.get(i);
                            if (temp.getName().equals(header)) {
                                flag = true;
                                break out;
                            }
                        }
                    }
                    if (flag) {
                        dynamicChangeAndSaveSourceAnnotation(headers, collectionClazz, excelMap);
                    } else {
                        ExcelCollection annotation = field.getAnnotation(ExcelCollection.class);
                        excelMap.put(field.getName(), annotation.name());
                        InvocationHandler handler = Proxy.getInvocationHandler(annotation);
                        changeAnnotationValue(handler, " ");
                    }
                }
                // @ExcelEntity注解
            } else if (field.isAnnotationPresent(ExcelEntity.class)) {
                Class entityClazz = field.getType();
                dynamicChangeAndSaveSourceAnnotation(headers, entityClazz, excelMap);
            }
        }
        return excelMap;
    }

    // 改变注解属性值，抽取的公共方法

    private static void changeAnnotationValue(InvocationHandler handler, String propertyValue) {
        try {
            Field field = handler.getClass().getDeclaredField("memberValues");
            field.setAccessible(true);
            Map<String, Object> memberValues = (Map<String, Object>) field.get(handler);
            memberValues.put("name", propertyValue);
        } catch (Exception e) {
            log.error("替换注解属性值出错！", e);
        }
    }


    /**
     *      * 递归恢复@Excel原始的name属性
     *      
     */
    public static void dynamicResetAnnotation(Class clazz, Map<String, String> excelMap) {
        if (excelMap.isEmpty()) {
            return;
        }
        Field[] fields = clazz.getDeclaredFields();
        try {
            for (Field field : fields) {
                if (field.isAnnotationPresent(Excel.class)) {
                    if (excelMap.containsKey(field.getName())) {
                        Excel annotation = field.getAnnotation(Excel.class);
                        InvocationHandler handler = Proxy.getInvocationHandler(annotation);
                        String sourceName = excelMap.get(field.getName());
                        changeAnnotationValue(handler, sourceName);
                    }
                } else if (field.isAnnotationPresent(ExcelCollection.class) && field.getType().isAssignableFrom(List.class)) {
                    // ExcelCollection修改过，才进行复原
                    if (excelMap.containsKey(field.getName())) {
                        ExcelCollection annotation = field.getAnnotation(ExcelCollection.class);
                        InvocationHandler handler = Proxy.getInvocationHandler(annotation);
                        String sourceName = excelMap.get(field.getName());
                        changeAnnotationValue(handler, sourceName);
                        // ExcelCollection未修改过，递归复原泛型字段
                    } else {
                        Type type = field.getGenericType();
                        if (type instanceof ParameterizedType) {
                            ParameterizedType pt = (ParameterizedType) type;
                            Class collectionClazz = (Class) pt.getActualTypeArguments()[0];
                            dynamicResetAnnotation(collectionClazz, excelMap);
                        }
                    }
                } else if (field.isAnnotationPresent(ExcelEntity.class)) {
                    Class entityClazz = field.getType();
                    dynamicResetAnnotation(entityClazz, excelMap);
                }
            }
        } catch (Exception e) {
            log.error("解析动态表头，恢复注解属性值出错！", e);
        }
    }


}

编写easypoi导出的工具类

EasyPoiExcelUtil

package com.shiwen.jdzx.server.util;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.NoSuchElementException;

/**
 * @author wangjie
 * @date 2019/12/23 18:24
 * @description
 * @company 石文软件有限公司
 */
public class EasyPoiExcelUtil {
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,
                                   String fileName, boolean isCreateHeader, HttpServletResponse response) {
        ExportParams exportParams = new ExportParams(title, sheetName);
        exportParams.setCreateHeadRows(isCreateHeader);
        defaultExport(list, pojoClass, fileName, response, exportParams);
    }

    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName,
                                   HttpServletResponse response) {
        defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
    }

    public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
        defaultExport(list, fileName, response);
    }

    private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName,
                                      HttpServletResponse response, ExportParams exportParams) {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        if (workbook != null) ;
        downLoadExcel(fileName, response, workbook);
    }

    private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
        try {
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            response.setCharacterEncoding("UTF-8");
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            //throw new NormalException(e.getMessage());
        }
    }

    private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
        Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
        if (workbook != null) ;
        downLoadExcel(fileName, response, workbook);
    }

    public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) {
        if (StringUtils.isBlank(filePath)) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
        } catch (NoSuchElementException e) {
            //throw new NormalException("模板不能为空");
        } catch (Exception e) {
            e.printStackTrace();
            //throw new NormalException(e.getMessage());
        }
        return list;
    }

    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) {
        if (file == null) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
        } catch (NoSuchElementException e) {
            // throw new NormalException("excel文件不能为空");
        } catch (Exception e) {
            //throw new NormalException(e.getMessage());
            System.out.println(e.getMessage());
        }
        return list;
    }


}

　编写控制层

DataReportExcelController

List<String> exportField参数是表头信息jh,jb......

package com.shiwen.jdzx.server.controller;

import com.shiwen.jdzx.common.WellCondition;
import com.shiwen.jdzx.model.daily.DrillingDaily;
import com.shiwen.jdzx.server.dao.mapper.DrillingDailyDao;
import com.shiwen.jdzx.server.service.DataReportExcelService;
import com.shiwen.jdzx.server.util.ExportExcelUtil;
import com.shiwen.jdzx.server.util.ModifyAnnotationValues;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.List;

/**
 * @author wangjie
 * @date 2019/12/21 11:39
 * @description 资料处理导出excel
 * @company 石文软件有限公司
 */
@RestController
@RequestMapping("/report")
public class DataReportExcelController {


    @Autowired
    private DataReportExcelService dataReportExcelService;

    /**
     * 根据传入的表头信息灵活导出
     *
     * @param response
     */
    @RequestMapping("/excel/{type}")
    public void excel(HttpServletResponse response,@PathVariable("type") String type,  String startDate, String endDate, String completed,
                      String oilField, String firstName, String wellType,String jh,@RequestParam("exportField") List<String> exportField) throws Exception {
        WellCondition condition = new WellCondition();
        condition.setStartDate(startDate);
        condition.setEndDate(endDate);
        condition.setOilField(oilField);
        condition.setFirstName(firstName);
        condition.setWellType(wellType);
        condition.setJh(jh);
        condition.setCompleted(completed);
        condition.setExport(exportField);
        dataReportExcelService.excel(response,condition,type);
    }

}

　编写导出的server层 

WellCondition condition 封装的是条件参数

String type 导出可能是有多次导出，这个是传入的路径  比如首页需要导出 /index 就接受index就行

package com.shiwen.jdzx.server.service;

import com.shiwen.jdzx.common.WellCondition;
import org.springframework.ui.ModelMap;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 * @author wangjie
 * @date 2019/12/21 11:45
 * @description
 * @company 石文软件有限公司
 */
public interface DataReportExcelService {

    void excel(HttpServletResponse response,WellCondition condition,String type) throws Exception;

}

　　编写导出的server层 的实现类

package com.shiwen.jdzx.server.service.impl;
import com.shiwen.jdzx.common.WellCondition;
import com.shiwen.jdzx.model.OverviewDaily;
import com.shiwen.jdzx.model.daily.DrillingDaily;
import com.shiwen.jdzx.server.dao.mapper.DrillingDailyDao;
import com.shiwen.jdzx.server.dao.mapper.WellDao;
import com.shiwen.jdzx.server.service.DataReportExcelService;
import com.shiwen.jdzx.server.util.Constant;
import com.shiwen.jdzx.server.util.EasyPoiExcelUtil;
import com.shiwen.jdzx.server.util.ModifyAnnotationValues;
import com.shiwen.publics.pager.PagerOracleImpl;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import javax.servlet.http.HttpServletResponse;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


/**
 * @author wangjie
 * @date 2019/12/21 11:50
 * @description
 * @company 石文软件有限公司
 */
@Slf4j
@Service
public class DataReportExcelServiceImpl implements DataReportExcelService {

    @Autowired
    private DrillingDailyDao dailyDao;

    @Autowired
     private WellDao wellDao;



    @Override
    public void excel(HttpServletResponse response, WellCondition condition,String type) throws Exception {
        Map<String, String> excelMap=new HashMap<>();
        Map<String, String> stringStringMap =null;
        PagerOracleImpl pager=new PagerOracleImpl();
        pager.setCurPage(0);
        switch (type){
            case Constant.DRILLING_DAILY:
                //修改注解 @Excel中的name值，
                stringStringMap=ModifyAnnotationValues.dynamicChangeAndSaveSourceAnnotation(condition.getExport(), DrillingDaily.class, excelMap);
                List<DrillingDaily> allDrillingDaily = dailyDao.getDrillingDaily(condition,pager);
                //导出excel
                EasyPoiExcelUtil.exportExcel(allDrillingDaily, Constant.mapList.get(type), Constant.mapList.get(type), DrillingDaily.class, Constant.mapList.get(type)+Constant.suffix, response);
                //导出完成恢复注解的原始值
                ModifyAnnotationValues.dynamicResetAnnotation( DrillingDaily.class,stringStringMap);
                break;
            case Constant.OVERVIEW_DAILY:
                stringStringMap=ModifyAnnotationValues.dynamicChangeAndSaveSourceAnnotation(condition.getExport(), OverviewDaily.class, excelMap);
                List<OverviewDaily> overviewDailies = wellDao.listOverviewDailyAll(condition);
                EasyPoiExcelUtil.exportExcel(overviewDailies, Constant.mapList.get(type), Constant.mapList.get(type), OverviewDaily.class, Constant.mapList.get(type)+Constant.suffix, response);
                ModifyAnnotationValues.dynamicResetAnnotation(OverviewDaily.class,stringStringMap);
                break;
            case Constant.SINGLE_DRILLING_DAILY:
                stringStringMap=ModifyAnnotationValues.dynamicChangeAndSaveSourceAnnotation(condition.getExport(), DrillingDaily.class, excelMap);
                List<DrillingDaily> singleDrillingDaily = dailyDao.getDrillingDailyAll(condition,pager);
                EasyPoiExcelUtil.exportExcel(singleDrillingDaily, Constant.mapList.get(type), Constant.mapList.get(type), DrillingDaily.class, Constant.mapList.get(type)+Constant.suffix, response);
                ModifyAnnotationValues.dynamicResetAnnotation(DrillingDaily.class,stringStringMap);
            default:
                break;
        }

    }

}

　前段请求

 /**
             * 导出
             */
            $scope.exportTable = function () {
                var param = packParam();
                $scope.selTitleField = [];
                $scope.selTitleName = [];
                angular.forEach($scope.gridOption, function (item, index) {
                    if (item.checked) {
                        $scope.selTitleField.push(item.field);
                    }
                });
                param.exportField = $scope.selTitleField;
                param.jh = $scope.filter.jh;
                var paramArr = [];
                for (let key  in param) {
                    if (param[key] || param[key] === 0) {
                        paramArr.push(key + '=' + param[key])
                    }
                }
                $window.open(url.excel + '?' + paramArr.join('&'));
            }

dao层我就不写了。以上代码根据自己的需要再改下就可以完成，根据传入的表头信息灵活导出excel，注意前段传入的表头信息，必须和实体的属性一直