ResultHandler流式查询导出Excel数据
// 导出Excel头部名称
List<String> headerArray = Arrays.asList("编码","姓名");
// 数据库对应字段
List<String> fieldArray = Arrays.asList("id", "name");
// 定义Excel文件名，不带“xlsx”后缀。
String fileName = getToday()+"导出数据-共计("+count+")"+"条";
//每次导出New 一个 handler 对象。将headerArray,fieldArray,fileName 传入参数。
ExcelResultHandler<查询对象> handler = new ExcelResultHandler<查询对象>(headerArray,fieldArray,fileName) {
public void tryFetchDataAndWriteToExcel() {
// this 指的是 New 对象，可添加查询 条件参数。
// mapper的方法需要是void返回，并且参数中含ResultHandler(流式查询遍历的条件)，可以加条件参数，
// fetchSize参数必填。
//见下图
//<select id="export" parameterType="" resultType="" fetchSize="-2147483648">
***Mapper.export(this);
}
};
//开始调用方法。原理：调用开始后，表头写入Excel，遍历结果集，一条一条数据写入Excel，最后关闭资源。
handler.startExportExcel();
源码会判断方法返回值是否void,是否含有ResultHandler
ExcelResultHandler工具类：
按需修改
public abstract class ExcelResultHandler<T> implements ResultHandler<T>{
private final Logger logger = LoggerFactory.getLogger(this.getClass());
private AtomicInteger currentRowNumber = new AtomicInteger(0);
private Sheet sheet = null;
private List<String> headerArray ;
private List<String> fieldArray ;
private int totalCellNumber;
private boolean isExportZip = true;
private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
private String exportFileName = UUID.randomUUID().toString().replace("-", "");
public ExcelResultHandler(List<String> headerArray,List<String> fieldArray){
this.headerArray = headerArray;
this.fieldArray = fieldArray;
this.totalCellNumber = headerArray.size();
}
public ExcelResultHandler(List<String> headerArray,List<String> fieldArray,boolean isExportZip){
this(headerArray,fieldArray);
this.isExportZip = isExportZip;
}
public ExcelResultHandler(List<String> headerArray,List<String> fieldArray,String exportFileName){
this(headerArray,fieldArray);
this.exportFileName = exportFileName;
}
public ExcelResultHandler(List<String> headerArray,List<String> fieldArray,String exportFileName,boolean isExportZip){
this(headerArray,fieldArray,exportFileName);
this.isExportZip = isExportZip;
}
public abstract void tryFetchDataAndWriteToExcel();
public void handleResult(ResultContext<? extends T> resultContext) {
Object aRowData = resultContext.getResultObject();
callBackWriteRowdataToExcel(aRowData);
}
public void startExportExcel() {
HttpServletResponse response = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getResponse();
ZipOutputStream zos = null;
OutputStream os = null;
try {
logger.info("--------->>>>写入Excel开始.." );
//写入文件
response.setContentType("application/octet-stream");
response.setHeader("Content-Disposition", "attachment;filename=" + new String((exportFileName+".zip").replaceAll(" ", "").getBytes("utf-8"),"iso8859-1"));
os = new BufferedOutputStream(response.getOutputStream());
if(isExportZip){
zos = new ZipOutputStream(os);
ZipEntry zipEntry = new ZipEntry(new String((exportFileName+".xlsx").replaceAll(" ", "")));
zos.putNextEntry(zipEntry);
}
SXSSFWorkbook wb = new SXSSFWorkbook();
wb.setCompressTempFiles(false);
sheet = wb.createSheet("Sheet 1");
Row row = sheet.createRow(0);
for (int cellNumber = 0; cellNumber < totalCellNumber; cellNumber++) {
Cell cell = row.createCell(cellNumber);
cell.setCellValue(headerArray.get(cellNumber));
}
tryFetchDataAndWriteToExcel();
logger.info("--------->>>> write to excel size now is {}", currentRowNumber.get() );
if(isExportZip){
wb.write(zos);
}else{
wb.write(os);
}
if (wb != null) {
wb.dispose();// 删除临时文件，很重要，否则磁盘可能会被写满
}
wb.close();
logger.info("--------->>>>全部数据写入Excel完成.." );
} catch (Exception e) {
logger.error("系统异常，请联系管理员",e);
} finally {
logger.info("--------->>>>关闭流系统.." );
if(isExportZip){
try {if(zos!=null) zos.close();} catch (IOException e1) {logger.error("关闭流系统异常，请联系管理员",e1); }
}else{
try {if(os!=null) os.close();} catch (IOException e1) {logger.error("关闭流系统异常，请联系管理员",e1); }
}
}
}
@SuppressWarnings("rawtypes")
public void callBackWriteRowdataToExcel(Object aRowData) {
MethodAccessor methodAccessor = Reflector.getMethodAccessor(aRowData.getClass());
currentRowNumber.incrementAndGet();
Row row = sheet.createRow(currentRowNumber.get());
for (int cellNumber = 0; cellNumber < totalCellNumber; cellNumber++) {
Object value = null;
if(aRowData instanceof Map){
value = ((Map)aRowData).get(fieldArray.get(cellNumber));
}else {
value = methodAccessor.getFieldValue(aRowData, fieldArray.get(cellNumber));
}
Cell cell = row.createCell(cellNumber);
if (value!=null && value instanceof Date){
cell.setCellValue(sdf.format(value));//
}else {
cell.setCellValue(value==null?"":value.toString());//写入数据
}
}
if(currentRowNumber.get() % 5000 == 0 ){
logger.info("--------->>>> write to excel size now is {}", currentRowNumber.get() );
}
}
}
因为无返回值，前端如何知道是否下载完成？
前端用下载监听器。
var index = layer.load(3, {
shade: [0.1, '#fff'],
content: '<span class="loadtip">下载中</span>',
success: function (layer) {
layer.find('.layui-layer-content').css({
'padding-top': '30px',
'width': '100px',
});
layer.find('.loadtip').css({
'font-size':'18px',
'margin-left':'5px'
});
}
});
const xhr = new XMLHttpRequest();
xhr.open('GET', '下载接口');
xhr.send(null);
// 设置服务端的响应类型
xhr.responseType = "blob";
// 监听下载
xhr.addEventListener('progress', event => {
// 计算出百分比
const percent = ((event.loaded / event.total) * 100).toFixed(2);
console.log(`下载进度：${percent}`);
}, false);
xhr.onreadystatechange = event => {
if (xhr.readyState == 4) {
if (xhr.status == 200) {
// 获取ContentType
const contentType = xhr.getResponseHeader('Content-Type');
// 文件名称 如果想和后端一样，需要处理编码
const fileName = "名称.zip";
// 创建一个a标签用于下载
const donwLoadLink = document.createElement('a');
donwLoadLink.download = fileName;
donwLoadLink.href = URL.createObjectURL(xhr.response);
// 触发下载事件，IO到磁盘
donwLoadLink.click();
// 释放内存中的资源
URL.revokeObjectURL(donwLoadLink.href);
// 关闭加载动画
layer.close(index);
} else if (response.status == 404) {
alert(`文件：${file} 不存在`);
} else if (response.status == 500) {
alert('系统异常');
}
}
}