【实战】excel分页写入导出大文件

类

 @RequestMapping("export")@ResponseBodypublic void export(HttpServletResponse response) {long start = System.currentTimeMillis();QueryVo query = new QueryVo();// response响应头setResponseHeader(response, "excel");ExcelWriter writer = ExcelUtil.genExcelWriter(response);// 表格WriteTable table = new WriteTable();WriteSheet sheet = new WriteSheet();sheet.setSheetNo(1);sheet.setSheetName("excel");writer.write(Lists.newArrayList(),sheet,table);int pageNo=1;query.setPageSize(exportPageSize);int index = 0;while (true) {// 构造分页信息int offset = exportPageSize * (pageNo++ - 1);query.setOffset(offset);// 查询一页数据List<Pharmacist> list = mybatisDao.getList(query);if (CollectionUtils.isEmpty(list)) {break;}// 分批写入writer.write(list,sheet);}long end = System.currentTimeMillis();log.info("导出excel耗时:{}", end - start);writer.finish();}

工具类

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.example.demo.utils.BizException;
import com.example.demo.utils.JsonUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;/*** @Description: excel工具*/
@Slf4j
public class ExcelUtil {/*** 读取excel并处理数据* @param inputStream* @param dataInfo  function中处理的数据文件格式为 List<List<String>>*/public static void readExcel(InputStream inputStream,ExcelDataInfo dataInfo){log.info("------读取excel文件开始-------");ExcelDataListener headerListener = new ExcelDataListener(dataInfo);EasyExcel.read(inputStream, null,headerListener).sheet().headRowNumber(1).doRead();log.info("------读取excel文件结束-----\n------表头------\n" + JsonUtil.toJson(headerListener.getHeader()));}/*** 读取excel并处理数据* @param inputStream* @param dataInfo  function中处理的数据文件格式为 List<List<String>>*/public static void readExcel(InputStream inputStream,ExcelDataInfo dataInfo, int headerRow){log.info("------读取excel文件开始-------");ExcelDataListener headerListener = new ExcelDataListener(dataInfo);EasyExcel.read(inputStream, null,headerListener).sheet().headRowNumber(headerRow).doRead();log.info("------读取excel文件结束-----\n------表头------\n" + JsonUtil.toJson(headerListener.getHeader()));}/*** 生成writer* @param response* @return*/public static ExcelWriter genExcelWriter(HttpServletResponse response){WriteCellStyle wcs = new WriteCellStyle();// 垂直居中、 左对齐wcs.setVerticalAlignment(VerticalAlignment.CENTER);wcs.setHorizontalAlignment(HorizontalAlignment.LEFT);WriteFont wf = new WriteFont();wf.setFontHeightInPoints((short)12);wcs.setWriteFont(wf);// 表头WriteCellStyle hwcs = new WriteCellStyle();hwcs.setVerticalAlignment(VerticalAlignment.CENTER);hwcs.setHorizontalAlignment(HorizontalAlignment.CENTER);WriteFont hwf = new WriteFont();hwf.setFontHeightInPoints((short)12);hwf.setBold(Boolean.TRUE);hwcs.setWriteFont(hwf);try {ExcelWriter writer = EasyExcel.write(response.getOutputStream()).registerWriteHandler(new HorizontalCellStyleStrategy(hwcs,wcs)).build();return writer;} catch (Exception e) {log.error("导出excel失败",e);throw new BizException("导出excel失败", e);}}/*** 设置导出response响应头* @param response* @param fileName*/public static void setResponseHeader(HttpServletResponse response,String fileName){response.setContentType("application/vnd.ms-excel");response.setCharacterEncoding("utf-8");response.setHeader("Content-Disposition","attachment;filename="+fileName);response.setHeader("Param","no-cache");response.setHeader("Cache-Control","no-cache");}
}