對于銷售人員，使用Excel建立發票是很常見的。但是該過程通常涉及許多容易出錯的手動操作，例如輸入資料，複制/粘貼等。如何實作一個可以将資料從資料庫自動填充到發票Excel模闆中，而無需再辛苦手動輸入，從繁重的手動錄入中解脫出來，并且避免認為錯誤這是每個人迫切的需求。蟲蟲一直奉行理念：真正的自動化是解決使用者痛點問題，把繁重人工勞動釋放出來。本文我們就介紹一個老外的利用Java編寫自動化程式實作自動化發票生成器的案例，案例中建立了一個Web應用程式Invoice Builder，并利用Excel模版檔案，Java和Keikai将這種手動發票錄入過程轉換為內建的自動化過程。
總體架構
體系圖
下圖顯示了發票生成器應用程式的體系圖：
首先，導入一個源Excel檔案，其中包含2個空表，客戶表和産品表。業務員将從這兩個表中選擇客戶和産品。
接着，根據資料庫查詢，将客戶和産品資料填充到相應的表中。
最後，導入2個發票模闆；它們是業務員在Excel中建立的。這些模闆将在以後使用。
工作流程：選擇客戶和産品後，應用将通過将客戶和産品資料與所選發票模闆結合在一起來建立發票。
應用的動圖示例如下：
MVC模式
Keikai也支援MVC模式，在本應用MVC模式，具體如下：
視圖：用ZUL編寫的XML檔案。ZK将zul檔案轉換為UI元件并在浏覽器中呈現。
控制器：擴充ZK的Java類，SelectorComposer用于監聽View觸發的事件并控制ZK UI元件。通過Spreadsheet和RangeAPI控制Keikai。
模型：本執行個體中是CustomerService，但是它也可以是任何其他的Java業務類，例如身份驗證，資料查詢等。
具體實作
建構界面UI
界面UI建構中在實際選擇了zul，當然也可以選擇純Java(例如Swing)來建構UI。可以使用建立元件new Image()，通過将元件添加到Groupbox(容器)appendChild()，并使用來注冊事件偵聽器addEventListener()。使用這些API，可以在組框内的模闆清單數組上動态建立模闆預覽圖：
@Wireprivate Groupbox templateBox;private String[] templateFileNameList = {"invoice-template1.xlsx", "invoice-template2.xlsx"};...private void buildTemplatePreview() {...Arrays.stream(templateFileNameList).forEach(fileName -> {...Image preview = new Image(fileNameWithoutExt + "-preview.jpg");templateBox.appendChild(preview);preview.setAttribute(TEMPLATE_KEY, fileName);preview.addEventListener(org.zkoss.zk.ui.event.Events.ON_CLICK, event ->selectTemplate((Image) event.getTarget()));...});...}
由于業務人員往往習慣使用Excel模闆，主程式界面采用了Keikai Spreadsheet，這樣可以繼續使用其現有模闆。
Keikai基于ZK UI架構，該架構提供了完整的UI元件集以及XML格式的UI語言。
按照ZK的文法，使用XML标簽中的以下UI元件建構此Web應用程式的UI：
：keikai電子表格。
：水準布置其子元件，垂直布置元件。
：帶有邊框和标題的元件分組。
每個标簽都支援一些屬性，例如：
src：指定要導入到Keikai的Excel檔案路徑。
maxVisibleRows：控制keikai在浏覽器中渲染工作表時的最大可見行數。
控制器
要為頁面指定控制器，隻需在apply屬性處指定了全限定的類名：
...
然後，該控制器可以控制其子元件。我通常在頁面的根元件上指定一個控制器。
自動填充客戶
現在可以顯示電子表格和源檔案，接着需要将資料自動填充到表。
源Excel檔案僅包含一個空客戶表，其表樣式如列名和标題顔色。這裡的一件好事是，這個Excel檔案是由我的銷售人員使用Excel建立的-他更清楚自己想在此表中看到的内容。
從服務類加載客戶清單，并将清單填充到表中：
private void populateCustomers() {List customers = CustomerService.getCustomerList();Range startingCell = customerTable.toCellRange(0, 1); //the 1st column is for checkboxfor (String[] c : customers) {RangeHelper.setValuesInRow(startingCell, c);startingCell = startingCell.toShiftedRange(1, 0);}}
CustomerService 也可以是您所提供的資料實體的任何Java類。
setValuesInRow() 用字元串數組(例如B2，C2，D2 ...)一行一行地填充多個單元格
toShiftedRange(1, 0)轉移startingCell到下一行。
用命名範圍填充資料
将資料填充到電子表格UI時，需要指定要将資料填充到的目标單元格。選擇命名範圍是因為它是一種靈活的方法。
首先，建立幾個指定範圍中的每個模闆檔案例如Name，Phone和Email客戶詳細資訊。最終使用者選擇客戶和産品後，控制器将每一行提取為地圖。索引是标題，該值是對應的單元格值，例如
{Name: Debra, Phone: 338-8777, Email: [email protected]}。
然後，從所選模闆中克隆發票表，并将客戶詳細資訊填充到相應的命名範圍中。
@Listen(org.zkoss.zk.ui.event.Events.ON_CLICK + "=#create")public void createInvoice() {...Book invoiceBook = Books.createBook("invoice.xlsx");for (Map customer : selectedCustomers) {Sheet invoiceSheet = Ranges.range(invoiceBook).cloneSheetFrom(customer.get("CompanyName").toString(), templates.get(getSelectedTemplateFileName()).getSheetAt(0));populateNamedRange(generateAgentData(), invoiceSheet);populateNamedRange(customer, invoiceSheet);...}...}private void populateNamedRange(Map fieldMap, Sheet sheet) {List namedRanges = Ranges.getNames(sheet);fieldMap.forEach((name, value) -> {if (namedRanges.contains(name)) {Range range = Ranges.rangeByName(sheet, name);range.setCellValue(value);}});}
使用者權限控制
在此應用程式中，客戶資料是從資料庫中填充的，不希望使用者可以更改，隻可以選擇這些記錄。是以，通過以下方式限制了它們在使用者界面上可以執行的操作：
隐藏工具欄和上下文菜單：
通過指定使工作表标簽可見showSheetbar="true"。預設情況下，其他所有内容(如工具欄，公式欄和上下文菜單)都是不可見的。這樣，使用者就不會無意間更改了UI上顯示的内容。
啟用工作表保護并禁用添加工作表
通過以下方式啟用工作表保護：protectSheet()将所有工作表設為隻讀，并禁止使用者通過添加工作表disableUserAction()。
private void limitAccess() {for (int i = 0; i < spreadsheet.getBook().getNumberOfSheets(); i++) {Ranges.range(spreadsheet.getBook().getSheetAt(i)).protectSheet(SELECTION_FILTER);}spreadsheet.disableUserAction(AuxAction.ADD_SHEET, true);}
工作表保護下的可編輯區域
在Excel中，可以取消選中鎖定狀态以在工作表保護下使單元格可編輯。其他單元将保持隻讀狀态。使用此設定，可以在受保護的圖紙中允許一定範圍的可編輯區域。導入到Keikai後，此設定将保留，是以可以在準備源檔案時從Excel端完成。
重用
在應用程式中，有2個Excel模闆，想一次導入它們，然後在需要時使用它們。
Keikai Importer将Excel xlsx檔案轉換為Book。可以将Book配置設定給Spreadsheet并将其呈現給浏覽器。或者，可以直接操作Bookwith Range，而無需将其配置設定給Spreadsheet。最常見的用法是從模闆書克隆表或複制單元格。每個需要Excel模闆的人都可以從其中擷取内容，而無需再次導入模闆檔案。在應用程式中，将Book2個模闆Excel檔案的對象存儲在Map(templateWarehouse)中，以備将來使用：
private static HashMap templateWarehouse = new HashMap<>();private static Importer importer = Importers.getImporter();...private void importInvoiceTemplate() {...for (String fileName : templateFileNameList) {if (!templateWarehouse.containsKey(fileName)) { //avoid importing againtemplateWarehouse.put(fileName, importer.imports(new File(WebApps.getCurrent().getRealPath(BookUtil.DEFAULT_BOOK_FOLDER), fileName), fileName));}}...}
importer.imports(new File(...)) 傳回一個Book。
總結
本文中我們示範了如何将現有的手動Excel檔案的流程轉換為具有Excel檔案，Java和Keikai的Web應用程式。該應用程式與後端服務內建在一起，包括資料庫和使用者權限控制。可以将相同的技術應用于涉及基于Excel的流程的任何其他方案，将手動工作流轉變為自動化和內建的Web Apps。