POI异步分批导出大量数据到excel

kenick

已于 2024-06-06 14:40:45 修改

阅读量2.3k

点赞数

分类专栏： java 文章标签： java 开发语言

于 2022-10-08 17:29:32 首次发布

本文链接：https://blog.csdn.net/leadseczgw01/article/details/127212346

版权

java 专栏收录该内容

89 篇文章 2 订阅

订阅专栏

主方法

    /**
     * <一句话功能简述> 导出大量数据到Excel
     * <功能详细描述>
     * author: zhanggw
     * @param lineHeadArray 列头信息
     * @param exportPath 导出路径
     * @param writeExcelDataDelegated 向excel写数据委托类，根据业务实现
     */
    public static void exportExcelBigData(String[] lineHeadArray, String exportPath, WriteExcelDataDelegated writeExcelDataDelegated) throws Exception {
        logger.info("开始导出数据到excel,导出路径:{}", exportPath);

        // 初始化EXCEL
        SXSSFWorkbook wb = initExcelSimple(lineHeadArray);

        // 调用委托类分批写入数据
        SXSSFSheet sheet = wb.getSheetAt(0);
        // 每次从mysql读取后写入到excel的最大数据量
        int pageSize = PER_WRITE_ROW_COUNT;
        for (int currentWriteNum = 1; currentWriteNum <= PER_SHEET_WRITE_COUNT; currentWriteNum++) { // 最多分PER_SHEET_WRITE_COUNT批写入
            if(!writeExcelDataDelegated.isHasMore()){ // 没有更多数据,停止获取写入
                break;
            }

            // excel写数据的起始位置
            int startRowCount = (currentWriteNum - 1) * pageSize + 1;
            // excel写数据的结束位置
            int endRowCount = startRowCount + pageSize - 1;
            logger.debug("startRowCount:{},endRowCount:{},getWriteRowNum:{}", startRowCount, endRowCount, writeExcelDataDelegated.getWriteRowNum());
            writeExcelDataDelegated.writeExcelData(wb, sheet, startRowCount, endRowCount, currentWriteNum, pageSize);
        }

        // 保存EXCEL到本地
        downLoadExcelToLocalPath(wb, exportPath);
        logger.info("导出完成,导出总记录数:{},导出路径:{}", writeExcelDataDelegated.getWriteRowNum(), exportPath);
    }

初始化excel方法

    /**
     * <一句话功能简述> 初始化excel
     * author: zhanggw
     * 创建时间:  2022/10/8
     * @param lineHeadArray 每列头信息，如["学号","姓名"]
     */
    private static SXSSFWorkbook initExcelSimple(String[] lineHeadArray) {
        // 在内存当中保留100行,超过的数据放到硬盘中
        SXSSFWorkbook wb = new SXSSFWorkbook(100);

        // excel样式
        CellStyle headerCellStyle = wb.createCellStyle();
        headerCellStyle.setAlignment(HorizontalAlignment.CENTER);// 居中
        headerCellStyle.setBorderBottom(BorderStyle.THIN); //下边框    
        headerCellStyle.setBorderLeft(BorderStyle.THIN); //左边框   
        headerCellStyle.setBorderTop(BorderStyle.THIN); //上边框    
        headerCellStyle.setBorderRight(BorderStyle.THIN); //右边框

        // 填充第一行每列头信息
        SXSSFSheet sheet = wb.createSheet("sheet1");
        SXSSFRow headRow = sheet.createRow(0);
        for (int k = 0; k < lineHeadArray.length; k++) {
            SXSSFCell headRowCell = headRow.createCell(k);
            headRowCell.setCellStyle(headerCellStyle);
            headRowCell.setCellValue(lineHeadArray[k]);
        }
        return wb;
    }

导出excel写数据委托类

import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFSheet;

/**
 * <一句话功能简述> 导出excel写数据委托类
 * <功能详细描述> 跟POIUtil的exportExcelBigData方法一起使用
 * author: zhanggw
 * 创建时间:  2022/10/8
 */
public abstract class WriteExcelDataDelegated {
    protected Long writeRowNum = 0L; // 已写入excel表的行数,在writeExcelData方法中设置
    protected boolean hasMore = true; // 是否还有更多数据待写入excel，在writeExcelData方法中设置

    /**
     * <一句话功能简述> excel写数据委托类，针对不同的情况自行实现
     * <功能详细描述>
     * author: zhanggw
     * 创建时间:  2019/06/18 14:33
     * @param currentSheet 当前写入excel的sheet页
     * @param startRowCount 当前写入excel的开始行
     * @param endRowCount 当前写入excel的结束行
     * @param currentPage 分批查询起始页码
     * @param pageSize 分批查询每次查询数据量
     */
    public abstract void writeExcelData(Workbook workbook, SXSSFSheet currentSheet, Integer startRowCount, Integer endRowCount, Integer currentPage, Integer pageSize) throws Exception;

    public Long getWriteRowNum(){
        return writeRowNum;
    }

    public void setWriteRowNum(Long writeRowNum) {
        this.writeRowNum = writeRowNum;
    }

    public boolean isHasMore() {
        return hasMore;
    }

    public void setHasMore(boolean hasMore) {
        this.hasMore = hasMore;
    }

}

保存excel到本地

    /**
     * <一句话功能简述> 保存excel到本地
     * author: zhanggw
     * 创建时间:  2019/06/18 14:39
     * @param wb excel对象
     * @param exportPath 保存路径
     */
    private static void downLoadExcelToLocalPath(SXSSFWorkbook wb, String exportPath) {
        FileOutputStream fops = null;
        BufferedOutputStream bos = null;
        try {
            fops = new FileOutputStream(exportPath);
            bos = new BufferedOutputStream(fops);
            wb.write(bos);
        } catch (Exception e) {
            logger.error(e.getMessage());
        } finally {
            if (null != wb) {
                try {
                    wb.dispose();
                } catch (Exception e) {
                    logger.error(e.getMessage());
                }
            }
            if (null != bos) {
                try {
                    bos.close();
                } catch (Exception e) {
                    logger.error(e.getMessage());
                }
            }
            if (null != fops) {
                try {
                    fops.close();
                } catch (Exception e) {
                    logger.error(e.getMessage());
                }
            }
        }
    }

使用方式如下

	public JSONObject exportStatisticsData(Date now) {
		JSONObject retJson = new JSONObject();
		try{
			// 统计商品信息
			Date startDate = DateUtils.getPastDateZero(now, 90);
			Date endDate = DateUtils.getPastDateZero(now, 0);
			Date offStartDate = DateUtils.getPastDateZero(now, 14);
			unMapper.deleteTmp();
			int saleNum = unMapper.insertSaleProduct(startDate, endDate);
			int offNum = unMapper.insertOffShelveProduct(offStartDate, endDate);
			logger.debug("插入在售商品数量:{},下架商品数量:{}", saleNum, offNum);
			unMapper.updateTmpSupplierName();
			unMapper.updateTmpThreeClass();
			unMapper.updateClassOneName();
			unMapper.updateClassTwoName();
			unMapper.updateClassThreeName();

			// 导出商品信息到excel
			WriteExcelDataDelegated writeExcelDataDelegated = new WriteExcelDataDelegated() {
				@Override
				public void writeExcelData(Workbook workbook, SXSSFSheet eachSheet, Integer startRowCount, Integer endRowCount, Integer currentPage, Integer pageSize) throws Exception {
					try{
						PageHelper.startPage(currentPage, pageSize); // 分页获取数据
						List<Map<String, Object>> dataList = unMapper.getExportProductInfo();

						if (dataList != null && dataList.size()>0) {
							CellStyle cellStyle = workbook.createCellStyle();
							cellStyle.setAlignment(HorizontalAlignment.CENTER);// 居中
							cellStyle.setBorderTop(BorderStyle.THIN); //上边框  
							cellStyle.setBorderRight(BorderStyle.THIN); //右边框
							cellStyle.setBorderBottom(BorderStyle.THIN); //下边框   
							cellStyle.setBorderLeft(BorderStyle.THIN); //左边框

							for (int i = startRowCount; i <= endRowCount; i++) {
								SXSSFRow eachDataRow = eachSheet.createRow(i);
								int dataArrayStartIndex = i - startRowCount;
								if (dataArrayStartIndex < dataList.size()) {
									Map<String, Object> dataMap = dataList.get(dataArrayStartIndex);

									// 商品信息
									POIUtil.setShipOrderCell(eachDataRow, 0, dataMap.get("item_name"), cellStyle);
									POIUtil.setShipOrderCell(eachDataRow, 1, dataMap.get("class_one_name"), cellStyle);
									POIUtil.setShipOrderCell(eachDataRow, 2, dataMap.get("class_two_name"), cellStyle);
									POIUtil.setShipOrderCell(eachDataRow, 3, dataMap.get("class_three_name"), cellStyle);
									POIUtil.setShipOrderCell(eachDataRow, 4, dataMap.get("show_price"), cellStyle);
									POIUtil.setShipOrderCell(eachDataRow, 5, dataMap.get("supplier_name"), cellStyle);
									POIUtil.setShipOrderCell(eachDataRow, 6, dataMap.get("themeName"), cellStyle);
									POIUtil.setShipOrderCell(eachDataRow, 7, dataMap.get("saleState"), cellStyle);
									POIUtil.setShipOrderCell(eachDataRow, 8, dataMap.get("productTime"), cellStyle);
									POIUtil.setShipOrderCell(eachDataRow, 9, dataMap.get("groupNum"), cellStyle);

									// 记录当前写入数据
									writeRowNum++;
								}
							}
						}else{
							hasMore = false;
						}
					}catch (Exception e) {
						logger.error("商品信息导出EXCEL异常!", e);
					}
				}
			};
			// 文件本地保存路径
			String filePath = getSaveLocalPath("statistics") + DateUtils.getNowDateStr("yyyy-MM-dd_HHmmss")+ ".xlsx";
			// excel每列头名称
			String[] lineHeadArray = {"商品名称","一级品类","二级品类","三级品类","价格","供应商","专题","在售情况","时间","组号"};
			// 执行导出
			POIUtil.exportExcelBigData(lineHeadArray, filePath, writeExcelDataDelegated);
			retJson.put("filePath", filePath);
		}catch (Exception e){
			logger.error("exportStatisticsData异常", e);
		}
		return retJson;
	}