两种方式,一种是直接查出所有的数据一次性写入excel,另一种是边读边写的流式查询,前一种情况适用于数据量较少的情况
1.构建实体类
@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
public class LogExportFieldInfo {
@ExcelProperty("执行人全名")
private String executorFullName;
@ExcelProperty("执行人账号")
private String executorName;
@ExcelProperty(value = "日志类型")
private String logType;
@ExcelProperty("客户端IP")
private String fromIp;
@ExcelProperty("执行时间")
private Date executeTime;
@ExcelProperty("模块名称")
private String moduleName;
@ExcelProperty("方法名称")
private String methodName;
@ExcelProperty(value = "执行结果", converter = StatusConverter.class)
private Integer result;
@ExcelProperty("执行日志明细")
private String detail;
}
@ExcelProperty注解声明的是实际excel导出后的表头,会将对应的java对象值写入到表格中,导出excel如下所示:
执行人全名 | 执行人账号 | 日志类型 | 客户端IP | 执行时间 | 模块名称 | 方法名称 | 执行结果 | 执行日志明细 |
---|---|---|---|---|---|---|---|---|
张三 | zhangsan | - | 127.0.0.1 | 2023-08-18 16:44:26 | 日志管理 | 新增日志 | 成功 |
2.导出少量数据
一次性查询出所有数据后通过字节流的形式写入到excel中,这种情况下如果检索数据过多很容易造成内存溢出OOM,适用于页面勾选几条数据后导出。
/**
* 日志导出(适用于数据量较少)
*
* @param info
* @throws IOException
*/
public void download(Info info) throws IOException {
HttpServletResponse response = info.getResponse();
response.setContentType("APPLICATION/OCTET-STREAM");
response.addHeader("Access-Contol-Expose-Headers", "Content-Type,Content-Disposition");
response.addHeader("Content-Disposition", "attachment;filename=" + info.getFileName());
ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
EasyExcel.write(byteArrayOutputStream, LogExportFieldInfo.class).sheet("log").doWrite(info.getData());
IOUtils.write(byteArrayOutputStream.toByteArray(), response.getOutputStream());
}
public class LogExportInfo {
HttpServletResponse response;
// 导出的数据集合
List<LogExportFieldInfo> data;
// 导出excel的名称
String fileName;
}
3.导出大量数据
/**
* 日志导出(适用于数据量较多)
*
* @param fileName
* @param resultHandler
* @throws IOException
*/
public void download(String fileName, CommonResultHandler resultHandler) throws IOException {
ServletOutputStream out = null;
try {
HttpServletResponse response = resultHandler.getResponse();
out = response.getOutputStream();
response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName).getBytes("gb2312"), "ISO-8859-1"));
response.setContentType("multipart/form-data");
response.setCharacterEncoding("utf-8");
resultHandler.getWriter().finish();
out.flush();
} finally {
if (out != null) {
try {
out.close();
} catch (Exception e) {
log.error("导出日志写入excel报错" + e.getMessage());
}
}
}
}
// manager层的download,负责从Dao层获取数据集合,处理包装后调用service层的下载服务
public void download(LogExportModel logExportModel, HttpServletResponse response) throws IOException {
CommonResultHandler resultHandler = new CommonResultHandler<LogInfo>(response, LogInfo.class) {
@Override
public LogExportFieldInfo processing(LogInfo logInfo) {
LogExportFieldInfo info = new LogExportFieldInfo();
// 我实际的数据库对象是LogInfo,但我仅需导出部分字段,因此做了一层映射转换
BeanUtil.copyProperties(logInfo, info);
return info;
}
};
QueryFilter queryFilter = new QueryFilter(logExportModel.getQueryBean());
// 查询数据
baseMapper.streamQuery(queryFilter.getMybatisParams(), resultHandler);
// service层封装导出方法,具体实现见上方
logExportService.download(fileName, resultHandler);
}
@Mapper
public interface LogItemDao {
// resultSetType 指定结果集的类型,这里是 ResultSetType.FORWARD_ONLY ,表示结果集只能向前遍历,不能回滚或向后遍历;
// fetchSize 指定获取数据的行数,这里是 Integer.MIN_VALUE ,表示获取所有可用的行数。
@Options(resultSetType = ResultSetType.FORWARD_ONLY, fetchSize = Integer.MIN_VALUE)
void streamQuery(Map<String, Object> params, ResultHandler<LogInfo> handler);
}
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.metadata.WriteSheet;
import lombok.Getter;
import lombok.SneakyThrows;
import org.apache.ibatis.session.ResultContext;
import org.apache.ibatis.session.ResultHandler;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
@Getter
public abstract class CommonResultHandler<T> implements ResultHandler<T> {
protected final HttpServletResponse response;
protected final ExcelWriter writer;
protected WriteSheet sheet;
protected ExcelWriterSheetBuilder excelWriterSheetBuilder;
protected final List<LogExportFieldInfo> rowDataList;
public CommonResultHandler(HttpServletResponse response, Class<? extends T> clazz) throws IOException {
this.response = response;
this.writer = EasyExcel.write(response.getOutputStream(), clazz).build();
rowDataList = new ArrayList<>(1);
this.initSheet();
}
public void initSheet() {
// 创建sheet
this.excelWriterSheetBuilder = EasyExcel.writerSheet();
// 设置表头
this.excelWriterSheetBuilder.head(LogExportFieldInfo.class);
this.sheet = this.excelWriterSheetBuilder.build();
}
@Override
@SneakyThrows
public void handleResult(ResultContext<? extends T> resultContext) {
// 获取单条检索数据
T obj = resultContext.getResultObject();
// 处理检索数据
rowDataList.add(processing(obj));
// 写入excel的sheet中
writer.write(rowDataList, sheet);
// 清除数据,释放资源
rowDataList.clear();
}
public abstract LogExportFieldInfo processing(T t);
}
核心实现通过mybatis的流式查询,handleResult会将每次查询出来的单条记录写入sheet中后立刻释放资源,避免的数据大量堆积。
4.数据转换
经常有数据在数据库存储为0,1,但是实际前端展示的时候显示为男,女,上面的实体类也是,我需要将result字段的1/0转换为成功/失败
在 Java 中, @ExcelProperty(value = “执行结果”, converter) 注解用于在读写操作期间将类的特定属性映射到 Excel 列。在这种情况下,被映射的属性名为 “result”, value 参数指定了相应的 Excel 列的名称。 converter 参数用于指定自定义转换器,将属性值与 Excel 单元格值进行相互转换。
@ExcelProperty(value = "执行结果", converter = StatusConverter.class)
private Integer result;
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
/**
* 描述:日志导出状态转换
* 创建人:
* 创建时间:
*/
public class StatusConverter implements Converter<Integer> {
@Override
public Class supportJavaTypeKey() {
return Integer.class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
}
@Override
public Integer convertToJavaData(CellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
return "成功".equals(cellData.getStringValue()) ? 1 : 0;
}
@Override
public CellData convertToExcelData(Integer value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
return new CellData(value.equals(1) ? "成功" : "失败");
}
}