第一步添加依赖
cn.afterturn
easypoi-spring-boot-starter
4.1.3
org.apache.poi
poi
org.apache.poi
poi-ooxml
org.apache.poi
poi-scratchpad
org.apache.poi
poi-ooxml-schemas
创建实体
DrillingDaily 注解@Excel表示需要导出那个字段，默认将其全部导出，不需要导出哪个列是，将注解
注解@Excel的值改为空字符串，就可以根据传入的表头信息灵活导出excel
package com.shiwen.jdzx.model.daily;
import cn.afterturn.easypoi.excel.annotation.Excel;
/**
*
Title:DrillingDaily
*
Description: 钻井日报
*
Company:西安石文软件有限公司
*
* @author liuguiyuan
* @date /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 packagecom.shiwen.jdzx.server.util;2
3 importcn.afterturn.easypoi.excel.annotation.Excel;4 importcn.afterturn.easypoi.excel.annotation.ExcelCollection;5 importcn.afterturn.easypoi.excel.annotation.ExcelEntity;6 importlombok.extern.slf4j.Slf4j;7
8 import java.lang.reflect.*;9 importjava.util.List;10 importjava.util.Map;11
12 /**
13 * @company: 石文软件有限公司14 * @description15 *@author: wangjie16 * @create: -01-10 11:1717 **/
18 @Slf4j19 public classModifyAnnotationValues {20
21 /**
22 * * 修改fields上@Excel注解的name属性，不需要下载的列，name修改增加_ignore.23 * * 保存原来的@Excel注解name属性值，本次生成后用来恢复24 * * @Params25 * * headers：用户勾选，由前端传来的列名，列名的key必须和Model字段对应26 * * clazz：model实体类27 * * excelMap：用来记录原值的map，因为用到了递归，这里返回值作为参数传入28 * *@returnMap 原实体类字段名和@Excel注解name属性值的映射关系29 *30 */
31 public static Map dynamicChangeAndSaveSourceAnnotation(List headers, Class clazz, MapexcelMap) {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 instanceofParameterizedType) {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 breakout;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 returnexcelMap;92 }93
94 //改变注解属性值，抽取的公共方法
95
96 private static voidchangeAnnotationValue(InvocationHandler handler, String propertyValue) {97 try{98 Field field = handler.getClass().getDeclaredField("memberValues");99 field.setAccessible(true);100 Map memberValues = (Map) 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, MapexcelMap) {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 instanceofParameterizedType) {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 mons.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 .URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.NoSuchElementException;
/**
* @author wangjie
* @date /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> 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> list, String fileName, HttpServletResponse response) {
Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
if (workbook != null) ;
downLoadExcel(fileName, response, workbook);
}
public static List importExcel(String filePath, Integer titleRows, Integer headerRows, Class pojoClass) {
if (StringUtils.isBlank(filePath)) {
return null;
}
ImportParams params = new ImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
List 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 List importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class pojoClass) {
if (file == null) {
return null;
}
ImportParams params = new ImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
List 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 exportField参数是表头信息jh,jb......
package com.shiwen.jdzx.server.controller;
import com.mon.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 /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 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.mon.WellCondition;
import org.springframework.ui.ModelMap;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
/**
* @author wangjie
* @date /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.mon.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 /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 excelMap=new HashMap<>();
Map 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 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 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 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，注意前段传入的表头信息，必须和实体的属性一直
