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

说明

通过接口，导出表格。

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

表格示例

依赖

版本

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

依赖

<!-- easypoi -->
<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 {@Autowiredprivate 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("[e-mail]");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("[e-mail]");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();}}