說(shuō)明

通過(guò)接口，導(dǎo)出表格。

使用SpringBoot框架和easypoi表格解析框架，生成Excel表格，并通過(guò)接口下載。

表格示例

依賴(lài)

版本

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

依賴(lài)

<!-- 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("導(dǎo)出用戶(hù)列表（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 {// 獲取用戶(hù)表格對(duì)象列表List<UserExcel> userExcelList = listUserExcel();// 獲取表格下載的輸出流OutputStream outputStream = FileUtil.getExcelOutputStream("用戶(hù)列表.xlsx", response);// 導(dǎo)出表格ExcelExportUtil.exportExcel(new ExportParams("用戶(hù)列表（抬頭）", "sheet"), UserExcel.class, userExcelList).write(outputStream);}/*** 獲取用戶(hù)表格對(duì)象列表*/private List<UserExcel> listUserExcel() {List<UserEntity> userEntities = listUserEntity();// 將查詢(xún)出來(lái)的 數(shù)據(jù)庫(kù)Entity，轉(zhuǎn)換為 Excel實(shí)體 。return userEntities.stream().map(item -> {UserExcel vo = new UserExcel();BeanUtils.copyProperties(item, vo);return vo;}).collect(Collectors.toList());}/*** 模擬從數(shù)據(jù)庫(kù)查詢(xún)出數(shù)據(jù)列表。*/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客戶(hù)");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("客戶(hù)已禁用");return Stream.of(user1, user2).collect(Collectors.toList());}}

表格實(shí)體

package com.example.data.excel;import cn.afterturn.easypoi.excel.annotation.Excel;
import io.swagger.annotations.ApiModel;
import lombok.Data;/*** 用戶(hù)信息-Excel對(duì)象;** @author : songguanxun* @date : 2023-9-8*/
@Data
@ApiModel(value = "用戶(hù)-Excel對(duì)象")
public class UserExcel {@Excel(name = "姓名", orderNum = "1", width = 30)private String name;@Excel(name = "賬號(hào)", 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 = "賬號(hào)狀態(tài)", orderNum = "5", replace = {"啟用_1", "禁用_2"}, width = 20)private Integer status;@Excel(name = "備注", orderNum = "6", width = 50)private String remark;}

數(shù)據(jù)庫(kù)實(shí)體

package com.example.db.entity;import lombok.Data;/*** 用戶(hù)** @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;}

文件工具類(lèi)

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;/*** 文件工具類(lèi)*/
public class FileUtil {/*** 獲取表格下載的輸出流** @param fileName 文件名* @param response 接口響應(yīng)對(duì)象* @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();}}