使用C#實(shí)現(xiàn)解析Excel公式
前言
在日常工作中,我們經(jīng)常需要在Excel中使用公式對表中數(shù)據(jù)進(jìn)行計(jì)算(求和、求差和求均值等)和分析,從而實(shí)現(xiàn)對數(shù)據(jù)的分類,通常情況下,當(dāng)數(shù)據(jù)量較少或場景變化單一的情況下,使用公式可以滿足用戶的要求,但當(dāng)數(shù)據(jù)量較大或者場景變化復(fù)雜的情況下,使用公式也無法滿足用戶的需求的情況。這個(gè)時(shí)候就可以用編碼的方式來解決,以下面的背景需求為例,小編將為大家介紹如何使用葡萄城公司基于 .NET 和 .NET Core 平臺(tái)的服務(wù)端高性能表格組件組件GrapeCity Documents for Excel (以下簡稱GcExcel)解析Excel中的現(xiàn)有公式并根據(jù)需求對其進(jìn)行修改。
背景需求
下圖是一張銷售數(shù)據(jù)表,左側(cè)顯示原始銷售數(shù)據(jù),包括銷售代表的姓名、地區(qū)、產(chǎn)品和銷售數(shù)量,右側(cè)顯示了從原始數(shù)據(jù)中提取的特定的銷售代表對應(yīng)的銷售分析結(jié)果,以及每個(gè)產(chǎn)品區(qū)域組合的月度銷售目標(biāo)進(jìn)度。目標(biāo)進(jìn)度的標(biāo)準(zhǔn)如下:
低于 2500:低于目標(biāo)
超過 3000:達(dá)到目標(biāo)
超過 5000:高于目標(biāo)
一般情況下,我們使用Excel中的 IF、ISNUMBER 和 FILTER 函數(shù)就可以實(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"))
但是這樣的話就會(huì)出現(xiàn)一個(gè)問題,對于不同的人名,小編需要將上面公式中銷售代表的姓名進(jìn)行替換,也就是需要不斷地手動(dòng)改變姓名執(zhí)行操作,這一舉動(dòng)不僅枯燥,而且很容易出錯(cuò)。因此這個(gè)時(shí)候就可以使用GcExcel通過解析公式并使用解析的語法樹輕松替換銷售代表姓名,可以簡化此任務(wù)。
使用 C# 解析和修改 Excel 公式
首先,創(chuàng)建一個(gè)新的 C#(.NET Core) 項(xiàng)目,并使用NuGet 包管理器安裝 GcExcel 包,然后按照前面的步驟操作。
1、使用示例數(shù)據(jù)初始化工作簿
實(shí)例化 Workbook 類的實(shí)例并從 Excel 文件導(dǎo)入示例數(shù)據(jù),如下所示。
//Create a new workbook var workbook = new GrapeCity.Documents.Excel.Workbook(); //Load sample data from excel file workbook.Open("SampleData.xlsx"); //Enable dynamic array formula workbook.AllowDynamicArray = true;
2、提取公式
在工作簿加載示例數(shù)據(jù)和預(yù)期公式后,我們從工作表中提取所需的公式,以便使用 Formula 屬性進(jìn)行解析和修改。
GcExcel API 提供的公式解析器希望傳遞的公式不帶“=”(等于)運(yùn)算符,以便成功進(jìn)行公式解析。因此,請注意如何在不使用“=”運(yùn)算符的情況下提取公式。
//Fetch worksheet var worksheet = workbook.Worksheets[0]; //Fetch the original formula which needs to be parsed. var originalFormula = worksheet.Range["H3"].Formula.Substring(1);
3、解析公式
調(diào)用 FormulaSynatxTree 類的 Parse 方法來解析公式并生成語法樹,幫助您理解公式包含的所有不同類型的值、運(yùn)算符和函數(shù)。
公式語法樹的每個(gè)標(biāo)記都由 GcExcel API 中的其他類表示,例如函數(shù)的 FunctionNode、運(yùn)算符的 OperatorNode 等。
下面的代碼解析了上一步中提取的銷售分析公式。然后,它將生成的 FormulaSyntaxTree 中的值附加到工作簿,該工作簿隨后保存為 Excel 文件,以幫助您了解公式的語法樹。
//Method to parse a formula and print the syntax tree public static void ParseAndPrint(IWorksheet worksheet, string formula) { // Get syntax tree var syntaxTree = FormulaSyntaxTree.Parse(formula); // Flatten nodes var displayItems = new List<(string TypeName, int IndentLevel, string Content)>(); void flatten(SyntaxNode node, int level) { displayItems.Add((node.GetType().Name, level, node.ToString())); foreach (var child in node.Children) { flatten(child, level + 1); } } flatten(syntaxTree.Root, 0); // Output worksheet.ShowRowOutline = false; worksheet.OutlineColumn.ColumnIndex = 1; // Header worksheet.Range["A1"].Value = "Formula"; worksheet.Range["A3"].Value = "Syntax node"; worksheet.Range["B3"].Value = "Part"; // Values worksheet.Range["B1"].Value = "'=" + formula; for (var i = 0; i < displayItems.Count; i++) { var item = displayItems[i]; var text = "'" + item.TypeName; worksheet.Range[i + 4, 0].Value = text; worksheet.Range[i + 4, 0].IndentLevel = item.IndentLevel; worksheet.Range[i + 4, 1].Value = "'" + item.Content; } //Apply styling worksheet.Range["A1:B3"].Interior.Color = System.Drawing.Color.FromArgb(68, 114, 196); worksheet.Range["A1:B3"].Font.Color = System.Drawing.Color.White; worksheet.Range["A1:B3"].Borders.Color = System.Drawing.Color.FromArgb(91, 155, 213); worksheet.Range["A1:B3"].Borders.LineStyle = BorderLineStyle.Thin; worksheet.Range["A1,A3,B3"].Font.Size = 14; worksheet.Range["A1,A3,B3"].Font.Bold = true; worksheet.Range["A:C"].EntireColumn.AutoFit(); }
下圖是生成的 FormulaSyntaxTree 的效果圖圖。請注意,這只是完整語法樹的一部分:
4、修改公式
從上一步生成的語法樹中,您可以看到銷售代表姓名以 TextNode 形式表示,并且在公式中多次出現(xiàn)。我們可以通過簡單的查找和替換操作來替換所有這些出現(xiàn)的情況,如下面的代碼所示:
- 了替換公式中的銷售代表姓名,我們從他們的姓名列表開始。我們使用 UNIQUE 函數(shù)從原始數(shù)據(jù)中過濾掉唯一名稱列表。然后使用這個(gè) UNIQUE 函數(shù)的結(jié)果來解析和修改所有銷售代表的銷售分析公式。
- 我們使用 TextNode 類修改銷售代表姓名。下面的代碼初始化 TextNode 類的實(shí)例,并將要在公式中搜索的銷售代表姓名作為參數(shù)傳遞。該實(shí)例可以稱為查找節(jié)點(diǎn)。
- 接下來,我們初始化 TextNode 類的另一個(gè)實(shí)例,并將公式中要替換的銷售代表姓名作為參數(shù)傳遞。該實(shí)例可以稱為替換節(jié)點(diǎn)。
- 下面的代碼中定義了一個(gè)遞歸函數(shù) replaceNode,用于遍歷語法樹的所有子節(jié)點(diǎn),并將每個(gè)出現(xiàn)的 Find 節(jié)點(diǎn)替換為 Replace 節(jié)點(diǎn)。每個(gè)銷售代表都會(huì)重復(fù)此操作。
- 修改公式后,新公式將分配給工作表中的單元格以生成預(yù)期的銷售報(bào)告。
下面的代碼包含一些格式化代碼來格式化銷售報(bào)告內(nèi)容。
//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.Range["F1"].Value = "Unique Rep"; worksheet.Range["F2"].Formula = "=UNIQUE(A2:A19)"; var uniqueRep = worksheet.Range["F2#"]; // Apply Styling worksheet.Range["F:F"].EntireColumn.AutoFit(); worksheet.Range["F1"].Interior.Color = System.Drawing.Color.FromArgb(68, 114, 196); worksheet.Range["F1"].Font.Color = System.Drawing.Color.White; worksheet.Range["F2#"].Borders.Color = System.Drawing.Color.FromArgb(91, 155, 213); worksheet.Range["F2#"].Borders.LineStyle = BorderLineStyle.Thin; //Get syntax tree var syntaxTree = FormulaSyntaxTree.Parse(originalFormula); //Find var findText = new TextNode("Fritz"); //Replacement var replaceText = new TextNode(""); //Loop through names list to modify the formula for each sales representative for (int r = 0, resultRow = 3; r < uniqueRep.Cells.Count; r++, resultRow = resultRow + 4) { //Get name to be replaced in the formula var cval = uniqueRep.Cells[r].Value.ToString(); if (findText.Value != cval) { //Assign name to be replaced to Replace TextNode replaceText.Value = cval; //Invoke the recursive method to perform find and replace operation replaceNode(syntaxTree.Root, findText, replaceText); //Assign the modified formula to a cell in the worksheet var resultRange = "H" + resultRow.ToString(); worksheet.Range[resultRange].Formula = "=" + syntaxTree.ToString(); worksheet.Range[resultRange + "#"].Borders.Color = System.Drawing.Color.FromArgb(91, 155, 213); worksheet.Range[resultRange + "#"].Borders.LineStyle = BorderLineStyle.Thin; //Update the value of Find node to perform find and replace operation for next sales representative name findText = replaceText; } } //Find and replace void replaceNode(SyntaxNode lookIn, SyntaxNode find, SyntaxNode replacement) { var children = lookIn.Children; for (var i = 0; i < children.Count; i++) { var child = children[i]; if (child.Equals(find)) { children[i] = replacement; } else { replaceNode(child, find, replacement); } } } }
這是修改后的公式之一:
=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)于使用C#實(shí)現(xiàn)解析Excel公式的文章就介紹到這了,更多相關(guān)C#解析Excel公式內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
C#中靜態(tài)構(gòu)造函數(shù)的幾點(diǎn)說明介紹
本篇文章主要是對C#中靜態(tài)構(gòu)造函數(shù)的幾點(diǎn)說明進(jìn)行了介紹,需要的朋友可以過來參考下,希望對大家有所幫助2014-01-01C#限速下載網(wǎng)絡(luò)文件的方法實(shí)例
本篇文章主要介紹了C#限速下載網(wǎng)絡(luò)文件的方法實(shí)例,可以限制下載文件的速度,非常具有實(shí)用價(jià)值,需要的朋友可以參考下。2016-12-12詳解如何獲取C#類中發(fā)生數(shù)據(jù)變化的屬性信息
這篇文章主要介紹了詳解如何獲取C#類中發(fā)生數(shù)據(jù)變化的屬性信息,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2020-05-05