導出 Excel 模板自動生成規則，避免用戶來回修改
一句話總結
Excel 導出、導入時，根據註解自動添加單元格驗證規則，避免用戶因填寫錯誤的枚舉欄位而反覆修改 Excel
需求背景
對於 Java Web 項目，總是不可避免的出現 Excel 導入、導出的需求，而 Excel 導入導出時，枚舉欄位和枚舉值的映射是非常常見的一種情況
例如：下面這張示例中的性別列
資料庫表結構：
Excel 中用戶需要輸入：男，女，未知
常見的 Excel 框架都已經覆蓋了枚舉映射的功能，例如：EasyPOI
但是這種操作方式對於用戶來說，並不是很方便，試想一下：假如用戶在性別列輸入了：男性，最終的結果一般就是程式拋出異常，用戶得到提示：性別輸入有誤，貼心的開發者可能會加上：請輸入 男 女 未知，做的更好一些的 可能在列頭添加標籤提示：該列僅能輸入 男 女 未知，但是這種弱限制也無法從根本上解決問題
更好一點的解決方案是：利用 Excel 的數據驗證功能，把單元格加上規則校驗，讓用戶只能輸入正確的枚舉值，避免因一次輸入錯誤而反覆返工，浪費用戶的時間和好心情
當用戶輸入了非枚舉值之後，Excel 會提示用戶輸入不合規，禁止用戶保存
這樣的交互就能從源頭保證用戶輸入正確的值
那這麼友好的設計，在 Java 中如何能方便且可擴展性更強的實現呢？
需求實現
我這邊的實現是基於 EasyPOI + 註解(EasyPOI 轉換映射關係註解復用) + 反射 實現的，解決了以上需求痛點的同時，可以滿足程式碼一處修改，多個功能都生效的目的
程式碼倉庫
實體類
@Excel 註解中的 replace 屬性，該屬性是 EasyPOI 用來做欄位映射的，我這裡復用他做 Excel 驗證的可選項，另外一個就是 orderNum 屬性，用該值來自動獲取某個欄位在 Excel 中的列的位置
@Data
public class Human extends BaseEntity {
private Long id;
@Excel(name = "姓名", orderNum = "1", width = 15)
private String name;
@Excel(name = "年齡", orderNum = "2", width = 15)
private Integer age;
@Excel(name = "性別", replace = {"男_1", "女_2", "未知_3"}, orderNum = "3", width = 15)
private Integer gender;
}
獲取列名和列位置的映射
該類在初始化時，需要指定當前導出 Excel 對應的實體類的類類型，然後通過遍歷類中欄位的註解，生成欄位和列排序(位置)的映射關係
public class FieldOrderMappingHelper<T> {
/**
* 支援的最大欄位數
*/
private final static int MAX_LIST_SIZE = 26;
public FieldOrderMappingHelper(Class<T> pojo) {
this.pojo = pojo;
initMap();
}
/**
* 解析注釋的 pojo 對象
*/
private Class<T> pojo;
/**
* 欄位和序號的映射關係
*/
private HashMap<String, Integer> fieldAndOrderMap;
/**
* 功能：初始化類的欄位內容，建立欄位和序號以及欄位和 excel 列名的映射關係
*
* @author [email protected]
* @date 2021/4/9 12:06
*/
private void initMap() {
HashMap<String, Integer> fieldAndOrderMap = new HashMap<>(16);
HashSet<Integer> existOrderNumSet = new HashSet<>(16);
List<FiledAndOrder> list = new ArrayList<>();
list = initList(list, pojo);
if (list.size() > MAX_LIST_SIZE) {
throw new RuntimeException(pojo.getName() + "目前最大支援 26 個欄位，26+ 需要改程式碼");
}
// 排序
list.sort(Comparator.comparing(FiledAndOrder::getOrder));
for (int i = 0; i < list.size(); i++) {
if (existOrderNumSet.contains(list.get(i).getOrder())) {
throw new RuntimeException(pojo.getName() + "類內部或與父類欄位中存在重複的 excel 排序，請修改");
}
existOrderNumSet.add(list.get(i).getOrder());
fieldAndOrderMap.put(list.get(i).getFiledName(), i);
}
this.fieldAndOrderMap = fieldAndOrderMap;
}
/**
* 功能：初始化類的欄位資訊，轉換成 ArrayList
*
* @return java.util.List<com.gridsum.ad.ooh.project.entity.FiledAndOrder>
* @author [email protected]
* @date 2021/4/9 12:09
*/
private List<FiledAndOrder> initList(List<FiledAndOrder> list, Class<?> pojoClass) {
if (Object.class.equals(pojoClass)) {
return list;
}
Field[] fields = pojoClass.getDeclaredFields();
for (Field f : fields) {
// 找到所有加了 Excel 註解的欄位
Excel annotation = f.getAnnotation(Excel.class);
if (annotation == null) {
continue;
}
// 過濾隱藏行
if (annotation.isColumnHidden()) {
continue;
}
FiledAndOrder filedAndOrder = new FiledAndOrder(f.getName(), Integer.parseInt(annotation.orderNum()));
list.add(filedAndOrder);
}
// 遞歸查找父類
Class<?> superclass = pojoClass.getSuperclass();
return initList(list, superclass);
}
public HashMap<String, Integer> getFieldAndOrderMap() {
return fieldAndOrderMap;
}
}
設置驗證規則
setValidation 方法有兩個參數，第一個是導出 Excel 對應的實體類的類類型，第二個是 FieldOrderMappingHelper.getFieldAndOrderMap() 獲取到的列名和排序映射，該類通過反射欄位上的註解，自動為生成的 workbook 添加驗證規則
public class ExcelStyleHelper {
/**
* 添加列值驗證的最小行
*/
public static final int EXCEL_VALID_ROW_MIN = 1;
/**
* 添加列值驗證的最大行
*/
public static final int EXCEL_VALID_ROW_MAX = (2 << 15) - 1;
/**
* Excel 對象
*/
private Workbook workbook;
/**
* Sheet 頁，默認取第一個 sheet 頁
*/
private Sheet sheet;
public ExcelStyleHelper(Workbook workbook) {
this.workbook = workbook;
this.sheet = workbook.getSheetAt(0);
}
/**
* 功能：單元格添加下拉框，僅支援 xls
*
* @author [email protected]
* @date 2021/4/8 18:55
*/
public void setValidation(Class<?> pojoClass, HashMap<String, Integer> map) {
// 遞歸到 Object 就停下
if (Object.class.equals(pojoClass)) {
return;
}
// 獲取所有的欄位
Field[] fields = pojoClass.getDeclaredFields();
for (Field field : fields) {
Excel annotation = field.getAnnotation(Excel.class);
if (annotation == null) {
continue;
}
String[] replace = annotation.replace();
if (replace.length == 0) {
continue;
}
String[] textList = new String[replace.length];
for (int i = 0; i < replace.length; i++) {
textList[i] = replace[i].split("_")[0];
}
// 根據欄位名獲取他在 excel 中的列數（結合 excel 註解中的排序）
Integer col = map.get(field.getName());
setValid(textList, col, col);
}
// 遞歸父類的註解
Class<?> superclass = pojoClass.getSuperclass();
setValidation(superclass, map);
}
/**
* 功能：設置驗證區間
*
* @author [email protected]
* @date 2021/4/9 15:11
*/
private void setValid(String[] textList, int firstCol, int endCol) {
// 設置數據有效性載入在哪個單元格上,四個參數分別是：起始行、終止行、起始列、終止列
CellRangeAddressList regions = new CellRangeAddressList(EXCEL_VALID_ROW_MIN, EXCEL_VALID_ROW_MAX, firstCol, endCol);
// 載入下拉列表內容
DVConstraint constraint = DVConstraint.createExplicitListConstraint(textList);
// 數據有效性對象
HSSFDataValidation dataList = new HSSFDataValidation(regions, constraint);
sheet.addValidationData(dataList);
}
}
示例導出程式碼
控制層程式碼如下
@RestController
public class ExcelExportController {
@GetMapping("excel")
public void excelExport(HttpServletResponse response) throws Exception {
List<Human> humanList = new ArrayList<>();
doWriteListToResponse(humanList, Human.class, response, "測試 Sheet", "測試 Excel.xls");
}
/**
* 功能：將結果寫入輸出流
*
* @author [email protected]
* @date 2021/4/14 14:46
*/
public <T> void doWriteListToResponse(List<T> list, Class<T> exportType, HttpServletResponse response, String sheetName, String excelName) throws IOException {
ExportParams ex = new ExportParams(null, sheetName, ExcelType.HSSF);
// 創建導出對象
Workbook workbook = ExcelExportUtil.exportExcel(ex, exportType, list);
// 初始化工具類
HashMap<String, Integer> map = new FieldOrderMappingHelper<>(exportType).getFieldAndOrderMap();
ExcelStyleHelper styleHelper = new ExcelStyleHelper(workbook);
// 添加規則
styleHelper.setValidation(exportType, map);
// 寫入輸出流，忽略此處硬編碼
response.setHeader("Content-Disposition", "attachment;filename=" + new String(excelName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1));
response.setCharacterEncoding("UTF-8");
response.setContentType("application/x-download");
workbook.write(response.getOutputStream());
}
}
