如何在Java中解析Excel公式

前言

在日常工作中，我们经常需要在Excel中使用公式对表中数据进行计算（求和、求差、求均值等）和分析，从而实现对数据的分类。通常情况下，当数据量较少或场景变化单一的情况下，使用公式可以满足用户的要求，但当数据量较大或者场景变化复杂的情况下，使用公式也无法满足用户的需求的情况。这个时候就可以用编码的方式来解决，以下面的背景需求为例，我们将为大家介绍如何使用葡萄城公司的Java API 组件GrapeCity Documents for Excel （以下简称GcExcel）解析Excel中的现有公式并根据需求对其进行修改。

背景需求

下图是一张销售数据表，左侧显示原始销售数据，包括销售代表的姓名、地区、产品和销售数量，右侧显示了从原始数据中提取的特定的销售代表对应的销售分析结果，以及每个产品区域组合的月度销售目标进度。目标进度的标准如下：

• 低于 2500：低于目标
• 超过 3000：达到目标
• 超过 5000：高于目标

一般情况下，我们使用Excel中的 IF、ISNUMBER 和 FILTER 函数就可以实现将左侧的销售原始数据转化为右侧的销售分析结果，如下所示：

=IF(ISNUMBER(FILTER(A2:D19,A2:A19="Fritz")),IFS(FILTER(A2:D19,A2:A19="Fritz")>5000,"Above Target",FILTER(A2:D19,A2:A19="Fritz")>3000,"On Target",FILTER(A2:D19,A2:A19="Fritz")<2500,"Below Target"),FILTER(A2:D19,A2:A19="Fritz"))

但是这样的话就会出现一个问题，对于不同的人名，需要将上面公式中销售代表的姓名进行替换，也就是需要不断地手动改变姓名执行操作，这一举动不仅枯燥，而且很容易出错。因此这个时候就可以使用GcExcel通过解析公式并使用解析的语法树轻松替换销售代表姓名，可以简化此操作。

使用 Java解析和修改 Excel 公式

首先，创建一个新的 Java项目，并使用 Maven 包管理器安装 GcExcel 包，然后按照前面的步骤操作。

1、使用示例数据初始化工作簿

实例化 Workbook 类的实例并从 Excel 文件导入示例数据，如下所示。

//Create a new workbook
Workbook workbook = new Workbook();
//Load sample data from excel file
workbook.open("SampleData.xlsx");

2、提取公式

在工作簿加载示例数据和预期公式后，我们从工作表中提取所需的公式，以便使用 Formula 属性进行解析和修改。

GcExcel API 提供的公式解析器希望传递的公式不带“=”（等于）运算符，以便成功进行公式解析。因此，请注意如何在不使用“=”运算符的情况下提取公式。

//Fetch worksheet
IWorksheet worksheet = workbook.getWorksheets().get(0);
//Fetch the original formula which needs to be parsed.
String originalFormula = worksheet.getRange("H3").getFormula().substring(1);

3、解析公式

调用 FormulaSynatxTree 类的 Parse 方法来解析公式并生成语法树，帮助您理解公式包含的所有不同类型的值、运算符和函数。

公式语法树的每个标记都由 GcExcel API 中的其他类表示，例如函数的 FunctionNode、运算符的 OperatorNode 等。

下面的代码解析了上一步中提取的销售分析公式。然后，它将生成的 FormulaSyntaxTree 中的值附加到工作簿，该工作簿随后保存为 Excel 文件，以帮助您了解公式的语法树。

public static class Tuple {
    String TypeName;
    int IndentLevel;
    String Content;

    Tuple(String TypeName, int IndentLevel, String Content) {
        this.TypeName = TypeName;
        this.IndentLevel = IndentLevel;
        this.Content = Content;
    }
}

public static void flatten(SyntaxNode node, int level, List<Tuple> displayItems) {
    displayItems.add(new Tuple(node.getClass().getSimpleName(), level, node.toString()));
    for (SyntaxNode child : node.getChildren()) {
        flatten(child, level + 1, displayItems);
    }
}

//Method to parse a formula and print the syntax tree
public static void ParseAndPrint(IWorksheet worksheet, String formula)
{
    // Get syntax tree
    FormulaSyntaxTree syntaxTree = FormulaSyntaxTree.Parse(formula);

    // Flatten nodes
    List<Tuple> displayItems = new ArrayList<>();
    flatten(syntaxTree.getRoot(), 0, displayItems);

    // Output
    worksheet.setShowRowOutline(false);
    worksheet.getOutlineColumn().setColumnIndex(1);

    // Header
    worksheet.getRange("A1").setValue("Formula");
    worksheet.getRange("A3").setValue("Syntax node");
    worksheet.getRange("B3").setValue("Part");

    // Values
    worksheet.getRange("B1").setValue("'=" + formula);
    for (int i = 0; i < displayItems.size(); i++)
    {
        Tuple item = displayItems.get(i);
        String text = "'" + item.TypeName;

        worksheet.getRange(i + 4, 0).setValue(text);
        worksheet.getRange(i + 4, 0).setIndentLevel(item.IndentLevel);
        worksheet.getRange(i + 4, 1).setValue("'" + item.Content);
    }

    //Apply styling
    worksheet.getRange("A1:B3").getInterior().setColor(Color.FromArgb(68, 114, 196));
    worksheet.getRange("A1:B3").getFont().setColor(Color.GetWhite());
    worksheet.getRange("A1:B3").getBorders().setColor(Color.FromArgb(91, 155, 213));
    worksheet.getRange("A1:B3").getBorders().setLineStyle(BorderLineStyle.Thin);
    worksheet.getRange("A1,A3,B3").getFont().setSize(14);
    worksheet.getRange("A1,A3,B3").getFont().setBold(true);
    worksheet.getRange("A:C").getEntireColumn().autoFit();
}

下图是生成的 FormulaSyntaxTree 的效果图。请注意，这只是完整语法树的一部分：

4、修改公式

从上一步生成的语法树中，可以看到销售代表姓名以 TextNode 形式表示，并且在公式中多次出现。我们可以通过简单的查找和替换操作来替换所有这些出现的情况，如下面的代码所示：

1. 替换公式中的销售代表姓名，从姓名列表开始。我们使用 UNIQUE 函数从原始数据中过滤掉唯一名称列表。然后使用这个 UNIQUE 函数的结果来解析和修改所有销售代表的销售分析公式。
2. 使用 TextNode 类修改销售代表姓名。下面的代码初始化 TextNode 类的实例，并将要在公式中搜索的销售代表姓名作为参数传递。该实例可以称为查找节点。
3. 接下来，我们初始化 TextNode 类的另一个实例，并将公式中要替换的销售代表姓名作为参数传递。该实例可以称为替换节点。
4. 下面的代码中定义了一个递归函数 replaceNode，用于遍历语法树的所有子节点，并将每个出现的 Find 节点替换为 Replace 节点。每个销售代表都会重复此操作。
5. 修改公式后，新公式将分配给工作表中的单元格以生成预期的销售报告。

下面的代码包含一些格式化代码来格式化销售报告内容。

//Find and replace
public static void replaceNode(SyntaxNode lookIn, SyntaxNode find, SyntaxNode replacement)
{
    List<SyntaxNode> children = lookIn.getChildren();

    for (int i = 0; i < children.size(); i++)
    {
        SyntaxNode child = children.get(i);
        if (child.equals(find))
        {
            children.set(i, replacement);
        }
        else
        {
            replaceNode(child, find, replacement);
        }
    }
}
//Method to parse and modify the formula
public static void ModifyFormula(IWorksheet worksheet, String originalFormula)
{
    //Apply UNIQUE formula to get unique sales representatives list
    worksheet.getRange("F1").setValue("Unique Rep");
    worksheet.getRange("F2").setFormula2("=UNIQUE(A2:A19)");
    IRange uniqueRep = worksheet.getRange("F2#");
    // Apply Styling
    worksheet.getRange("F:F").getEntireColumn().autoFit();
    worksheet.getRange("F1").getInterior().setColor(Color.FromArgb(68, 114, 196));
    worksheet.getRange("F1").getFont().setColor(Color.GetWhite());
    worksheet.getRange("F2#").getBorders().setColor(Color.FromArgb(91, 155, 213));
    worksheet.getRange("F2#").getBorders().setLineStyle(BorderLineStyle.Thin);

    //Get syntax tree
    FormulaSyntaxTree syntaxTree = FormulaSyntaxTree.Parse(originalFormula);

    //Find
    TextNode findText = new TextNode("Fritz");

    //Replacement
    TextNode replaceText = new TextNode("");

    //Loop through names list to modify the formula for each sales representative
    for (int r = 0, resultRow = 3; r < uniqueRep.getCells().getCount(); r++, resultRow = resultRow + 4)
    {
        //Get name to be replaced in the formula
        String cval = uniqueRep.getCells().get(r).getValue().toString();

        if (!Objects.equals(findText.getValue(), cval))
        {
            //Assign name to be replaced to Replace TextNode
            replaceText.setValue(cval);

            //Invoke the recursive method to perform find and replace operation
            replaceNode(syntaxTree.getRoot(), findText, replaceText);

            //Assign the modified formula to a cell in the worksheet
            String resultRange = "H" + resultRow;
            worksheet.getRange(resultRange).setFormula2("=" + syntaxTree.toString());
            worksheet.getRange(resultRange + "#").getBorders().setColor(Color.FromArgb(91, 155, 213));
            worksheet.getRange(resultRange + "#").getBorders().setLineStyle(BorderLineStyle.Thin);

            //Update the value of Find node to perform find and replace operation for next sales representative name
            findText = replaceText;
        }
    }
}

这是修改后的公式之一：

=IF(ISNUMBER(FILTER(A2:D19,A2:A19="Xi")),IFS(FILTER(A2:D19,A2:A19="Xi")>5000,"Above Target",FILTER(A2:D19,A2:A19="Xi")>3000,"On Target",FILTER(A2:D19,A2:A19="Xi")<2500,"Below Target"),FILTER(A2:D19,A2:A19="Xi"))

5、保存 Excel 文件

将所有修改的公式添加到工作表后，将调用 Workbook 类的 Save 方法来保存 Excel 文件，如下面的代码所示：

//Save modified Excel file
workbook.save("ModifiedFormula.xlsx", SaveFileFormat.Xlsx);

打开保存的 Excel 文件可以看到下图：

总结

以上就是使用Java实现解析Excel的全过程，如果您想了解更多信息，欢迎点击这里查看更多资料。

GcExcel | 下载试用

GrapeCity Documents for Excel （简称：GcExcel）是一款基于 Java 平台的服务端高性能表格组件，可与纯前端表格控件 SpreadJS 前后端兼容，无需依赖 Office、POI 或第三方应用软件，在前端展示电子表格数据，在服务端批量创建、加载、编辑、打印、导入/导出 Excel 文档，为您开发的应用程序提供在线文档的前后端数据同步、在线填报与服务端批量导出与打印，以及类 Excel 报表模板设计与服务端高性能处理等一整套类 Excel 全栈解决方案。