如何在Java中解析Excel公式

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

发布于 2024/02/27 12:10

GcExcel

前言

在日常工作中，我们经常需要在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 全栈解决方案。

热门博客分类
ComponentOne Enterprise SpreadJS ActiveReports 活字格 Wyn 商业智能 WijmoJS GcExcel ActiveReportsJS
相关产品

GcExcel

高效创建、编辑、解析、导出多种流行文档

免费试用 产品详情
推荐相关案例
云南柠能 - SpreadJS与GcExcel协同，实现BI-N平台一站式解决方案

云南柠能企业管理咨询有限公司是一家致力于打造业财融合、综合绩效管理平台的数字化产品研发及服务提供商。其中BI-N是一款一站式PaaS平台，主要包含数据平台、数据应用、数据分析、数据云产品四大部分，使用SpreadJS表格控件+Hadoop生态构建，提供数据分析、绩效管理等功能。SpreadJS在平台中能够还原Excel功能，支持多维数据分析和大数据场景下的表格渲染处理，配合GcExcel实现高性能公式计算，提高平台的访问效率，同时丰富的API接口也能够帮助用户灵活扩展平台的业务功能。

苏交科 - SpreadJS解锁TIC行业报表开发难题

通过使用SpreadJS+GcExcel助力TIC系统开发，加快了报表模板开发速度，提升了报表的计算效率，提高了系统的稳定性和用户体验。

上海佳软 - 优化多人协作，SpreadJS+GcExcel构建无缝协同系统

上海佳软推出了雷鸟365在线表格文档系统，这是一款有着Excel强大功能、允许多人实时协作编辑的产品。雷鸟365的出现让Excel联网成为可能，只需要导入本地文件，即可实现多人共享编辑文档，还可以设置访问修改权限。

首厚智能 - GcExcel助力LIMS系统，共塑卓越实验室之路

首厚智能以“互联网+智慧实验室解决方案”为核心，业务范围覆盖实验室智能化、数字化发展等多态融合的全新实验室服务，为科研企业提供应用系统开发、定制化系统开发、异构系统集成、信息系统运行维护、系统培训、软件及计算机硬件产品销售等服务。

某基金公司 - GcExcel助力基金数据监控，提升操作效率

某大型基金公司为提升投资管理水平，引入SpreadJS实现在线Excel文件管理、监管报送、数据解析和数据订阅等功能，提升工作效率和协作效果。SpreadJS带来类Excel的交互体验、数据处理能力和数据共享协作，提供了高效的投资管理解决方案。

查看更多精彩案例
关注微信
葡萄城社区二维码

关注“葡萄城社区”

活字格低代码二维码

关注“活字格低代码”

想了解更多信息，请联系我们, 随时掌握技术资源和产品动态

葡萄城热门产品

活字格
打通现有软件，快速定制面向未来的个性化应用
免费试用 产品详情
SpreadJS
可嵌入您系统的在线Excel的纯前端表格控件
免费试用 产品详情
Wyn商业智能
可与企业自有系统深度集成，增强数据分析的 BI 工具
免费试用 产品详情