一、引入 jar 包
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.5</version>
</dependency>
EasyExcel支持对实体类中的属性进行注解，方便后续进行读写操作。
id 为 表头，index 代表的是 第0行
@ExcelProperty(value="id",index=0)
二、编写excel导入对应实体类
@Data
public class ImportTradeDetailExcelMode implements Serializable {
@ApiModelProperty(value = "贝壳楼盘名")
@ExcelProperty("贝壳楼盘名")
private String bkGardenName;
@ApiModelProperty(value = "贝壳小区ID")
@ExcelProperty("贝壳小区ID")
private String bkGardenId;
@ApiModelProperty(value = "城市区域")
@ExcelProperty("区域")
private String regionName;
@ApiModelProperty(value = "城市名称")
@ExcelProperty("城市")
private String cityName;
@ApiModelProperty(value = "成交日期")
@ExcelProperty("成交时间")
@DateTimeFormat("yyyy-MM-dd")
private String tradeDate;
@ApiModelProperty(value = "房源价格")
@ExcelProperty("房源价格")
private String roomPrice;
@ApiModelProperty(value = "楼层")
@ExcelProperty("楼层")
private String floor;
@ApiModelProperty(value = "房源面积(单位M2)")
@ExcelProperty("房源面积")
private BigDecimal roomArea;
@ApiModelProperty(value = "朝向")
@ExcelProperty("朝向")
private String towards;
@ApiModelProperty(value = "户型")
@ExcelProperty("户型")
private String roomType;
@ApiModelProperty(value = "房屋用途")
@ExcelProperty("房屋用途")
private String roomUse;
@ExcelProperty("错误消息")
private String errorMsg;
}
三、导入
3.1、编写导入监听类
3.1.1 导入数据抽象数据监听类
/**
* @author: huangyibo
* @Date: 2022/3/24 16:03
* @Description: 导入数据抽象类明细数据监听类
*/
@Slf4j
public abstract class ImportDataAbstractListener<T> extends AnalysisEventListener<T> {
/**
* 每隔3000条存储数据库， 然后清理successList ，方便内存回收
*/
private static final int BATCH_COUNT = 3000;
public static int getBatchCount() {
return BATCH_COUNT;
}
/**
* 获取导入结果
* @return
*/
public abstract ImportDataReturnVo<T> getResult();
}
3.1.2 编写具体监听类
/**
* @author: huangyibo
* @Date: 2022/3/24 16:03
* @Description: 交易明细数据监听类
*/
@Slf4j
public class TradeDetailImportListener extends ImportDataAbstractListener<ImportTradeDetailExcelMode> {
/**
* 解析成功的数据
*/
private List<DictImportTradeDetailDTO> successList = new ArrayList<>();
/**
* 格式有问题的数据
*/
private List<ImportTradeDetailExcelMode> failList = new ArrayList<>();
/**
* 导入返回结果
*/
private ImportDataReturnVo<ImportTradeDetailExcelMode> returnVo;
private DictImportTradeDetailRemote dictImportTradeDetailRemote;
private String userId;
public TradeDetailImportListener(DictImportTradeDetailRemote dictImportTradeDetailRemote, String userId) {
this.dictImportTradeDetailRemote = dictImportTradeDetailRemote;
this.returnVo = new ImportDataReturnVo<>();
this.returnVo.setImportId(IdUtils.getUuid());
this.userId = userId;
}
@Override
public void invoke(ImportTradeDetailExcelMode data, AnalysisContext analysisContext) {
if("{}".equals(JSON.toJSONString(data))){
//log.info("成交数据为空, 不做处理, data:{}", JSON.toJSONString(data));
return;
}
DictImportTradeDetailDTO tradeDetailDTO = new DictImportTradeDetailDTO();
BeanUtils.copyProperties(data, tradeDetailDTO);
if (!validateEntity(data, tradeDetailDTO)) {
log.info("该数据必传参数校验不通过, data:{}", JSON.toJSONString(data));
return;
}
String tradeDate = data.getTradeDate();
String newTread = tradeDate.replaceAll("\\.", "-");
newTread = newTread.replaceAll("\\/", "-");
Date date = Date.from(LocalDate.parse(newTread, DateTimeFormatter.ofPattern("yyyy-MM-dd")).atStartOfDay(ZoneOffset.ofHours(8)).toInstant());
tradeDetailDTO.setTradeDate(date);
tradeDetailDTO.setImportId(returnVo.getImportId());
tradeDetailDTO.setCreateId(userId);
tradeDetailDTO.setModifyId(userId);
successList.add(tradeDetailDTO);
//每隔3000条存储数据库， 然后清理successList ，方便内存回收
if (successList.size() >= getBatchCount()) {
//分批存储数据
saveData();
successList.clear();
}
}
/**
* 校验表头，表头必须严格按照此格式和顺序
* @param headMap
* @param context
*/
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
String[] headList = {"日期", "贝壳楼盘名", "寻渡小区ID", "贝壳小区ID", "区域", "商圈", "城市",
"成交时间", "房源价格", "楼层", "房源面积", "朝向", "户型", "房屋用途", "房源ID"};
Collection<String> values = headMap.values();
for (String head : headList) {
if (!values.contains(head)) {
throw new RequestResultException("成交数据导入excel表头缺少[" + head + "]列");
}
}
//表头顺序严格校验
/*for (int i = 0; i < headList.length; i++) {
if(!Objects.equals(headList[i], headMap.get(i))){
throw new RequestResultException("成交数据导入excel表头["+headMap.get(i)+"]和解析格式excel表头["+headList[i]+"]不一致");
}
}*/
}
/**
* 校验必传参数
* @param data
* @param tradeDetailDTO
* @return
*/
private boolean validateEntity(ImportTradeDetailExcelMode data, DictImportTradeDetailDTO tradeDetailDTO) {
log.info("校验导入交易数据, data:{}", JSON.toJSONString(data));
if(StringUtils.isEmpty(data.getBatchNum())){
data.setErrorMsg("日期不能为空");
failList.add(data);
return false;
}
if(StringUtils.isEmpty(data.getKfGardenId())){
data.setErrorMsg("看房网小区ID不能为空");
failList.add(data);
return false;
}
if(StringUtils.isEmpty(data.getRoomLink())){
data.setErrorMsg("房源ID不能为空");
failList.add(data);
return false;
}
if(StringUtils.isEmpty(data.getRoomPrice())){
data.setErrorMsg("房源价格不能为空");
failList.add(data);
return false;
}else{
try {
BigDecimal roomPrice = new BigDecimal(String.valueOf(data.getRoomPrice()))
.multiply(new BigDecimal(10000));
if(roomPrice.compareTo(new BigDecimal(0)) <= 0){
data.setErrorMsg("房源价格必须大于0");
failList.add(data);
return false;
}
tradeDetailDTO.setRoomPrice(roomPrice);
} catch (Exception e) {
data.setErrorMsg("房源价格格式错误");
failList.add(data);
return false;
}
}
if(StringUtils.isEmpty(data.getRoomArea())){
data.setErrorMsg("房源面积不能为空");
failList.add(data);
return false;
}else{
try {
BigDecimal roomArea = new BigDecimal(String.valueOf(data.getRoomArea()));
if(roomArea.compareTo(new BigDecimal(0)) <= 0){
data.setErrorMsg("房源面积必须大于0");
failList.add(data);
return false;
}
tradeDetailDTO.setRoomArea(roomArea);
} catch (Exception e) {
data.setErrorMsg("房源面积格式错误");
failList.add(data);
return false;
}
}
if(StringUtils.isEmpty(data.getTradeDate())){
data.setErrorMsg("成交时间不能为空");
failList.add(data);
return false;
}
return true;
}
/**
* 所有数据读取完之后调用该方法
* @param analysisContext
*/
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
if(!CollectionUtils.isEmpty(successList)){
if(returnVo.getSuccessNum() == null){
returnVo.setSuccessNum(successList.size());
}else {
returnVo.setSuccessNum(returnVo.getSuccessNum() + successList.size());
}
log.info("存储成交明细数据, size:{}, successList:{}", successList.size(), JSON.toJSONString(successList));
dictImportTradeDetailRemote.batchInsert(successList);
log.info("存储成交明细数据SUCCESS, size:{}", successList.size());
}
returnVo.setFailList(failList);
returnVo.setFailNum(failList.size());
returnVo.setImportTotalNum(returnVo.getSuccessNum() + returnVo.getFailNum());
log.info("存储成交明细数据导入成功, size:{}, returnVo:{}", successList.size(), JSON.toJSONString(returnVo));
}
/**
* 分批存储数据库
*/
private void saveData() {
if(returnVo.getSuccessNum() == null){
returnVo.setSuccessNum(successList.size());
}else {
returnVo.setSuccessNum(returnVo.getSuccessNum() + successList.size());
}
log.info("存储成交明细数据, size:{}, successList:{}", successList.size(), JSON.toJSONString(successList));
dictImportTradeDetailRemote.batchInsert(successList);
log.info("存储成交明细数据SUCCESS, size:{}", successList.size());
}
/**
* 获取导入结果
* @return
*/
@Override
public ImportDataReturnVo<ImportTradeDetailExcelMode> getResult() {
return returnVo;
}
}
3.2、编写导入工具类
/**
* @author: huangyibo
* @Date: 2022/3/24 20:11
* @Description:
*/
public class EasyExcelUtil {
/**
* 下载文件时，针对不同浏览器，进行附件名的编码
*
* @param filename 下载文件名
*
* @param agent 客户端浏览器
*
* @return 编码后的下载附件名
* @throws IOException
*/
public static String encodeDownloadFilename(String filename, String agent)
throws IOException {
if (agent.contains("Firefox")) { // 火狐浏览器
filename = "=?UTF-8?B?"
+ Base64.getEncoder().encode(filename.getBytes("utf-8"))
+ "?=";
filename = filename.replaceAll("\r\n", "");
} else { // IE及其他浏览器
filename = URLEncoder.encode(filename, "utf-8");
filename = filename.replace("+"," ");
}
return filename;
}
/**
* 设置导出Excel格式
* @param response
* @param request
* @param filename
* @throws IOException
*/
public static void setExportExcelFormat(HttpServletResponse response, HttpServletRequest request, String filename) throws IOException {
String agent = request.getHeader("user-agent");//获得游览器
filename = filename + ".xlsx";
String downloadFilename = encodeDownloadFilename(filename, agent); //使用工具类解决文件乱码的问题
response.setCharacterEncoding("UTF-8");
// 设置响应输出的头类型
response.setHeader("content-Type", "application/vnd.ms-excel");
// 下载文件的默认名称
response.setHeader("Content-Disposition", "attachment;filename="+downloadFilename);
response.setHeader("Pragma", "public");
response.setHeader("Cache-Control", "no-store");
response.addHeader("Cache-Control", "max-age=0");
}
/**
* 读取导入excel数据(单sheet页读取)
* @param file 上传文件
* @param clazz 导入实体类型
* @param excelListener 数据导入监听器
* @return ImportDataReturnVo<?>
* @throws IOException
*/
public static ImportDataReturnVo<?> read(MultipartFile file, Class<?> clazz, ImportDataAbstractListener<?> excelListener) throws IOException {
EasyExcel.read(file.getInputStream(), clazz, excelListener).sheet().doRead();
return excelListener.getResult();
}
/**
* 读取导入excel数据(读取全部sheet页)
* @param file 上传文件
* @param clazz 导入实体类型
* @param excelListener 数据导入监听器
* @return ImportDataReturnVo<?>
* @throws IOException
*/
public static ImportDataReturnVo<?> readAll(MultipartFile file, Class<?> clazz, ImportDataAbstractListener<?> excelListener) throws IOException {
EasyExcel.read(file.getInputStream(), clazz, excelListener).doReadAll();
return excelListener.getResult();
}
/**
* 读取导入excel数据(读取指定sheet页)
* @param file 上传文件
* @param clazz 导入实体类型
* @param excelListener 数据导入监听器
* @param sheetNumList 指定的sheetNum
* @return ImportDataReturnVo<?>
* @throws IOException
*/
public static ImportDataReturnVo<?> readSheet(MultipartFile file, Class<?> clazz, ImportDataAbstractListener<?> excelListener, List<Integer> sheetNumList) throws IOException {
ExcelReader excelReader = EasyExcel.read(file.getInputStream()).build();
// 读取不同的数据, 需要使用不同的head 和Listener
List<ReadSheet> readSheetList = new ArrayList<>();
sheetNumList.forEach(sheetNum -> {
// readSheet参数设置读取sheet的序号
ReadSheet readSheet = EasyExcel.readSheet(sheetNum).head(clazz).registerReadListener(excelListener).build();
readSheetList.add(readSheet);
});
// 这里注意 一定要把readSheetList一起传进去，不然有个问题就是03版的excel 会读取多次，浪费性能
excelReader.read(readSheetList);
// 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的
excelReader.finish();
return excelListener.getResult();
}
/**
* 将数据写入Excel并写入流中
* @param outputStream 输出流
* @param clazz 导出实体类型
* @param list 导出数据集合
* @param sheetName sheet页名称
*/
public static void writeExcel2Stream(ByteArrayOutputStream outputStream, Class<?> clazz, List<?> list, String sheetName){
EasyExcel.write(outputStream, clazz).sheet(sheetName).doWrite(list);
}
/**
* 单sheet页写入Excel
* @param outputStream 输出流
* @param clazz 导出实体类型
* @param list 导出数据集合
* @param sheetName sheet页名称
*/
public static void writeExcel(ServletOutputStream outputStream, Class<?> clazz, List<?> list, String sheetName) {
EasyExcel.write(outputStream, clazz)
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
.sheet(sheetName)
.doWrite(list);
}
/**
* 多sheet页写入Excel
* @param excelWriter excelWriter写出对象
* @param clazz 导出实体类型
* @param list 导出数据集合
* @param num sheet页码
* @param sheetName sheet页名称
*/
public static void writerSheetExcel(ExcelWriter excelWriter, Class<?> clazz, List<?> list, Integer num, String sheetName) {
WriteSheet writeSheet = EasyExcel.writerSheet(num, sheetName).head(clazz)
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
excelWriter.write(list, writeSheet);
}
}
3.3、业务层调用
/**
* 导入数据源记录 前端控制器
*
* @author huangyibo
* @since 2022-03-11
*/
@Lazy
@RestController
@RequestMapping("/security/dictImportDataRecord")
@Api(tags = ApiSwaggerTags.DICT_IMPORT,value = "导入数据源记录")
@Slf4j
public class DictImportDataRecordController extends WebBaseController {
@Resource
private DictImportDataRecordRemote dictImportDataRecordRemote;
@Resource
private DictImportTradeDetailRemote dictImportTradeDetailRemote;
@Resource
private AliyunOss aliyunOss;
@PostMapping("/importTrade")
@ApiOperation(value = "导入成交明细数据")
@ApiResponses({
@ApiResponse(message = "导入成交明细数据", code = 200, response = ImportDataReturnVo.class)
})
public String importTradeData(@RequestParam("file") MultipartFile file) {
JwtUserInfo userInfo = getCurrentUserInfo();
DictImportDataRecordDTO dataRecordDTO = new DictImportDataRecordDTO();
ImportDataReturnVo<?> result = new ImportDataReturnVo<>();
try {
TradeDetailImportListener excelListener = new TradeDetailImportListener(dictImportTradeDetailRemote, userInfo.getUserId());
result = EasyExcelUtil.read(file, ImportTradeDetailExcelMode.class, excelListener);
if(!CollectionUtils.isEmpty(result.getFailList())){
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
//将格式错误数据写入Excel并写入流中
EasyExcelUtil.writeExcel2Stream(outputStream, ImportTradeDetailExcelMode.class, result.getFailList(), "成交数据");
MultipartFile multipartFile = FileUtil.getMultipartFileFromStream(outputStream, "成交数据错误数据.xlsx");
//上传aliyun 对象存储 OSS
AliyunUploadRequest uploadRequest = new AliyunUploadRequest(multipartFile);
AliyunUploadResult uploadResult = aliyunOss.fileUpload(uploadRequest);
log.info("导入成交明细数据, 校验不通过数据写入excel上传到阿里云oss成功, result:{}", JSON.toJSONString(result));
if(uploadResult != null){
dataRecordDTO.setFailDataFileUrl(uploadResult.getUrl());
}
}
BeanUtils.copyProperties(result, dataRecordDTO);
dataRecordDTO.setDataType(DataTypeEnum.TRADE_DATA);
dataRecordDTO.setCreateId(userInfo.getUserId());
dataRecordDTO.setModifyId(userInfo.getUserId());
dataRecordDTO.setId(result.getImportId());
dictImportDataRecordRemote.insert(dataRecordDTO);
result.setSuccess(Boolean.TRUE);
} catch (Exception e) {
result.setSuccess(Boolean.FALSE);
result.setErrCode(DictReturnCodeEnum.F6000000.name());
if(e instanceof RequestResultException){
result.setErrMessage(e.getMessage());
}else {
result.setErrMessage(DictReturnCodeEnum.F6000000.getMessage());
}
log.info("导入成交明细数据 Exception, result:{}", JSON.toJSONString(result), e);
}
return successInfo(result);
}
}
四、导出
- EASYEXCEL导出百万级连表查询XLSX数据方法实测102万105秒
4.1 编写excel导出对应实体类
@Data
@ApiModel(description="估价表")
public class DictGardenAppraisalExport implements Serializable {
@ApiModelProperty(value = "城市名称")
@ColumnWidth(20)
@ExcelProperty(value = "城市", index = 0)
private String cityName;
@ApiModelProperty(value = "城市区域名称")
@ColumnWidth(20)
@ExcelProperty(value = "区域", index = 1)
private String regionName;
@ApiModelProperty(value = "商圈名称")
@ColumnWidth(20)
@ExcelProperty(value = "商圈", index = 2)
private String businessAreaName;
@ApiModelProperty(value = "楼盘名称")
@ColumnWidth(20)
@ExcelProperty(value = "楼盘名称", index = 3)
private String gardenName;
@ApiModelProperty(value = "物业类型(冗余字段，存储格式：普通住宅/公寓/酒店)")
@ColumnWidth(20)
@ExcelProperty(value = "物业类型", index = 4)
private String usageName;
@ApiModelProperty(value = "委托套数")
@ColumnWidth(20)
@ExcelProperty(value = "委托套数", index = 5)
private Integer trustNum;
@ApiModelProperty(value = "委托均价")
@ColumnWidth(20)
@ExcelProperty(value = "委托均价", index = 6)
private BigDecimal trustAvgPrice;
@ApiModelProperty(value = "成交套数")
@ColumnWidth(20)
@ExcelProperty(value = "成交套数", index = 7)
private Integer tradeNum;
@ApiModelProperty(value = "成交均价")
@ColumnWidth(20)
@ExcelProperty(value = "成交均价", index = 8)
private BigDecimal tradeAvgPrice;
@ApiModelProperty(value = "外部参考价")
@ColumnWidth(20)
@ExcelProperty(value = "外部参考价", index = 9)
private BigDecimal outerReferPrice;
@ApiModelProperty(value = "参考价")
@ColumnWidth(20)
@ExcelProperty(value = "参考价", index = 10)
private BigDecimal referPrice;
@ApiModelProperty(value = "政府指导价")
@ColumnWidth(20)
@ExcelProperty(value = "政府指导价", index = 11)
private BigDecimal governGuidePrice;
@ApiModelProperty(value = "价差幅度")
@ColumnWidth(20)
@ExcelProperty(value = "价差幅度", index = 12)
@NumberFormat("##0.##0%")
private BigDecimal diffPriceRange;
@ApiModelProperty(value = "参考价/第三方委托均价")
@ColumnWidth(20)
@ExcelProperty(value = "幅度1", index = 13)
@NumberFormat("##0.##0%")
private BigDecimal referThirdTrust;
@ApiModelProperty(value = "参考价/第三方成交均价")
@ColumnWidth(20)
@ExcelProperty(value = "幅度2", index = 14)
@NumberFormat("##0.##0%")
private BigDecimal referThirdTrade;
@ApiModelProperty(value = "参考价/第三方参考价")
@ColumnWidth(20)
@ExcelProperty(value = "幅度3", index = 15)
@NumberFormat("##0.##0%")
private BigDecimal referThirdRefer;
@ApiModelProperty(value = "估价日期")
@ColumnWidth(20)
@ExcelProperty(value = "估价日期", index = 16)
@DateTimeFormat("yyyy-MM-dd")
private Date referDate;
@ApiModelProperty(value = "发布状态：已发布=PUBLISHED、待发布=TOBE_PUBLISH、已撤回=REVOKE")
@ColumnWidth(20)
@ExcelProperty(value = "状态", index = 17)
private String publishStatus;
}
4.2导入逻辑编写
@PostMapping(value = "/exportAppraisal")
@ApiOperation(value = "导出估价信息")
@ApiResponses({
@ApiResponse(message = "导出估价信息", code = 200, response = DictGardenAppraisalVo.class)
})
public String exportAppraisal(@RequestBody DictGardenAppraisalQueryPageForm queryPageForm, HttpServletResponse response, HttpServletRequest request) {
try {
ServletOutputStream outputStream = response.getOutputStream();
queryPageForm.setPageSize(PAGE_SIZE);
Pagination<DictGardenAppraisalVo> page = dictGardenAppraisalRemote.queryDictGardenAppraisalPage(queryPageForm);
EasyExcelUtil.setExportExcelFormat(response,request,"估价信息");
//如果总数据量多余10万，分页导出
if(page.getRecordCount() > PAGE_SIZE){
//必须放到循环外，否则会刷新流
ExcelWriter excelWriter = EasyExcel.write(outputStream).build();
for (int i = 1; i <= page.getPageCount(); i++) {
queryPageForm.setCurrentPage(i);
Pagination<DictGardenAppraisalVo> appraisalPage = dictGardenAppraisalRemote.queryDictGardenAppraisalPage(queryPageForm);
if (!CollectionUtils.isEmpty(appraisalPage.getItems())) {
List<DictGardenAppraisalExport> exportList = getDictGardenAppraisalExports(appraisalPage);
WriteSheet writeSheet = EasyExcel.writerSheet(i, "估价信息" + i).head(DictGardenAppraisalExport.class)
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
excelWriter.write(exportList, writeSheet);
}
}
//刷新流
excelWriter.finish();
}else {
if (!CollectionUtils.isEmpty(page.getItems())) {
List<DictGardenAppraisalExport> exportList = getDictGardenAppraisalExports(page);
EasyExcel.write(outputStream,DictGardenAppraisalExport.class).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).sheet("估价信息").doWrite(exportList);
}
}
outputStream.flush();
response.getOutputStream().close();
return successInfo(true);
} catch (Exception e) {
log.error("估价信息数据导出excel Exception",e);
return successInfo(false);
}
}
//@AuthorityAnnotation(AuthorityAnnotationEnums.ADMIN)
@PostMapping(value = "/exportAppraisal")
@ApiOperation(value = "导出楼盘估价信息")
@ApiResponses({
@ApiResponse(message = "导出楼盘估价信息", code = 200, response = Boolean.class)
})
public String exportAppraisal(@RequestBody DictGardenAppraisalQueryPageForm queryPageForm, HttpServletResponse response, HttpServletRequest request) {
try {
ServletOutputStream outputStream = response.getOutputStream();
queryPageForm.setPageSize(PAGE_SIZE);
Pagination<DictGardenAppraisalVo> page = dictGardenAppraisalRemote.queryDictGardenAppraisalPage(queryPageForm);
EasyExcelUtil.setExportExcelFormat(response, request,"KF楼盘估价");
//如果总数据量多余10万，分页导出
if(page.getRecordCount() > PAGE_SIZE){
//必须放到循环外，否则会刷新流
ExcelWriter excelWriter = EasyExcel.write(outputStream).build();
for (int i = 1; i <= page.getPageCount(); i++) {
queryPageForm.setCurrentPage(i);
Pagination<DictGardenAppraisalVo> appraisalPage = dictGardenAppraisalRemote.queryDictGardenAppraisalPage(queryPageForm);
if (!CollectionUtils.isEmpty(appraisalPage.getItems())) {
List<DictGardenAppraisalExport> exportList = getDictGardenAppraisalExports(appraisalPage);
EasyExcelUtil.writerSheetExcel(excelWriter, DictGardenAppraisalExport.class, exportList, i, "楼盘估价" + i);
}
}
//刷新流
excelWriter.finish();
}else {
//总数据量小余10万，单页导出
if (!CollectionUtils.isEmpty(page.getItems())) {
List<DictGardenAppraisalExport> exportList = getDictGardenAppraisalExports(page);
EasyExcelUtil.writeExcel(outputStream, DictGardenAppraisalExport.class, exportList, "楼盘估价");
}
}
outputStream.flush();
outputStream.close();
return successInfo(true);
} catch (Exception e) {
log.error("楼盘估价数据导出excel Exception",e);
return successInfo(false);
}
}
实测：导出用时105秒左右
每个表10万条
参考：
https://www.yuque.com/easyexcel/doc/easyexcel
https://alibaba-easyexcel.github.io/
https://www.freesion.com/article/65771115640/
