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

    一、需求

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

    二、pom依赖

            
        <dependency>
            <groupId>org.redissongroupId>
            <artifactId>redisson-spring-boot-starterartifactId>
            <version>3.14.0version>
            <exclusions>
                <exclusion>
                    <groupId>org.redissongroupId>
                    <artifactId>redisson-spring-data-23artifactId>
                exclusion>
            exclusions>
        dependency>
        <dependency>
            <groupId>org.redissongroupId>
            <artifactId>redisson-spring-data-20artifactId>
            <version>3.14.0version>
        dependency>
        <dependency>
            <groupId>com.alibabagroupId>
            <artifactId>fastjsonartifactId>
            <version>1.2.83version>
        dependency>
        
        <dependency>
            <groupId>com.alibabagroupId>
            <artifactId>easyexcelartifactId>
            <version>2.1.4version>
        dependency>

        <dependency>
            <groupId>commons-iogroupId>
            <artifactId>commons-ioartifactId>
            <version>2.4version>
        dependency>

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34

    三、定义表实体

    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 +
                '}';
    }
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82
    • 83
    • 84
    • 85
    • 86
    • 87
    • 88
    • 89
    • 90
    • 91
    • 92
    • 93
    • 94
    • 95
    • 96
    • 97
    • 98
    • 99
    • 100
    • 101
    • 102
    • 103
    • 104
    • 105
    • 106
    • 107
    • 108
    • 109

    四、定义写入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 +
                '}';
    }
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82
    • 83
    • 84
    • 85
    • 86
    • 87
    • 88
    • 89
    • 90
    • 91
    • 92
    • 93
    • 94
    • 95

    五、定义接口

    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();

}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

    六、定义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);
            }
        }
    }
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82
    • 83
    • 84
    • 85
    • 86
    • 87
    • 88
    • 89
    • 90
    • 91
    • 92
    • 93
    • 94
    • 95
    • 96
    • 97
    • 98
    • 99
    • 100
    • 101
    • 102
    • 103
    • 104
    • 105
    • 106
    • 107
    • 108
    • 109
    • 110
    • 111
    • 112
    • 113
    • 114
    • 115
    • 116

    注意：本地测试需要先注释掉这段代码
    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;

    }
}


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36

    八、配置环境参数

    注意：换成自己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
    在这里插入图片描述

    十、结果

    在这里插入图片描述
    在这里插入图片描述

    在这里插入图片描述

  • 相关阅读:
    提高组CSP-S初赛模拟试题整理2
    docker系列：dockerfile及其用法解析
    SQL基础理论篇（七）：多表关联的连接算法
    如何保证数据库与缓存数据一致性？
    Vue｜props配置
    一、docker简介及卸载、安装
    Web会话跟踪：Cookie与Session
    CSDN21天学习挑战赛之折半插入排序
    websocket通信案例
    使用idea，操作git创建分支,回滚版本，合并分支 ---全流程配图---
  • 原文地址：https://blog.csdn.net/weixin_43969830/article/details/134082184