如何自動化你的Excel導入導出(Java)？

2020 年 12 月 7 日
筆記
AutoExcel, Excel export, Excel import, Excel導入, Excel導出, JAVA, POI

為什麼使用AutoExcel？

Excel導入導出在軟體開發中非常常見，只要你接觸過開發，就一定會遇到。相信很多人會跟我一樣選擇用Apache POI來完成這項工作，在感受到POI功能強大的同時，我的團隊也遇到了以下問題：

直接使用POI操作Excel將產生大量硬編碼，你會在編碼中寫死行索引和列索引
大量不可復用的格式控制編碼，如背景色、對齊方式、單元格樣式等
實施顧問明明提供了現成的模板，卻還要開發用程式碼實現一遍，開發效率低下
模板調整時不得不動用開發資源
簡單的導出也需要寫特定的程式碼

AutoExcel解決了上述問題，它非常簡單，只需要少量的程式碼即可完成複雜的導入導出；使用它時，程式設計師對導入導出無感，即不需要直接操作POI；與此同時，實施顧問提供的Excel即是導入導出模板，除非新增數據源或欄位，否則模板更新不需要動用開發資源。

AutoExcel並沒有對POI進行過重的封裝，而是充分利用了Excel本身具有的特性——名稱管理器，通過一些小技巧，將單元格與數據源產生映射，從而解耦程式設計師與POI，避免產生硬編碼，讓導入導出工作變得愉快而不再是枯燥乏味。

版本說明

當前版本：v2.0.0

支援Excel格式：2007

特點

模板導出
- 支援多個sheet
- 支援基礎對象和表格數據
- 單個sheet支援多個不定長數據源
- 支援橫向填充數據
- 自動應用單元格樣式
- 自動填充行號
- 自動填充公式
- 自動合計
直接導出
- 支援多個sheet
- 導出帶基本樣式
- 自動列寬
導入
- 支援多個sheet
- 數據類型自動轉換
支援百萬數據秒級導入導出

功能預覽

導出前	導出後

實現以上所有導出只需要編寫以下少量程式碼（你需要額外的程式碼來準備數據源，例如從資料庫中獲取。示例中使用DataGenerator生成demo數據）

// 設置導出參數，如數據源名稱、數據源等
List<TemplateExportPara> paras = new ArrayList<>();
paras.add(new TemplateExportPara("BusinessUnit", DataGenerator.genBusinessUnit()));
paras.add(new TemplateExportPara("Contract", DataGenerator.genContracts()));
paras.add(new TemplateExportPara("Project", DataGenerator.genProjects(1)));

List<Product> products = DataGenerator.genProducts(1);
TemplateExportPara para3 = new TemplateExportPara("Product", products);
// 單個sheet有多個數據源時，上方數據源應設置為插入
para3.setInserted(true);
paras.add(para3);

TemplateExportPara para5 = new TemplateExportPara("Product2", products);
// 橫向填充
para5.setDataDirection(DataDirection.Right);
paras.add(para5);

//（可選操作）移除不需要的sheet
ExcelSetting excelSetting = new ExcelSetting();
excelSetting.setRemovedSheets(Arrays.asList("will be removed"));

AutoExcel.save(this.getClass().getResource("/template/Export.xlsx").getPath(),
               this.getClass().getResource("/").getPath() + "AutoExcel.xlsx",
               paras,
               excelSetting);

認識模板

要實現以上導出，首先需要完成模板的製作。一些報表製作工具如微軟的RDL，你會在RDL中製作好導出模型，然後結合程式碼將數據導出到Excel。這個過程，RDL僅僅起到中介作用，意味著每次有新的導出任務，都得先製作一個導出模型。在AutoExcel中，Excel即模板，如果你的Excel來源是實施顧問，很可能這個Excel已經設定好數據格式、單元格樣式等，就等著你往上填數據，既然如此，何不就將這份Excel作為我們的導出模板，我們要做的，僅僅是在其中加入我們的東西而已。

名稱管理器

Excel中的名稱管理器，這個被大多數人忽視的功能，卻成為AutoExcel中連接數據源與單元格的橋樑。你可以通過點擊菜單公式->名稱管理器來打開名稱管理器，其中每一個名稱都對應Excel中的某個具體位置，可以是一個區域，也可以是一個單元格，當然，在這裡，我們定義的名稱都指向單元格。因此可以這麼理解，名稱管理器就是用來給單元格命名的。正是因為單元格有了名字，我們才能實現給單元格自動賦值而無需個性化的程式碼。

為單元格定義了名稱之後，當你再次點擊該單元格，會發現左上角的位置顯示了你剛才定義的名稱

除了在名稱管理器中新增名稱，還有一種方式更加直觀快捷。點擊你想要命名的單元格，然後直接在左上角輸入名稱，最後按Entry鍵即可。推薦使用這種方式創建名稱。

名稱規則

由於單元格名稱決定了何種數據按什麼方式進行填充，因此必須按以下規則進行命名：

數據源名稱.欄位名稱[.合計類型]，用於填充普通欄位或普通欄位的合計值，如：product.SaleArea.sum
數據源名稱.Formula.xxxx，用於填充公式，如：product.Formula.1
數據源名稱.RowNo，用於填充行號，如：product.RowNo

所有名稱均不區分大小寫，以下會根據具體場景分別進行介紹

導出

基礎對象

如圖所示，批註中註明了每個單元格的名稱，按照數據源名稱.欄位名稱的規則書寫

String templatePath = this.getClass().getResource("/template/Export.xlsx").getPath();
String outputPath = this.getClass().getResource("/").getPath() + "AutoExcel.xlsx";
//DataGenerator.genBusinessUnit()用於生成demo數據
TemplateExportPara para = new TemplateExportPara("BusinessUnit", DataGenerator.genBusinessUnit());
AutoExcel.save(templatePath, outputPath, para);

單表

如果你想導出的是一個列表數據，你只需要按照基礎對象的書寫規則進行命名即可。當然，列表數據導出往往比基礎對象複雜，比如，你可能需要一列行號，而又不想在程式碼中做特殊處理，這時候你可以使用數據源名稱.RowNo，將工作交給AutoExcel來處理。注意，RowNo是內置欄位，如果數據源中包含此欄位，將會被覆蓋。

還有一種情況非常常見，你有一個帶公式的單元格在表格中，如：=E6+F6，你希望下一行的單元格被賦值=E7+F7，這時你應該使用數據源名稱.Formula.xxxx，你可以使用任何你喜歡的公式，AutoExcel最終都會幫你自動填充。xxxx的部分你可以隨便書寫，只要保證名稱唯一即可。Formula同樣是內置欄位。

String templatePath = this.getClass().getResource("/template/Export.xlsx").getPath();
String outputPath = this.getClass().getResource("/").getPath() + "AutoExcel.xlsx";
//DataGenerator.genContracts()用於生成demo數據
TemplateExportPara para = new TemplateExportPara("Contract", DataGenerator.genContracts());
AutoExcel.save(templatePath, outputPath, para);

如果你已經運行上面的程式碼，你會發現AutoExcel自動幫你應用了單元格樣式，任何你想應用在導出數據上的樣式，都可以通過在模板中設置數據開始行（即你命名的單元格所在行）的樣式來控制。

多表

在一個Sheet中導出多個表格，如果你有這樣的需求，請在程式碼中將不是處於最下面的表格的導出參數設置為：setInserted(true)，如上圖，products對應的導出參數para應做如下設置：para.setInserted(true)。要知道，AutoExcel是不理會數據導出是否有足夠空間的，它只會一個勁地輸出，所以當你的模板空間不夠時，你需要告訴AutoExcel，之後AutoExcel會在導出前騰出足夠的空間來容納你的數據。

這裡引入了新的命名規則：數據源名稱.欄位名稱.合計類型，用於對指定欄位進行合計，目前支援兩種合計類型：Sum和Avg。

String templatePath = this.getClass().getResource("/template/Export.xlsx").getPath();
String outputPath = this.getClass().getResource("/").getPath() + "AutoExcel.xlsx";
List<TemplateExportPara> paras = new ArrayList<>();
//DataGenerator.genProjects()用於生成demo數據
paras.add(new TemplateExportPara("Project", DataGenerator.genProjects()));

//DataGenerator.genProducts()用於生成demo數據
TemplateExportPara para = new TemplateExportPara("Product", DataGenerator.genProducts());
para.setInserted(true);  //導出空間不夠時需設置
paras.add(para);

AutoExcel.save(templatePath, outputPath, paras);

橫向填充

如果你需要將數據向右而不是向下填充，你只需要使用setDataDirection(DataDirection.Right)即可。

String templatePath = this.getClass().getResource("/template/Export.xlsx").getPath();
String outputPath = this.getClass().getResource("/").getPath() + "AutoExcel.xlsx";
TemplateExportPara para = new TemplateExportPara("Product2", DataGenerator.genProducts());
para.setDataDirection(DataDirection.Right);  //向右填充
AutoExcel.save(templatePath, outputPath, para);

直接導出

直接導出，即導出過程不需要藉助模板，適用於集成到後台系統的通用導出功能中，程式碼非常簡單

String outputPath = this.getClass().getResource("/").getPath() + "Export Directly.xlsx";
DirectExportPara para = new DirectExportPara(DataGenerator.genProjects(1));
AutoExcel.save(outputPath, para);

效果：

如你所見，導出自帶基本樣式並自動調整列寬。

當然，你並不會喜歡這樣的標題和標題順序，因此，你需要藉助FieldSetting來讓你的標題可讀且按照你喜歡的順序來展現。

List<FieldSetting> fieldSettings = new ArrayList<FieldSetting>() {{
    add(new FieldSetting("projName", "Project Name"));
    add(new FieldSetting("projInfo", "Project Info."));
    add(new FieldSetting("saleStartDate", "Sales Start Date"));
    add(new FieldSetting("availablePrice", "Available Price"));
    add(new FieldSetting("availableAmount", "Available Amount"));
}};
String outputPath = this.getClass().getResource("/").getPath() + "Export Directly.xlsx";
DirectExportPara para = new DirectExportPara(DataGenerator.genProjects(), "Projects", fieldSettings);
AutoExcel.save(outputPath, para);

最終效果：

另外，你也可以一次導出多個sheet

String outputPath = this.getClass().getResource("/").getPath() + "Export Directly.xlsx";
List<DirectExportPara> paras = new ArrayList<>();
paras.add(new DirectExportPara(DataGenerator.genProjects(200), "Projects",
        DataGenerator.genProjectFieldSettings()));
paras.add(new DirectExportPara(DataGenerator.genContracts()));
AutoExcel.save(outputPath, paras);

自定義操作

AutoExcel致力於處理導入導出的通用場景，如果有個性化的需求，你應該取回Workbook的控制權，根據你的需求進行個性化處理。save方法提供了兩個Consumer，其中actionAhead會在導出操作開始之前被調用，actionBehind會在導出完成之後被調用，你完全可以通過這兩個Consumer來添加你想要的功能。

String templatePath = this.getClass().getResource("/template/Export.xlsx").getPath();
String outputPath = this.getClass().getResource("/").getPath() + "AutoExcel.xlsx";
List<TemplateExportPara> paras = new ArrayList<>();
paras.add(new TemplateExportPara("BusinessUnit", DataGenerator.genBusinessUnit()));
Consumer<Workbook> actionAhead = Workbook -> {
    //做任何你想做的事情
};        
Consumer<Workbook> actionBehind = workbook -> {
    //做任何你想做的事情
};
AutoExcel.save(templatePath, outputPath, paras, actionAhead, actionBehind);

導入

V2.0.0中的導入不再使用模板，因此需要通過FieldSetting指定列名和欄位名的映射關係，該映射關係可能存儲在你的資料庫中。支援多個sheet同時導入，可指定標題行索引和數據行索引。

List<ImportPara> importParas = new ArrayList<ImportPara>() {{
    add(new ImportPara(0, DataGenerator.genProductFieldSettings()));
    add(new ImportPara(1, DataGenerator.genProjectFieldSettings(), 1, 5));
}};
String fileName = this.getClass().getResource("/template/Import.xlsx").getPath();
DataSet dataSet = AutoExcel.read(fileName, importParas);
// 方式一、獲取原始數據，沒有類型轉換，可通過這種方式檢驗數據是否符合要求
List<Map<String, Object>> products = dataSet.get("Product");
List<Map<String, Object>> projects = dataSet.get("Project");
// 方式二、通過sheet索引獲取指定類的數據，類型自動轉換，轉換失敗將拋出異常
// List<Product> products = dataSet.get(0, Product.class);
// List<Project> projects= dataSet.get(1, Project.class);
// 方式三、通過sheet名稱獲取指定類的數據，類型自動轉換，轉換失敗將拋出異常
// List<Product> products = dataSet.get("Product", Product.class);
// List<Project> projects = dataSet.get("Project", Project.class);

public static List<FieldSetting> genProjectFieldSettings() {
    List<FieldSetting> fieldSettings = new ArrayList<>();
    fieldSettings.add(new FieldSetting("projName", "Project Name"));
    fieldSettings.add(new FieldSetting("projInfo", "Project Info."));
    fieldSettings.add(new FieldSetting("basalArea", "Basal Area"));
    fieldSettings.add(new FieldSetting("availableArea", "Available Area"));
    fieldSettings.add(new FieldSetting("buildingArea", "Building Area"));
    fieldSettings.add(new FieldSetting("buildingsNumber", "Buildings Number"));
    fieldSettings.add(new FieldSetting("saleStartDate", "Sales Start Date"));
    fieldSettings.add(new FieldSetting("landAcquisitionTime", "Land Acquisition Time"));
    fieldSettings.add(new FieldSetting("availablePrice", "Available Price"));
    fieldSettings.add(new FieldSetting("availableAmount", "Available Amount"));
    fieldSettings.add(new FieldSetting("insideArea", "Inside Area"));
    return fieldSettings;
}

public static List<FieldSetting> genProductFieldSettings() {
    List<FieldSetting> fieldSettings = new ArrayList<FieldSetting>() {{
        add(new FieldSetting("projName", "Project Name"));
        add(new FieldSetting("basalArea", "Basal Area"));
        add(new FieldSetting("availableArea", "Available Area"));
        add(new FieldSetting("buildingArea", "Building Area"));
        add(new FieldSetting("buildingsNumber", "Buildings Number"));
    }};
    return fieldSettings;
}

ImportPara構造方法入參：

sheetIndex：必填，sheet索引
fieldSettings：必填，列名與欄位名映射設置
titleIndex：可省略，標題行開始索引，默認為0
dataStartIndex：可省略，數據行開始索引，默認為1

為什麼使用FieldSetting而不採用註解的方式聲明列名？

非侵入式，不影響原來的程式碼

在設計系統的時候，為了重用相同的配置，如頁面展示、導出、導入、列印等，所有這些都展示相同的列名，我們會將這些配置存放在存儲介質如資料庫，待使用時載入出來，這種方式也可以避免硬編碼，還可以方便地進行動態配置，採用FieldSetting就是為了配合這種方式。AutoExcel儘可能讓導入導出融入到你的自動化系統。

百萬數據耗時測試

單位：毫秒

	10W行10列數據	100W行10列數據
模板導出	6,258	23,540
直接導出	5,711	24,952
導入	4,466	21,595
導入+類型轉換	4,823	26,279

運行示例程式碼

完整的示例程式碼請前往這裡查看

Tags: AutoExcel Excel export Excel import Excel導入 Excel導出 JAVA POI