具有Java灵活性的Excel UI：在Keikai中合并电子表格数据

介绍大多数格式化数据表都有一个共同的最终目标。我们想分析，比较和使用我们收集的完整数据集。

如果数据是在单独的工作表和表格中收集的，有时是重复的条目，那么我们需要能够将所有数据合并到一个简单易用的表格中。

这是通常使事情变得复杂的地方。最后，有些通常会最终将数据行从单个工作表中复制并粘贴到汇总表中。

真实示例 以预算为例。即使您的组织只有四个部门，您最终也会得到四个不同的预算表。其中一些部门之间的费用会重复。例如，工资和薪水将成为任何组织单位的项目。其中一些对于特定团队来说是唯一的，例如广告是营销部门专有的。然后，只有几个（但不是全部）团队可以共享一些，甚至可以在团队表中重复。

最重要的是，每个工作表都必须由不同的团队经理来填写，而团队经理通常最终会通过电子邮件传递同一文件的多个副本。

所有这些最终达到高潮，链中的最后一个人收到同一文件的五个版本，其中一些包含旧数据，一些包含不完整的数据，其中一个莫名其妙地是空白的。采取这种千差万别的数据集并使其可利用需要大量的精力和大量的时间。

摘要表的要求 我们希望通过两种方式对其进行改进。首先，我们需要使共享和协作变得更加容易。数据输入过程应该简化，具有单点访问并且任何指定的用户都可以访问。

我们还需要改进工作表工作流程本身。Excel公式在许多情况下可能功能强大，但也有其局限性。我们已经在本文档中设置了公式，以计算每个部门工作表内的总和，比率，平均值等。这些，我们将绝对保留。这是Excel的公式发挥最大作用的地方，我们将依靠Keikai来简单地使用我们已经拥有的公式。

另一方面，Excel公式不是执行数据集操作的理想选择。搜索，合并和过滤是可能的，但是它们花费了大量的时间来设置和执行。它们也很脆弱，并且经常依靠静态的页面布局来进行维护。Keikai在Java Web服务器中运行，因此我们将数据处理委托给这一层来改善工作流程。

在这种情况下，我们需要生成两个合并的数据摘要。每个期间合并一个，每个部门合并另一个。

“每个期间”表将需要使用相同的标签合并每个条目，并根据每个年度（Q1到Q4）对它们的值求和。

“每个部门”表将需要使用相同的标签合并每个条目，并根据宣布此费用的部门对它们的值求和。如果要在Excel中创建它们，则可能需要复杂的公式检查并匹配每个源表中的结果，或者需要使用自定义合并查询。

Pure Excel工作流程的局限性 Excel的“搜索和检索”公式的主要内容是VLOOKUP。孤立地使用该公式是一个合理的公式，但是出于过滤和合并大量值的目的，它存在一个主要问题：使用此公式的每个像元将使处理时间倍增。一种简单的思考方法是，如果单个VLOOKUP完成需要X毫秒，那么使用VLOOKUP完成具有N个单元的工作表将使用（X）N毫秒来完成。

当您的工作表仅包含10行时，看起来似乎并不多，但是我们都知道，在现实世界中，费用表很少包含“仅10行”，并且在某个时候，有人必然会要求最后一份报告10年的活动。那将是该指数处理时间从明显过渡到崩溃的关键点。

在纯Excel中，这远非唯一基于公式的处理会遭受复杂性和效率上限的困扰。在经典情况下，公式本身是工作表的一部分，并且可能会受到工作表中更改的影响，因此必须加以保护以防止最终用户修改它们。

Keikai Java工作流程改进 首先，我们将把现有文档制作成Keikai支持的网页。第一个好处是立竿见影的。没有更多的电子邮件，没有更多的重复文件。每个人都在同时处理同一文档。协作模式带来了更多优势，因为您可以实时查看其他用户的活动并相应地调整您的输入。

当我们与Keikai合作时，有一种更强大的方法可以实现这一目标。Keikai依赖Java服务器，因此，我们可以在特定的工作表事件上运行Java代码。

我们可以访问服务器端Java编程，这意味着我们可以实现高效的算法，而不是链接函数。每增加一行数据，这一点将变得更加明显。

实施逻辑 在此示例中，我们从单个工作表中检索混合数据。每个工作表都采用相同的格式，因此我们可以为每个工作表使用相同的“从工作表中获取条目”方法。如此说来，如果我们有多个结构，则可以使各个“从工作表中获取条目”方法与所使用的每个表结构相匹配。

这使我们可以建立由BudgetEntryjava类表示的每个单独行的完整数据集。这些对象具有获取器，用于检索与此工作流相关的值：费用标签，费用来源部门以及每个期间的值。

然后，我们将此数据集传递给我们的合并函数。再一次，由于我们使用纯Java代码工作，因此我们可以使用高性能算法并获得线性处理时间。如果处理1个条目需要X毫秒，那么处理N个条目需要N * X毫秒。

合并本身就像读取完整数据集中的每个条目并将其值添加到相关输出中一样简单。合并操作的行为完全取决于我们。我们要汇总重复的条目还是要将它们显示为单独的项目？这只是我们现阶段可以做出的选择之一。

我们的每个合并功能都将生成一个合并的数据集，我们将使用该数据集将数据写回到工作表中。

与“从工作表中获取条目”步骤一样，“将数据写入工作表”方法可以自定义为任何目标工作表结构。由于电子表格格式非常适合表格数据，因此，我们将简单地获取条目列表，并在表格中将每个合并的数据集一一写入。

与Kekai合并运营

执行现在我们知道我们在做什么，让我们看看我们在做什么。在本节中，我将介绍此工作流程的关键技术步骤注册事件侦听器

注册事件侦听器有多种方法。我们在Keikai + ZK上工作，因此我们可以使用任何事件侦听器结构。

在这种情况下，我选择使用@Listen注释。使用这种语法，我可以侦听ON_SHEET_SELECT“ spreadsheet”类型的组件发送的类型的事件。

由于导航到部门条目表时不需要重新计算摘要表，因此我还添加了if语句，仅在选择摘要表时才执行合并工作流。

@Listen(io.keikai.ui.event.Events.ON_SHEET_SELECT + "=spreadsheet")
public void onCellClick(SheetSelectEvent e) {
    String sheetName = e.getSheet().getSheetName();
    if(SUMMARY_SHEET_NAME.equals(sheetName)) {
        doMergeWorkflow();
    }
}

GitHub上的代码示例

从表中检索数据我的工作流程的第一步是从每个表中检索数据行。就我而言，所有条目表都具有相同的结构，因此我可以重复使用相同的数据提取器方法。

我首先创建一个空列表sheetEntries来保存结果对象。然后，我遍历可用工作表的列表，并getBudgetEntries为每个工作表运行该方法。

由于表已接收名称，因此我可以简单地使用Ranges.rangeByName(book, name)检索包含数据的Range并将其传递给getBudgetEntry方法。

我收集所有结果行sheetEntries，并成为我的完整数据集。

private List<BudgetEntry> getBudgetData(){
    List<BudgetEntry> sheetEntries = new ArrayList<BudgetEntry>();
    for (Map.Entry<String, String> sheetNamesEntry : sheetNames.entrySet()) {
        sheetEntries.addAll(getBudgetEntries(Ranges.rangeByName(spreadsheet.getBook().getSheet(sheetNamesEntry.getKey()), sheetNamesEntry.getValue()),sheetNamesEntry.getKey()));
    }
    return sheetEntries;
}

GitHub上的代码示例

该getBudgetEntry 方法是我们从工作表中提取数据的地方。我们只需要使用for循环遍历范围行即可。

在此循环内，我们遍历各列，并使用逐个检索单元格的值cellRange.getCellValue()。

对于这些行中的每行，我们创建一个新的BudgetEntry对象，并返回作为此方法的结果生成的条目的列表。

private Collection<? extends BudgetEntry> getBudgetEntries(Range dataRange, String deptName) {
    List<BudgetEntry> entries = new ArrayList<BudgetEntry>();
    for (int i = dataRange.getRow(); i < dataRange.getRow() + dataRange.getRowCount(); i++) {
        List<Number> periodList = new ArrayList<Number>();
        for (int j = 1; j <= 4; j++) {
            periodList.add((Number)Ranges.range(dataRange.getSheet(), i, j).getCellValue());
        }
        entries.add(new BudgetEntry((String)Ranges.range(dataRange.getSheet(), i, 0).getCellValue(),deptName, periodList));
    }
    return entries;
}

GitHub上的代码示例

合并数据集 如上所述，这是Java代码中的纯数据操作。这里没有使用Keikai或ZK功能，我们可以根据需要自由实现此合并操作。为了提高效率，我选择只在初始结果列表中运行一次，但是我们可以从大量现有算法中提取经验。

GitHub上的代码示例

写回工作表将主要数据集合并为两个可利用的摘要集后，我们只需要将它们写回到工作表即可。

这几乎与读取过程相同，除了.getCellValue()我们将.setCellValue()用来将数据写回到工作表之外，而不是用于检索数据。

private void fillPeriodTable(Range periodTable, Map<String, List<Number>> mergedMap) {
    for (int i = periodTable.getRow(); i < periodTable.getRow() + periodTable.getRowCount(); i++) {
        String rowLabel = (String) Ranges.range(periodTable.getSheet(), i, 0).getCellValue();
        for (int j = 0; j < 4; j++) {
            Ranges.range(periodTable.getSheet(), i, j+1).setCellValue(mergedMap.get(rowLabel).get(j));
        }
    }
}

GitHub上的代码示例

结果应用 这是更新后的工作流程的简短视频：

现在，每个团队负责人都可以直接处理同一文档，并且Java工作流基于我们定制的合并工作流提供了快速有效的数据摘要。

结论通过平衡使用默认电子表格功能并利用从工作表中提取数据到高度可定制的Java编码层的独特可能性，我们可以创建有效的工作流程，充分利用工作表中单元格之间的关系以及完整的编码语言。

原文链接：http://codingdict.com

具有Java灵活性的Excel UI：在Keikai中合并电子表格数据

热门标签