第一步添加依赖
<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
1 package com.shiwen.jdzx.server.util;
2
3 import cn.afterturn.easypoi.excel.annotation.Excel;
4 import cn.afterturn.easypoi.excel.annotation.ExcelCollection;
5 import cn.afterturn.easypoi.excel.annotation.ExcelEntity;
6 import lombok.extern.slf4j.Slf4j;
7
8 import java.lang.reflect.*;
9 import java.util.List;
10 import java.util.Map;
11
12 /**
13 * @company: 石文软件有限公司
14 * @description
15 * @author: wangjie
16 * @create: 2020-01-10 11:17
17 **/
18 @Slf4j
19 public class ModifyAnnotationValues {
20
21 /**
22 * * 修改fields上@Excel注解的name属性，不需要下载的列，name修改增加_ignore.
23 * * 保存原来的@Excel注解name属性值，本次生成后用来恢复
24 * * @Params
25 * * headers：用户勾选，由前端传来的列名，列名的key必须和Model字段对应
26 * * clazz：model实体类
27 * * excelMap：用来记录原值的map，因为用到了递归，这里返回值作为参数传入
28 * * @return Map<String, String> 原实体类字段名和@Excel注解name属性值的映射关系<字段名，@Excel注解name属性值>
29 *
30 */
31 public static Map<String, String> dynamicChangeAndSaveSourceAnnotation(List<String> headers, Class clazz, Map<String, String> excelMap) {
32 Field[] fields = clazz.getDeclaredFields();
33 for (Field field : fields) {
34 // @Excel注解
35 if (field.isAnnotationPresent(Excel.class)) {
36 boolean flag = true;
37 for (int i = 0; i < headers.size(); i++) {
38 String header = headers.get(i);
39 if (field.getName().equals(header)) {
40 flag = false;
41 break;
42 }
43 }
44 // 下载列不包括该字段，进行隐藏，并记录原始值
45 if (flag) {
46 Excel annotation = field.getAnnotation(Excel.class);
47 // 保存注解
48 excelMap.put(field.getName(), annotation.name());
49 InvocationHandler handler = Proxy.getInvocationHandler(annotation);
50 String value = annotation.name().toString();
51 changeAnnotationValue(handler, " ");
52 }
53 // @ExcelCollection注解
54 } else if (field.isAnnotationPresent(ExcelCollection.class) && field.getType().isAssignableFrom(List.class)) {
55 Type type = field.getGenericType();
56 if (type instanceof ParameterizedType) {
57 ParameterizedType pt = (ParameterizedType) type;
58 Class collectionClazz = (Class) pt.getActualTypeArguments()[0];
59 // 解决@ExcelCollection如果没有需要下载列的异常，java.lang.IllegalArgumentException: The 'to' col (15) must not be less than the 'from' col (16)
60 // 如果没有需要下载列，将@ExcelCollection忽略
61 Field[] collectionFields = collectionClazz.getDeclaredFields();
62 boolean flag = false;
63 out:
64 for (Field temp : collectionFields) {
65 if (!temp.isAnnotationPresent(Excel.class)) {
66 continue;
67 }
68 for (int i = 0; i < headers.size(); i++) {
69 String header = headers.get(i);
70 if (temp.getName().equals(header)) {
71 flag = true;
72 break out;
73 }
74 }
75 }
76 if (flag) {
77 dynamicChangeAndSaveSourceAnnotation(headers, collectionClazz, excelMap);
78 } else {
79 ExcelCollection annotation = field.getAnnotation(ExcelCollection.class);
80 excelMap.put(field.getName(), annotation.name());
81 InvocationHandler handler = Proxy.getInvocationHandler(annotation);
82 changeAnnotationValue(handler, " ");
83 }
84 }
85 // @ExcelEntity注解
86 } else if (field.isAnnotationPresent(ExcelEntity.class)) {
87 Class entityClazz = field.getType();
88 dynamicChangeAndSaveSourceAnnotation(headers, entityClazz, excelMap);
89 }
90 }
91 return excelMap;
92 }
93
94 // 改变注解属性值，抽取的公共方法
95
96 private static void changeAnnotationValue(InvocationHandler handler, String propertyValue) {
97 try {
98 Field field = handler.getClass().getDeclaredField("memberValues");
99 field.setAccessible(true);
100 Map<String, Object> memberValues = (Map<String, Object>) field.get(handler);
101 memberValues.put("name", propertyValue);
102 } catch (Exception e) {
103 log.error("替换注解属性值出错！", e);
104 }
105 }
106
107
108 /**
109 * * 递归恢复@Excel原始的name属性
110 *
111 */
112 public static void dynamicResetAnnotation(Class clazz, Map<String, String> excelMap) {
113 if (excelMap.isEmpty()) {
114 return;
115 }
116 Field[] fields = clazz.getDeclaredFields();
117 try {
118 for (Field field : fields) {
119 if (field.isAnnotationPresent(Excel.class)) {
120 if (excelMap.containsKey(field.getName())) {
121 Excel annotation = field.getAnnotation(Excel.class);
122 InvocationHandler handler = Proxy.getInvocationHandler(annotation);
123 String sourceName = excelMap.get(field.getName());
124 changeAnnotationValue(handler, sourceName);
125 }
126 } else if (field.isAnnotationPresent(ExcelCollection.class) && field.getType().isAssignableFrom(List.class)) {
127 // ExcelCollection修改过，才进行复原
128 if (excelMap.containsKey(field.getName())) {
129 ExcelCollection annotation = field.getAnnotation(ExcelCollection.class);
130 InvocationHandler handler = Proxy.getInvocationHandler(annotation);
131 String sourceName = excelMap.get(field.getName());
132 changeAnnotationValue(handler, sourceName);
133 // ExcelCollection未修改过，递归复原泛型字段
134 } else {
135 Type type = field.getGenericType();
136 if (type instanceof ParameterizedType) {
137 ParameterizedType pt = (ParameterizedType) type;
138 Class collectionClazz = (Class) pt.getActualTypeArguments()[0];
139 dynamicResetAnnotation(collectionClazz, excelMap);
140 }
141 }
142 } else if (field.isAnnotationPresent(ExcelEntity.class)) {
143 Class entityClazz = field.getType();
144 dynamicResetAnnotation(entityClazz, excelMap);
145 }
146 }
147 } catch (Exception e) {
148 log.error("解析动态表头，恢复注解属性值出错！", e);
149 }
150 }
151
152
153 }
编写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;
}
}
}
前段请求
1 /**
2 * 导出
3 */
4 $scope.exportTable = function () {
5 var param = packParam();
6 $scope.selTitleField = [];
7 $scope.selTitleName = [];
8 angular.forEach($scope.gridOption, function (item, index) {
9 if (item.checked) {
10 $scope.selTitleField.push(item.field);
11 }
12 });
13 param.exportField = $scope.selTitleField;
14 param.jh = $scope.filter.jh;
15 var paramArr = [];
16 for (let key in param) {
17 if (param[key] || param[key] === 0) {
18 paramArr.push(key + '=' + param[key])
19 }
20 }
21 $window.open(url.excel + '?' + paramArr.join('&'));
22 }
dao层我就不写了。以上代码根据自己的需要再改下就可以完成，根据传入的表头信息灵活导出excel，注意前段传入的表头信息，必须和实体的属性一直