一、前言
專案開發過程中，免不了需要資料匯出功能，常見的匯出工具包有poi，easypoi，easyexcel，它們各有優缺點，簡要來說：
- poi：功能強大，使用起來相對複雜，大資料時可能導致記憶體溢位
- easypoi：基於poi實現，功能強大，簡單易用，大資料時可能導致記憶體溢位，小資料量時推薦
- easyexcel：基於poi實現，效能更優，大資料量時推薦
- 本文只介紹easyexcel，並介紹常見問題比如字典、樣式的解決方案
二、業務流程
介紹資料匯出業務流程之前，先梳理下資料匯出，什麼是資料匯出，為什麼需要資料匯出？
我們都知道資料儲存在資料庫中，資料庫中的資料可以透過前端頁面或者APP呈現給使用者，其實透過excel將資料匯出也是一種呈現方式，而且可以透過excel對資料做更復雜的處理。
前面說了，excel匯出和前端頁面、APP一樣都是一種呈現方式，所以前端頁面和APP在將資料呈現給使用者的過程中遇到的問題，像屬性轉換（資料庫儲存的name，要呈現給使用者名稱）、字典轉換（資料庫儲存的1/0，要呈現給使用者啟用/停用）等等問題，excel匯出一樣會遇到。下面介紹下資料呈現必須要經過業務流程
- 獲取需要匯出的資料集合
- 資料屬性和自然語言對映關係，將資料物件的屬性轉為使用者可以理解的自然語言
- 資料字典值和自然語言對映關係，將屬性的字典值轉為使用者可以理解的自然語言（非字典值，是什麼值就呈現什麼值）
- 資料樣式和自然語言樣式對映關係，將資料樣式轉為使用者可以理解的自然語言樣式
- 設定表格樣式
- 將資料集合按照上述對映關係和表格樣式，寫入到excel中
- 使用者下載excel
三、實現
1、引入easyexcel、fastjson、lombok包
<!--easy excel-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>4.0.3</version>
</dependency>
<!--fastjson-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.46</version>
</dependency>
<!--工具-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.0</version>
</dependency>
2、建立Json工具類
package com.yu.demo.tools;
import com.alibaba.fastjson.JSON;
import com.fasterxml.jackson.core.type.TypeReference;
import java.lang.reflect.Type;
import java.util.Map;
/**
* JSON工具類
*
* @author admin
*/
public abstract class JsonUtil {
private JsonUtil() {
}
public final static Type MAP_INTEGER_STRING = new TypeReference<Map<Integer, String>>() {
}.getType();
/**
* json串轉Map(Map的value型別一致時使用)
*
* @param jsonString json串
* @return 物件
*/
public static <K, V> Map<K, V> json2Map(String jsonString, Type type) {
return JSON.parseObject(jsonString, type);
}
}
3、建立自定義字典轉換註解
package com.yu.demo.tools;
import java.lang.annotation.*;
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface DictSource {
/**
* 字典型別主鍵
*/
String dictTypeId() default "";
/**
* 字典內容json串
*/
String dictContentJson() default "";
}
4、建立字典轉換實現類
package com.yu.demo.tools;
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
import org.apache.poi.util.StringUtil;
import java.lang.reflect.Field;
import java.util.Map;
public class IntegerDictConverter implements Converter<Integer> {
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
}
@Override
public WriteCellData<?> convertToExcelData(Integer value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
//屬性值為空時，直接返回
if (value == null) {
//為空時的處理，與前端展示保持一致即可
return new WriteCellData<>("");
}
//獲取新增@ExcelProperty註解且converter = IntegerDictConverter.class的屬性
Field field = contentProperty.getField();
//獲取該屬性的DictConverter註解資訊
DictSource dictSource = field.getAnnotation(DictSource.class);
//配置了converter = IntegerDictConverter.class的屬性，但是沒有新增DictSource註解的直接返回
if (dictSource == null) {
return new WriteCellData<>(String.valueOf(value));
}
//獲取配置的dictTypeId
String dictTypeId = dictSource.dictTypeId();
//獲取配置的dictContentJson
String dictContentJson = dictSource.dictContentJson();
//判斷dictTypeId是否為空
boolean nullDictType = StringUtil.isBlank(dictTypeId);
//判斷nullDictContentJson是否為空
boolean nullDictContentJson = StringUtil.isBlank(dictContentJson);
//字典配置都為空時，將屬性值轉為字串直接返回
if (nullDictType && nullDictContentJson) {
return new WriteCellData<>(String.valueOf(value));
}
//優先使用dictTypeId處理轉換
if (!nullDictType) {
//透過dictTypeId獲取字典內容集合：List<DictContent> dictContents = dictContentService.listByDictTypeId(dictTypeId);//主鍵是數值的，將dictTypeId轉為數值
//遍歷字典內容，匹配屬性值與字典值：value.equals(dictContent.getValue())
//匹配成功後獲取字典名稱返回：return new WriteCellData<>(dictContent.getName());
//如果沒有匹配成功使用dictContentJson處理轉換
}
if (!nullDictContentJson) {
Map<Integer, String> dictContentMap = JsonUtil.json2Map(dictContentJson, JsonUtil.MAP_INTEGER_STRING);
String cnName = dictContentMap.get(value);
if (StringUtil.isNotBlank(cnName)) {
return new WriteCellData<>(cnName);
}
}
//沒有轉換成功時使用預設屬性值
return new WriteCellData<>(String.valueOf(value));
}
}
5、建立資料物件類
package com.yu.demo.web.easyexcel.entity;
import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.converters.date.DateStringConverter;
import com.yu.demo.web.easyexcel.component.DictSource;
import com.yu.demo.web.easyexcel.component.IntegerDictConverter;
import lombok.Getter;
import lombok.Setter;
import lombok.ToString;
import java.util.Date;
@Setter
@Getter
@ToString
//類上新增@ExcelIgnoreUnannotated時，屬性沒有@ExcelProperty註解時不匯出
//類上未新增@ExcelIgnoreUnannotated，屬性沒有@ExcelProperty註解時也匯出
@ExcelIgnoreUnannotated
public class User {
/**
* 名稱
*/
@ExcelProperty("名稱")
private String name;
/**
* 密碼
* 類新增@ExcelIgnoreUnannotated，屬性未新增@ExcelProperty，不匯出
*/
private String password;
/**
* 生日
* 日期樣式處理
* 1.使用@DateTimeFormat設定匯出樣式
* 2.使用DateStringConverter處理匯出
*/
@DateTimeFormat("yyyy-MM-dd HH:mm:ss")
@ExcelProperty(value = "生日", converter = DateStringConverter.class)
private Date birthday;
/**
* 性別
* 字典轉換處理
*/
@ColumnWidth(7)//指定列寬度，優先順序高於LongestMatchColumnWidthStyleStrategy
@ExcelProperty(value = "性別", converter = IntegerDictConverter.class)
@DictSource(dictContentJson = "{0:'女',1:'男',2:'保密'}")
private Integer sex;
}
6、建立多sheet頁封裝物件
package com.yu.demo.tools;
import lombok.Getter;
import lombok.Setter;
import lombok.ToString;
import java.util.List;
/**
* excel匯入匯出資料物件
*/
@Setter
@Getter
@ToString
public class SheetEntity<T> {
/**
* sheet頁名稱（匯出引數）
* 可以為空，為空時，單sheet頁沒有名稱，多sheet頁序號為名稱
*/
private String sheetName;
/**
* 資料型別（匯入匯出引數）
*/
private Class<T> head;
/**
* 資料（匯出引數）
*/
private List<T> data;
}
7、建立Excel匯出工具類
匯出的資料有如下三種及其說明
- 透過全路徑檔名匯出，easyexcel透過全路徑檔名建立檔案，將資料寫入檔案，當路徑不存在時報錯，適合場景：一次匯出，多次下載
- 透過檔案匯出，將資料寫入檔案，當路徑不存在報錯，適合場景：一次匯出，多次下載
- 透過輸出流匯出，將資料寫入輸出流，適合場景：匯出一次下載一次
package com.yu.demo.tools;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.handler.WriteHandler;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.util.StringUtil;
import java.io.File;
import java.io.OutputStream;
import java.util.List;
/**
* excel匯入匯出工具類（easyExcel實現）
* easyPoi：併發量和資料量都不大時推薦，定製化的匯出支援非常的豐富
* easyExcel：高併發、大資料量時推薦
*/
public abstract class ExcelUtil {
// 設定居中對齊的樣式
private static final WriteCellStyle CONTENT_WRITE_CELL_STYLE;
private static final WriteHandler HORIZONTAL_CELL_STYLE_STRATEGY;
static {
CONTENT_WRITE_CELL_STYLE = new WriteCellStyle();
//水平居中
CONTENT_WRITE_CELL_STYLE.setHorizontalAlignment(HorizontalAlignment.CENTER);
//垂直居中
CONTENT_WRITE_CELL_STYLE.setVerticalAlignment(VerticalAlignment.CENTER);
HORIZONTAL_CELL_STYLE_STRATEGY = new HorizontalCellStyleStrategy(null, CONTENT_WRITE_CELL_STYLE);
}
private ExcelUtil() {
}
/**
* 使用EasyExcel匯出
*
* @param fullFileName 檔案路徑+檔名+字尾（檔案已存在時覆蓋）
* @param sheetName sheet名稱（為空時使用預設值0）
* @param head 資料型別（為空時沒有表頭，只有資料）
* @param exportData 需要匯出的資料（為空時，沒有資料）
*/
public static void exportByEasyExcel(String fullFileName, String sheetName, Class<?> head, List<?> exportData) {
File targetFile = new File(fullFileName);
// 判斷檔案父目錄是否存在
if (!targetFile.getParentFile().exists()) {
boolean mkdirResult = targetFile.getParentFile().mkdirs();
if (!mkdirResult) {
return;
}
}
ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(targetFile, head);
if (fullFileName.endsWith(ExcelTypeEnum.XLS.getValue())) {
excelWriterBuilder.excelType(ExcelTypeEnum.XLS);
} else if (fullFileName.endsWith(ExcelTypeEnum.CSV.getValue())) {
excelWriterBuilder.excelType(ExcelTypeEnum.CSV);
} else {
excelWriterBuilder.excelType(ExcelTypeEnum.XLSX);
}
excelWriterBuilder
//設定列按最大長度調整
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
//設定水平垂直居中
.registerWriteHandler(HORIZONTAL_CELL_STYLE_STRATEGY)
.sheet(sheetName)
.doWrite(exportData);
}
/**
* 使用EasyExcel匯出
*
* @param outputStream 輸出流
* @param sheetName sheet名稱（為空時使用預設值0）
* @param head 資料型別（為空時沒有表頭，只有資料）
* @param exportData 需要匯出的資料（為空時，沒有資料）
*/
public static void exportByEasyExcel(OutputStream outputStream, ExcelTypeEnum excelType, String sheetName, Class<?> head, List<?> exportData) {
EasyExcel.write(outputStream, head)
.excelType(excelType)
//設定列按最大長度調整，非執行緒安全，每次都需要new
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
//設定水平垂直居中
.registerWriteHandler(HORIZONTAL_CELL_STYLE_STRATEGY)
.sheet(sheetName)
.doWrite(exportData);
}
/**
* 使用EasyExcel匯出多sheet頁資料
*
* @param outputStream 輸出流
* @param sheetEntities 匯出資料物件集合
*/
public static void exportByEasyExcel(OutputStream outputStream, ExcelTypeEnum excelType, List<SheetEntity<?>> sheetEntities) {
ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(outputStream).excelType(excelType);
writeSheets(excelWriterBuilder, sheetEntities);
}
private static void writeSheets(ExcelWriterBuilder excelWriterBuilder, List<SheetEntity<?>> sheetEntities) {
excelWriterBuilder
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
.registerWriteHandler(HORIZONTAL_CELL_STYLE_STRATEGY);
ExcelWriter excelWriter = excelWriterBuilder.build();
for (int i = 0; i < sheetEntities.size(); i++) {
SheetEntity<?> sheetEntity = sheetEntities.get(i);
Class<?> head = sheetEntity.getHead();
List<?> exportData = sheetEntity.getData();
String sheetName = StringUtil.isBlank(sheetEntity.getSheetName()) ? String.valueOf(i + 1) : sheetEntity.getSheetName();
WriteSheet writeSheet = EasyExcel.writerSheet(i + 1, sheetName).head(head).build();
excelWriter.write(exportData, writeSheet);
}
excelWriter.finish();
}
}
8、建立測試類
package com.yu.demo.web.easyexcel.web;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.yu.demo.web.easyexcel.entity.SheetEntity;
import com.yu.demo.web.easyexcel.entity.User;
import com.yu.demo.web.easyexcel.util.ExcelUtil;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.http.HttpHeaders;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.annotation.PostConstruct;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
@RestController
@RequestMapping("user")
public class UserController {
@Value("${download.path}")
private String filePath;
private List<User> users;
private List<SheetEntity<?>> sheetEntities;
@PostConstruct
public void init() {
users = new ArrayList<>(5);
for (int i = 0; i < 5; i++) {
User user = new User();
user.setName(i + "號使用者");
user.setPassword(String.valueOf(i * 1000));
user.setBirthday(new Date());
user.setSex(i % 3);
users.add(user);
}
sheetEntities = new ArrayList<>(2);
for (int i = 0; i < 2; i++) {
SheetEntity<User> sheetEntity = new SheetEntity<>();
sheetEntity.setSheetName(i + "號sheet");
sheetEntity.setHead(User.class);
sheetEntity.setData(users);
sheetEntities.add(sheetEntity);
}
}
/**
* 單sheet頁透過全路徑檔名匯出測試介面（也可以透過檔案流匯出）
* 返回檔名，前端透過web路徑+檔名下載檔案
*/
@GetMapping("/filePath")
public String filePath() {
String fileName = "使用者.xlsx";
String fullFileName = filePath + fileName;
ExcelUtil.exportByEasyExcel(fullFileName, "使用者", User.class, users);
return fileName;
}
/**
* 多sheet頁透過檔案流匯出（也可以透過全路徑檔名匯出）
*/
@GetMapping("/download")
public void download(HttpServletResponse response) throws IOException {
String fileName = "使用者";
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding(StandardCharsets.UTF_8.name());
String encodeFileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename=*=utf-8''" + encodeFileName + ExcelTypeEnum.XLSX.getValue());
ExcelUtil.exportByEasyExcel(response.getOutputStream(), ExcelTypeEnum.XLSX, sheetEntities);
}
}
9、application.yml中新增檔案下載路徑配置
download:
#配置檔案下載路徑
path: C:\Users\Administrator\Desktop
spring:
web:
resources:
static-locations:
#註冊檔案下載路徑
- file:${download.path}
#系統預設配置
- classpath:/META-INF/resources/
- classpath:/resources/
- classpath:/static/
- classpath:/public/
四、介面測試
1、啟動專案
2、使用全路徑檔名方式匯出
- 訪問介面地址：http://localhost:8080/user/filePath
- 訪問下載檔案地址：http://localhost:8080/使用者.xlsx
3、使用檔案流方式匯出
- 訪問介面地址：http://localhost:8080/user/download
五、總結
- 使用Entity物件作為關係對映的載體，使用@ExcelProperty註解對映屬性名稱，並可以指定轉換器、序號等資訊；使用@DateTimeFormat註解和指定轉換器設定時間格式；使用自定義註解@DictSource註解和指定轉換器轉換字典值
- 使用@ColumnWidth或其同目錄下的其他註解、WriteHandler設定Excel樣式
- 使用全路徑檔名、檔案、檔案流作為資料匯出的載體匯出資料。
- SpringBoot整合easyexcel資料匯出案例下載