Excel匯入匯出神器（Java）

馮海濤發表於2020-09-28
ExcelJava

連結

為什麼使用AutoExcel？

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

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

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

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

功能預覽

匯出前匯出後
imageimage
imageimage
imageimage
imageimage

實現以上匯出只需要編寫以下少量程式碼（你需要額外的程式碼來準備資料來源，例如從資料庫中獲取）

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()));

List<Product> products = DataGenerator.genProducts();
TemplateExportPara para3 = new TemplateExportPara("Product", products);
para3.setInserted(true);
paras.add(para3);

TemplateExportPara para5 = new TemplateExportPara("Product2", products);
para5.setDataDirection(DataDirection.Right);
paras.add(para5);

ExcelSetting excelSetting = new ExcelSetting();
excelSetting.setRemovedSheets(Arrays.asList("will be removed"));

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

認識模板

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

名稱管理器

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

image

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

image

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

image

名稱規則

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

  1. 資料來源名稱.欄位名稱[.合計型別]，用於填充普通欄位或普通欄位的合計值，如：product.SaleArea.sum
  2. 資料來源名稱.Formula.xxxx，用於填充公式，如：product.Formula.1
  3. 資料來源名稱.RowNo，用於填充行號，如：product.RowNo

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

匯出

基礎物件

image

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

Java程式碼：

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

單表

image

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

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

Java程式碼：

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

多表

image

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

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

Java程式碼：

String templatePath = this.getClass().getResource("/template/Common.xlsx").getPath();
String outputPath = this.getClass().getResource("/").getPath() + "ExportWithTemplate.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);

向右填充

image

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

Java程式碼：

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

直接匯出

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

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

效果：

image

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

List<FieldSetting> fieldSettings = new ArrayList<FieldSetting>() {{
    add(new FieldSetting("projName", "Project Name"));
    add(new FieldSetting("basalArea", "Basal Area"));
    add(new FieldSetting("buildingArea", "Building Area"));
    add(new FieldSetting("insideArea", "Inside Area"));
    add(new FieldSetting("availableArea", "Available Area"));
    add(new FieldSetting("availablePrice", "Available Price"));
    add(new FieldSetting("availableAmount", "Available Amount"));
}};
String outputPath = this.getClass().getResource("/").getPath() + "Export.xlsx";
DirectExportPara para = new DirectExportPara(DataGenerator.genProjects(), "Projects", fieldSettings);
AutoExcel.save(outputPath, para);

最終效果：

image

自定義操作

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

String templatePath = this.getClass().getResource("/template/Common.xlsx").getPath();
String outputPath = this.getClass().getResource("/").getPath() + "ExportWithTemplate.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);

匯入

相較於匯出，匯入具有以下特點：

  1. 僅支援一種名稱規則：資料來源名稱.欄位名稱
  2. 暫不支援一個Sheet有多個表的情況
  3. 預設資料讀取方向（DataDirection）為null，即讀取基礎物件，如需讀取列表，需指明讀取方向為Down，暫不支援Right方向的讀取。

Java程式碼：

List<ImportPara> importParas = new ArrayList<ImportPara>() {{
    add(new ImportPara("BusinessUnit"));
    add(new ImportPara("Contract", DataDirection.Down));
    add(new ImportPara("Project", DataDirection.Down));
    //add(new ImportPara("Product", DataDirection.Down));   暫不支援
}};
String fileName = this.getClass().getResource("/").getPath() + "ExportWithTemplate.xlsx";
HashMap<String, List<HashMap<String, Object>>> datas = AutoExcel.read(fileName, importParas);

執行示例程式碼

完整的示例程式碼請前往單元測試檢視

GitHub地址

image

相關文章