【jeecg大数据导出Excel以及优化】

助助助助助手

已于 2024-01-05 15:07:15 修改

阅读量1.2k

点赞数 8

分类专栏： java 文章标签：大数据 excel java

于 2023-12-19 17:25:16 首次发布

本文链接：https://blog.csdn.net/qq_39445235/article/details/135089676

版权

java 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

jeecg大数据导出Excel以及优化

控制层优化

版本1（初始版本）

   @ApiOperation(value = "导出", notes = "导出")
   @GetMapping("/exportTruckNoReportList")
   public ModelAndView exportTruckNoReportList(@RequestParam(name = "sap_type", required = false) String sap_type,
                                               @RequestParam(name = "start_time", required = false) String start_time,
                                               @RequestParam(name = "end_time", required = false) String end_time,
                                               @RequestParam(name = "gascode", required = false) String gas_code,
                                               @RequestParam(name = "truck_no", required = false) String truck_no,
                                               @RequestParam(name = "is_all_org", defaultValue = "false") String isAllOrg,
                                               HttpServletRequest req) {
       UserInfo userInfo = BaseContextHandler.getUserInfo();

       QueryWrapper<TruckNoReportExport> queryWrapper = new QueryWrapper<>();
       if (StringUtils.isNotBlank(start_time) && StringUtils.isNotBlank(end_time)) {
           queryWrapper.between("trade_at", start_time, end_time);
       } else if (StringUtils.isBlank(start_time) && StringUtils.isNotBlank(end_time)) {
           queryWrapper.le("trade_at", end_time);
       } else if (StringUtils.isNotBlank(start_time) && StringUtils.isBlank(end_time)) {
           queryWrapper.ge("trade_at", start_time);
       }

       queryWrapper.like(StringUtils.isNotBlank(truck_no), "truck_no", truck_no);
       queryWrapper.ne("trade_type","1200");

       if ("true".equals(isAllOrg)) {
           queryWrapper.eq((StringUtils.isNotBlank(gas_code)), "gas_code", gas_code);
       }else {
           queryWrapper.like((StringUtils.isNotBlank(gas_code)), "gas_code", gas_code);
       }
       queryWrapper.groupBy("truck_no");
       queryWrapper.groupBy("gas_code");
       queryWrapper.orderByDesc("trade_at");

       long startTime = System.currentTimeMillis();

       List<TruckNoReportExport> list = tradesOrderPayService.exportTruckNoReportList(queryWrapper);

       long endTime1 = System.currentTimeMillis();
       long elapsedTime1 = endTime1 - startTime;
       log.info("从数据库查询时间{}", String.valueOf(elapsedTime1));

       long endTime2 = System.currentTimeMillis();

       log.info("导出数据量:{}", list.size());
       long elapsedTime = endTime2 - endTime1;
       log.info("循环处理时间：{}", String.valueOf(elapsedTime));
       ModelAndView mv = new ModelAndView(new JeecgEntityExcelView());
       //此处设置的filename无效 ,前端会重更新设置一下
       mv.addObject(NormalExcelConstants.FILE_NAME, "统计");
       mv.addObject(NormalExcelConstants.CLASS, TruckNoReportExport.class);
       //update-begin--Author:liusq  Date:20210126 for：图片导出报错，ImageBasePath未设置--------------------
       ExportParams exportParams = new ExportParams("统计", "导出人:" + userInfo.getTrueName(), "统计");
//        exportParams.setImageBasePath(upLoadPath);
       //update-end--Author:liusq  Date:20210126 for：图片导出报错，ImageBasePath未设置----------------------
       mv.addObject(NormalExcelConstants.PARAMS, exportParams);
       mv.addObject(NormalExcelConstants.DATA_LIST, list);

       return mv;
   }

版本2（通过分页查询，优化了查询时内存的开销）

   @ApiOperation(value = "导出", notes = "导出")
   @GetMapping("/exportTruckNoReportList")
   public ModelAndView exportTruckNoReportList(@RequestParam(name = "sap_type", required = false) String sap_type,
                                               @RequestParam(name = "start_time", required = false) String start_time,
                                               @RequestParam(name = "end_time", required = false) String end_time,
                                               @RequestParam(name = "gascode", required = false) String gas_code,
                                               @RequestParam(name = "truck_no", required = false) String truck_no,
                                               @RequestParam(name = "is_all_org", defaultValue = "false") String isAllOrg,
                                               HttpServletRequest req) {
       UserInfo userInfo = BaseContextHandler.getUserInfo();

       QueryWrapper<TruckNoReportExport> queryWrapper = new QueryWrapper<>();
       if (StringUtils.isNotBlank(start_time) && StringUtils.isNotBlank(end_time)) {
           queryWrapper.between("trade_at", start_time, end_time);
       } else if (StringUtils.isBlank(start_time) && StringUtils.isNotBlank(end_time)) {
           queryWrapper.le("trade_at", end_time);
       } else if (StringUtils.isNotBlank(start_time) && StringUtils.isBlank(end_time)) {
           queryWrapper.ge("trade_at", start_time);
       }

       queryWrapper.like(StringUtils.isNotBlank(truck_no), "truck_no", truck_no);
       queryWrapper.ne("trade_type","1200");

       if ("true".equals(isAllOrg)) {
           queryWrapper.eq((StringUtils.isNotBlank(gas_code)), "gas_code", gas_code);
       }else {
           queryWrapper.like((StringUtils.isNotBlank(gas_code)), "gas_code", gas_code);
       }
       queryWrapper.groupBy("truck_no");
       queryWrapper.groupBy("gas_code");
       queryWrapper.orderByDesc("trade_at");

       long startTime = System.currentTimeMillis();

       Page<TruckNoReportExport> pages = new Page<>(1, 3000);
       Page<TruckNoReportExport> pageList = tradesOrderPayService.exportTruckNoReportList(pages,queryWrapper);

       List<TruckNoReportExport> list = new ArrayList<>();

       for (int pageNo = 1; pageNo <= pageList.getPages(); pageNo++) {
           Page<TruckNoReportExport> page1 = new Page<>(pageNo, 3000);
           // 设置不执行 count 查询，每次page查询都会先执行一遍count太花费时间了，这里已经知道有数据了所以不需要
           page1.setSearchCount(false);
           Page<TruckNoReportExport> pageList1 = tradesOrderPayService.exportTruckNoReportList(page1,queryWrapper);
           list.addAll(pageList1.getRecords());
       }

       long endTime1 = System.currentTimeMillis();
       long elapsedTime1 = endTime1 - startTime;
       log.info("从数据库查询时间{}", String.valueOf(elapsedTime1));

       long endTime2 = System.currentTimeMillis();

       log.info("导出数据量:{}", list.size());
       long elapsedTime = endTime2 - endTime1;
       log.info("循环处理时间：{}", String.valueOf(elapsedTime));
       ModelAndView mv = new ModelAndView(new JeecgEntityExcelView());
       //此处设置的filename无效 ,前端会重更新设置一下
       mv.addObject(NormalExcelConstants.FILE_NAME, "统计");
       mv.addObject(NormalExcelConstants.CLASS, TruckNoReportExport.class);
       //update-begin--Author:liusq  Date:20210126 for：图片导出报错，ImageBasePath未设置--------------------
       ExportParams exportParams = new ExportParams("统计", "导出人:" + userInfo.getTrueName(), "统计");
//        exportParams.setImageBasePath(upLoadPath);
       //update-end--Author:liusq  Date:20210126 for：图片导出报错，ImageBasePath未设置----------------------
       mv.addObject(NormalExcelConstants.PARAMS, exportParams);
       mv.addObject(NormalExcelConstants.DATA_LIST, list);

       return mv;
   }

版本3（把数据存入OOS，用户通过OOS下载，节省内存开销）

**部分代码实例**
（略）//oos存表-导出中
 long startTime = System.currentTimeMillis();

        ByteArrayOutputStream byteArrayOutputStream = null;
        ByteArrayInputStream byteArrayInputStream = null;
        try {

            byteArrayOutputStream = new ByteArrayOutputStream();
            long count  = tradesOrderPayService.selectCount(queryWrapper);// 总记录数
            long pageSize = 3000;// 每页记录数
            int totalPages = (int) Math.ceil((double) count / pageSize); // 计算总页数


            HSSFWorkbook workbook = null;
            for (int pageNo = 1; pageNo <= totalPages; pageNo++) {
                List<TradesOrderPayExport> lists = tradesOrderPayService.exportOrderPayList((pageNo - 1) * pageSize,pageSize, queryWrapper);

                log.info("获取当前{}", System.currentTimeMillis() - startTime);

                ExcelUtil<TradesOrderPayExport> util = new ExcelUtil<>(TradesOrderPayExport.class);
                workbook = util.convert2WorkbookPage(lists, workbook,"导出",pageNo,3000);

            }

            long endTime1 = System.currentTimeMillis();
            long elapsedTime1 = endTime1 - startTime;
            log.info("从数据库查询时间{}", String.valueOf(elapsedTime1));

            long endTime2 = System.currentTimeMillis();

            log.info("导出数据量:{}", count);

            workbook.write(byteArrayOutputStream);
            byteArrayInputStream = new ByteArrayInputStream(byteArrayOutputStream.toByteArray());

            String bizPath = "total/prod/";
            String format = DateUtil.format(new Date(), "yyyy-MM-dd");
            long l = System.currentTimeMillis();
            bizPath = bizPath + format + "/" + l + "导出.xls";

            String upload = OssBootUtil.upload(byteArrayInputStream, bizPath);
            System.err.println(upload);

            fossUploadUrl1.setUrl(upload);
            fossUploadUrl1.setUpdateTime(new Date());
            fossUploadUrl1.setUpdateName(userInfo.getTrueName());
            fossUploadUrl1.setStatus("2");//oos存表-导出成功
            fossApi.updateUploadUrl(fossUploadUrl1);

            long elapsedTime = endTime2 - endTime1;
            log.info("上传成功时间：{}", String.valueOf(elapsedTime));

        } catch (IOException e) {

            fossUploadUrl1.setUpdateTime(new Date());
            fossUploadUrl1.setUpdateName(userInfo.getTrueName());
            fossUploadUrl1.setStatus("3");//oos存表-导出失败
            fossApi.updateUploadUrl(fossUploadUrl1);
            throw new RuntimeException(e);
        } finally {
            if(byteArrayOutputStream != null){
                try {
                    byteArrayOutputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if(byteArrayInputStream != null){
                try {
                    byteArrayInputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }

        }

总体优化思路：

1.建立基础的连表查询，要求达到需求
2.去除不必要的字段，尽量`减少连表`，将数据量比较少切不变动的表使用case替换
3.表里建议`索引优化`
4.发现查询速度还是很慢，通过`建立视图`，优化连表查询速度，同时视图查询会导致索引失效
5.查询数据量比较大，几百名用户同时导出会造成内存溢出的问题，那么改成`分页查询`，测试到以数万条的数据量，每次分页以3000时间空间上都比较可以接受
6.page分页查询执行sql时，内部会自动执行一遍count()查询，这里页数已经查询出来了，可以`去掉count()`减少查询时间
7.数据量太大时还是会造成内存溢出的问题，所以将查询出来的数据写入excel里再`放入oos里`，建立个表存放下载地址，时间，用户，前端直接通过存放地址在oos下载，考虑到节省内存，可以分页的时候`循环写入excel`里，存入流，再上传到oos，修改`保留地址`
8.查询速度还是慢了，发现sql可以继续优化，通过`将主表进行子查询嵌套`的方式，可以先查询主表，再添加其余的连表字段，那么就是说`查询条件可以也放入到主表`里通过子查询先查出来，再进行连表，优化了不少的查询时间，==但是这里发现排序放到主表失效了==
9.发现mybatisplus的分页条查询，会把查询条件替换到${ew.customSqlSegment}位置，但是分页仍然会放到连表之后，那么把`分页也放到主表`里把。那么需要传入#{pageNum},#{pageSize}两个字段，增加查询一个`查询总个数的count`方法（通过`查询主表`个数就行了，这里我是直接查询视图，因为字段重命名了），
10.最后把`排序`加到连表的最后面

**这里给出最终优化方案的查询sql实例**

   <select id="exportOrderPayList" resultType="com.bee.trades.api.vo.TradesOrderPayExport">
       select
           v1.*,
           `info`.`oil_name` AS `oil_total_name`,
           round( ( `info`.`mac_price` / 100 ), 2 ) AS `mac_price`,
           `info`.`mileage` AS `mileage`,
           `info`.`remark` AS `remark`,
           `org1`.`org_name` AS `gas_name`,
           `org1`.`org_name` AS `org_name`,
           `org1`.`org_root_name` AS `org_root_name`,
           `station`.`station_name` AS `station_name`,
           ( CASE WHEN ( `station`.`source` = 1 ) THEN '自营' WHEN ( `station`.`source` = 2 ) THEN '合作对接' ELSE '' END ) AS `station_source_name`,
           `supplier`.`supplier_name` AS `pname`

       FROM   (
          select * from v_order_pay_view //这里是主表的视图，这里有两千行放入到mapper层会影响到控制台查看数据
           ${ew.customSqlSegment}//mybatisplus取查询条件
           limit #{pageNum},#{pageSize}//分页
       ) AS v1

       LEFT JOIN `is_trades`.`trades_order_pay_info` `info` ON  `v1`.`id` = `info`.`order_pay_id`
       LEFT JOIN `is_base`.`base_org` `org1` ON  `v1`.`gas_code` =  `org1`.`org_code`
       LEFT JOIN `is_gas`.`gas_station` `station` ON  `v1`.`station_id` =  `station`.`id`
       LEFT JOIN `is_base`.`base_supplier` `supplier` ON `v1`.`pcode` =  `supplier`.`scode`
       ORDER BY v1.trade_at DESC
   </select>
   <select id="getCount" resultType="java.lang.Long"> //查询总条数
       select count(0) from v_order_pay_view
       ${ew.customSqlSegment}
   </select>

sql优化

1.表里建立索引优化

2.建立视图查询

3.连表获取所有的字段

4.尽量减少连表，数据量比较少的表使用case替换

5.去掉不必要的字段

注意：这里有一个问题

假如使用了SUM等统计字段，后面一定要接GROUP BY进行分组

如果是先根据条件查询，再分组那么就不能根据视图查询，下面代码实例

    <select id="exportTruckNoReportList" resultType="com.bee.trades.api.vo.TruckNoReportExport">
        SELECT
            v1.*,
            `org1`.`org_name` AS gas_name,
            `org2`.`org_name` AS org_root_name
        FROM
        (
            SELECT
                `trades`.`trade_at` AS `trade_at`,
                `trades`.`trade_type` AS `trade_type`,
                ( CASE WHEN ( `trades`.`trade_type` LIKE '11%' ) THEN '加油' WHEN ( `trades`.`trade_type` LIKE '12%' ) THEN '撤销' END ) AS `trade_type_name`,
                round( ( sum( `trades`.`pay_money` ) / 100 ), 2 ) AS `pay_money`,
                `trades`.`is_oil` AS `is_oil`,
                `trades`.`oil_num` AS `oil_num`,
                `trades`.`oil_name` AS `oil_name`,
                sum( IF ( ( `trades`.`is_oil` = 11 ), `trades`.`oil_num`, 0 ) ) AS `oil_num_count`,
                round( ( sum( IF ( ( `trades`.`is_oil` = 11 ), `trades`.`pay_money`, 0 ) ) / 100 ), 2 ) AS `oil_pay_money`,
                round( ( sum( IF ( ( `trades`.`is_oil` = 12 ), `trades`.`pay_money`, 0 ) ) / 100 ), 2 ) AS `not_oil_pay_money`,
                round( ( sum( IF ( ( `trades`.`oil_name` = 'e6d070d8-f95c-11e8-bfa7-02abba8d56d2' ), `trades`.`pay_money`, 0 ) ) / 100 ), 2 ) AS `diesel_oil_pay_money`,
                sum( IF ( ( `trades`.`oil_name` = 'e6d070d8-f95c-11e8-bfa7-02abba8d56d2' ), `trades`.`oil_num`, 0 ) ) AS `diesel_oil_num`,
                round( ( sum( IF ( ( `trades`.`oil_name` = 'e07bc2be-f95c-11e8-8f4b-02abba978494' ), `trades`.`pay_money`, 0 ) ) / 100 ), 2 ) AS `gasoline_pay_money`,
                sum( IF ( ( `trades`.`oil_name` = 'e07bc2be-f95c-11e8-8f4b-02abba978494' ), `trades`.`oil_num`, 0 ) ) AS `gasoline_oil_num`,
                `trades`.`truck_no` AS `truck_no`,
                `trades`.`gas_code` AS `gas_code`,
                CASE
                WHEN LENGTH( `trades`.`gas_code` ) > 0 THEN
                SUBSTRING( `trades`.`gas_code`, 1, 6 ) ELSE NULL
                END AS org_root
            FROM
                `is_trades`.`trades_order_pay` `trades`

                ${ew.customSqlSegment}
            ) AS v1
        LEFT JOIN v_base_org_name_view `org1` ON v1.gas_code = org1.org_code
        LEFT JOIN v_base_org_name_view `org2` ON v1.gas_code = org2.org_code
    </select>
//这里通过连表查询了gas_code对应的机构名称gas_name,和对应的顶级机构名称org_root_name
//通过查询之后再连表，优化了查询时间

${ew.customSqlSegment} 是mybatisplus的参数接收,内部是使用ew接收的参数

mapper层

 Page<TruckNoReportExport> exportTruckNoReportList(@Param(Constants.WRAPPER) QueryWrapper<TruckNoReportExport> queryWrapper, Page<TruckNoReportExport> pages);

entity实体类

package com.bee.trades.api.vo;

import com.fasterxml.jackson.annotation.JsonFormat;
import com.fasterxml.jackson.annotation.JsonProperty;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import org.jeecgframework.poi.excel.annotation.Excel;
import org.springframework.format.annotation.DateTimeFormat;

import java.io.Serializable;
import java.math.BigDecimal;
import java.util.Date;

/**
 * 功能描述
 *
 * @author: scott
 * @date: 2023年12月01日 20:23
 */
@Data
public class TruckNoReportExport implements Serializable {
    private static final long serialVersionUID = 1L;


    /**
     * 车牌号
     */
    @Excel(name = "车牌号", width = 15)
    @ApiModelProperty(value = "车牌号")
    @JsonProperty("truck_no")
    private String truckNo;
    /**
     * 所属机构
     */
    @ApiModelProperty(value = "所属机构")
    @JsonProperty("gas_code")
    private String gasCode;

    @Excel(name = "所属机构", width = 25)
    @JsonProperty("gas_name")
    private String gasName;
    /**
     * 顶级机构编码
     */
    @ApiModelProperty(value = "顶级机构编码")
    @JsonProperty("org_root")
    private String orgRoot;

    @Excel(name = "顶级机构", width = 25)
    @JsonProperty("org_root_name")
    private String orgRootName;
    /**
     * 消费总额
     */
    @Excel(name = "消费总额", width = 15, type = 4)
    @ApiModelProperty(value = "消费总额")
    @JsonProperty("pay_money")
    private BigDecimal payMoney;
    /**
     * 油品总量
     */
    @Excel(name = "油品总量", width = 15, type = 4)
    @ApiModelProperty(value = "油品总量")
    @JsonProperty("oil_num_count")
    private BigDecimal oilNumCount;
    /**
     * 油品总额
     */
    @Excel(name = "油品总额", width = 15, type = 4)
    @ApiModelProperty(value = "油品总额")
    @JsonProperty("oil_pay_money")
    private BigDecimal oilPayMoney;
    /**
     * 柴油总量
     */
    @Excel(name = "柴油总量", width = 15, type = 4)
    @ApiModelProperty(value = "柴油总量")
    @JsonProperty("diesel_oil_num")
    private BigDecimal dieselOilNum;
    /**
     * 柴油总额
     */
    @Excel(name = "柴油总额", width = 15, type = 4)
    @ApiModelProperty(value = "柴油总额")
    @JsonProperty("diesel_oil_pay_money")
    private BigDecimal dieselOilPayMoney;
    /**
     * 汽油总量
     */
    @Excel(name = "汽油总量", width = 15, type = 4)
    @ApiModelProperty(value = "汽油总量")
    @JsonProperty("gasoline_oil_num")
    private BigDecimal gasolineOilNum;
    /**
     * 汽油总额
     */
    @Excel(name = "汽油总额", width = 15, type = 4)
    @ApiModelProperty(value = "汽油总额")
    @JsonProperty("gasoline_pay_money")
    private BigDecimal gasolinePayMoney;
    /**
     * 非油总额
     */
    @Excel(name = "非油总额", width = 15, type = 4)
    @ApiModelProperty(value = "非油总额")
    @JsonProperty("not_oil_pay_money")
    private BigDecimal notOilPayMoney;


    /**
     * 交易时间
     */
    @Excel(name = "交易时间", width = 25, format = "yyyy-MM-dd HH:mm:ss")
    @JsonFormat(timezone = "GMT+8",pattern = "yyyy-MM-dd HH:mm:ss")
    @DateTimeFormat(pattern="yyyy-MM-dd HH:mm:ss")
    @ApiModelProperty(value = "交易时间")
    @JsonProperty("trade_at")
    private Date tradeAt;
    /**
     * 交易类型 1开头代表加油，正常加油1111，修改后的1112，加油补录 1113 撤销 1200 撤销中 1201
     */
    @ApiModelProperty(value = "交易类型")
    @JsonProperty("trade_type")
    private String tradeType;

    @Excel(name = "交易类型", width = 25)
    @JsonProperty("trade_type_name")
    private String tradeTypeName;
}