使用Java實現(xiàn)解析Excel公式
前言
在日常工作中，我們經(jīng)常需要在Excel中使用公式對表中數(shù)據(jù)進行計算（求和、求差和求均值等）和分析，從而實現(xiàn)對數(shù)據(jù)的分類，通常情況下，當(dāng)數(shù)據(jù)量較少或場景變化單一的情況下，使用公式可以滿足用戶的要求，但當(dāng)數(shù)據(jù)量較大或者場景變化復(fù)雜的情況下，使用公式也無法滿足用戶的需求的情況。這個時候就可以用編碼的方式來解決，以下面的背景需求為例，小編將為大家介紹如何使用葡萄城公司的Java API 組件GrapeCity Documents for Excel （以下簡稱GcExcel）解析Excel中的現(xiàn)有公式并根據(jù)需求對其進行修改。
背景需求
下圖是一張銷售數(shù)據(jù)表，左側(cè)顯示原始銷售數(shù)據(jù)，包括銷售代表的姓名、地區(qū)、產(chǎn)品和銷售數(shù)量，右側(cè)顯示了從原始數(shù)據(jù)中提取的特定的銷售代表對應(yīng)的銷售分析結(jié)果，以及每個產(chǎn)品區(qū)域組合的月度銷售目標(biāo)進度。目標(biāo)進度的標(biāo)準(zhǔn)如下：
低于 2500：低于目標(biāo)
超過 3000：達到目標(biāo)
超過 5000：高于目標(biāo)
一般情況下，我們使用Excel中的 IF、ISNUMBER 和 FILTER 函數(shù)就可以實現(xiàn)將左側(cè)的銷售原始數(shù)據(jù)轉(zhuǎn)化為右側(cè)的銷售分析結(jié)果，如下所示：
=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”))
但是這樣的話就會出現(xiàn)一個問題，對于不同的人名，小編需要將上面公式中銷售代表的姓名進行替換，也就是需要不斷地手動改變姓名執(zhí)行操作，這一舉動不僅枯燥，而且很容易出錯。因此這個時候就可以使用GcExcel通過解析公式并使用解析的語法樹輕松替換銷售代表姓名，可以簡化此操作。
使用 Java解析和修改 Excel 公式
首先，創(chuàng)建一個新的 Java項目，并使用 Maven 包管理器安裝 GcExcel 包，然后按照前面的步驟操作。
1、使用示例數(shù)據(jù)初始化工作簿
實例化 Workbook 類的實例并從 Excel 文件導(dǎo)入示例數(shù)據(jù)，如下所示。
//Create a new workbook
Workbook workbook = new Workbook();
//Load sample data from excel file
workbook.open("SampleData.xlsx");
2、提取公式
在工作簿加載示例數(shù)據(jù)和預(yù)期公式后，我們從工作表中提取所需的公式，以便使用 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、解析公式
調(diào)用 FormulaSynatxTree 類的 Parse 方法來解析公式并生成語法樹，幫助您理解公式包含的所有不同類型的值、運算符和函數(shù)。
公式語法樹的每個標(biāo)記都由 GcExcel API 中的其他類表示，例如函數(shù)的 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 形式表示，并且在公式中多次出現(xiàn)。我們可以通過簡單的查找和替換操作來替換所有這些出現(xiàn)的情況，如下面的代碼所示：
1.替換公式中的銷售代表姓名，從姓名列表開始。我們使用 UNIQUE 函數(shù)從原始數(shù)據(jù)中過濾掉唯一名稱列表。然后使用這個 UNIQUE 函數(shù)的結(jié)果來解析和修改所有銷售代表的銷售分析公式。
2.使用 TextNode 類修改銷售代表姓名。下面的代碼初始化 TextNode 類的實例，并將要在公式中搜索的銷售代表姓名作為參數(shù)傳遞。該實例可以稱為查找節(jié)點。
3.接下來，我們初始化 TextNode 類的另一個實例，并將公式中要替換的銷售代表姓名作為參數(shù)傳遞。該實例可以稱為替換節(jié)點。
4.下面的代碼中定義了一個遞歸函數(shù) replaceNode，用于遍歷語法樹的所有子節(jié)點，并將每個出現(xiàn)的 Find 節(jié)點替換為 Replace 節(jié)點。每個銷售代表都會重復(fù)此操作。
5.修改公式后，新公式將分配給工作表中的單元格以生成預(yù)期的銷售報告。
下面的代碼包含一些格式化代碼來格式化銷售報告內(nèi)容。
//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 文件
將所有修改的公式添加到工作表后，將調(diào)用 Workbook 類的 Save 方法來保存 Excel 文件，如下面的代碼所示：
//Save modified Excel file
workbook.save("ModifiedFormula.xlsx", SaveFileFormat.Xlsx);
打開保存的 Excel 文件可以看到下圖：
到此這篇關(guān)于使用Java實現(xiàn)解析Excel公式的文章就介紹到這了,更多相關(guān)Java解析Excel公式內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
SpringBoot基于沙箱環(huán)境實現(xiàn)支付寶支付教程
本文介紹了如何使用支付寶沙箱環(huán)境進行開發(fā)測試,包括沙箱環(huán)境的介紹、準(zhǔn)備步驟、在Spring Boot項目中結(jié)合支付寶沙箱進行支付接口的實現(xiàn)與測試2025-03-03
springboot用thymeleaf模板的paginate分頁完整代碼
本文根據(jù)一個簡單的user表為例，展示 springboot集成mybatis，再到前端分頁完整代碼,需要的朋友可以參考下2017-07-07
這篇文章主要為大家詳細(xì)介紹了java實現(xiàn)FTP文件上傳與文件下載的相關(guān)資料，通過以下兩種方式實現(xiàn)，通過JDK自帶的API實現(xiàn)，二是通過Apache提供的API是實現(xiàn)，感興趣的小伙伴們可以參考一下2016-04-04
這篇文章主要介紹了Java?Runtime的使用詳解，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2021-12-12
文章總結(jié)了Java 8中流(Stream) API的多種常用方法,包括創(chuàng)建流、過濾、遍歷、分組、排序、去重、查找、匹配、轉(zhuǎn)換、歸約、打印日志、最大最小值、統(tǒng)計、連接、函數(shù)式接口等,展示了流API在處理集合數(shù)據(jù)時的強大和靈活性,感興趣的朋友跟隨小編一起看看吧2025-01-01
這篇文章主要為大家詳細(xì)匯總了Java程序員需要掌握的英語詞組 ，具有一定的參考價值，感興趣的小伙伴們可以參考一下2018-04-04
去年，JetBrains 網(wǎng)站進行了本地化，提供了 8 種不同的語言版本，而現(xiàn)在，團隊正在對基于 IntelliJ 的 IDE 進行本地化2020-12-12
SpringBoot生成Excel文件的實現(xiàn)示例
本文介紹了Spring Boot項目中生成Excel文件,使用了Apache POI庫,包括poi和poi-ooxml依賴,通過遍歷用戶信息列表,將數(shù)據(jù)寫入Excel文件,感興趣的可以了解一下2025-02-02
springboot結(jié)合redis實現(xiàn)搜索欄熱搜功能及文字過濾
本文主要介紹了springboot結(jié)合redis實現(xiàn)搜索欄熱搜功能及文字過濾，文中通過示例代碼介紹的非常詳細(xì)，具有一定的參考價值，感興趣的小伙伴們可以參考一下2022-02-02
本篇文章是對jni調(diào)用時,jvm的報錯問題進行了詳細(xì)的分析介紹，需要的朋友參考下2013-05-05