pom文件
<!--Excel导出-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.7</version>
</dependency>
我把他抽取成了一个公共的bean复制粘贴使用即可
package com.fuyubl.areca.device.biz.export.template;
/**
* Excel导出工具类
*
* @author 7
* @date 2022/8/29 15:12
*/
import javax.servlet.http.HttpServletResponse;
import java.util.List;
public interface BaseExcelTableExport {
/**
* 默认的文件导出
*
* @param response 响应
* @param clazz 标头
* @param dataList 数据集合
* @param fileName 文件名称
*/
void export(HttpServletResponse response, Class clazz, List dataList, String fileName);
}
实现类
package com.fuyubl.areca.device.biz.export.template;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.poi.excel.ExcelUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.handler.WriteHandler;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.style.column.SimpleColumnWidthStyleStrategy;
import com.alibaba.excel.write.style.row.SimpleRowHeightStyleStrategy;
import com.fuyubl.areca.security.common.exception.auth.BusinessException;
import com.google.common.collect.Lists;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.Date;
import java.util.List;
/**
* @author 7
* @date 2022/8/29 15:14
*/
@Service
@Slf4j
public class ExcelPort implements BaseExcelTableExport {
/**
* @param response 响应
* @param objClazz 标头信息
* @param dataList 数据集合
* @param fileName 文件名称
*/
@Override
public void export(HttpServletResponse response, Class objClazz, List dataList, String fileName) {
try {
log.info("开始Excel导出 - - - - - - - - - - - ");
if (CollUtil.isEmpty(dataList) || ObjectUtil.isEmpty(objClazz) || ObjectUtil.isEmpty(response)) {
log.error("Response - - - - - - - >为:{}", response);
log.error("标头为:{}", objClazz);
log.error("需要导出数据为- - - - - - - >:{}", dataList);
throw new BusinessException("Excel导出失败! 可能是因为参数错误引起的 ~");
}
// 字节数组输出流
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
// 构建写入对象
ExcelWriter excelWriter = EasyExcel.write(outputStream).build();
// 这里可以多个WriteSheet对象
WriteSheet obj = EasyExcel.writerSheet(0, "").head(objClazz).build();
// 这里可以写多个write
excelWriter.write(dataList, obj);
// 写入完成
excelWriter.finish();
fileName = fileName == null ? DateUtil.format(new Date(), "yyyy-MM-dd") + ".xlsx" : fileName;
outputStream.writeTo(response.getOutputStream());
response.setContentType("application/octet-stream");
fileName = URLEncoder.encode(fileName, "UTF-8");
response.setHeader("Content-Disposition", String.format("attachment; filename=\"%s\"; filename*=utf-8''%s", fileName, fileName));
response.setContentLengthLong(outputStream.size());
} catch (IOException e) {
log.error("Excel导出失败, 详情原因请查询日志", e, e.getMessage());
e.printStackTrace();
}
}
}
标头的pojo和你要展现的数据的实体要一致
/**
* @author 7
* @date 2022/8/29 15:24
*/
@Accessors
@Getter
@Setter
@EqualsAndHashCode
@ContentRowHeight(15)
@HeadRowHeight(20)
@ColumnWidth(15)
public class BatchExportTitle {
/**
* 支付状态 0-未付款 1-已付款 2-财务审核 3-已完成
*/
@ExcelProperty(value = "支付状态", index = 6,converter = PaymentConverter.class)
@ColumnWidth(8)
private Integer payStatus;
/**
* 编号
*/
@ExcelProperty(value = "编号", index = 0)
@ColumnWidth(22)
private String id;
@ColumnWidth(22) 通过这个注解调节高宽
像这种 支付状态 0-未付款 1-已付款 2-财务审核 3-已完成
通过 @ExcelProperty(value = "支付状态", index = 6,converter = PaymentConverter.class)
实现 implements Converter<Integer> 这个接口
@Override
public Class supportJavaTypeKey() {
return null;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return null;
}
@Override
public Integer convertToJavaData(CellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
return null;
}
@Override
public CellData convertToExcelData(Integer value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
return PaymentStatus.getMsg(value) == null ? new CellData("未知") : new CellData(PaymentStatus.getMsg(value));
}
我这边是写了一个枚举
package com.fuyubl.areca.device.biz.export.enums;
import lombok.AllArgsConstructor;
import lombok.Getter;
/**
* @author 7
* @date 2022/8/29 17:22
*/
@Getter
@AllArgsConstructor
public enum PaymentStatus {
// 支付状态 0-未付款 1-已付款 2- 3-
UNPAID(0, "未付款"),
PAID(1, "已付款"),
FINANC_IALAUDIT(2, "财务审核"),
COMPLETED(3, "已完成");
private Integer code;
private String msg;
public static String getMsg(Integer code) {
for (PaymentStatus obj : PaymentStatus.values()) {
if (obj.getCode().equals(code)) {
return obj.getMsg();
}
}
return null;
}
}
Controller或者service直接使用 注入之前的bean
@RequestMapping(value = "/batchExport", method = RequestMethod.GET)
public DataResult batchExport(@RequestBody BatchExport batch, HttpServletResponse response) {
List<CashVerifyCouponRecordVo> obj = exportReceiptBiz.batch(batch);
export.export(response, BatchExportTitle.class, obj, DateUtil.now() + "销售订单统计.xlsx");
return DataResult.ok("success");
}
很简单的
最后看看效果