Java 将数据导出到Excel并发送到在线文档

一、需求

现将列表数据，导出到excel,并将文件发送到在线文档，摒弃了以往的直接在前端下载的老旧模式。

二、pom依赖

        <!-- redission -->
        <dependency>
            <groupId>org.redisson</groupId>
            <artifactId>redisson-spring-boot-starter</artifactId>
            <version>3.14.0</version>
            <exclusions>
                <exclusion>
                    <groupId>org.redisson</groupId>
                    <artifactId>redisson-spring-data-23</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>org.redisson</groupId>
            <artifactId>redisson-spring-data-20</artifactId>
            <version>3.14.0</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.83</version>
        </dependency>
        <!-- easyexcel 主要依赖  这一个基本上就够了-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.1.4</version>
        </dependency>

        <dependency>
            <groupId>commons-io</groupId>
            <artifactId>commons-io</artifactId>
            <version>2.4</version>
        </dependency>

三、定义表实体

import com.fasterxml.jackson.annotation.JsonFormat;

import java.util.Date;
import java.util.Objects;

/**
 * @Author: 
 * @Description
 * @Date: 下午5:18 2023/10/26
 */
public class EntityData {
    private String name;
    private String code;
    private Double score;
    private Integer age;
    private String phone;
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private Date createTime;

    public EntityData() {
    }

    public EntityData(String name, String code, Double score, Integer age, String phone, Date createTime) {
        this.name = name;
        this.code = code;
        this.score = score;
        this.age = age;
        this.phone = phone;
        this.createTime = createTime;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getCode() {
        return code;
    }

    public void setCode(String code) {
        this.code = code;
    }

    public Double getScore() {
        return score;
    }

    public void setScore(Double score) {
        this.score = score;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        EntityData that = (EntityData) o;
        return Objects.equals(name, that.name) &&
                Objects.equals(code, that.code) &&
                Objects.equals(score, that.score) &&
                Objects.equals(age, that.age) &&
                Objects.equals(phone, that.phone) &&
                Objects.equals(createTime, that.createTime);
    }

    @Override
    public int hashCode() {
        return Objects.hash(name, code, score, age, phone, createTime);
    }

    @Override
    public String toString() {
        return "EntityData{" +
                "name='" + name + '\'' +
                ", code='" + code + '\'' +
                ", score=" + score +
                ", age=" + age +
                ", phone='" + phone + '\'' +
                ", createTime=" + createTime +
                '}';
    }
}

四、定义写入Excel实体

import com.alibaba.excel.annotation.ExcelProperty;
import com.fasterxml.jackson.annotation.JsonFormat;

import java.util.Date;
import java.util.Objects;

/**
 * @Author:
 * @Description
 * @Date: 下午5:18 2023/10/26
 */
public class ExcelData {


    @ExcelProperty(value = "姓名")
    private String name;

    @ExcelProperty(value = "学号")
    private String code;

    @ExcelProperty(value = "分数")
    private Double score;

    @ExcelProperty(value = "统计时间")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private Date createTime;

    public ExcelData() {
    }

    public ExcelData(String name, String code, Double score, Date createTime) {
        this.name = name;
        this.code = code;
        this.score = score;
        this.createTime = createTime;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getCode() {
        return code;
    }

    public void setCode(String code) {
        this.code = code;
    }

    public Double getScore() {
        return score;
    }

    public void setScore(Double score) {
        this.score = score;
    }

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        ExcelData excelData = (ExcelData) o;
        return Objects.equals(name, excelData.name) &&
                Objects.equals(code, excelData.code) &&
                Objects.equals(score, excelData.score) &&
                Objects.equals(createTime, excelData.createTime);
    }

    @Override
    public int hashCode() {
        return Objects.hash(name, code, score, createTime);
    }

    @Override
    public String toString() {
        return "ExcelData{" +
                "name='" + name + '\'' +
                ", code='" + code + '\'' +
                ", score=" + score +
                ", createTime=" + createTime +
                '}';
    }
}

五、定义接口

import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

/**
 * @Author: 
 * @Description
 * @Date: 下午4:45 2023/10/26
 */
@RestController
@RequestMapping("/file")
public interface FileApi {

    @GetMapping(path = "/export")
    ResponseData<String> export();

}

六、定义service

import com.alibaba.excel.EasyExcel;
import com.example.exception.CustomException;
import org.redisson.api.RLock;
import org.redisson.api.RedissonClient;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;

import javax.annotation.Resource;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.concurrent.TimeUnit;

/**
 * @Author:
 * @Description
 * @Date: 下午4:45 2023/10/26
 */
@Service
public class FileService implements FileApi {

    @Resource
    private RedissonClient redissonClient; //这里使用redisson分布式锁 需要导入pom依赖和spring配置
    private static final String DOWNLOAD_EXCEL_SHEETS_KEY = "download:";
    @Autowired
    private ThreadPoolTaskExecutor taskExecutor;

    @Override
    public ResponseData<String> export() {
        //TODO 条件允许的话获取当前登录人信息作为redis key 的一部分，请自行填充
        String token = "";
        //加锁 防重复下载
        RLock lock = redissonClient.getLock(DOWNLOAD_EXCEL_SHEETS_KEY + token);
        try {
            if (lock.isLocked()) {
                throw new CustomException("您当前有导出中的任务尚未完成，请稍后再试！");
            }
            //这里使用看门狗机制 等待5秒，-1即开启看门狗
            boolean flag = lock.tryLock(5, -1, TimeUnit.SECONDS);
            //占用失败,抛出异常
            if (!flag) {
                throw new CustomException("锁定导出失败");
            }
            //模拟查询列表数据，可以从数据库查询
            List<EntityData> list = new ArrayList<>();
            list.add(new EntityData("张三", "001", 78.72, 11, "159888888888", new Date()));
            list.add(new EntityData("李四", "002", 45.87, 12, "159888888777", new Date()));
            list.add(new EntityData("王五", "003", 83.5, 13, "159888888666", new Date()));
            //判断列表数据是否为空
            if (CollectionUtils.isEmpty(list)) {
                throw new CustomException("列表没有数据!");
            }
            //异步导出 注意这里使用的异步操作，如果需要一些本地变量，如用户token信息，需要当参数透传
            taskExecutor.submit(() -> this.convertTExpConfirmationSheetExcel(list, "user"));
        } catch (InterruptedException ee) {
            Thread.currentThread().interrupt();
        } catch (CustomException eee) {
            throw new CustomException(eee.getMessage());
        } catch (Exception e) {
            throw new CustomException("导出出错");
        } finally {
            lock.unlock();
        }
        return ResponseData.ok("请稍后到XXXX查看");
    }

    /**
     * 确认单列表导出逻辑处理
     *
     * @param data
     * @param currentUser
     */
    private void convertTExpConfirmationSheetExcel(List<EntityData> data, String currentUser) {
        List<ExcelData> excelDataList = new ArrayList<>();
        //将数据拼装为导出数据
        for (EntityData sheet : data) {
            ExcelData excelData = new ExcelData();
            BeanUtils.copyProperties(sheet, excelData);
            excelDataList.add(excelData);
        }
        SimpleDateFormat slf = new SimpleDateFormat("yyyyMMddHHmmss");
        String time = slf.format(new Date());
        String fileName = String.format("数据导出%s.xlsx", time);
        String filePath = "";
        if (System.getProperty("os.name").toLowerCase().contains("mac")) {
            filePath = "/Users/admin/Downloads" + File.separator + fileName;
        } else {
            //配置服务器磁盘地址
//            filePath = "/home" + File.separator + "temp" + File.separator + fileName;
        }
        // 2、生成本地 excel
        EasyExcel.write(filePath, ExcelData.class).sheet("数据导出").doWrite(excelDataList);
        // 上传oss
        try (InputStream inputStream = new FileInputStream(new File(filePath))) {
            //TODO 调用上传服务

        } catch (Exception e) {
            throw new CustomException("长传导出异常");
        } finally {
            //删除临时文件
            try {
                org.apache.commons.io.FileUtils.forceDelete(new File(filePath));
            } catch (IOException e) {
                System.out.println("删除文件异常" + e);
            }
        }
    }
}

注意：本地测试需要先注释掉这段代码
try {
org.apache.commons.io.FileUtils.forceDelete(new File(filePath));
} catch (IOException e) {
System.out.println(“删除文件异常” + e);
}

七、配置线程池

这里用到了异步操作，需要配置线程池参数

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

import java.util.concurrent.ThreadPoolExecutor;

@Configuration
public class ExecutorConfig {

    private static final int CORE_POOL_SIZE = 30;
    private static final int MAX_POOL_SIZE = CORE_POOL_SIZE * 2 + 1;

    @Bean(name="taskExecutor")
    public ThreadPoolTaskExecutor taskExecutor(){
        ThreadPoolTaskExecutor poolTaskExecutor = new ThreadPoolTaskExecutor();
        /**
         * 此方法返回可用处理器的虚拟机的最大数量; 不小于1
         * int core = Runtime.getRuntime().availableProcessors();
         */
        //设置核心线程数
        poolTaskExecutor.setCorePoolSize(CORE_POOL_SIZE);
        //设置最大线程数
        poolTaskExecutor.setMaxPoolSize(MAX_POOL_SIZE);
        //除核心线程外的线程存活时间
        poolTaskExecutor.setKeepAliveSeconds(3);
        //如果传入值大于0，底层队列使用的是LinkedBlockingQueue,否则默认使用SynchronousQueue
        poolTaskExecutor.setQueueCapacity(40);
        //线程名称前缀
        poolTaskExecutor.setThreadNamePrefix("thread-execute");
        //设置拒绝策略
        poolTaskExecutor.setRejectedExecutionHandler(new ThreadPoolExecutor.CallerRunsPolicy());
        return poolTaskExecutor;

    }
}

八、配置环境参数

注意：换成自己redis的服务器地址
server.port=8888
spring.redis.database = 1
spring.redis.host = localhost
spring.redis.port = 6379
spring.redis.password =123456
spring.redis.jedis.pool.max-active = 8
spring.redis.jedis.pool.max-wait = -1ms
spring.redis.jedis.pool.min-idle = 0

九、测试类

1、启动项目

2、浏览器访问地址：http://localhost:8888/file/export

十、结果