匯出 Excel 模板自動生成規則，避免使用者來回修改

後青春期的Keats發表於2021-06-16
Excel

一句話總結

Excel 匯出、匯入時，根據註解自動新增單元格驗證規則，避免使用者因填寫錯誤的列舉欄位而反覆修改 Excel

需求背景

對於 Java Web 專案，總是不可避免的出現 Excel 匯入、匯出的需求，而 Excel 匯入匯出時，列舉欄位和列舉值的對映是非常常見的一種情況

例如：下面這張示例中的性別列

資料庫表結構：

image-20210616140903264

Excel 中使用者需要輸入：男，女，未知

image-20210616140615962

常見的 Excel 框架都已經覆蓋了列舉對映的功能，例如：EasyPOI

但是這種操作方式對於使用者來說，並不是很方便，試想一下：假如使用者在性別列輸入了：男性，最終的結果一般就是程式丟擲異常，使用者得到提示：性別輸入有誤，貼心的開發者可能會加上：請輸入 男 女 未知，做的更好一些的 可能在列頭新增標籤提示：該列僅能輸入 男 女 未知，但是這種弱限制也無法從根本上解決問題

image-20210616141205671

更好一點的解決方案是：利用 Excel 的資料驗證功能，把單元格加上規則校驗，讓使用者只能輸入正確的列舉值，避免因一次輸入錯誤而反覆返工，浪費使用者的時間和好心情

image-20210616141620860

當使用者輸入了非列舉值之後，Excel 會提示使用者輸入不合規，禁止使用者儲存

image-20210616141707359

這樣的互動就能從源頭保證使用者輸入正確的值

那這麼友好的設計，在 Java 中如何能方便且可擴充套件性更強的實現呢？

需求實現

我這邊的實現是基於 EasyPOI + 註解(EasyPOI 轉換對映關係註解複用) + 反射 實現的，解決了以上需求痛點的同時，可以滿足程式碼一處修改，多個功能都生效的目的

程式碼倉庫

GayHub

實體類

@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 kangshuai@gridsum.com
     * @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 kangshuai@gridsum.com
     * @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 kangshuai@gridsum.com
     * @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 kangshuai@gridsum.com
     * @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 kangshuai@gridsum.com
     * @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());
    }
}

相關文章