大量数据导出Excel方案_zwrlj527的专栏

序
最近可能会遇到大量数据导出Excel的场景，今天趁现在需求告一段落来做下技术预研，然后这里就顺便分享给大家。

一、数据量预判

因为我们是做物联网的，这里要导出的数据就是设备的上报数据。客户说要这些数据导出成excel进行分析，又或是其他什么原因，咱不管。咱就分析下数据量，目前设备数量1500，2小时上报一次数据（最小可设置为半小时），要求可以导出3年的数据。
数据量初步估算：1500 * 12 * 30 * 12 * 3 = 19,440,000 ，设备而且据说还要上，所以估计数据量就是千万级吧。
现在Excel2007以后单个sheet能放的数据是1,048,576 ，列肯定是够用的，sheet数量以前是255，现在肯定也是够用的。但是唯一一点就是这个excel文件能不能打开还跟电脑的内存有关，这个肯定要跟客户说清楚。了解这些后就可以开始先跟客户掰扯下，告知风险，看能不能干掉这样的需求，又或是提供一些方案。

二、方案设计

之前小数据量是接口返回数据给前端，前端生成excel文件。现在这么大的数据量，估计要后端实现了。后端也不能一次性把数据查询出来，内存也吃不消，搞不好就报oom错误了。所以批量查询时肯定的。方案设计如下：
1、批量查询写入数据到excel，控制单个sheet的数据量，即将超过就新建sheet继续写入
2、在1的基础上批量查询改为流式查询（应该流式查询性能会高，数据库的连接次数、查询用的缓存应该是下降的）
3、在2的基础上分多个excel文件（理论跟实际还是有差异，理论上数据量没有问题，实际上怕客户电脑内存不够打开不了）
4、在3的基础上引入自动任务，定期生成历史数据excel文件
5、在3、4的基础上，合并多个excel文件为zip包，给客户下载
6、特殊方案，前端与后端建立ws连接，后端接口流式查询将结果发布到ws，前端消费ws里的数据，分sheet写入，分excel写入。

三、流式查询

单表是基于tk.mybatis，上关键代码跟测试示例吧：

extend就是基于tk.mybatis的实现的单表流式查询。
StreamExampleProvider

import org.apache.ibatis.mapping.MappedStatement;
import tk.mybatis.mapper.mapperhelper.MapperHelper;
import tk.mybatis.mapper.provider.ExampleProvider;

/**
 * @author zhengwen
 **/
public class StreamExampleProvider extends ExampleProvider {

    /**
     *
     * @param mapperClass
     * @param mapperHelper
     */
    public StreamExampleProvider(Class<?> mapperClass, MapperHelper mapperHelper) {
        super(mapperClass, mapperHelper);
    }


    /**
     * 根据Example流式查询
     *
     * @param ms
     * @return
     */
    public String selectStreamByExampleMapper(MappedStatement ms) {
        return this.selectByExample(ms);
    }

    /**
     * 根据Example和RowBounds流式查询
     *
     * @param ms
     * @return
     */
    public String selectStreamByExampleRowBoundsMapper(MappedStatement ms) {
        return this.selectByExample(ms);
    }

}

SelectStreamByExampleMapper

import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.SelectProvider;
import org.apache.ibatis.mapping.ResultSetType;
import org.apache.ibatis.session.ResultHandler;
import tk.mybatis.mapper.annotation.RegisterMapper;

/**
 * @author zhengwen
 **/
@RegisterMapper
public interface SelectStreamByExampleMapper<T> {

    /**
     * 根据example条件和RowBounds进行流式查询
     *
     * @param example
     * @param resultHandler
     */
    @Options(resultSetType = ResultSetType.FORWARD_ONLY, fetchSize = 1000)
    @SelectProvider(type = StreamExampleProvider.class, method = "dynamicSQL")
    void selectStreamByExampleMapper(Object example, ResultHandler resultHandler);
}

SelectStreamByExampleRowBoundsMapper

import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.SelectProvider;
import org.apache.ibatis.mapping.ResultSetType;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import tk.mybatis.mapper.annotation.RegisterMapper;

/**
 * @author zhengwen
 **/
@RegisterMapper
public interface SelectStreamByExampleRowBoundsMapper<T> {


    /**
     * 根据example条件和RowBounds进行流式查询
     *
     * @param example
     * @param rowBounds
     * @param resultHandler
     */
    @Options(resultSetType = ResultSetType.FORWARD_ONLY, fetchSize = 1000)
    @SelectProvider(type = StreamExampleProvider.class, method = "dynamicSQL")
    void selectStreamByExampleRowBoundsMapper(Object example, RowBounds rowBounds, ResultHandler resultHandler);

}

StreamMapper

import tk.mybatis.mapper.annotation.RegisterMapper;

/**
 * @author zhengwen
 **/
@RegisterMapper
public interface StreamMapper<T> extends
        SelectStreamByExampleMapper<T>,
        SelectStreamByExampleRowBoundsMapper<T> {
}

我们的mapper实现 StreamMapper

import com.easylinkin.bm.core.extend.StreamMapper;
import tk.mybatis.mapper.common.BaseMapper;
import tk.mybatis.mapper.common.ConditionMapper;
import tk.mybatis.mapper.common.IdsMapper;
import tk.mybatis.mapper.common.MySqlMapper;
import tk.mybatis.mapper.common.special.InsertListMapper;

/**
 * 定制版MyBatis Mapper插件接口，如需其他接口参考官方文档自行添加。
 *
 * @author zhengwen
 */
public interface Mapper<T>
        extends
        BaseMapper<T>,
        ConditionMapper<T>,
        IdsMapper<T>,
        InsertListMapper<T>,
        MySqlMapper<T>,
        StreamMapper<T> {
}

这样，我们的业务mapper:

/**
 * @author unknown
 */
public interface DeviceSensirionRecordMapper extends Mapper<DeviceSensirionRecord> {
     /**
     * 分组排序list数据
     *
     * @param deviceSensirionRecordPageVo 页面入参vo
     * @return 上报数据分组排序
     */
    List<DeviceSensirionRecordDto> listGroupAndSort(DeviceSensirionRecordPageVo deviceSensirionRecordPageVo);

    /**
     * 分组排序list数据流式查询
     *
     * @param deviceSensirionRecordPageVo 页面入参vo
     * @return 上报数据分组排序数据游标cursor
     */
    Cursor<DeviceSensirionRecordDto> listGroupAndSortStream(DeviceSensirionRecordPageVo deviceSensirionRecordPageVo);
}

四、流式查询使用

我的junit测试类演示了3种使用方式：

@Test
    public void mapperStreamQueryTest() {
        log.info("---流式查询测试--");

        //组织模拟查询条件对象
        DeviceSensirionRecordPageVo deviceSensirionRecordPageVo = new DeviceSensirionRecordPageVo();
        log.info("---{}", JSONObject.toJSONString(deviceSensirionRecordPageVo));
     
        DeviceSensirionRecord exp = deviceSensirionRecordPageVo.getDeviceSensirionRecord();
        Condition condition = new Condition(DeviceSensirionRecord.class);
        Example.Criteria cri = condition.createCriteria();
        cri.andEqualTo("deviceNo", exp.getDeviceNo());

        //基于我们刚刚扩展tk.mybatis单表流式查询
        deviceSensirionRecordMapper.selectStreamByExampleMapper(condition, resultHandler -> {
            DeviceSensirionRecord rs = (DeviceSensirionRecord) resultHandler.getResultObject();
            log.info("----{}", JSONObject.toJSONString(rs));
        });

        //基于游标
        Cursor<DeviceSensirionRecordDto> cs = deviceSensirionRecordMapper.listGroupAndSortStream(deviceSensirionRecordPageVo);
        cs.forEach(c->{
            log.info("----Cursor-------{}", JSONObject.toJSONString(c));
        });

        //基于sqlSessionTemplate
      sqlSessionTemplate.select("com.xx.xx.dao.DeviceSensirionRecordMapper.listGroupAndSort",deviceSensirionRecordPageVo,new RowBounds(1,1000) , rs->{
            DeviceSensirionRecordDto ds = (DeviceSensirionRecordDto) rs.getResultObject();
            log.info("----{}", JSONObject.toJSONString(ds));
        });

    }

里面有3种方式：
1、基于我们刚刚扩展tk.mybatis单表流式查询
2、基于游标，mybatis支持的
3、基于sqlSessionTemplate
本来还准备扩展下tk.mybatis的流式查询支持复杂查询的，但是试了2、3后决定放弃了，直接用也很香很简单，没有意义在扩展。

五、流式查询配合多sheet生成excel

方案应该就上面那些，今天先研究到的是流式查询配合多sheet生成。我这里流式查询研究到了单表的、自定义的。这里直接上复杂的流式查询配合多sheet生成excel，看我的junit测试类：

@Test
    public void streamQueryExcel() {
        log.info("---流式查询生成Excel--");

        String name = DateUtil.format(DateUtil.date(), "yyyyMMddHHmmss");
        String fileName = baseTempPath + File.separator + name + ".xlsx";
        File exFile = new File(fileName);
        ExcelWriter excelWriter = ExcelUtil.getBigWriter(exFile, "上报数据-1");

        List<Map<String, String>> ls = new ArrayList<>();

        //sheet的index、数据序号初始
        AtomicInteger sheetIndx = new AtomicInteger(0);
        AtomicInteger index = new AtomicInteger(0);

        DeviceSensirionRecordPageVo deviceSensirionRecordPageVo = new DeviceSensirionRecordPageVo();

        //mybatis流式查询
        SqlSession sqlSession = sqlSessionTemplate.getSqlSessionFactory().openSession();
        Cursor<DeviceSensirionRecordDto> cs = sqlSession.getMapper(DeviceSensirionRecordMapper.class).listGroupAndSortStream(deviceSensirionRecordPageVo);
        cs.forEach(c -> {
            log.info("----Cursor-------{}", JSONObject.toJSONString(c));

            //数据转换
            converToExcelData(index, c, ls);

            //达到量就批量写入
            if (index.get() == 10000) {
                writeExcelData(sheetIndx, excelWriter, ls);

                //序号、数据对象清空
                ls.clear();
                index.set(0);
            }

        });

        //最后一批数据写入excel
        if (CollectionUtil.isNotEmpty(ls)) {
            writeExcelData(sheetIndx, excelWriter, ls);
        }

        //最后刷新文件
        excelWriter.flush(exFile);
        excelWriter.close();

        log.info("---------end------------");

    }

    /**
     * 数据转换
     *
     * @param index
     * @param dto
     * @param ls
     */
    private void converToExcelData(AtomicInteger index, DeviceSensirionRecordDto dto, List<Map<String, String>> ls) {
        index.set(index.get() + 1);

        Map<String, String> mp = new HashMap<>();
        mp.put("index", String.valueOf(index.get()));
        mp.put("deviceNo", dto.getDeviceNo());
        mp.put("temperature", String.valueOf(dto.getTemperature()));
        mp.put("humidity", String.valueOf(dto.getHumidity()));
        mp.put("power", String.valueOf(dto.getPower()));
        mp.put("recordTime", DateUtil.format(dto.getRecordTime(), "yyyy-MM-dd HH:mm:ss"));
        ls.add(mp);
    }

    /**
     * 批量写数据到excel
     *
     * @param sheetIndx
     * @param excelWriter
     * @param ls
     */
    private void writeExcelData(AtomicInteger sheetIndx, ExcelWriter excelWriter, List<Map<String, String>> ls) {

        sheetIndx.set(sheetIndx.get() + 1);
        if (sheetIndx.get() != 0) {
            //设置sheet名称
            excelWriter.setSheet("上报数据-" + sheetIndx);
        }

        //列头
        writeExcelHead(excelWriter);
        //数据写入
        excelWriter.write(ls, true);
        excelWriter.autoSizeColumnAll();

    }

    /**
     * 列头设置
     * @param excelWriter
     */
    private void writeExcelHead(ExcelWriter excelWriter) {
        excelWriter.addHeaderAlias("index", "序号");
        excelWriter.addHeaderAlias("deviceNo", "设备编码");
        excelWriter.addHeaderAlias("temperature", "温度");
        excelWriter.addHeaderAlias("humidity", "湿度");
        excelWriter.addHeaderAlias("power", "电量");
        excelWriter.addHeaderAlias("recordTime", "上报时间");
    }

我这里是使用的HuTool的工具包，不得不说，真香啊。以前也用easyExcel、myExcel,或者直接用poi，但是都对代码有一定的侵入性，我抛弃了。

六、流式查询配合多sheet生成excel效果

最后说一点，流式查询应该是长时间需要占数据库连接的，所以也需要谨慎使用。
就先分享到这里，希望可以启发大家。特殊方案要前端配合，没有下载环节，但是写数据到sheet、生成多个excel这些逻辑都是前端写了，估计一般的前端都不会愿意。他们的理论是他们是使用数据，哈哈。其实吧我觉得这也是使用数据啊，不纠结，我们后端啥都可以干，没必要别人不愿意还非要，那就有点QJ了。