문제

데이터의 소스와 관계없이, 구조화된 문서에 데이터를 바인딩하는 것은 많은 기업에서 흔히 해야 하는 작업입니다. 물류, 배송, 판매, 금융, 마케팅, 보건의료, 교육 등 어떤 업종에 종사하든 간에 우리는 날마다 Excel 보고서 몇 개쯤은 직접 만들게 됩니다. 공급업체, 구매자, 판매자, 의뢰인, 고객에게 전자적으로 배포하는 보고서나 송장, 배송, 납품 또는 구매 주문서도 여기에 포함될 수 있습니다.

기업은 이러한 작업을 수동으로 관리하는 방식에서 자동화된 프로세스로 전환하고 있습니다. 즉 개발자는 기본 템플릿을 정의하고, 체계적인 형식으로 데이터를 입력하고, 최소한의 프로그래밍만으로 몇 가지 보고서를 생성하기만 하면 됩니다.

웹에서 다양한 템플릿 언어 도구를 구할 수 있습니다. 사용 중인 프레임워크, 언어 또는 스택에 적합한 것을 선택하시면 됩니다. 모든 개발자는 여러 가지 사용 사례에 맞춰 데이터와 출력 파일 간의 격차를 효율적으로 해소할 방법을 고민하게 됩니다.

단, 다음과 같은 여러 가지 요구 사항을 해결할 수 있는 완벽한 템플릿 언어가 .NET Framework 안에 있어야 합니다.

• 개발자는 보고서를 생성하기 위해 복잡한 API를 배우기를 원치 않음

• 개발자가 템플릿 파일을 쉽게 편집할 수 있어야 함

• 복합적인 사용 사례에 맞춰 어떤 분야나 상황에서도 알맞은 Excel 파일을 생성할 수 있어야 함

• DB, 문자열, ADO.NET 개체 등 원하는 형식의 데이터 개체에 연결 가능해야 함

• 빠른 데이터 계산을 위해 Excel 수식을 지원해야 함

솔루션

GrapeCity Documents for Excel(GcExcel Java | GcExcel .NET Core)은 Excel 문서를 데이터에 바인딩하여 고급 레이아웃의 Excel 보고서를 생성할 수 있는 API와 포괄적인 구문을 담은 새로운 템플릿을 지원합니다. 데이터 확대 및 수식에 유연한 구문과 손쉬운 표기법을 사용할 수 있는 새 템플릿의 구문과 API를 이용하여 Excel 템플릿을 손쉽게 정의하고 수많은 사용 사례에 잘 맞는 Excel 보고서를 생성해 보십시오.

GcExcel API를 이용해 Excel 보고서를 .xlsx 파일 또는 PDF 형식으로 직접 저장할 수도 있습니다.

개발자는 새로운 템플릿 구문(중괄호 "{{" 및 "}}" 사용)으로 Excel 템플릿을 만들어 데이터베이스 필드, 변수, 수식을 정의할 수 있습니다. 각 위치에서 정보를 변경하려면 템플릿 필드를 정의하면 됩니다. 이제 코드의 Workbook.AddDatasource() 메서드를 통해 통합 문서에 데이터 소스를 추가하고 Workbook.ProcessTemplate() 메서드로 데이터를 템플릿에 바인딩할 수 있습니다.

템플릿 API는 템플릿 필드를 감지하고 데이터베이스와 비교한 다음 실제 데이터로 바꿔 줍니다.

Visual Studio Tools for Office(VSTO) 기반의 Excel API와 템플릿 지원을 조합하여 전문가다운 완벽한 Excel 보고서를 생성하고 데이터 분석 및 배포에 이용할 수 있습니다. 이 솔루션은 Windows, Linux, macOS 등 모든 플랫폼에서 사용할 수 있습니다.

비즈니스 사용 사례와 새로운 템플릿 지원으로 문제를 해결하는 간단한 예가 아래에 나와 있습니다.

Excel 템플릿의 작동 방식

어떤 회사에서 구매 주문서를 작성하고 있습니다. 구매 주문서는 대개 Excel 스프레드시트나 PDF 파일로 전송합니다. 이 회사에서는 하루에도 여러 건의 구매 주문서를 작성하는데, 데이터베이스에서 공급업체 및 배송 정보를 가져오는 프로세스를 자동화하고 각 공급업체에 보낼 구매 주문서를 Excel 스프레드시트로 생성하고자 합니다.

템플릿 구문으로 작업하는 사용자는 MS Excel에서 템플릿을 디자인하고, 간단한 코드로 템플릿을 데이터베이스에 바인딩하고, 특정 공급업체에 보낼 구매 주문서별로 Excel 스프레드시트를 생성하기만 하면 됩니다.

이 회사는 구매 주문서별로 유사한 Excel 스프레드시트를 생성하고자 합니다.

그림 1

이 Excel 템플릿을 만들었으면 이 템플릿을 GcExcel의 통합 문서 개체로 로드합니다.

var templateFile = this.GetResourceStream("xlsx\\Template.xlsx");
workbook.Open(templateFile);​

구매 주문서 테이블의 데이터 소스를 DataTable로 추가합니다.

//dt 데이터 테이블이 데이터 코드에 정의되어 있다고 가정
reportBook.AddDataSource("po", dt); 

통합 문서의 'ProcessTemplate()' 메서드를 호출합니다. 데이터 필드는 이를 통해 템플릿에 자동으로 바인딩됩니다.

reportBook.ProcessTemplate();

다음과 같이 통합 문서를 Excel 파일이나 PDF로 저장합니다.

reportBook.Save("PurchaseOrder.xlsx");

또는

reportBook.Save("PurchaseOrder.pdf");

이로써 위 그림 1과 비슷한 구매 주문서가 생성되었습니다.

Excel 템플릿의 기능

데이터 소스 지원

템플릿 API는 Excel 템플릿에 다음 개체의 데이터를 직접 바인딩할 수 있습니다.

1. .Net의 DataSet/DataTable 및 Java의 ResultSet: 모든 DB 유형의 데이터 소스를 처리할 수 있습니다.

2. 사용자 정의 개체: 이것은 사용자가 정의한 개체로서, JSON String/File/XML 등의 데이터 소스를 처리할 수 있습니다. 즉 사용자 정의 개체로 직렬화할 수 있는 모든 데이터 소스를 처리할 수 있습니다.

3. 변수: 코드의 사용자 정의 변수입니다.

4. 배열/목록: 코드의 사용자 정의 배열/목록입니다.

단순한 언어 구문

이 템플릿 구문은 "{{" 및 "}}"를 템플릿의 표식으로 사용합니다. 이 표식 안에 데이터베이스 필드를 정의할 수 있습니다.

여기서 'inv'는 '송장' 테이블을 나타내며, 'itemnumber'는 데이터 필드입니다.

데이터 확장 방향 정의

보고서의 데이터를 가로 및 세로 방향으로 확장할 수 있습니다. 템플릿 구문에서 'E=H' 또는 'E=V' 속성을 사용해 데이터 확장 방향을 정의하면 됩니다. 여기서 E는 'Expansion', 즉 '확장'을 뜻합니다. 기본 확장은 세로 방향입니다.

다음 데이터에서는 E=V로 지정하여 항목 번호를 세로로 확장합니다.

다음 데이터에서는 E=H로 지정하여 제품 이름을 가로로 확장합니다.

데이터 확장의 컨텍스트 정의

데이터는 컨텍스트(또는 데이터 필드)를 기준으로 가로 또는 세로로 확장할 수 있습니다. 템플릿에 여러 셀이 바인딩되어 있으면 셀 사이에 그룹 또는 필터 관계가 자연스럽게 형성되어 셀이 쌍으로 확장됩니다. 이것을 컨텍스트 관계라고 합니다.

컨텍스트의 유형은 없음, 기본값, 사용자 정의의 세 가지입니다.

기본 컨텍스트는 왼쪽에 있는 인접 셀이거나(E=V인 경우) 위쪽에 있는 인접 셀입니다(E= H인 경우). 왼쪽 인접 셀도 위쪽 인접 셀도 아니라면 명시적으로 지정해야 합니다. 컨텍스트는 예를 들면 C=E4와 같이 'C' 속성으로 정의합니다(여기서 E4는 데이터 필드 셀).

다음 예를 참조하십시오.

컨텍스트 = 없음(C=None): Area를 가로로 확장하고, Area에 의존하지 않고 Salesman을 기본적으로 확장하려는 경우입니다. 따라서 Salesman 확장의 "컨텍스트"를 "없음"으로 정의합니다.

컨텍스트 = 기본값

하지만 Salesman이 Area에 따라 확장되게 하고(즉 Area가 가로로 확장됨) Salesman이 Area 아래에서 세로로 확장되게 하려면 Salesman의 컨텍스트를 정의하지 마십시오. A4가 Area 확장을 가로로 정의하고 있으므로({{ds.Area(E=H)}}) Salesman 셀(A5)은 자동으로 Area(A4)를 컨텍스트로 받아들입니다.

컨텍스트 = 사용자 정의(C=Custom): 컨텍스트 기준으로 데이터를 확장해야 하지만 컨텍스트 셀이 인접하지 않은 경우에는 해당 셀에 대해 컨텍스트를 명시적으로 설정하면 됩니다. 아래 예에서 Salesman의 컨텍스트는 A4로 정의되어 있습니다.

컨텍스트 셀을 기준으로 수식을 정의할 수도 있습니다. 아래 예에서 소계는 C5 셀(ProductType)에 대해서만 계산하게 되어 있으므로 Fruit의 소계와 Vegetable의 소계가 계산됩니다. 따라서 여기서 컨텍스트는 C5 셀, 즉 'ProductType'입니다.

한편 GrandTotal은 Salesman이 판매한 모든 제품에 대해 계산되므로 컨텍스트는 B5 셀(Salesman)입니다.

데이터 범위 정의

컨텍스트를 지정하는 대신 템플릿 셀의 범위를 정의할 수 있습니다. 범위를 정의하는 셀 다음에는 다른 셀의 확장을 위한 컨텍스트가 나옵니다.

예: 이 보고서에는 각 학생의 가족에 대한 세부 정보가 나와 있습니다. 따라서 "Student Name" 기준으로 데이터를 확장해야 합니다. 이어서 두 줄을 건너뛴 다음, 다른 학생의 가족 세부 정보를 표시해야 합니다. 모든 필드에 컨텍스트를 추가하는 대신 Student Name에 R=A3:B9 범위를 정의합니다. 즉 {{report.student.name (R=A3:B9)}}로 코딩하여 학생 이름을 기준으로 가족 세부 정보를 확장하고 그룹화해야 합니다.

여러 데이터 소스에서 데이터 추가

여러 데이터 소스 또는 데이터 소스 내 여러 데이터 표에서 데이터를 검색할 수 있습니다. 구문은 단순합니다. 데이터 소스 개체 다음에 데이터 필드를 사용하면 됩니다.

이 예에서는 두 데이터 소스의 데이터를 병합합니다. 즉 데이터 표 하나에서 직원 정보를, 다른 표에서는 부서 정보를 가져옵니다.

데이터 그룹화

특정한 데이터가 반복되고 해당 데이터 필드를 기준으로 표를 그룹화해야 하는 경우가 있습니다. 새로운 템플릿 구문을 사용하면 정상, 병합, 반복 또는 목록과 같은 방법으로 데이터를 그룹화할 수 있습니다.

아래 예에서는 일반적인 데이터를 병합하고 이 그룹을 기준으로 추가 열을 그룹화하는 방법을 보여줍니다. 그런 다음, 다른 데이터 필드의 데이터를 병합할지 아니면 행마다 데이터를 반복할지도 선택합니다.

아웃라인 그룹

그룹을 병합하고 싶지 않다면 아웃라인 그룹만 만든 다음, 이 그룹에 어떤 그룹 속성도 지정하지 말고 아웃라인 그룹에 따라 확장하도록 컨텍스트를 제공하여 내부 데이터를 정렬하십시오. 이 예에서는 모든 Area의 Salesman이 표시되게 하려고 합니다. 따라서 컨텍스트를 C4로 지정합니다(또는 {{ds.Area}}).

그런 다음 각 제품의 Sales를 표시하고자 합니다. 그러려면 컨텍스트를 D3으로 지정하면 됩니다(제품 유형별 매출 표시).

여기서 가장 중요한 것은 셀 스타일을 사용하고 데이터를 아웃라인으로 표시함과 동시에 내부 행의 컨텍스트를 지정하여 데이터를 아웃라인 그룹으로 표시할 수 있다는 점입니다.

중첩된 데이터 필드 지정

데이터가 중첩되어 있을 때 내부 개체 필드를 기준으로 보고서를 정렬하려는 경우, 새로운 템플릿 구문으로 중첩된 데이터 필드를 정의할 수 있습니다.

예를 들어 데이터(JSON)가 다음과 같다고 가정하겠습니다.

학생 정보가 나열된 보고서가 있습니다. 'father' 및 'mother'는 'student'->'family' 내에 중첩된 개체로, 부모의 이름과 직업을 나열합니다.

{
  "report":[
      {
          "student": [
              {
                  "name": "Jane",
                  "address": "101, Halford Avenue, Fremont, CA"
                  "family"[
                      {
                          "father":[
                                          {
                                              "name": "Patrick James",
                                              "occupation": "Surgeon",
                                          }
                                      ]
                          "mother":[
                                          {
                                              "name": "Diana James",
                                              "occupation": "Surgeon",
                                          }
                                        ]
                      }
              {
                  "name": "Mark",
                  "address": "101, Halford Avenue, Fremont, CA"
                  "family"[
                      {
                          "father":[
                                          {
                                              "name": "Jonathan Williams",
                                              "occupation": "Product Engineer",
                                          }
​
                                      ]
​
                          "mother":[
​
                                          {
                                              "name": "Joanna Williams",
                                              "occupation": "Surgeon",
                                          }
​
                                        ]
​
                      }
                    ]
​
​
                  }
              ]
          }
      ]
​
}

동일한 학년의 모든 학생을 나열하고 학생의 가족 정보를 표시하는 보고서를 생성해야 합니다. 이 경우 템플릿 구문을 사용하면 중첩된 개체인 'father', 'mother'를 나타내는 구문이 아주 간단해집니다. 중첩된 개체를 '.'로 구분해 주기만 하면 됩니다. 아래에서 템플릿과 생성된 보고서를 확인하십시오.

수식 함수 정의

이 템플릿은 몇 가지 표준 MS Excel 수식 함수를 지원합니다. 이 함수를 사용해 데이터 계산을 수행할 수 있습니다. 셀 및 데이터 필드에 수식을 적용하거나 식으로 사용할 수 있습니다. 아래 예에서는 Invoice 데이터에 적용되는 다양한 유형의 수식을 보여줍니다. 중괄호 "{{"로 식을 시작하고 보통 MS Excel에서 하는 것처럼 Excel 수식을 입력한 후 중괄호를 닫으십시오.

{{=Sum(inv.price)}}와 같은 식으로 셀의 합계를 구할 수도 있습니다.

식 정의: '+', '-', '*', '/' 및 '()'와 같은 연산자를 사용해 템플릿의 식 필드를 정의할 수 있습니다.

예:

{{=ds.count*ds.price}}
{{=A18*0.05}}

인라인 필드 정의

템플릿 구문을 이용해 셀 안의 텍스트로 인라인 필드를 정의할 수 있습니다. 이렇게 하면 메일 병합 보고서와 같은 보고서가 생성됩니다. 예를 들어 이렇게 직원 정보와 셀 텍스트를 병합한 템플릿은 그림과 같이 출력됩니다.

정렬 식 정의

Excel 템플릿에서 정렬 방향을 ‘오름차순’, ‘내림차순’ 또는 ‘없음’으로 선언할 수 있습니다.

다음 예에서는 세 가지 식을 사용하는 방법을 보여줍니다.

스타일 설정

이 템플릿에서는 셀에 적용된 셀 스타일 및 병합된 스타일을 준수합니다. 데이터는 셀에 적용된 스타일에 따라 증가합니다.

워크시트 이름에 템플릿 필드를 추가하여 여러 워크시트 생성

워크시트의 이름에 데이터 필드/식 필드를 삽입할 수 있습니다. 그러면 런타임에 여러 워크시트가 생성됩니다. 예를 들어 여러 시트에 여러 직원의 세부 정보를 표시하려면 워크시트 이름에 {{ds.EmpName}}을 지정하면 됩니다. 그러면 각 직원에 대해 새 워크시트가 생성되는데, 이때 직원의 이름이 워크시트에 표시됩니다.

페이지 나누기 추가

필드 다음에 새 페이지를 추가하려면 set PageBreak=true를 정의하고 데이터 필드를 정의하면 됩니다. 페이지 나누기는 MS Excel의 '페이지 나누기 삽입'과 비슷하게 동작합니다.

• 템플릿 셀이 첫 번째 열에 있으면 가로 페이지 나누기가 추가됩니다.

• 템플릿 셀이 첫 번째 행에 있으면 세로 페이지 나누기가 추가됩니다.

• 템플릿 셀이 첫 번째 행, 첫 번째 열이 아닌 위치에 있으면 가로 및 세로 페이지 나누기가 모두 추가됩니다.

한 시트에 여러 개의 보고서

시트 하나에 보고서를 여러 개 생성할 수 있습니다. 데이터 소스는 같을 수도 있고 다를 수도 있습니다. 아래의 보고서는 시트 하나에 세 개의 보고서가 있는 경우를 보여줍니다. 보고서 하나에는 여행의 세부 정보가 나열되어 있고, 그다음 보고서에는 숙박 세부 정보가 나와 있습니다. 각 고객 및 목적지에 따라 비상 연락처와 여행 동반자는 달라집니다.

생성할 수 있는 Excel 보고서

새로운 템플릿 지원을 사용해 실시간 보고서를 생성할 수 있습니다. 올바른 구문과 속성만 알면 됩니다. 데이터 확장, 컨텍스트 및 수식 함수로 간단한 사용 사례에서 복잡한 사용 사례에 이르기까지 가장 널리 사용되는 유형의 보고서를 생성해 보십시오.

다음과 같은 보고서를 생성할 수 있습니다.

• 선 보고서

• 메일 병합 보고서

• 교차표 분석

• 보고서 하나에 여러 보고서 포함

• Tablix 보고서

• 수취 보고서

• 판매 보고서

• 구매 주문 보고서

• 송장 보고서

다음은 템플릿 지원을 사용해 작성한 보고서의 그림입니다.

송장

구매 주문서

재무 대시보드

배송 및 출고 지시서

여행 세부 정보 보고서

판매 Tablix 보고서

세로로 그룹화된 보고서

데모 및 문서 확인:

.NET 도움말 | Java 도움말 | .NET 데모 | Java 데모