Springboot+POI实现通用的我的导出功能

前言

在项目开发过程中，我们经常会对某个功能模块的数据进行Excel导出，且每次都要重复的实现导出功能，那我们是否可以对齐进行抽象封装，在需要做导出功能的时候只需要写查询数据即可，答案是必须的。本章就是笔者基于项目中导出功能实战的总结。

一、技术选型

本章主要使用的技术栈：Springboot+JPA+POI+MYSQL
在这里插入图片描述

二、设计数据表

设计我的导出记录表对导出记录进行存储。表字段如下：

CREATE TABLE `my_import_t` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `file_name` varchar(255) DEFAULT NULL COMMENT '文件名称',
  `file_id` varchar(0) DEFAULT NULL COMMENT '文件存储id',
  `task_status` varchar(255) DEFAULT NULL COMMENT '任务状态(1：导出中、2：导出成功、3：导出失败)',
  `creation_by` varchar(255) DEFAULT NULL,
  `creation_date` datetime DEFAULT NULL,
  `last_updated_by` varchar(0) DEFAULT NULL,
  `last_updated_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

三、功能拆解

本章主要针对导出进行设计和代码实现，导入在后续完善。
在这里插入图片描述

四、接口定义

导出接口定义图：
在这里插入图片描述
导出接口源码：

package com.imk.cases.my.imports.imports.starter.service;

import com.imk.cases.my.imports.imports.starter.po.ExportPO;
import org.apache.poi.ss.usermodel.Sheet;

import java.io.File;
import java.util.List;

/**
 * 描述
 *
 * @author darrn.xiang
 * @date 2022/11/13 16:50
 */
public interface IExportService {

    int FILE_MAX_BATCH_ROWS = 50000;

    int QUERY_MAX_BATCH_ROWS = 500;

    int FILL_DATA_START_INDEX = 3;

    int FILED_ROW_INDEX = 2;

    /**
     * 导出入口
     *
     * @param exportFileName
     * @param exportRecord
     */
    void export(String exportFileName, ExportPO exportRecord) ;

    /**
     * 下载导出模板
     *
     * @param templateName
     * @return
     */
    String downloadTemplate(String templateName);

    /**
     * 导出的总行数
     *
     * @return
     */
    int getTotal();

    /**
     * 分页获取导出的数据行
     *
     * @param startIndex
     * @param endIndex
     * @return
     */
    List<?> getPagedData(int startIndex,int endIndex);

    /**
     * 批量填充数据
     *
     * @param templateFile 模板文件
     * @param outputDir 输出目录
     * @param exportFileName 导出的文件名称
     */
    String batchFillData(File templateFile,String outputDir,String exportFileName);


    /**
     *
     *
     */
    void fillData(Sheet sheet, List<?> dataList, int fillDataStartIndex);

    /**
     * 将填充好的数据文件上传到文档服务器
     *
     * @return
     */
    String uploadDataFile(String localFilePath);

    /**
     * 创建或更新导出记录
     *
     * @param exportRecord
     * @return
     */
    ExportPO createOrUpdateExportRecord(ExportPO exportRecord);

}

导出记录持久化接口：

package com.imk.cases.my.imports.imports.starter.repository;

import com.imk.cases.my.imports.imports.starter.po.ExportPO;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

/**
 * 描述
 *
 * @author darrn.xiang
 * @date 2022/11/13 17:01
 */
@Repository
public interface ExportRepository extends JpaRepository<ExportPO, Integer> {
}

package com.imk.cases.my.imports.imports.starter.po;

import lombok.Data;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import java.time.ZonedDateTime;

/**
 * 描述
 *
 * @author darrn.xiang
 * @date 2022/11/13 17:00
 */
@Data
@Entity
@Table(name = "my_export_t")
public class ExportPO {
    @Id
    private Integer id;
    private String fileName;
    private String fileId;
    private String taskStatus;
    private String createdBy;
    private ZonedDateTime creationDate;
    private String lastUpdatedBy;
    private ZonedDateTime lastUpdatedDate;

}

五、统一模板实现

基于模板方法实现，其中上传到如文档服务器功能还未完成实现。

package com.imk.cases.my.imports.imports.starter.service.impl;

import com.alibaba.fastjson.JSONObject;
import com.imk.cases.my.imports.imports.starter.po.ExportPO;
import com.imk.cases.my.imports.imports.starter.repository.ExportRepository;
import com.imk.cases.my.imports.imports.starter.service.IExportService;
import com.imk.cases.my.imports.imports.starter.utils.ZipUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;

import java.io.*;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ArrayBlockingQueue;
import java.util.concurrent.ThreadPoolExecutor;
import java.util.concurrent.TimeUnit;

/**
 * 描述
 *
 * @author darrn.xiang
 * @date 2022/11/13 17:27
 */
public abstract class AbstractExportService implements IExportService {

    @Autowired
    private ExportRepository exportRepository;

    public abstract String getTemplateName();

    @Override
    public void export(String exportFileName, ExportPO exportRecord) {
        try{
            // 下载模板
            String templateName = getTemplateName();
            String downloadTemplateFilePath = downloadTemplate(templateName);
            File templateFile = new File(downloadTemplateFilePath);

            // 填充数据
            String outputDir = "export/"+exportFileName+"-"+System.currentTimeMillis();
            String localFilePath = this.batchFillData(templateFile, outputDir, exportFileName);

            // 上传到文档服务器
            String fileId = this.uploadDataFile(localFilePath);
            exportRecord.setFileId(fileId);
            exportRecord.setTaskStatus("2");
        }catch (Exception exception){
            exportRecord.setTaskStatus("3");
        }

        // 更新导出任务状态
        createOrUpdateExportRecord(exportRecord);
    }

    private XSSFWorkbook getBook(File file ){
        try(InputStream inputStream = new FileInputStream(file)){
            XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
            return workbook;
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public String downloadTemplate(String templateName) {
        return null;
    }

    @Override
    public String batchFillData(File template,String outputDir,String exportFileName) {
        int total = this.getTotal();
        if(total <= FILE_MAX_BATCH_ROWS){
            return exportSingleFile(template,outputDir,exportFileName,total);
        }

        // 分文件批次
        int fileCount = total / FILE_MAX_BATCH_ROWS;
        int lastBatchCount = total % FILE_MAX_BATCH_ROWS;
        if( lastBatchCount > 0){
            fileCount++;
        }

        ThreadPoolExecutor threadPoolExecutor = new ThreadPoolExecutor(1,2,0, TimeUnit.SECONDS,new ArrayBlockingQueue<>(10));

        // 遍历生成多个文件
        for (int i = 1;i<=fileCount;i++){
            String tmpExportFileName = exportFileName + "-"+i;
            int fillRows = (i==fileCount)?lastBatchCount:FILE_MAX_BATCH_ROWS;
            threadPoolExecutor.execute(() -> exportSingleFile(template,outputDir,tmpExportFileName,fillRows));
        }
        threadPoolExecutor.shutdown();

        // 打包zip
        String exportZipName = outputDir+"/"+exportFileName +".zip";
        try {
            ZipUtils.zip(exportZipName,outputDir);

        } catch (Exception e) {
            e.printStackTrace();
        }
        return exportZipName;
    }

    private String exportSingleFile(File template,String outputDir,String exportFileName,int fillRows){
        // 获取excel sheet
        XSSFWorkbook book = getBook(template);
        XSSFSheet sheet = book.getSheetAt(0);

        // 计划循环分页的数量
        int pages = fillRows/QUERY_MAX_BATCH_ROWS;
        int lastPageSize = fillRows% QUERY_MAX_BATCH_ROWS;
        if(lastPageSize>0){
            pages++;
        }

        // 循环分页查询并填充数据
        for(int i=0;i<pages;i++){
            int startIndex = QUERY_MAX_BATCH_ROWS * i;
            int endIndex = QUERY_MAX_BATCH_ROWS * (i+1);
            int fillDataStartIndex = FILL_DATA_START_INDEX + endIndex;

            // 查询数据和填充数据
            List<?> pagedData = this.getPagedData(startIndex, endIndex);
            this.fillData(sheet,pagedData,fillDataStartIndex);
        }

        // 删除字段行
        Row fieldRow = sheet.getRow(FILED_ROW_INDEX);
        sheet.removeRow(fieldRow);

        // 生成导出文件
        String tempFilePath = outputDir+"/"+exportFileName + ".xlsx";
        File exportFile = new File(tempFilePath);
        try(OutputStream outputStream = new FileOutputStream(exportFile)){
            book.write(outputStream);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return tempFilePath;
    }

    @Override
    public void fillData(Sheet sheet, List<?> dataList, int fillDataStartIndex) {
        Row fieldRow = sheet.getRow(FILED_ROW_INDEX);
        int cellCount = fieldRow.getPhysicalNumberOfCells();
        int currIndex= fillDataStartIndex;
        for (Object rowData:dataList) {
            Map<String,Object> rowDataMap = JSONObject.parseObject(JSONObject.toJSONString(rowData));
            Row createRow = sheet.createRow(currIndex);
            currIndex++;
            for (int i=0;i<cellCount;i++) {
                String fieldName = fieldRow.getCell(i).getStringCellValue();
                Object value = rowDataMap.get(fieldName);
                if(value == null){
                    value = "";
                }
                Cell cell = createRow.createCell(i);
                cell.setCellValue(value.toString());
            }
        }
    }

    @Override
    public String uploadDataFile(String localFilePath) {
        File file = new File(localFilePath);
        // 上传到服务器
        String fileId = "调用上传服务接口";

        // 删除本地文件
        file.delete();

        return fileId;
    }

    @Override
    public ExportPO createOrUpdateExportRecord(ExportPO exportRecord) {
        exportRepository.save(exportRecord);
        return exportRecord;
    }
}

六、异步调用方案

通过线程池实现异步调用方案。

配置线程池

#配置核心线程数
thread-pool.corePoolSize = 4
# 配置最大线程数
thread-pool.maxPoolSize = 100
# 配置队列大小
thread-pool.queueCapacity = 500
# 线程最多存活时间/s
thread-pool.keepAliveSeconds = 120
# 配置线程池中的线程的名称前缀
thread-pool.threadNamePrefix = my-thread-


package com.imk.cases.my.imports.imports.starter.configuration;

import lombok.Data;
import org.springframework.boot.context.properties.ConfigurationProperties;

/**
 * 线程池参数配置
 *
 * @author darrn.xiang
 * @date 2022/8/21 17:22
 */
@Data
@ConfigurationProperties(prefix = "thread-pool")
public class MyThreadPoolProperties {

    private int  corePoolSize;

    private int  maxPoolSize;

    private int  queueCapacity;

    private String  threadNamePrefix;

    private int keepAliveSeconds;
}

package com.imk.cases.my.imports.imports.starter.configuration;

import org.springframework.boot.context.properties.EnableConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;

import java.util.concurrent.ThreadPoolExecutor;

/**
 * 配置线程池实例
 *
 * @author darrn.xiang
 * @date 2022/8/21 17:26
 */
@Configuration
@EnableConfigurationProperties(MyThreadPoolProperties.class)
public class MyThreadPoolConfiguration {

    @Bean("myThreadExecutor")
    public ThreadPoolTaskExecutor myThreadExecutor(MyThreadPoolProperties properties){
        ThreadPoolTaskExecutor executor = new ThreadPoolTaskExecutor();
        executor.setCorePoolSize(properties.getCorePoolSize());
        executor.setMaxPoolSize(properties.getMaxPoolSize());
        executor.setQueueCapacity(properties.getQueueCapacity());
        executor.setThreadNamePrefix(properties.getThreadNamePrefix());

        executor.setKeepAliveSeconds(properties.getKeepAliveSeconds());

        // 拒绝策略,交给调用这线程处理
        executor.setRejectedExecutionHandler(new ThreadPoolExecutor.CallerRunsPolicy());

        // 初始化
        executor.initialize();

        return executor;
    }
}

实现任务异步处理逻辑

package com.imk.cases.my.imports.imports.starter.service;

/**
 * 描述
 *
 * @author darrn.xiang
 * @date 2022/11/13 18:37
 */
public interface IAsyncTaskService {

    void execute();
}

package com.imk.cases.my.imports.imports.starter.service.impl;

import com.imk.cases.my.imports.imports.starter.po.ExportPO;
import com.imk.cases.my.imports.imports.starter.service.IAsyncTaskService;
import com.imk.cases.my.imports.imports.starter.service.IExportService;
import com.imk.cases.my.imports.imports.starter.utils.AppContextUtils;
import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;

/**
 * 描述
 *
 * @author darrn.xiang
 * @date 2022/11/13 18:38
 */

public class ExportTaskService<S extends IExportService> implements IAsyncTaskService {
    private S targetService;
    private ThreadPoolTaskExecutor threadPoolTaskExecutor;
    private String exportFileName;
    private ExportPO exportRecord;

    public ExportTaskService(S targetService,String exportFileName){
        targetService = targetService;
        threadPoolTaskExecutor = AppContextUtils.getContext().getBean("myThreadExecutor",ThreadPoolTaskExecutor.class);
        exportFileName = exportFileName;
        ExportPO createRecord = new ExportPO();
        createRecord.setTaskStatus("1");
        createRecord.setFileName(exportFileName);
        targetService.createOrUpdateExportRecord(createRecord);
        exportRecord = createRecord;
    }

    @Override
    public void execute() {
        threadPoolTaskExecutor.execute(() -> targetService.export(exportFileName,exportRecord));
    }
}

七、使用案例

业务导出接口样例代码

package com.imk.cases.my.imports.imports.starter.service.impl;

import com.imk.cases.my.imports.imports.starter.po.ExportPO;
import com.imk.cases.my.imports.imports.starter.service.IUserExportService;
import com.imk.cases.my.imports.imports.starter.service.dto.UserExportDTO;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * 描述
 *
 * @author darrn.xiang
 * @date 2022/11/13 18:28
 */
@Service
public class UserExportService extends AbstractExportService implements IUserExportService {

    @Override
    public String export(UserExportDTO dto) {
        String exportFileName = "用户信息导出";
        ExportTaskService<UserExportService> exportTaskService = new ExportTaskService<>(this,exportFileName);
        exportTaskService.execute();
        return "success";
    }

    @Override
    public int getTotal() {
        return 10000;
    }

    @Override
    public List<?> getPagedData(int startIndex, int endIndex) {
        return null;
    }

    @Override
    public String getTemplateName() {
        return "user.xlsx";
    }
}

模板数据的模板样例

第1、2行为标题中英文
第3行为导出的javabean字段的名称用于字段位置匹配。
在这里插入图片描述

总结

该项目还未完全实现完成，目前还处理核心逻辑实现中，其中文档服务器上传下载还需结合服务调试。给广大的猿友提供一个实现思路吧！

原文链接：https://blog.csdn.net/u012042111/article/details/127836441

前言