1 引入pom文件
<!--easypoi 一对多导入导出 -->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>4.2.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>4.2.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>4.2.0</version>
</dependency>
2 实体类封装
@Data
public class ExportOrderVo {
/**
* 客户名称
*/
@Excel(name = "客户名称",needMerge = true)
private String customName;
/** 客户状态(1-正常，0-停用) */
@Excel(name = "客户状态",needMerge = true)
private String customState;
/** 客户级别(001-战略客户,002-重点客户,003-普通客户) */
@Excel(name = "客户级别",needMerge = true)
private String level;
/** 订单总价 */
@Excel(name = "订单总价",needMerge = true)
private BigDecimal orderTotalPrice;
/** 联系人 */
@Excel(name = "联系人",needMerge = true)
private String contacts;
/** 发货方式 */
@Excel(name = "发货方式",needMerge = true)
private String deliveryStr;
/**
*/
@Excel(name = "订单状态",needMerge = true)
private String orderStatus;
/**
* 产品集合
*/
@ExcelCollection(name = "产品信息")
private List<ExportOrderProductVo> exportOrderProductVos;
}
@Data
public class ExportOrderProductVo {
/**
* 产品名称
*/
@Excel(name = "产品名称")
private String prodInfoName;
/** 单价 */
@Excel(name = "单价")
private BigDecimal price;
/** 数量 */
@Excel(name = "数量")
private Long num;
/** 产品代码 */
@Excel(name = "产品代码")
private String prodCode;
/** 产品编号 */
@Excel(name = "产品编号")
private String prodNumber;
}
3工具类封装
package com.zehong.common.utils.poi;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import org.apache.poi.ss.usermodel.Workbook;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.util.List;
public class EasyPoiExcelUtil {
public static <T> void downLoadExcel(String fileName, HttpServletResponse response,Workbook workbook) throws RuntimeException{
ExportParams params = new ExportParams();
params.setSheetName("data");//设置sheet名
try {
fileName = new String(fileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1);
response.setCharacterEncoding("utf-8");
response.setHeader("content-Type", "application/octet-stream");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
workbook.write(response.getOutputStream());
} catch (IOException e) {
// 一个自定义枚举 错误信息的
e.printStackTrace();
throw new RuntimeException("下载出错");
}
}
public static <T> void downLoadExcel(String fileName, HttpServletResponse response,Class clazz, List<T> dataList) throws RuntimeException{
ExportParams params = new ExportParams();
params.setSheetName("data");//设置sheet名
params.setTitle("订单");
params.setType(ExcelType.XSSF);
List<ExcelExportEntity> excelExportEntities = dynamicTemplate(hideOrDisplayHeadTitle,clazz);
workbook = ExcelExportUtil.exportExcel(params,excelExportEntities, dataList);
downLoadExcel(fileName, response, workbook);
}
}
4 动态组装标题头方法-dynamicTemplate
主要：组装多的那个 ExcelExportEntity 时，需要设置一个 Method，例如： Method exportOrderProductVos = clazz.getMethod("getExportOrderProductVos", null); excelentity.setMethod(exportOrderProductVos);
public static List<ExcelExportEntity> dynamicTemplate1(List<Map<String,Object>> hideOrDisplayHeadTitle,Class clazz) {
List<ExcelExportEntity> entity = new ArrayList<>();
ExcelExportEntity excelentity = null;
ExcelExportEntity exportProductItem = null;
List<ExcelExportEntity> excelExportEntities = new ArrayList<>();
for (Map map : hideOrDisplayHeadTitle) {
//构造普通字段
Object label = map.get("label");
String value = "";
boolean needMerge = false;
if("订单编号".equals(label.toString())) {
value = "orderNumber";
needMerge = true;
} else if("客户名称".equals(label.toString())) {
value = "customName";
needMerge = true;
} else if("客户状态".equals(label.toString())) {
value = "customState";
needMerge = true;
} else if("产品名称".equals(label.toString())) {
needMerge = false;
}
if (needMerge) {
excelentity = new ExcelExportEntity(label.toString(), value);
excelentity.setNeedMerge(needMerge);
entity.add(excelentity);
} else {
excelentity = new ExcelExportEntity("产品","exportOrderProductVos");
try {
Method exportOrderProductVos = clazz.getMethod("getExportOrderProductVos", null);
excelentity.setMethod(exportOrderProductVos);
} catch (NoSuchMethodException e) {
throw new RuntimeException(e);
}
exportProductItem = new ExcelExportEntity("产品名称","prodInfoName");
excelExportEntities.add(exportProductItem);
exportProductItem = new ExcelExportEntity("单价","price");
excelExportEntities.add(exportProductItem);
exportProductItem = new ExcelExportEntity("数量","num");
excelExportEntities.add(exportProductItem);
exportProductItem = new ExcelExportEntity("产品代码","prodCode");
excelExportEntities.add(exportProductItem);
exportProductItem = new ExcelExportEntity("产品编号","prodNumber");
excelExportEntities.add(exportProductItem);
excelentity.setList(excelExportEntities);
entity.add(excelentity);
}
}
return entity;
}
5 接口调用
@GetMapping("/exportAttr")
public void exportAttr(TOrder order, HttpServletResponse response)
{
List<ExportOrderVo> exportOrderVos = productsProvidedToOrdersService.exportListOrder(order);
EasyPoiExcelUtil.downLoadExcel("订单.xlsx", response, ExportOrderVo.class, exportOrderVos);
}
6 js注意点 - responseType:blob
export function exportAttr(query) {
return request({
url: '/order/exportAttr',
method: 'get',
responseType: 'blob',
params: query
})
}
7 调用
handleExport() {
exportAttr(this.queryParams).then((res) =>{
let blob = new Blob([res], {type: 'data:application/vnd.ms-excel;base64;charset=utf-8'});
let downloadElement = document.createElement('a');
let href = window.URL.createObjectURL(blob); //创建下载的链接
downloadElement.href = href;
downloadElement.download = '订单'+'.xlsx'; //下载后文件名
document.body.appendChild(downloadElement);
downloadElement.click(); //点击下载
document.body.removeChild(downloadElement); //下载完成移除元素
window.URL.revokeObjectURL(href); //释放掉blob对象
return true
})
}
