基于Excel和Java自动化工作流程：发票生成器示例

对于销售人员，使用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：

maxVisibleRows="6" maxVisibleColumns="8"src="/WEB-INF/books/invoice-source.xlsx"showSheetbar="true"/>

：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: debra@...}。

然后，从所选模板中克隆发票表，并将客户详细信息填充到相应的命名范围中。

@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。