SpringBoot 查询数据库导出报表文件

「爱情、让人受尽委屈。」 2022-09-10 00:08 187阅读 0赞

# 一、背景 #

### 1、需求 ###

几千万条报表数据导出到Excel中

### 2、问题 ###

1.  在数据量导出不大时，我们的常规做法是使用MySQL直接查询出全部数据，整理规划成Excel列表，使用POI写入到Excel文件中
2.  但是当数据量较大时，使用MySQL查询出所有数据，一会超时断开连接，二会内存溢出，使用POI暂时不支持分布写入数据到Excel中

### 3、解决 ###

1.  使用数据库流式读取可以解决数据库读取时间过长，内存溢出问题，这个解决了一次性读取全部数据到内存中
2.  使用CSV文件代替xlsx/xls文件写入，CSV也可以使用Excel打开操作，并且也可另存为xlsx/xls，CSV本质是文本文件，不用确定尾结点，可以如TXT一样持续向文件追加内容

# 二、代码实现 #

### 1、mapper文件 ###

使用Cursor游标标识，流式查询数据数据

@Select("<script>"
          + "select * from `user_report`"
          + "</script>")
      Cursor<UserReport> selectCursorByCondition();

## 2、读取写入文件 ##

1.  @Transactional(readOnly = true)：搭配数据库Cursor查询使用，事务注解为只读，保证数据整体的一致性
2.  fieldArr：字段数组，规定要写入文件的字段项，比如查询的全部字段为a,b,c,d，而我只想显示a,c,d
3.  headerArr：字段名数组，对应文件标题栏，需要不字段数组a，c，d名称一一对应
4.  CsvUtils：一个简单的工具类，后续贴代码

/**
       * 导出Excel
       * @param fieldArr       字段数组
       * @param headerArr      字段名数组
       * @param fileName       文件名
       */
      @Transactional(readOnly = true)
      public void exportList(HttpServletResponse response,
          String[] fieldArr, String[] headerArr, String fileName) {
        //设置文件格式
        response.setCharacterEncoding("UTF-8");
        response.addHeader("Content-Type", "application/csv");
        try {
           //文件名 设置为中文
          fileName = new String(fileName.getBytes("gb2312"), "iso8859-1");
        } catch (Exception e) {
          log.error("file name show error：{}", e.getMessage());
        }
        //响应头部
        response.addHeader("Content-Disposition", "attachment; filename=" + fileName + "(" +
            DateUtil.format(DateUtil.date(), DatePattern.NORM_DATETIME_PATTERN) + ").csv");
        //查询数据
        Cursor<UserReport> modelStream = xxMapper.selectCursorByCondition();
    
        try {
          PrintWriter out = response.getWriter();
          try {
            //写入标题行
            out.write(CsvUtils.getTitleLine(headerArr));
            //写入数据行
            modelStream.forEach(item -> {
              out.write(CsvUtils.getRowLine(fieldArr, item));
            });
            out.flush();
          } catch (Exception e) {
            log.error("write file error:{}", e.getMessage());
          } finally {
            out.close();
          }
        } catch (Exception e) {
          log.error("exportList error:{}", e.getMessage());
        }
    
    
      }

### 3、CsvUtils工具类 ###

import java.lang.reflect.Field;
    import java.util.HashSet;
    import java.util.Set;
    
    /**
     * @Author: catcoder
     * @Desc:
     * @Time: 10:05 2021/8/2
     **/
    public class CsvUtils {
    
      /**
       * CSV文件列分隔符
       */
      public static final String CSV_COLUMN_SEPARATOR = ",";
    
      /**
       * CSV文件行分隔符
       */
      public static final String CSV_ROW_SEPARATOR = System.lineSeparator();
    
    
      /**
       * 获取标题行
       *
       * @param headerArr 标题数组
       * @return
       */
      public static String getTitleLine(String[] headerArr) {
        StringBuffer line = new StringBuffer("");
        for (String title : headerArr) {
          line.append(title).append(CSV_COLUMN_SEPARATOR); //添加标题行数据
        }
        line.append(CSV_ROW_SEPARATOR); //换行数据
        return line.toString();
      }
    
      /**
       * 或去数据行
       *
       * @param fieldArr 字段数组
       * @param obj      实体对象
       * @return
       */
      public static String getRowLine(String[] fieldArr, Object obj) {
        StringBuffer line = new StringBuffer("");
        Class<?> srcClass = obj.getClass();
    
        //获取Obj 所有字段
        Set<String> objFiled = new HashSet<>();
        Field[] fields = obj.getClass().getDeclaredFields();
        for (Field field : fields) {
          objFiled.add(field.getName());
        }
    
        for (String field : fieldArr) {
          try {
            // 获取对象对应的Field
            if (objFiled.contains(field)) {
              Field objField = srcClass.getDeclaredField(field);
              objField.setAccessible(true); //设置private可访问
              Object value = objField.get(obj);
              line.append(value); //添加元素
            }
          } catch (Exception e) {
            e.printStackTrace();
          } finally {
            line.append(CSV_COLUMN_SEPARATOR); //CSV间隔数据
          }
        }
        line.append(CSV_ROW_SEPARATOR);//换行数据
        return line.toString();
      }
    }