摘要

创建 Scheduled 定时任务，利用 Poi 自动将数据库数据导出为 Excel 文件

1、数据库建表

1）data_export 表用于保存数据导出记录的信息

CREATE TABLE `data_export` (
  `export_id` varchar(100) NOT NULL COMMENT '导出id',
  `export_name` varchar(100) DEFAULT NULL COMMENT '导出名称',
  `sql` text COMMENT '转化sql',
  `export_table` varchar(100) DEFAULT NULL COMMENT '导出表',
  `export_time` timestamp NULL DEFAULT NULL COMMENT '导出时间',
  `export_stat` varchar(100) DEFAULT NULL COMMENT '导出状态：0未导出，1正在导出，2导出完成，3导出失败',
  PRIMARY KEY (`export_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='数据导出记录';

2）data_export_excel 表用于保存数据导出的 excel 信息

CREATE TABLE `data_export_excel` (
  `export_id` varchar(100) DEFAULT NULL COMMENT '导出id',
  `excel_id` varchar(100) DEFAULT NULL COMMENT 'excel id'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='数据导出excel信息';

2、引入项目插件

1）excle 文件生成需要使用到 poi-ooxml 依赖

<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi-ooxml</artifactId>
  <version>4.1.0</version>
</dependency>

2）由于 scheduled 已经包含在了 spring-boot-starter 模块中，所以不需要额外引入依赖

<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-test</artifactId>
  <scope>test</scope>
</dependency>

3、Excel 生成工具类实现（核心代码）

1）创建 excel 表头信息类

public class ExcelHeaderCell {
    private String columnName;
    private String columnCode;
    private String dataType;
    
    // getter、setter、toString 方法省略 
}

2）创建 ExcelWriteUtil 类，实现 excel 文件生成功能

public class ExcelWriteUtil {
    // 单个 sheet 最大数据条数
    private static final int MAX_SINGLE_SHEET_NUM = 10000;
    private BufferedOutputStream outputStream;
    private List<ExcelHeaderCell> headerCells;
    private SXSSFWorkbook sxssfWorkbook;
    private SXSSFSheet sxssfSheet;
    private CellStyle cellStyle;
    // 当前工作表索引
    private int currentSheetIndex = 0;
    // 当前行索引
    private int currentRowIndex = 0;
    // 单元格索引
    private int cellIndex = 0;

    public ExcelWriteUtil(String filePath, List<ExcelHeaderCell> headerCells) {
        this.headerCells = headerCells;
        this.init(filePath);
    }

    /**
     * 文件初始化
     *
     * @param filePath 文件目录
     */
    private void init(String filePath) {
        try {
            // SXSSFWorkbook在内存中只保留1000条数据，其余写入磁盘
            sxssfWorkbook = new SXSSFWorkbook(this.getXSSFWorkbook(filePath), 1000);
            cellStyle = getAndSetXSSFCellStyle(sxssfWorkbook);
            outputStream = new BufferedOutputStream(Files.newOutputStream(Paths.get(filePath)));
            headerWrite();
        } catch (Exception e) {
            close();
            throw new RuntimeException("文件初始化失败！" + e.getMessage());
        }
    }

    /**
     * 创建 XSSFWorkbook 对象
     *
     * @param filePath 文件目录
     */
    private XSSFWorkbook getXSSFWorkbook(String filePath) {
        BufferedOutputStream stream = null;
        XSSFWorkbook workbook = null;
        try {
            File excelFilePath = new File(filePath);
            if (!excelFilePath.getParentFile().exists()) {
                excelFilePath.getParentFile().mkdirs();
            }
            stream = new BufferedOutputStream(Files.newOutputStream(excelFilePath.toPath()));
            workbook = new XSSFWorkbook();
            workbook.write(stream);
        } catch (Exception e) {
            throw new RuntimeException(e.getMessage());
        } finally {
            if (stream != null) {
                try {
                    stream.close();
                } catch (IOException e) {
                    throw new RuntimeException(e.getMessage());
                }
            }
        }
        return workbook;
    }

    /**
     * 设置单元格数据样式
     *
     * @param sxssfWorkbook XSSFWorkbook 对象
     */
    private CellStyle getAndSetXSSFCellStyle(SXSSFWorkbook sxssfWorkbook) {
        XSSFCellStyle xssfCellStyle = (XSSFCellStyle) sxssfWorkbook.createCellStyle();
        XSSFDataFormat dataFormat = (XSSFDataFormat) sxssfWorkbook.createDataFormat();
        // 自动换行
        xssfCellStyle.setWrapText(false);
        // 水平居中
        xssfCellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 垂直居中
        xssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 防止数字显示为科学计数法
        xssfCellStyle.setDataFormat(dataFormat.getFormat("0"));
        return xssfCellStyle;
    }

    /**
     * 写入表头
     */
    private void headerWrite() {
        sxssfSheet = sxssfWorkbook.createSheet("sheet" + currentSheetIndex);
        sxssfSheet.setDefaultColumnWidth(50);
        SXSSFRow row = sxssfSheet.createRow(currentRowIndex);
        headerCells.forEach(item -> {
            SXSSFCell cell = row.createCell(cellIndex);
            cell.setCellStyle(cellStyle);
            cell.setCellType(STRING);
            cell.setCellValue(item.getColumnName());
            cellIndex++;
        });
        currentRowIndex++;
    }

    /**
     * 写入数据
     *
     * @param data 数据
     */
    public void writeBatch(List<Map<String, Object>> data) {
        try {
            for (Map<String, Object> map : data) {
                if (currentRowIndex == 0) {
                    sxssfSheet = sxssfWorkbook.createSheet("sheet" + currentSheetIndex);
                    sxssfSheet.setDefaultColumnWidth(50);
                }
                SXSSFRow row = sxssfSheet.createRow(currentRowIndex);
                cellIndex = 0;
                for (ExcelHeaderCell headerCell : headerCells) {
                    SXSSFCell cell = row.createCell(cellIndex);
                    cell.setCellType(STRING);
                    cell.setCellStyle(cellStyle);
                    Object val = map.get(headerCell.getColumnCode());
                    if (val == null) {
                        cell.setCellValue("");
                    } else {
                        cell.setCellValue(val.toString());
                    }
                    cellIndex++;
                }
                if (currentRowIndex == MAX_SINGLE_SHEET_NUM) {
                    currentRowIndex = 0;
                    currentSheetIndex++;
                } else {
                    currentRowIndex++;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        }
    }

    /**
     * 刷新
     */
    public void flush() {
        try {
            sxssfWorkbook.write(outputStream);
            outputStream.flush();
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        } finally {
            close();
        }
    }

    /**
     * 关闭资源
     */
    private void close() {
        if (outputStream != null) {
            try {
                outputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
                throw new RuntimeException("文件关闭流失败！ " + e.getMessage());
            }
        }
        if (sxssfWorkbook != null) {
            sxssfWorkbook.dispose();
        }
    }
}

4、创建导出任务（定时任务按需求添加）

1）创建 DataExportExcelJob 类，实现数据导出功能

public class DataExportExcelJob {
    public static final Logger logger = LoggerFactory.getLogger(DataExportExcelJob.class);
    private DataExportReadService dataExportReadService;
    private DataExportWriteService dataExportWriteService;
    private JdbcTemplate jdbcTemplate;
    private DataExportVo dataExportVo;
    // 文件总目录
    private String allFilePath;
    // excel文件目录
    private String filePath;
    // 数据导出目标表信息
    private List<Map<String, Object>> tableInfoMap;
    private int limit = 1000;

    public DataExportExcelJob(DataExportReadService dataExportReadService, DataExportWriteService dataExportWriteService, JdbcTemplate jdbcTemplate, String filePath) {
        this.dataExportReadService = dataExportReadService;
        this.dataExportWriteService = dataExportWriteService;
        this.jdbcTemplate = jdbcTemplate;
        this.filePath = filePath;
    }

    /**
     * 初始化任务信息
     */
    public void init(String exportId) {
        // 获取数据导出记录信息
        dataExportVo = dataExportReadService.queryDataExportDataById(exportId);
        // 获取数据导出目标表信息
        tableInfoMap = dataExportReadService.queryTableInfoByTableName(dataExportVo.getExportTable());
        Date exportTime = dataExportVo.getExportTime();
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyyMM");
        String format = simpleDateFormat.format(exportTime);
        allFilePath = filePath + format + File.separatorChar;
    }

    /**
     * 导出任务执行
     *
     * @param exportId 导出id
     */
    public void executionJob(String exportId) {
        try {
            init(exportId);
            logger.info("{}开始导出", dataExportVo.getExportName());
            
            // 表头生成
            List<ExcelHeaderCell> excelHeaderCellList = new ArrayList<>();
            for (Map<String, Object> map : tableInfoMap) {
                ExcelHeaderCell excelHeaderCell = new ExcelHeaderCell();
                // COLUMN_NAME 数据库表字段名
                excelHeaderCell.setColumnCode(String.valueOf(map.get("COLUMN_NAME")));
                // COLUMN_COMMENT 数据库表字段注释
                excelHeaderCell.setColumnName(String.valueOf(map.get("COLUMN_COMMENT")));
                excelHeaderCell.setDataType(DataTypeConstant.STRING_TYPE.getValue());
                excelHeaderCellList.add(excelHeaderCell);
            }
            // excel信息数据
            List<DataExportVo> excelList = dataExportReadService.queryDataExportExcelListById(dataExportVo.getExportId());
            for (DataExportVo excel : excelList) {
                try {
                    // 开始生成文件
                    singleExport(excel, excelHeaderCellList);
                } catch (Exception e) {
                    e.printStackTrace();
                    logger.error("{}文件导出失败！", excel.getExcelId());
                }
            }
            // 导出完成，修改导出信息状态
            dataExportVo.setExportStat(DataExportTypeConstant.SUCCESS_EXPORT.getValue());
            dataExportWriteService.updateDataExportStat(dataExportVo);
        } catch (Exception e) {
            e.printStackTrace();
            // 导出失败，修改导出信息状态
            dataExportVo.setExportStat(DataExportTypeConstant.FAILURE_EXPORT.getValue());
            dataExportWriteService.updateDataExportStat(dataExportVo);
        }
        logger.info("{}导出结束",dataExportVo.getExportName());
    }

    /**
     * excel文件生成
     *
     * @param excel               excel信息
     * @param excelHeaderCellList 表格信息
     */
    private void singleExport(DataExportVo excel, List<ExcelHeaderCell> excelHeaderCellList) {
        String sql = excel.getSql();
        // 文件名称
        String fileName = excel.getExcelId() + ".xlsx";
        ExcelWriteUtil excelWriteUtil = new ExcelWriteUtil(allFilePath + fileName, excelHeaderCellList);
        logger.info("{}文件生成", allFilePath + fileName);

        // 写入数据
        int offset = 0;
        List<Map<String, Object>> data = null;
        do {
            String sqlLimit = sql + " limit " + limit + " offset " + offset;
            // 执行 sql
            data = jdbcTemplate.query(sqlLimit, (resultSet, row) -> {
                Map<String, Object> res = new HashMap<>();
                ResultSetMetaData metaData = resultSet.getMetaData();
                for (int i = 1; i <= metaData.getColumnCount(); i++) {
                    res.put(metaData.getColumnName(i), resultSet.getObject(i));
                }
                return res;
            });
            excelWriteUtil.writeBatch(data);
            offset += limit;
        } while ((!data.isEmpty()) && data.size() == limit);
        excelWriteUtil.flush();
    }
}

2）创建 DataExportScheduleTask 类，添加数据导出功能的定时任务

@Component
public class DataExportScheduleTask {
    private static final Logger logger = LoggerFactory.getLogger(DataExportScheduleTask.class);
    @Resource
    private DataExportReadService dataExportReadService;
    @Resource
    private DataExportWriteService dataExportWriteService;
    @Resource
    private JdbcTemplate jdbcTemplate;
    @Value("${export.file-path}")
    private String filePath;

    @Scheduled(cron = "${scheduled.export-data}")
    public void scheduledTaskByConfig() {
        logger.info("-----数据导出任务开始-----");
        try {
            List<DataExportVo> dataExportVoList = dataExportReadService.queryDataExportListByExportStat(DataExportTypeConstant.RUN_EXPORT.getValue());
            if (!dataExportVoList.isEmpty()) {
                for (DataExportVo dataExportVo : dataExportVoList) {
                    // 开始导出job
                    DataExportExcelJob dataExportExcelJob = new DataExportExcelJob(dataExportReadService, dataExportWriteService, jdbcTemplate, filePath);
                    dataExportExcelJob.executionJob(dataExportVo.getExportId());
                }
            }
        } catch (Exception e) {
            logger.error("数据导出任务出错：{}\n", e.getMessage(), e);
        }
        logger.info("-----数据导出任务结束-----");
    }
}

对应的 application.yml：

# 定时任务
    scheduled:
      export-data: 0/30 * * * * ?

    # 文件目录
    export:
      file-path: D:\

5、测试功能

1）创建 controller 类

@RestController
@RequestMapping("dataExportController")
public class DataExportController {
    private static final Logger logger = LoggerFactory.getLogger(DataExportController.class);
    @Resource
    private DataExportReadService dataExportReadService;
    @Resource
    private DataExportWriteService dataExportWriteService;

    /**
     * 数据导出为 excel 文件
     *
     * @param dataExportVo 数据导出信息
     */
    @PostMapping("dataExportToExcel")
    public void dataExportToExcel(DataExportVo dataExportVo) {
        // 创建数据导出任务
        try {
            dataExportWriteService.addDataExport(dataExportVo);
        } catch (Exception e) {
            e.printStackTrace();
            logger.error("数据导出任务创建失败");
        }
    }
}

2）通过使用 api 工具调用 controller 接口，传入导出名称（exportName）和导出表（exportTable）信息，创建导出信息

3）在本地查看 excel 文件是否生成，并成功导出数据

注意事项

excel 表的表头是根据数据表的 column_name 和 column_comment 信息生成，所以在创建数据表一定要添加字段对应的 comment注释！！！

到此，重要代码已经完成，以下是代码中涉及的相关代码。

6、相关类编写

6.1 constant 常量类

/**
 * description: 数据导出任务状态常量
 */
public enum DataExportTypeConstant {
    // 未导出
    NO_EXPORT("0"),
    // 正在导出
    RUN_EXPORT("1"),
    // 导出成功
    SUCCESS_EXPORT("2"),
    // 导出失败
    FAILURE_EXPORT("3");

    private final String value;

    DataExportTypeConstant(String value) {
        this.value = value;
    }

    public String getValue() {
        return value;
    }
}

/**
 * description: 数据类型常量
 */
public enum DataTypeConstant {
    INTEGER_TYPE("1"),
    FLOAT_TYPE("2"),
    STRING_TYPE("3"),
    DATE_TYPE("4"),
    OBJECT_TYPE("5");

    private final String value;

    DataTypeConstant(String value) {
        this.value = value;
    }

    public String getValue() {
        return value;
    }
}

6.2 entity 实体类

/**
 * description: 数据导出记录
 */
public class DataExportEntity implements Serializable {
    private static final long serialVersionUID = -4314200922466284541L;
    private String exportId;
    private String exportName;
    private String sql;
    private String exportTable;
    private Date exportTime;
    private String exportStat;
    
    // getter、setter、toString 方法省略
}

/**
 * description: 数据导出excel信息
 */
public class DataExportExeclEntity implements Serializable {
    private static final long serialVersionUID = 601452983176198118L;
    private String exportId;
    private String excelId;
    
     // getter、setter、toString 方法省略
}

6.3 vo 类

public class DataExportVo implements Serializable {
    private static final long serialVersionUID = -766478413455009960L;
    private String exportId;
    private String exportName;
    private String sql;
    private String exportTable;
    private Date exportTime;
    private String exportStat;
    private String excelId;
    
    // getter、setter、toString 方法省略
}

6.4 mapper 类

/**
* 数据导出 mapper
*/
@Mapper
public interface DataExportReadMapper {
    /**
     * 根据导出状态查询数据导出记录
     */
    List<DataExportVo> queryDataExportListByExportStat(String exportStat);
    /*
        对应 mapper 语句：
        <resultMap id="DataExportVoResultMap" type="com.example.data.export.vo.DataExportVo">
            <result property="exportId" column="export_id" jdbcType="VARCHAR"/>
            <result property="exportName" column="export_name" jdbcType="VARCHAR"/>
            <result property="sql" column="sql" jdbcType="LONGVARCHAR"/>
            <result property="exportTable" column="export_table" jdbcType="VARCHAR"/>
            <result property="exportTime" column="export_time" jdbcType="TIMESTAMP"/>
            <result property="exportStat" column="export_stat" jdbcType="VARCHAR"/>
            <result property="excelId" column="excel_id" jdbcType="VARCHAR"/>
        </resultMap>
        <select id="queryDataExportListByExportStat" resultMap="DataExportVoResultMap">
            select *
            from data_export
            where export_stat = #{exportStat, jdbcType=VARCHAR}
        </select>
     */

    /**
     * 根据id获取单条导出记录
     */
    DataExportVo queryDataExportDataById(String exportId);
    /*
        对应 mapper 语句：
        <select id="queryDataExportDataById" resultMap="DataExportVoResultMap">
            select *
            from data_export
            where export_id = #{exportId, jdbcType=VARCHAR}
        </select>
     */

    /**
     * 根据表名获取目标数据表字段信息
     */
    List<Map<String, Object>> queryTableInfoByTableName(String exportTable);
    /*
        对应 mapper 语句：
        <select id="queryTableInfoByTableName" resultType="java.util.Map">
            select *
            from information_schema.COLUMNS
            where TABLE_SCHEMA = (select database())
              and TABLE_NAME = #{exportTable, jdbcType=VARCHAR}
        </select>
     */

    /**
     * 修改导出状态
     */
    void updateDataExportStat(DataExportEntity dataExportEntity);
    /*
        对应 mapper 语句：
        <update id="updateDataExportStat" parameterType="com.example.data.export.entity.DataExportEntity">
            update data_export
            set export_stat = #{exportStat, jdbcType=VARCHAR}
            where export_id = #{exportId, jdbcType=VARCHAR}
        </update>
     */

    /**
     * 新增数据导出任务
     */
    void addDataExport(DataExportEntity dataExportEntity);
    /*
        对应 mapper 语句：
        <insert id="addDataExport" parameterType="com.example.data.export.entity.DataExportEntity">
            insert into data_export(export_id, export_name, `sql`, export_table, export_time, export_stat)
            values (#{exportId, jdbcType=VARCHAR},
                    #{exportName, jdbcType=VARCHAR},
                    #{sql, jdbcType=LONGVARCHAR},
                    #{exportTable, jdbcType=VARCHAR},
                    #{exportTime, jdbcType=TIMESTAMP},
                    #{exportStat, jdbcType=VARCHAR})
        </insert>
     */
}

/**
* 数据导出 excel Mapper
*/
@Mapper
public interface DataExportExcelMapper {
    /**
     * 根据导出id获取excel列表信息
     */
    List<DataExportVo> queryDataExportExcelListById(String exportId);
    /*
        对应 mapper 语句:
            <resultMap id="DataExportExcelResultMap" type="com.example.data.export.vo.DataExportVo">
                <result property="exportId" column="export_id" jdbcType="VARCHAR"/>
                <result property="excelId" column="excel_id" jdbcType="VARCHAR"/>
            </resultMap>
            
            <select id="queryDataExportExcelListById" resultMap="DataExportExcelResultMap">
                select *
                from data_export de
                join data_export_excel dee on dee.export_id = de.export_id
                where de.export_id = #{exportId, jdbcType=VARCHAR}
            </select>
     */
    
    /**
     * 新增数据导出任务
     */
    void addDataExportExcel(DataExportExeclEntity dataExportExeclEntity);
    /*
        对应 mapper 语句：
            <insert id="addDataExportExcel" parameterType="com.example.data.export.entity.DataExportExeclEntity">
            insert into data_export_excel(export_id, excel_id)
            values (#{exportId, jdbcType=VARCHAR},#{excelId, jdbcType=VARCHAR})
            </insert>
     */
}

6.5 service 类

1）ReadService：

public interface DataExportReadService {
    /**
     * 根据导出状态查询数据导出记录
     *
     * @param exportStat 导出状态
     */
    List<DataExportVo> queryDataExportListByExportStat(String exportStat);

    /**
     * 根据id获取单条导出记录
     *
     * @param exportId 导出id
     */
    DataExportVo queryDataExportDataById(String exportId);

    /**
     * 根据表名获取目标数据表字段信息
     *
     * @param exportTable 目标表
     */
    List<Map<String, Object>> queryTableInfoByTableName(String exportTable);

    /**
     * 根据导出id获取excel列表信息
     * @param exportId 导出id
     */
    List<DataExportVo> queryDataExportExcelListById(String exportId);
}


// 实现类
@Service("dataExportReadService")
public class DataExportReadServiceImpl implements DataExportReadService {
    @Resource
    private DataExportReadMapper dataExportReadMapper;
    @Resource
    private DataExportExcelReadMapper dataExportExcelReadMapper;

    @Override
    public List<DataExportVo> queryDataExportListByExportStat(String exportStat) {
        return dataExportReadMapper.queryDataExportListByExportStat(exportStat);
    }

    @Override
    public DataExportVo queryDataExportDataById(String exportId) {
        return dataExportReadMapper.queryDataExportDataById(exportId);
    }

    @Override
    public List<Map<String, Object>> queryTableInfoByTableName(String exportTable) {
        return dataExportReadMapper.queryTableInfoByTableName(exportTable);
    }

    @Override
    public List<DataExportVo> queryDataExportExcelListById(String exportId) {
        return dataExportExcelReadMapper.queryDataExportExcelListById(exportId);
    }
}

2）WriteService：

public interface DataExportWriteService {
    /**
     * 修改导出状态
     *
     * @param dataExportVo 导出信息
     */
    void updateDataExportStat(DataExportVo dataExportVo);

    /**
     * 新增数据导出任务
     *
     * @param dataExportVo 导出信息
     */
    void addDataExport(DataExportVo dataExportVo);
}


// 实现类
@Service("dataExportWriteService")
@Transactional
public class DataExportWriteServiceImpl implements DataExportWriteService {
    @Resource
    private DataExportWriteMapper dataExportWriteMapper;
    @Resource
    private DataExportExcelWriteMapper dataExportExcelWriteMapper;

    @Override
    public void updateDataExportStat(DataExportVo dataExportVo) {
        DataExportEntity dataExportEntity = new DataExportEntity();
        dataExportEntity.setExportId(dataExportVo.getExportId());
        dataExportEntity.setExportStat(dataExportVo.getExportStat());
        dataExportWriteMapper.updateDataExportStat(dataExportEntity);
    }

    @Override
    public void addDataExport(DataExportVo dataExportVo) {
        // 新增数据导出信息
        DataExportEntity dataExportEntity = new DataExportEntity();
        dataExportEntity.setExportId(UUID.randomUUID().toString().replace("-", ""));
        dataExportEntity.setExportName(dataExportVo.getExportName());
        dataExportEntity.setSql("select * from " + dataExportVo.getExportTable());
        dataExportEntity.setExportTable(dataExportVo.getExportTable());
        dataExportEntity.setExportTime(new Date());
        dataExportEntity.setExportStat(DataExportTypeConstant.RUN_EXPORT.getValue());
        dataExportWriteMapper.addDataExport(dataExportEntity);

        // 新增导出 excel 信息
        DataExportExeclEntity dataExportExeclEntity = new DataExportExeclEntity();
        dataExportExeclEntity.setExportId(dataExportEntity.getExportId());
        dataExportExeclEntity.setExcelId(UUID.randomUUID().toString().replace("-", ""));
        dataExportExcelWriteMapper.addDataExportExcel(dataExportExeclEntity);
    }
}