Excel文件生成与下载（SpringBoot项目）（easypoi）

冷不防 2023-10-15 18:42 14阅读 0赞

## 说明 ##

通过接口，导出表格。

使用`SpringBoot`框架和`easypoi`表格解析框架，生成Excel表格，并通过接口下载。

## 表格示例 ##

![在这里插入图片描述][5caba7b5dcd545489bf3b5bf203416d4.png]

## 依赖 ##

### 版本 ###

<easypoi.version>4.4.0</easypoi.version>

### 依赖 ###

<dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>${easypoi.version}</version>
    </dependency>
    <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>${easypoi.version}</version>
    </dependency>
    <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>${easypoi.version}</version>
    </dependency>

## 代码 ##

### Controller ###

package com.example.service;
    
    import com.example.service.UserExcelService;
    import io.swagger.annotations.Api;
    import io.swagger.annotations.ApiOperation;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.GetMapping;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RestController;
    
    import javax.servlet.http.HttpServletResponse;
    import java.io.IOException;
    
    @RestController
    @RequestMapping("file")
    @Api(tags = "文件")
    public class FileController {
        
    
        @Autowired
        private UserExcelService userExcelService;
    
    
        @GetMapping("export/user_excel")
        @ApiOperation("导出用户列表（Excel表格，以附件形式下载）")
        public void exportUserExcel(HttpServletResponse response) throws IOException {
        
            userExcelService.downloadUserExcel(response);
        }
    
    }

### Service ###

package com.example.service;
    
    import cn.afterturn.easypoi.excel.ExcelExportUtil;
    import cn.afterturn.easypoi.excel.entity.ExportParams;
    import com.example.data.excel.UserExcel;
    import com.example.db.entity.UserEntity;
    import com.example.util.FileUtil;
    import org.springframework.beans.BeanUtils;
    import org.springframework.stereotype.Service;
    
    import javax.servlet.http.HttpServletResponse;
    import java.io.IOException;
    import java.io.OutputStream;
    import java.util.List;
    import java.util.stream.Collectors;
    import java.util.stream.Stream;
    
    @Service
    public class UserExcelService {
        
    
    
        public void downloadUserExcel(HttpServletResponse response) throws IOException {
        
            // 获取用户表格对象列表
            List<UserExcel> userExcelList = listUserExcel();
            // 获取表格下载的输出流
            OutputStream outputStream = FileUtil.getExcelOutputStream("用户列表.xlsx", response);
            // 导出表格
            ExcelExportUtil.exportExcel(new ExportParams("用户列表（抬头）", "sheet"), UserExcel.class, userExcelList).write(outputStream);
        }
    
    
        /**
         * 获取用户表格对象列表
         */
        private List<UserExcel> listUserExcel() {
        
            List<UserEntity> userEntities = listUserEntity();
    
            // 将查询出来的 数据库Entity，转换为 Excel实体 。
            return userEntities.stream().map(item -> {
        
                UserExcel vo = new UserExcel();
                BeanUtils.copyProperties(item, vo);
                return vo;
            }).collect(Collectors.toList());
        }
    
    
        /**
         * 模拟从数据库查询出数据列表。
         */
        private List<UserEntity> listUserEntity() {
        
            UserEntity user1 = new UserEntity();
            user1.setId("1");
            user1.setName("张三");
            user1.setAccount("zhangsan");
            user1.setPassword("123456");
            user1.setAge(25);
            user1.setEmail("zhangsan@example.com");
            user1.setStatus(1);
            user1.setRemark("VIP客户");
    
            UserEntity user2 = new UserEntity();
            user2.setId("2");
            user2.setName("李四");
            user2.setAccount("lisi");
            user2.setPassword("111222");
            user2.setAge(28);
            user2.setEmail("lisi@example.com");
            user2.setStatus(2);
            user2.setRemark("客户已禁用");
    
            return Stream.of(user1, user2).collect(Collectors.toList());
        }
    
    
    }

### 表格实体 ###

package com.example.data.excel;
    
    import cn.afterturn.easypoi.excel.annotation.Excel;
    import io.swagger.annotations.ApiModel;
    import lombok.Data;
    
    /**
     * 用户信息-Excel对象;
     *
     * @author : songguanxun
     * @date : 2023-9-8
     */
    @Data
    @ApiModel(value = "用户-Excel对象")
    public class UserExcel {
        
    
        @Excel(name = "姓名", orderNum = "1", width = 30)
        private String name;
    
        @Excel(name = "账号", orderNum = "2", width = 30)
        private String account;
    
        @Excel(name = "年龄", orderNum = "3", width = 20)
        private Integer age;
    
        @Excel(name = "电子邮箱", orderNum = "4", width = 30)
        private String email;
    
        @Excel(name = "账号状态", orderNum = "5", replace = {
        "启用_1", "禁用_2"}, width = 20)
        private Integer status;
    
        @Excel(name = "备注", orderNum = "6", width = 50)
        private String remark;
    
    }

### 数据库实体 ###

package com.example.db.entity;
    
    import lombok.Data;
    
    /**
     * 用户
     *
     * @author : songguanxun
     * @date : 2023-9-8
     */
    @Data
    public class UserEntity {
        
    
        private String id;
    
        private String name;
    
        private String account;
    
        private String password;
    
        private Integer age;
    
        private String email;
    
        private Integer status;
    
        private String remark;
    
    }

### 文件工具类 ###

package com.example.util;
    
    import com.example.enumeration.ContentDispositionEnum;
    import org.springframework.http.HttpHeaders;
    
    import javax.servlet.http.HttpServletResponse;
    import java.io.IOException;
    import java.io.OutputStream;
    import java.net.URLEncoder;
    import java.nio.charset.StandardCharsets;
    
    /**
     * 文件工具类
     */
    public class FileUtil {
        
    
        /**
         * 获取表格下载的输出流
         *
         * @param fileName 文件名
         * @param response 接口响应对象
         * @return 输出流
         */
        public static OutputStream getExcelOutputStream(String fileName, HttpServletResponse response) throws IOException {
        
            String fileNameEncoded = URLEncoder.encode(fileName, StandardCharsets.UTF_8.name());
            response.setHeader(HttpHeaders.CONTENT_DISPOSITION, ContentDispositionEnum.ATTACHMENT.getCode() + ";fileName=" + fileNameEncoded);
            return response.getOutputStream();
        }
    
    }

[5caba7b5dcd545489bf3b5bf203416d4.png]: https://img-blog.csdnimg.cn/5caba7b5dcd545489bf3b5bf203416d4.png