Excel文件导出总结,包含大数据量的分批导出方式

已于 2023-08-15 10:34:41 修改
阅读量5.7k 收藏 35
点赞数 8
分类专栏： 学习记录 文章标签： java mysql spring boot
于 2020-09-17 14:49:13 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u010827544/article/details/108642952
版权

文章目录

目前本文章中分配导出的相关代码已更新至本人github的练习项目中
https://github.com/kimikudo/practice-back

更新记录

2023-08-10 谁能想到拖延很久很久终于在去年完成的笔记,居然真的帮助了我一次,遇到了一个类似的需求,根据模板导出数据,数据量大概1W行,300+列,文件大小15M左右. 本来的导出方式就是POI,一般的业务场景也不会有这么多列的情况,所以在尝试导出时候项目可能会直接崩溃.因为是个比较大比较老的项目,很多工具类都是封装好的,也不好重新引入其他工具,所以就在原来的基础上进行了一些修改优化,最终实现了这个需求,具体的我记录到下面POI原生方式-大数据量分批导出下面.

需求背景

前两天项目中有个将数据导出为Excel的功能,一开始用的是Excel4J进行导出,但是由于数据量较大导出很慢,而且会出现内存溢出的问题.所以准备进行优化.首先想到的是分批查询数据进行导出.搜索了一些资料也准备开始做了.但是同事说可以直接使用阿里的EasyExcel,导出速度和内存占用方面确实提升明显.但是分批导出的方式还是顺便自己业余时间实现一下并且做个记录吧!

参考内容

这里要感谢一下我搜到的一篇博客
POI百万级大数据量EXCEL导出
以及该作者另一篇EasyExcel实现的博客
阿里开源（EasyExcel）—导出EXCEL
之前发现Excel4J方式出现内存溢出问题之后,查询资料发现可以使用POI的SXSSFWorkbook来解决,于是就找到了这篇内容,他的解决方案给了我很大的启发,所以我才觉得自己实现并进行总结的.在此感谢这位作者!

导出方式

目前暂时总结了三种数据导出为Excel的方式,分别是Excel4J方式,POI原生方式,和阿里EasyExcel方式,其中Excel4J的效果不太理想,我也暂时不考虑使用该方法进行大数据量导出,只总结了该方式的普通导出用法,大数据量仅使用另外两种方式进行实现.

代码实现

Excel4J

普通导出

  1. 添加Excel4J依赖
    <!-- Excel4j导出 -->
<!-- https://mvnrepository.com/artifact/com.github.crab2died/Excel4J -->
<dependency>
    <groupId>com.github.crab2died</groupId>
    <artifactId>Excel4J</artifactId>
    <version>3.0.0</version>
</dependency>
  2. 使用注解标识导出表头
    这里使用一张IMSI数据表进行演示,使用注解方式,在数据实体类的属性上,使用@ExcelField(title = "IMSI", order = 1)注解将属性指定为表头,并可添加列序号,
    @Data
@ToString
@ApiModel(value = "IMSI源数据")
public class ImsiSourceData {
    /**
     * 源数据表主键
     */
    @ApiModelProperty("源数据表主键")
    private Integer id;
    /**
     * 设备编号
     */
    @ApiModelProperty("设备编号")
    @ExcelField(title = "设备编号", order = 2)
    private String deviceid;
    /**
     * imei
     */
    @ApiModelProperty("imei")
    private String imei;
    /**
     * imsi
     */
    @ApiModelProperty("imsi")
    @ExcelField(title = "IMSI", order = 1)
    private String imsi;
    /**
     * 上报时间
     */
    @ApiModelProperty("上报时间")
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    @ExcelField(title = "采集时间", order = 3, writeConverter = DateTimeConverter.class)
    private Long reportTime;
    /**
     * 归属地
     */
    @ApiModelProperty("归属地")
    @ExcelField(title = "归属地", order = 4)
    private String homeLocation;
    /**
     * 运营商
     */
    @ApiModelProperty("运营商")
    @ExcelField(title = "运营商", order = 5)
    private String networkOperator;
    /**
     * 网络制式
     */
    @ApiModelProperty("网络制式")
    @ExcelField(title = "网络制式", order = 6)
    private String terminalNetworkInformation;
}
  3. 数据导出
    通过查询数据库获得数据,再直接调用自带方法即可实现导出;
    其中,exportObjects2Excel()方法的参数,第三个可以指定为输出流,为了处理输出流 ,可能需要对响应头的属性进行设置.
    public void exportByExcel4J(HttpServletResponse response) throws IOException {
    List<ImsiSourceData> list = this.list(
            new QueryWrapper<ImsiSourceData>()
                    .orderByDesc("report_time")
                    .last(" LIMIT 50")
    );
    //Excel4J方式导出
    try {
        ExcelUtils.getInstance().exportObjects2Excel(list,ImsiSourceData.class,"IMSI导出测试.xlsx");
    } catch (Excel4JException e) {
        e.printStackTrace();
    }
}
  4. 效果测试
    我这里直接指定的文件名,即在项目根目录成功导出文件.
    在这里插入图片描述

POI原生方式

普通导出

这里只是简单的记录一下POI原生的导出方式,与本次博客内容关系不大,本次主要是记录分批导出的设计方案.

  1. 添加POI依赖
    <!-- POI依赖 -->
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
</dependency>
  2. 创建Excel对象,创建标题行
    //HSSFWorkbook是处理.xls文件的,XSSFWorkbook处理.xlsx文件
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("sheet1");
//标题行
HSSFRow headRow = sheet.createRow(0);
headRow.createCell(0).setCellValue("ID");
headRow.createCell(1).setCellValue("IMSI");
headRow.createCell(2).setCellValue("采集时间");
headRow.createCell(3).setCellValue("运营商");
  3. 遍历数据,创建内容行,并写入文件/输出流
    //写数据
List<ImsiSourceData> list = this.listData();
int i = 1;
for (ImsiSourceData imsiSourceData : list) {
    HSSFRow row = sheet.createRow(i);
    row.createCell(0).setCellValue(imsiSourceData.getId());
    row.createCell(1).setCellValue(imsiSourceData.getImsi());
    //时间戳字段的处理
    SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    row.createCell(2).setCellValue(format.format(new Date(imsiSourceData.getReportTime())));
    row.createCell(3).setCellValue(imsiSourceData.getNetworkOperator());
    i++;
}
FileOutputStream os = new FileOutputStream("POI导出_" + System.currentTimeMillis() + ".xls");
workbook.write(os);
os.flush();
os.close();
  4. 生成的文件效果
    在这里插入图片描述

大数据量分批导出

由于POI的工作簿对象会存在内存溢出的问题,所以在进行大数据量导出是,考虑使用SXSSFWorkbook对象,这是一个基于XSSF的工作簿对象,适用于大对象的创建和大数据量的导出,同时支持.xlsx后缀名.
POI的分批导出参考的是这篇博客: POI百万级大数据量EXCEL导出 自己参照这篇博客的方式尝试实现了一遍,该说不说原博主写的还不错!这里记录一下自己的实现过程.

2023-08更新 实际应用记录

遇到一个需求需要读取模板,然后向模板中写入相应数据,数据量大概在1W行300列 文件大小15M左右,正常使用XSSFWorkbook一样会挂,所以想到是否可以使用SXSSFWorkbook来实现写入操作. 由于数据量不算特别大,而且是多数据源的数据源根据人员拼接在一行里面,所以没必要才去之前记录的分次分批写入的操作,直接使用此工作簿对象进行写入即可.

  • 加载Workbook
    指定文件路径,读取为inputStream,再根据流创建XSSFWorkbook对象,最后使用XSSFWorkbook创建SXSSFWorkbook,并指定窗口大小,不指定也可以,会有默认的
/**
     * 打开exel模板文件-SXSSFWorkbook
     *
     * @param tempFileName
     * @return
     */
    public static SXSSFWorkbook loadExcelTemplateSXSSF(String tempFileName, Integer windowSize) {

        // 打开模板文件
        InputStream in = ExcelUtil.class.getClassLoader()
                .getResourceAsStream(tempFileName);
        // 新建HSSFWorkbook
        XSSFWorkbook workbook = null;
        SXSSFWorkbook sxssfWorkbook = null;
        try {
            workbook = new XSSFWorkbook(in);
            if (windowSize != null) {
                sxssfWorkbook = new SXSSFWorkbook(workbook, windowSize);
            } else {
                sxssfWorkbook = new SXSSFWorkbook(workbook);
            }

        } catch (IOException e) {
            System.err.println("创建SXSSFWorkbook失败");
            e.printStackTrace();
        }

        try {
            in.close();
        } catch (IOException e) {
            System.err.println("关闭模板文件失败");
            e.printStackTrace();
        }

        return sxssfWorkbook;
    }
  • 写数据
    之后正常使用此workbook获取sheet进行写数据即可,就可以实现窗口分批写入数据了,超简单!
SXSSFSheet sheet = sxssfWorkbook.getSheetAt(0);
  • !!!需要读取文件进行修改操作时
    由于此次是读取模板进行写数据,模板难免会有需要修改一下表头的情况,当出现这种应用场景,如果使用SXSSFSheet 时一般会提示xxx行已写入磁盘不可修改之类的异常,其实这种时候只要将写入数据和修改模板的操作分开,前后进行就行了.写入大数据量数据时使用SXSSFSheet,修改模板时使用XSSFSheet即可,结合使用更好用哦!
Sheet sheet= workbook.getXSSFWorkbook().getSheetAt(0);
依赖版本
  • POI 5.2.2 用于excel写入
  • MyBatisPlus 3.4 用于数据查询
实现思路

在贴代码之前先说一下这个功能的实现思路
数据量大的情况下导出数据,需将数据划分为不同的sheet,再根据不同sheet进行写入,所以在写入前需先确定单个sheet保存多少条数据,单次写入多少条数据,从而可以计算出单个sheet的写入次数.再根据次数进行循环写入.数据查询也是在每次写入时完成,可以直接理解为分页查询,根据当次的写入量查询对应数量的数据来完成写入,从而尽可能不发生内存溢出的问题.
规定数据条数的常量如下

public class ExcelConstant {
    /**
     * 每次写入的记录条数
     */
    public static final Integer WRITE_ROW_COUNT = 100000;

    /**
     * 单个sheet保存的记录数
     */
    public static final Integer SHEET_ROW_COUNT = 500000;

    /**
     * 每个sheet的写入次数: 总数/单次写入
     */
    public static final Integer WRITE_TIMES = SHEET_ROW_COUNT / WRITE_ROW_COUNT;
}
POI工具类

该工具类包含根据数据量来初始化Workbook,创建对应数量的sheet,并设置表头;将Workbook保存到文件或输出流;分批将数据写入Workbook等功能

import javax.servlet.http.HttpServletResponse;

import com.kay.practiceback.practice.excel.poi.constant.ExcelConstant;
import com.kay.practiceback.practice.excel.poi.delegate.WriteDataDelegated;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import java.io.*;
import java.nio.charset.StandardCharsets;

/**
 * POI工具类
 *
 * @author Kay
 * @date 2022-05-19
 */
@Slf4j
public class PoiUtil {
    /**
     * 初始化表格
     *
     * @param totalRow 数据总行数
     * @param titles   列名集合
     * @return workbook
     */
    public static SXSSFWorkbook init(Integer totalRow, String[] titles) {
        //定义窗口行数,即进行读写时,内存中存在100条数据
        SXSSFWorkbook wb = new SXSSFWorkbook(1000);
        //计算需要多少个sheet
        int sheetCount = (totalRow % ExcelConstant.SHEET_ROW_COUNT == 0) ?
                (totalRow / ExcelConstant.SHEET_ROW_COUNT) : (totalRow / ExcelConstant.SHEET_ROW_COUNT + 1);
        log.info("初始化excel,共计 {} 条数据,共计 {} 个sheet", totalRow, sheetCount);

        for (int i = 0; i < sheetCount; i++) {
            //初始化sheet
            SXSSFSheet sheet = wb.createSheet("sheet" + (i + 1));
            //创建表头行,并写入表头
            SXSSFRow headRow = sheet.createRow(0);
            for (int j = 0; j < titles.length; j++) {
                SXSSFCell headCell = headRow.createCell(j);
                headCell.setCellValue(titles[j]);
            }
        }
        log.info("Excel文件初始化完成,共计{}个sheet", sheetCount);
        return wb;
    }

    /**
     * 将excel保存到本地路径
     *
     * @param wb   excel对象
     * @param path 保存路径
     */
    public static void saveExcelToPath(SXSSFWorkbook wb, String path) {
        FileOutputStream fos = null;
        try {
            fos = new FileOutputStream(path);
            wb.write(fos);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (null != wb) {
                wb.dispose();
            }
            if (null != fos) {
                try {
                    fos.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 将excel转为输出流
     *
     * @param wb       excel
     * @param response response
     * @param fileName 文件名
     */
    public static void saveExcelToStream(SXSSFWorkbook wb, HttpServletResponse response, String fileName) throws UnsupportedEncodingException {
        response.setHeader("Content-disposition", "attachment;filename=" + new String((fileName + ".xlsx").getBytes(StandardCharsets.UTF_8), "ISO8859-1"));
        OutputStream outputStream = null;
        try {
            outputStream = response.getOutputStream();
            wb.write(outputStream);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (null != wb) {
                try {
                    wb.dispose();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            if (null != outputStream) {
                try {
                    outputStream.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 导出Excel到指定路径
     *
     * @param totalRow           总记录数
     * @param titles             标题
     * @param exportPath         导出路径
     * @param writeDataDelegated 数据写入委托
     */
    public static void exportToPath(Integer totalRow, String[] titles, String exportPath, WriteDataDelegated writeDataDelegated) {
        log.info("开始导出excel文件...");
        //初始化工作簿
        SXSSFWorkbook workbook = PoiUtil.writeWorkbook(totalRow, titles, writeDataDelegated);
        //写入成功后将excel保存到指定路径
        PoiUtil.saveExcelToPath(workbook, exportPath);
        log.info("Excel文件导出完成");
    }

    /**
     * 导出Excel到指定路径
     *
     * @param totalRow           总记录数
     * @param titles             标题
     * @param response           导出的输出流
     * @param fileName           文件名
     * @param writeDataDelegated 数据写入委托
     */
    public static void exportToStream(Integer totalRow, String[] titles, HttpServletResponse response, String fileName, WriteDataDelegated writeDataDelegated) throws UnsupportedEncodingException {
        log.info("开始导出excel到流...");
        SXSSFWorkbook workbook = PoiUtil.writeWorkbook(totalRow, titles, writeDataDelegated);
        //写入成功后将excel保存到指定路径
        PoiUtil.saveExcelToStream(workbook, response, fileName);
        log.info("Excel输出流导出完成");
    }

	/**
     * 数据写入,创建工作簿并写入数据
     *
     * @param totalRow           总数据条数
     * @param titles             表头
     * @param writeDataDelegated 写数据委托
     * @return workbook
     */
    public static SXSSFWorkbook writeWorkbook(Integer totalRow, String[] titles, WriteDataDelegated writeDataDelegated) {
        log.info("写入数据开始...");
        //初始化工作簿
        SXSSFWorkbook workbook = PoiUtil.init(totalRow, titles);
        //获取sheet数目,遍历写入sheet
        int sheetCount = workbook.getNumberOfSheets();
        for (int i = 0; i < sheetCount; i++) {
            log.info("开始第{}次写入", i + 1);
            SXSSFSheet sheet = workbook.getSheetAt(i);
            //根据单个sheet和单次写入数量计算的写入次数,来进行多次写入
            for (int j = 0; j < ExcelConstant.WRITE_TIMES; j++) {
                int pageNum = i * ExcelConstant.WRITE_TIMES + j + 1;
                int pageSize = ExcelConstant.WRITE_ROW_COUNT;
                int startRow = j * ExcelConstant.WRITE_ROW_COUNT + 1;
                writeDataDelegated.writeData(sheet, startRow, pageNum, pageSize);
            }
        }
        return workbook;
    }
}
数据写入

参考原博客的委托方式,这里也创建了一个委托接口,可在接口的实现中自行查询数据并将数据逐行写入sheet,我这里创建了一个订单表的写数据委托实现,如果需要其他内容的导出,再创建一个接口实现即可
接口

import org.apache.poi.xssf.streaming.SXSSFSheet;

/**
 * Excel数据写入委托类
 *
 * @author Kay
 * @date 2022-07-13
 */
public interface WriteDataDelegated {
    /**
     * 分页查询并将数据写入excel
     *
     * @param sheet    写入的数据表
     * @param startRow 写入开始行
     * @param pageNum  数据查询分页页数
     * @param pageSize 数据查询记录条数
     */
    void writeData(SXSSFSheet sheet, Integer startRow, Integer pageNum, Integer pageSize);
}

实现
这里仅作练习和测试使用,所以数据比较简单只是单表查询,导出的字段也随便填了几个,实际应用中可以根据实际需求进行增减和格式化

import cn.hutool.core.util.NumberUtil;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.kay.practiceback.db.order.entity.OrderRecord;
import com.kay.practiceback.db.order.service.OrderRecordService;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.util.List;

/**
 * 订单记录的Excel数据写入委托
 *
 * @author Kay
 * @date 2022-07-13
 */
@Slf4j
@Component
public class OrderWriteDataDelegated implements WriteDataDelegated {

    @Autowired
    private OrderRecordService orderRecordService;

    @Override
    public void writeData(SXSSFSheet sheet, Integer startRow, Integer pageNum, Integer pageSize) {
        log.info("开始分批查询并写入数据...");
        //根据条件查询待写入的数据
        List<OrderRecord> orderList = orderRecordService.page(new Page<OrderRecord>(pageNum, pageSize),
                new LambdaQueryWrapper<OrderRecord>().orderByDesc(OrderRecord::getCreateTime)).getRecords();
        log.info("本次写入起始行号为:{},共计 {} 条数据", startRow, orderList.size());
        if (orderList.size() > 0) {
            for (int i = startRow; i < orderList.size() + startRow; i++) {
                OrderRecord data = orderList.get(i - startRow);
                SXSSFRow row;
                if (null != sheet.getRow(i)) {
                    row = sheet.getRow(i);
                } else {
                    row = sheet.createRow(i);
                }
                //写入单行数据,根据实际需要写入所需字段,这里仅写入几列进行测试
                row.createCell(0).setCellValue(data.getId().toString());
                row.createCell(1).setCellValue(data.getClientId().toString());
                row.createCell(2).setCellValue(data.getAccount().toString());
                row.createCell(3).setCellValue(NumberUtil.div(data.getDeno().toString(), "100",0).toString());
                row.createCell(4).setCellValue(NumberUtil.decimalFormat("#.##", NumberUtil.div(data.getPrice().toString(), "100")));
                row.createCell(5).setCellValue(data.getState());
                row.createCell(6).setCellValue(data.getCreateTime().toString());
            }
        }
        log.info("本次写入完成,当前sheet总行数为:{}", sheet.getLastRowNum());
    }
}
调用测试

创建服务类,定义文件名,进行到处之前先计算一下要导出的数据总数,之后执行导出即可

@Service
public class PoiExportServiceImpl implements PoiExportService {

    @Autowired
    private OrderRecordService orderRecordService;
   	/**
     * 写数据委托类,使用注解注入所需的实现
     */
    @Autowired
    @Qualifier("orderWriteDataDelegated")
    private OrderWriteDataDelegated orderWriteDataDelegated;

    @Override
    public void exportOrder(String fileName) {
    	//定义表头,去委托中相同
        String[] titles = {"订单号", "渠道编号", "充值账号", "面额", "售价", "状态", "创建时间"};
        String path = "D:\\Kay\\export\\";
        File pathFile = new File(path);
        if (!pathFile.exists()) {
            pathFile.mkdirs();
        }
        //计算总条数,与委托类中的查询相同
        Long totalCount = orderRecordService.count(new LambdaQueryWrapper<OrderRecord>().orderByDesc(OrderRecord::getCreateTime));
        PoiUtil.exportToPath(totalCount.intValue(), titles, path + fileName, orderWriteDataDelegated);
    }
}
测试结果

我的数据表中大概有66W条数据,导出总共用时1分钟左右,文件大小29M左右,功能基本上是实现了的,截图了一sheet1的行数,与预期一致,时间可以格式化的,偷懒没弄!就这样吧!
在这里插入图片描述

一个小意外

代码刚完成开始测试时候,第二次写入总是报错 Attempting to write a row[100001] in the range [0,661376] that is already written to disk.,开始以为是循环时候写入的行号不正确,仔细审阅了代码发现也没什么问题…于是开始逐行debug…最后发现了…是第一次写入时候数据查询的分页没有生效!单次就像给我写入总条数了!..赶紧加上了MyBatisPlus的分页配置,问题解决

@Configuration
public class MyBatisPlusConfig {
    @Bean
    public MybatisPlusInterceptor paginationInnerInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.H2));
        return interceptor;
    }
}

EasyExcel

因为这个博客坑了太久了,期间其实EasyExcel也更新了,所以这里就按我现在填坑时候的操作来写吧!

普通导出

普通导出参考官方文档的步骤即可: 写Excel|EasyExcel
这里的版本是3.1.1

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.1.1</version>
</dependency>

在实体类的属性上添加@ExcelProperty注解规定需要导出的列,注解可以规定index指定顺序,无需导出的添加@ExcelIgnore即可,对于日期或数值类型的字段可以使用 @DateTimeFormat或@NumberFormat进行格式化,对于需要自定义输出格式的可以自行实现Converter<T>接口进行格式化
对于Long型的字段,可以使用EasyExcel自带的LongStringConverter将Long转为String

@ApiModelProperty("订单号")
    @ExcelProperty(value = "订单号", index = 0, converter = LongStringConverter.class)
    private Long id;

    @ExcelProperty(value = "渠道编码", index = 1)
    private Integer clientId;

    @ExcelProperty(value = "支付金额", index = 6, converter = AmountConverter.class)
    private Integer price;

    @ExcelIgnore
    private Integer payType;

    @ExcelProperty(value = "充值金额", index = 5, converter = AmountConverter.class)
    private Integer deno;

    @ExcelProperty(value = "创建时间", index = 8)
    @DateTimeFormat("yyyy-MM-dd HH:mm:ss")
    private LocalDateTime createTime;

Converter:

import cn.hutool.core.util.NumberUtil;
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.converters.WriteConverterContext;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.data.WriteCellData;

public class AmountConverter implements Converter<Integer> {
    @Override
    public Class<Integer> supportJavaTypeKey() {
        return Integer.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    @Override
    public WriteCellData<?> convertToExcelData(WriteConverterContext<Integer> context) throws Exception {
        return new WriteCellData<String>(NumberUtil.div(context.getValue().toString(), "100", 2).toString());
    }
}

之后直接进行写Excel操作即可,其他写Excel方式参考文档就好了~

@Override
    public void simpleWrite() {
        String fileName = "D:\\Kay\\export\\订单导出_" + System.currentTimeMillis() + ".xlsx";
        EasyExcel.write(fileName, OrderRecord.class).sheet("订单记录").doWrite(() -> {
            return orderRecordService.lambdaQuery().orderByDesc(OrderRecord::getCreateTime).last(" LIMIT 100").list();
        });
    }

最后的效果
在这里插入图片描述

大数据量分批导出

EasyExcel的分批导出相较于POI简单了许多,原理与POI是相同的,都是分页查询数据,然后再写入sheet,循环查询并写入直到所有数据导出完成,代码也几行就搞定了!重点关注EasyExcel相关的几行操作即可,如创建excelWriter,创建sheet并写入数据,最后excelWriter.finish()输出文件

	@Override
    public void batchExport() {
        log.info("开始导出数据...");
        String fileName = "D:\\Kay\\export\\订单导出_批量_" + System.currentTimeMillis() + ".xlsx";
        ExcelWriter excelWriter = EasyExcel.write(fileName, OrderRecord.class).build();
        //数据总量
        long totalCount = orderRecordService.count(new LambdaQueryWrapper<OrderRecord>().orderByDesc(OrderRecord::getCreateTime));
        //单个sheet保存的数据量
        int perSheetCount = 100000;
        //sheet总数
        long sheetCount = totalCount % perSheetCount == 0 ? (totalCount / perSheetCount) : (totalCount / perSheetCount + 1);
        log.info("总数据量:{},共计{}个sheet", totalCount, sheetCount);
        for (int i = 0; i < sheetCount; i++) {
            log.info("开始第{}个sheet写入", i);
            //分页查询写入数据
            List<OrderRecord> orderList = orderRecordService.page(new Page<OrderRecord>(i + 1, perSheetCount),
                    new LambdaQueryWrapper<OrderRecord>().orderByDesc(OrderRecord::getCreateTime)).getRecords();
            //创建sheet
            WriteSheet sheet = EasyExcel.writerSheet(i, "订单记录-" + i).build();
            excelWriter.write(orderList, sheet);
        }
        excelWriter.finish();
        log.info("数据导出完成");
    }

结果测试

导出文件
在这里插入图片描述

EasyExcel我导出的列相较于POI多几个,为了测试自定义格式转换的功能,而且sheet数量也不同,最终导出66W数据耗时1分钟16s,文件大小45.5M

结语

至此!这个已经挖了快两年的坑终于被我填上了!当时挖坑是因为我们的项目中的导出功能在数据量很大时会产生内存溢出,后来切换为EasyExcel就解决了当时的问题,但是当时翻阅资料时候看到了最开始提到的那位作者的文章,想着自己一定也要再来实现一次才好!但是拖延症加上好后来跳槽了之后变得异常忙碌,一直搁置了没有填坑,最近难得闲下来了,开始回头重新学些这些!期间EasyExcel也出了好几版,这里用的是目前最新的版本,也算给自己留个存档吧!!!
好了!还有个前端学习的坑要填!继续努力去咯!

KimiKudo
关注
  • 8
    点赞
  • 踩
  • 35
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
常见的场景优化方案：导出Excel大批量数据的优化过程
weixin_71690375的博客
05-30 2242
团队目前在做一个用户数据看板（下面简称看板），基本覆盖用户的所有行为数据，并生成分析报表，用户行为由多个数据来源组成（餐饮、生活日用、充值消费、交通出行、通讯物流、交通出行、医疗保健、住房物业、运动健康...），看看标红的代码，这个生成Excel的方式是对Excel中的每一个cell进行渲染，逐行的进行数据填充，效率太慢了，根据日志分析发现：基本时间都耗费在数据生成Excel上。目前遇到的主要问题是，随着行为能力逐渐的完善闭环，用户数据沉淀的也越来越多了，同时业务量的也在不断扩大。
导出大量数据到excel
05-31
导出大量数据到excel,本人已亲测，对几百万数据已测试过，导出数据比较乐观，里面自带压缩文件功能。
easyExcel怎么大规模导入导出
weixin_35752122的博客
02-13 365
EasyExcel是一个用于Java的快速、高效的读写Excel的开源库。在大规模导入导出时，EasyExcel提供了以下几种解决方案： 分批读写：通过分批读写的方式，每次读取或写入一定量的数据，减少内存消耗。 边读边处理：通过读取数据并实时处理，减少内存消耗。 异步读写：通过异步读写的方式，可以提高读写效率。 通过使用上述方法，可以大大提高EasyExcel的读写效率，帮助您解决大规模...
easy-excel批量导出数据
weixin_44699742的博客
11-20 1690
easy-excel高级使用
2024年大数据最新EasyExcel导出大数据文件（100万条）_easyexcel导出百万数据，面试官必问的技术问题之一
最新发布
2301_78399616的博客
05-09 430
可以看出，主要的耗时都集中在导出中。下图为jconsole工具在进程运行期间的参数展示，可以看出内存几乎和CPU占有率都挺高的。
大数据量导出到Excel方法总结
热门推荐
小团团的专栏
07-30 1万+
欢迎大家访问我的博客：地址​​​​​​​ 最近项目中牵扯到大数据量导出到Excel。传统的jxl，poi等在后端生成excel的方法就不见得有多奏效。 1. JXL后端生成Excel代码（struts2 action方法代码）： public String excel() throws Exception{ ByteArrayOutputStream os = new ByteA...
JAVA将百万级数据高效的导出到EXCEL表单
波波的博客
02-02 3346
JAVA将百万级数据高效的导出到EXCEL表单
Java导出大批量数据(分批查询导出篇)
夜の雨的博客
09-05 8585
​上篇文章介绍了 java导出文件格式篇xls,xlsx,csv 本篇介绍下 大批量数据的导出思路和方法 导出数据慢的原因: 一次性查询太多数据 sql会很慢 太多数据导出处理很慢 这里我讲解下分批查询和分批导出分批查询方法 一: 根据数据量 分割每一部分数据 二: 根据查询时间间隔 分割为每一天一个时间段 ​
java web开发中大量数据导出Excel超时(504)问题解决
08-30
在Java Web开发中，当需要将大量数据导出到Excel文件时，可能会遇到超时（如504错误）的问题。这种问题通常是由于服务器处理时间过长，超过了Web服务器或应用服务器设置的超时限制。以下是一些解决此类问题的方法和...
C#实现几十万级数据导出Excel及Excel各种操作实例
08-31
在C#中，处理大量数据并将其导出到Excel文件是一项常见的任务，特别是在数据分析、报表生成和数据交换的场景中。本文将详细讲解如何使用C#实现几十万级数据高效地导出到Excel，以及执行其他Excel操作。我们将探讨...
Java实现excel大数据量导入
09-07
Java实现Excel大数据量导入是一项常见的任务，特别是在处理企业级数据导入导出时。Apache POI 是Java中广泛使用的库，用于处理Microsoft Office格式的文件，包括Excel。然而，当涉及大量数据时，直接使用POI的用户...
C#导出数据到excel如何提升性能
08-18
以下将详细讨论如何通过优化关键步骤来提升C#导出数据到Excel的性能。 首先，我们需要识别性能瓶颈。通常，有两个主要的耗时环节：数据库查询和数据处理。对于数据库查询，如果数据量较小，我们可以使用一次性查询...
Java导出超大Excel文件，防止内存溢出
CXY_QIQI的博客
05-20 1万+
将业务数据导出到Excel表中，导出任务数据量较大时，导出的项目就会内存溢出，本文通过Java操作Poi的SXSSFWorkbook类进行导出，解决内存溢出问题。 1.采用Poi中的SXSSFWorkbook 在实现excel导出时，在数据量过大的情况下，总是容易发生内存溢出的情况。可以使用POI提供的 SXSSFWorkbook 类来避免内存溢出。 2.maven中引入Poi <!-- poi start --> <dependency> &lt
Excel300万数据导入导出优化方案，从80s优化到8s（实测）
公众号-老炮说Java
09-30 2564
大家好，我是老赵！前景在项目开发中往往需要使用到数据的导入和导出，导入就是从Excel中导入到DB中,而导出就是从DB中查询数据然后使用POI写到Excel上。写本文的背景是因为在工作中遇到了大数据的导入和导出，问题既然来了逃跑不如干掉它！！！只要这一次解决了，后期遇到同样的问题就好解决了。废话不多说，开始撸起来！！！1 传统POI的的版本优缺点比较其实想到数据的导入导出，理所当然的会想到apac...
【JAVA - POI 合集】之 POI EXCEL 大数据量导出 + 实现一个通用的EXCEL导出工具类
baidu_29596947的博客
04-14 3077
POI EXCEL 大数据量导出 和如果实现一个通用的EXCEL导出工具类
easy-excel 解决百万数据导入导出，性能很强
竹林幽深
04-25 2140
先自我介绍一下，小编13年上师交大毕业，曾经在小公司待过，去过华为OPPO等大厂，18年进入阿里，直到现在。深知大多数初中级java工程师，想要升技能，往往是需要自己摸索成长或是报班学习，但对于培训机构动则近万元的学费，着实压力不小。刚来领导要求我改造一下，在 gitee 和 度娘搜索比较了几个，easy-excel 性能是非常非常高的，具体还要结合自身的网络环境和代码环境。//发送一个报头，告诉浏览器当前页面不进行缓存，每次访问的时间必须从服务器上读取最新的数据。官网上有示例，不过自己封装一下会更好。
mybatisPlus、BigExcelWriter大数据量流式查询并导出
weixin_44792849的博客
08-06 1142
对于大量数据输出，采用EasyExcel.write()或者hutool的ExcelWriter容易引起内存溢出，因此有了BigExcelWriter。Oracle的fetchSize建议设置成1000，MySQL的fetchSize建议设置成Integer.MIN_VALUE。因此流式查询则使用数据库游标，查一行处理一行，消耗内存较少，不阻塞数据库IO,在大数据处理场景中推荐使用。DAO层的流式处理函数的声明。
大数据量导出Excel
romantic_PK的专栏
03-06 1143
基于Excel操作:POI,FastExcel,JXL，CVS 基于HTML操作:HTML格式,XML格式 (其实这两种是一样的,只是头部文件和规范不一样) 只要头部按MS官网上公布的填写,并按其规则生成结构即可以Excel打开   http://msdn.microsoft.com/en-us/library/aa140062(office.10).aspx 官方的符合excel xm
大数据量导出excel
07-27
大数据量导出到Excel可以采用以下几种方法： 1. 使用Excel插件：一些Excel插件可以帮助处理大数据量的导入和导出。例如，使用Power Query插件可以连接到大型数据库或数据源，并将数据导入Excel。另外，使用Power Pivot插件可以处理数百万行的数据，并进行数据建模和分析。 2. 分批导出：如果数据量过大，可以将数据分批导出到多个Excel文件中。可以通过编写脚本或程序来实现自动分批导出的功能。 3. 使用数据库工具：如果数据存储在数据库中，可以使用数据库工具（如SQL Server Management Studio、MySQL Workbench等）执行查询语句并将结果导出为CSV或其他格式的文件。然后，再将CSV文件导入到Excel中进行进一步处理。 4. 使用编程语言：使用编程语言（如Python、Java等）可以编写代码来处理大数据量的导出。例如，可以使用Python的pandas库来读取和处理数据，然后将结果保存为Excel文件。 以上是几种常见的方法，具体选择哪种方法取决于数据量的大小、数据来源和个人偏好。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值