C#을 사용하여 Excel에서 수식을 프로그래밍 방식으로 구문 분석하는 방법 > 블로그 & Tips

본문 바로가기

.NET Core API

블로그 & Tips

C#을 사용하여 Excel에서 수식을 프로그래밍 방식으로 구문 분석하는 방법

페이지 정보

작성자 GrapeCity 작성일 21-11-30 11:49 조회 967회 댓글 0건

본문

수식은 Excel의 핵심으로, Excel 파일을 만드는 데 핵심적인 부분입니다. Excel의 각 수식은 의미 체계 모델로 표현되어 사용자가 수식을 만들거나 수정하도록 합니다.

수식을 활용하기 위해 이러한 의미 체계 모델에 대해 알아보고 싶지 않으신가요? 수식을 구문 분석하고 구문 트리를 생성하여 의미 체계 모델을 표현할 수 있습니다.

GcExcel은 수식 구문 분석 기능을 지원합니다. 이 기능을 사용하면 수식을 구문 분석하여 수식의 의미 체계 모델을 나타내는 구문 트리를 생성하고 수식의 서식을 지정하며 수식을 수정할 수 있습니다.

또한 구문 트리를 생성하여 수식을 만들 수도 있습니다. 수식에서 생성된 수식 구문 트리를 보여 주는 다음 이미지를 참조하세요.

수식

수식에 구문 분석 기능을 제공하기 위해 GcExcel APIGrapeCity.Documents.Excel.Expressions 네임스페이스가 추가되었습니다. FormulaSyntaxTree 클래스는 Formula Expressions API의 항목입니다.

이 문서의 수식 구문 분석기 주제에서는 기술적인 내용을 자세히 다룹니다. GcExcel 데모는 수식을 구문 분석하여 수행할 수 있는 모든 작업을 보여 줍니다.

이 블로그에서는 기존 수식을 Excel 스프레드시트에서 구문 분석하는 방법과 C#에서 요구 사항에 따라 수정하는 방법에 대해 배워 봅니다.


사용 사례

Excel을 사용하여 판매 보고서를 생성하는 것은 널리 사용되는 시나리오입니다. 이 블로그에서는 GcExcel API에서 제공되는 수식 구문 분석 기능을 사용하여 간단한 판매 보고서를 만들어 보겠습니다.

아래 스크린샷은 Excel 파일에서 캡처한 것입니다. 왼쪽에는 판매 담당자의 이름, 지역, 제품, 판매 수량으로 구성된 원시 판매 데이터가 나와 있습니다.

오른쪽에는 원시 데이터에서 추출한 특정 판매 담당자에 해당하는 판매 분석 결과와 각 제품과 지역을 조합한 상태에서 월간 매출 목표를 향한 달성 상황을 보여 줍니다. 이러한 목표는 Units Sold 필드 값을 다음 세 가지 등급으로 범주화하여 파생되었습니다.

  • 2500개 미만: 목표 달성 못함

  • 3000개 초과: 목표 달성

  • 5000개 초과: 목표 초과 달성

사용 사례


오른쪽의 판매 분석은 Excel 수식을 사용하여 만들었으며, 원하는 결과를 얻기 위해 여러 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"))


판매 보고서를 완성하기 위해 각 판매 담당자에 대한 유사한 판매 분석 결과가 Excel 파일에 추가됩니다. 이 작업은 위 수식을 수정하여 완료합니다. 위 수식에서 판매 담당자의 이름을 판매 분석이 필요한 다른 판매 담당자의 이름으로 바꿔야 합니다.

데이터 서식 지정과 함께 각 담당자에 대한 다음 내용은 판매 분석 결과를 추가한 후 완성된 판매 보고서의 모습입니다.

판매

이와 같이 수식을 수정하기 위해 수식에서 판매 담당자의 이름 변수가 있는 위치를 알아야 합니다. 이 작업을 직접 수행하면 지루한 작업이 될 것이고 오류가 발생하기 쉽습니다.

수식을 구문 분석하고 구분 분석된 구문 트리를 사용하여 이 작업을 간소화해 판매 담당자 이름을 쉽게 바꿀 수 있습니다.

이 블로그에서는 GcExcel 및 C#을 사용하여 수식 구문 분석과 수정을 수행하는 방법을 알아봅니다. 앞에서 설명하는 단계에서는 GcExcel API를 사용하여 위에서 설명한 판매 분석 수식을 구문 분석하고 원하는 결과를 얻기 위해 C#을 사용하여 수정하는 방법을 안내합니다.


C#을 사용하여 Excel 수식 구문 분석 및 수정

새 C# 콘솔 응용 프로그램(.Net Core)을 생성하고 Nuget 패키지 관리자를 사용하여 시작하며 GcExcel 패키지를 설치하고 아래 단계를 먼저 수행해 보겠습니다. 자세한 내용은 GcExcel 빠른 시작을 참조하세요.


1. 샘플 데이터를 사용하여 통합 문서 초기화

아래에 표시된 대로 Workbook 클래스의 인스턴스를 인스턴스화하고 Excel 파일에서 샘플 데이터를 가져옵니다. 아래 코드는 Workbook 클래스의 AllowDynamicArray 속성을 true로 설정하여 GcExcel에서 동적 배열 지원을 활성화합니다. 계산에 사용되는 수식에서 동적 배열 함수인 FILTER 함수를 사용하기 때문에 이 단계가 필요합니다.

//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. 수식 추출

샘플 데이터 및 원하는 수식과 함께 통합 문서가 로드되면 구문을 분석하고 Formula 속성을 사용하여 수정하기 위해 워크시트에서 필요한 수식을 추출합니다.

GcExcel API에서 제공하는 수식 구문 분석기에서는 성공적인 수식 구문 분석을 위해 '='(같음) 연산자 없이 수식이 전달되어야 합니다. 따라서 아래에서 어떻게 "=" 연산자 없이 수식이 추출되었는지 살펴보세요.

//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. 수식 구문 분석

FormulaSynatxTree 클래스의 Parse 메서드를 호출하여 수식을 구문 분석하고 수식에 포함된 여러 유형의 모든 값, 연산자, 함수를 이해하는 데 도움이 되는 구문 트리를 생성합니다.

수식 구문 트리의 이러한 각 토큰은 GcExcel API의 다른 클래스로 표현됩니다(예: 함수의 경우 FunctionNode, 연산자의 경우 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로 표현되고 수식에서 여러 번 나온 것을 볼 수 있습니다. 아래 코드에 표시된 것처럼 이렇게 나타난 이름들은 간단한 찾기 및 바꾸기 작업으로 바꿀 수 있습니다.

  1. 수식에서 판매 담당자 이름을 바꾸기 위해 담당자 이름 목록으로 시작합니다. UNIQUE 함수를 사용하여 원시 데이터에서 고유한 이름 목록을 필터링합니다. 그런 다음 UNIQUE 함수 결과를 사용하여 모든 판매 담당자에 대한 판매 분석 수식을 구문 분석하고 수정합니다.

  2. TextNode 클래스를 사용하여 판매 담당자 이름을 수정합니다. 아래 코드는 TextNode 클래스의 인스턴스를 초기화하여 수식에서 검색되는 판매 담당자의 이름을 매개 변수로 전달합니다. 이 인스턴스를 찾기 노드라고 부를 수 있습니다.

  3. 다음으로, TextNode 클래스의 인스턴스를 초기화하여 수식에서 바꿀 판매 담당자의 이름을 매개 변수로 전달합니다. 이 인스턴스를 바꾸기 노드라고 부를 수 있습니다.

  4. 구문 트리의 모든 자식 노드를 이동하고 발생한 각 찾기 노드를 바꾸기 노드로 변경하기 위해 아래 코드에는 재귀 함수 replaceNode가 정의되어 있습니다. 이 작업은 각 판매 담당자에 대해 반복됩니다.

  5. 수식이 수정되면 통합 문서의 셀에 새 수식이 할당되어 원하는 판매 보고서를 생성합니다.

아래 코드에는 판매 보고서 콘텐츠의 서식을 지정하기 위한 서식 지정 코드가 포함되어 있습니다.

//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 파일 저장

수정한 모든 수식을 통합 문서에 추가한 후에는 아래 코드에서 보여 주는 것처럼 Workbook 클래스의 Save 메서드를 호출하여 Excel 파일을 저장합니다.

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


저장된 Excel 파일을 열면 출력은 다음과 같습니다.

excel

샘플을 다운로드하여 앞에서 언급한 시나리오를 구현해 보면 여기서 설명한 개념을 좀 더 확실하게 이해할 수 있습니다.

자세한 내용은 문서데모를 참조하세요.




지금 바로 GcExcel .NET Core를 다운로드하여 직접 테스트해보세요!

gcexcel-core.png

 
  • 페이스북으로 공유
  • 트위터로  공유
  • 구글플러스로 공유
  • 카카오톡으로 보내기

댓글목록

등록된 댓글이 없습니다.

그레이프시티 홈페이지를 통해 제품에 대해서 더 자세히 알아 보세요!
홈페이지 바로가기

태그

그레이프시티 홈페이지를 통해 제품에 대해서 더 자세히 알아 보세요!
홈페이지 바로가기
이메일 : sales-kor@grapecity.com | 전화 : 1670-0583 | 경기도 안양시 동안구 시민대로 230, B-703(관양동, 아크로타워) 그레이프시티(주) 대표자 : 허경명 | 사업자등록번호 : 123-84-00981 | 통신판매업신고번호 : 2013-경기안양-00331 Copyright ⓒ 2022 GrapeCity inc.