Excel 파일로 작업하는 동안 가장 일반적으로 발생하는 상황 중 하나는 여러 Excel 파일에서 단일 파일로 데이터를 가져오는 것입니다.

예를 들어 판매 부서에서는 추가 연구나 여러 Excel 파일에서 추세 분석을 위해 여러 Excel 파일에서 단일 Excel 파일로 데이터를 병합해야 할 수 있습니다(다양한 지역 또는 몇 해 동안의 판매 데이터 포함).

이러한 통합 문서에서 복사하여 수동으로 데이터를 수집하는 것은 다음을 비롯한 여러 이유로 인해 좋은 옵션이 아닙니다.

• 소스 통합 문서에 다양한 레코드나 워크시트가 있는 경우 시간이 많이 소요되는 연습이 될 수 있음

• 원본 소스 통합 문서에 변경이 생기는 경우 더 높은 유지보수 발생

• 오류 도입으로 인한 잘못된 분석

Excel은 외부 참조 수식(외부 통합 문서 링크 또는 교차 통합 문서 수식으로도 알려짐)을 만들어 이 문제를 해결합니다. 수식은 소스와 대상 통합 문서 간에 링크를 만들어 시간을 절약하고 오류를 줄이고 데이터 무결성을 유지합니다.

한편, 수동으로 복사-붙여넣기 작업을 수행하면 오류가 발생하고 데이터 무결성 및 정확성 문제가 생길 수 있습니다. 링크를 만들면 여러 Excel 파일에서 동일한 데이터를 유지하지 않아도 됩니다.

이 블로그는 개발자에게 외부 통합 문서 링크를 만들기 위해 프로그래밍 방식으로 C#용 GcExcel API를 사용하는 방법에 대해 알려줍니다.

사용 사례 예제

고객은 고객이 판매를 추적하는 데 사용하는 여러 시스템에서 팀이 데이터를 집계하는 데 도움이 되도록 참여합니다.

안타깝게도 각 판매 지역은 판매를 추적하는 데 다른 시스템을 활용합니다. 하지만 유사하거나 정확한 레이아웃으로도 Excel 파일을 만들도록 관리하였으므로 몇 년 동안 여기에서 데이터를 수동으로 집계했습니다.

새로운 기술적 우세는 이 프로세스의 효율성에 질문을 던졌으며 팀에 솔루션을 제안할 것을 요청했습니다. 이러한 파일의 예제는 아래 표시됩니다.

"AggregatedInfo" 시트는 "판매 주문 시트"에서 데이터에 대해 집계된 정보를 보관합니다.

고객은 시스템이 아래 표시된 대로 요약 보고서에 세부 정보를 채우도록 "AggregatedInfo" 시트에서 정보를 사용하길 바랍니다.

여기를 클릭하여 이 블로그에서 사용한 코드 스니펫의 전체 샘플을 다운로드하십시오.

이는 GcExcel과 C#을 사용하여 쉽게 달성되었다고 결정했으며 몇 개의 간단한 코드 줄로 이를 수행할 수 있습니다. 하지만 외부 통합 문서 링크를 추가하기 위해 구문을 살펴보겠습니다.

외부 통합 문서 수식용 구문

외부 링크 수식은 =(같음)로 시작되며 작은따옴표('') 안에 소스 Excel 파일의 경로가 이어집니다. 경로에서 Excel 통합 문서의 파일 이름은 시트 이름 앞에 대괄호 []가 있습니다.

참조 범위/셀은 뒤 따옴표 다음에 있으며 아래 표시된 대로 느낌표(!)로 구분됩니다.

='Path to Source File [Excel workbook name.xlsx] SheetName' ! CellReference

소스 파일의 경로는 다음이 될 수 있습니다.

• 절대 파일 경로

='D:\SharedFolder\[SourceWorkbook.xlsx]Sheet1'!A1

• 상대 경로 위치

='\SharedFolder\[SourceWorkbook.xlsx]Sheet1'!A1

• 웹 URL

= 'http://wwww.grapecity.com/gcexcel/[SourceWorkbook.xlsx]Sheet1'!A1

따라오는 섹션에서 볼 수 있듯이 셀 참조는 단일 셀("A1"), 셀의 범위("A1:D4") 또는 이름이 지정된 범위나 표가 될 수 있습니다.

외부 통합 문서 수식 프로그래밍 방식으로 추가

GcExcel 및 C#을 사용한 외부 통합 문서 수식 추가에는 다음 단계가 포함됩니다.

1. 대상 통합 문서 초기화

2. 대상 통합 문서에서 교차 통합 문서 수식 추가

3. Excel 링크 업데이트

4. 대상 통합 문서 저장

1단계 - 대상 통합 문서 초기화

외부 통합 문서 링크를 추가하는 첫 번째 단계는 소스 통합 문서의 데이터가 병합되는 대상 통합 문서를 초기화하는 것입니다.

var destination_workbook = new Workbook();
destination_workbook.Open("SalesSummaryReport.xlsx");

2단계 - 대상 통합 문서에서 교차 통합 문서 수식 추가

교차 통합 문서 또는 외부 통합 문서 링크는 아래에 표시된 대로 IRange.Formula 속성을 사용하여 추가됩니다.

destination_workbook.ActiveSheet.Range["A1"].Formula = <external workbook reference>;

샘플에서는 이 블로그에 대해 만들었으며 Excel 파일(Northeast.xlsx 및 Midwest.xlsx)의 출처는 로컬 디스크이며 South.xlsx 및 West.xlsx 파일의 출처는 포함된 리소스로서입니다. 따라서 외부 통합 문서 수식은 다음과 같이 설정됩니다.

destination_workbook.ActiveSheet.Range["C2"].Formula = "='xlsx\\[Northeast.xlsx]AggregatedInfo'!C2";   //for file on disk

destination_workbook.ActiveSheet.Range["D2"].Formula = "='[South.xlsx]AggregatedInfo'!C2";   //for file as embedded resource

3단계- Excel 링크 업데이트

이 단계에서는 2단계에서 구성된 참조 수식은 지정된 참조에서 최신 값을 가져오도록 평가됩니다. GcExcel은 세 개의 메서드를 제공하여 외부 통합 문서 링크를 업데이트합니다.

• IWorkbook.UpdateExcelLink(linkname)

• IWorkbook.UpdateExcelLink(linkname, sourceWorkbook)

• IWorkbook.UpdateExcelLinks()

참고:

위의 메서드에서 "linkName" 인수는 링크 수식에서 구성된 소스 Excel 파일 경로를 참조합니다. 수동으로 이를 설정하거나 Workbook.GetExcelLinkSources 메서드를 사용할 수 있습니다. 사람의 오류를 방지하려면 후자를 사용하는 것을 권장합니다.

IWorkbook.UpdateExcelLink(linkname)

이 메서드는 통합 문서에서 개별 링크를 업데이트합니다. 이 메서드는 디스크의 외부 소스 파일에서 직접 대상 통합 문서 캐시를 업데이트하므로 소스 통합 문서를 디스크에서 액세스할 수 있는 경우 이 메서드를 사용하는 것을 권장합니다.

destination_workbook.ActiveSheet.Range["A1"].Formula = "='D:\\shared folder\\[Source.xlsx]Sheet1'!C3";
foreach (var link in destination_workbook.GetExcelLinkSources())
{
    destination_workbook.UpdateExcelLink(link);
}

위의 사용 사례에서는 아래 코드에 표시된 대로 출처가 로컬인 Excel 파일에 이 메서드를 사용했습니다.

...
string[] files = Directory.GetFiles(path + "\\xlsx\\","*.xlsx");
...
if (file.Contains("Northeast"))
  {
       destination_workbook.ActiveSheet.Range["C2"].Formula = "='xlsx\\[Northeast.xlsx]AggregatedInfo'!C2";
       destination_workbook.ActiveSheet.Range["C3"].Formula = "='xlsx\\[Northeast.xlsx]AggregatedInfo'!C3";
       destination_workbook.ActiveSheet.Range["C4"].Formula = "=C3 - 'xlsx\\[Northeast.xlsx]AggregatedInfo'!C4";
       destination_workbook.ActiveSheet.Range["C5"].Formula = "=C4-C2";
                   
       foreach (var link in destination_workbook.GetExcelLinkSources())
      {
           if (link.Contains("Northeast"))
               destination_workbook.UpdateExcelLink(link);
      }                  
  }
...

UpdateExcelLink(linkname, source_workbook)

이는 이전 메서드와 동일합니다. 하지만 차이점은 통합 문서 인스턴스에 캐시된 값을 가져오는 것입니다. 이 메서드는 소스 파일은 디스크에서 직접 액세스할 수 없는 상황에 적합합니다(예: HTTP 서버 또는 OneDrive와 같은 온라인 공유).

var source_workbook = new Workbook();
source_workbook.Open("xlsx\\Source.xlsx");  //load the file into a source workbook instance

destination_workbook.ActiveSheet.Range["A1"].Formula = "='[Source.xlsx]Sheet1'!C2";

foreach (var link in destination_workbook.GetExcelLinkSources())
{
   destination_workbook.UpdateExcelLink(link, source_workbook);
}

위의 사용 사례에서는 아래 코드에 표시된 대로 포함된 Excel 파일에 이 메서드를 사용했습니다.

. . .

var south_SourceWb = new Workbook();
south_SourceWb.Open(GetResourceStream("xlsx\\South.xlsx"));

. . .

destination_workbook.ActiveSheet.Range["D2"].Formula = "='[South.xlsx]AggregatedInfo'!C2";
destination_workbook.ActiveSheet.Range["D3"].Formula = "='[South.xlsx]AggregatedInfo'!C3";
destination_workbook.ActiveSheet.Range["D4"].Formula = "=D3 - '[South.xlsx]AggregatedInfo'!C4";
destination_workbook.ActiveSheet.Range["D5"].Formula = "=D4-D2";

. . .

foreach (var link in destination_workbook.GetExcelLinkSources())
{
   if (link.Contains("South"))
       destination_workbook.UpdateExcelLink(link, south_SourceWb);
  . . .
}

UpdateExcelLinks()

통합 문서에서 모든 링크를 한 번에 업데이트하려는 경우 이 메서드를 사용합니다. 하지만 이 메서드는 내부에서 UpdateExcelLink(linkname) 메서드를 사용하고 소스 파일이 디스크에 있는 경우에만 작동됩니다.

destination_workbook.ActiveSheet.Range["A1"].Formula = "='xlsx\\[Source1.xlsx]Sheet1'!C1";
destination_workbook.ActiveSheet.Range["A2"].Formula = "='xlsx\\[Source2.xlsx]Sheet1'!C2";
destination_workbook.ActiveSheet.Range["A3"].Formula = "='xlsx\\[Source2.xlsx]Sheet1'!C3";

destination_workbook.UpdateExcelLinks();

참고: 일부 소스 파일은 샘플에서 로컬로 사용할 수 없으므로 이 메서드를 사용하지 않았습니다.

4단계 - 대상 통합 문서 저장

외부 통합 문서 수식이 설정되고 소스에서 최신 값을 가져오도록 업데이트되면 대상 통합 문서를 저장합니다. 수행할 계산이 있는 경우 아래 코드에서처럼 파일을 저장하기 전에 IWorkbook.Calculate 메서드를 호출합니다.

destination_workbook.Calculate();          
destination_workbook.Save("SalesSummaryReport.xlsx");

샘플을 다운로드하여 실행 중인 기능을 확인하십시오.

외부 통합 문서 링크의 기타 일반 사용 사례

위의 예제에서 소스 통합 문서에서 셀 값에 액세스했습니다. 하지만 종종 범위, 이름이 지정된 범위 또는 표에 액세스해야 하며 수식 내에서 외부 참조를 사용합니다. 이러한 사례에서 수식이 표시되는 방식을 살펴보겠습니다.

범위에 액세스

교차 통합 문서 수식에서 범위에 액세스하려면 아래 표시된 대로 범위 문자열을 사용합니다.

worksheet.Range["F3:F6"].Formula = @"='xlsx\\[Midwest.xlsx]Summary Report'!C3:C6";

이름이 지정된 범위에 액세스

교차 통합 문서 수식에서 이름이 지정된 범위에 액세스하려면 아래 표시된 대로 범위의 이름을 사용합니다.

worksheet.Range["F3"].Formula = @"='xlsx\\Midwest.xlsx'!RangeName";

이름이 지정된 범위에 액세스하는 경우 소스 통합 문서 이름은 대괄호 [ ] 내에 둘러싸이지 않습니다.

표에 액세스

교차 통합 문서 수식에서 표에 액세스하려면 아래 표시된 대로 Excel 표로 구조화된 참조 구문을 사용합니다.

worksheet.Range["F3"].Formula = @"='xlsx\\Midwest.xlsx'!TableName[#Data]";  //get informaton from the data cells
worksheet.Range["F4"].Formula = @"='xlsx\\Midwest.xlsx'!TableName[[Column1],[#Data]]"; //get information from data cells of Column1
worksheet.Range["F5"].Formula = @"='xlsx\\Midwest.xlsx'!TableName[[#Headers],[#Totals]]"; //get information from headers and totals row

소스 통합 문서는 이름이 지정된 범위와 유사하게 대괄호 [ ] 내에 둘러싸이지 않습니다.

수식 함수에서 외부 통합 문서 링크 사용

수식 함수 내의 다른 참조와 같이 외부 통합 문서 링크는 수식 함수 내에서 사용하거나 함께 결합할 수 있습니다. 예를 들어 모든 지역의 생산 비용 합계 가져오기는 아래 수식으로 달성할 수 있습니다.

worksheet.Range["A2"].Value = "Total Production Cost:";
worksheet.Range["A2"].Formula = "=SUM('xlsx\\[Midwest.xlsx]AggregatedInfo'!C2, 'xlsx\\[Northeast.xlsx]AggregatedInfo'!C2, '[West.xlsx]AggregatedInfo'!C2, '[South.xlsx]AggregatedInfo'!C2";

이 블로그를 확인해주셔서 감사드리며 데이터 분석 요구 사항을 잘 해결하시기 바랍니다!

외부 통합 문서 수식에 대해 자세히 알아보려면 .NET 데모 | .NET 도움말 | Java 데모 | Java 도움말을 확인하십시오.

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

• GcExcel .NET Core 공식 홈페이지
• GcExcel .NET Core 튜토리얼 및 샘플
• GcExcel .NET Core Q&A
• GcExcel .NET Core FAQ