[Office] XElement를 이용하여 Excel XML 작성하기

Microsoft Office Excel 문서를 작성하는 방법 중에 쉽게 프로그래밍으로 구현할 수 있는 방법이 바로 Excel XML을 이용하는 방법입니다. 여기에서는 Microsoft Excel 2003부터 지원하는 Excel XML을 이용하여 프로그래밍으로 Excel 문서를 쉽게 출력하는 방법을 안내 드립니다.

1. Excel 문서를 작성합니다. 프린트 될 때의 형태, 비율, 폰트 명, 색상 등 자유롭게 작성합니다.
   

   작성 규칙

   • 위 사진과 같이 Header1, CompanyName, MainTitle 등의 텍스트는 모두 프로그래밍에 의해서 실제 데이터로 대체될 것이므로 적당히 알아서 지정합니다. 제목과 Header 등 리스트 행 정보같이 반복되는 정보가 아닌 부분을 모두 완성합니다.
     
   • 프로그래밍 시 반복될 부분을 지정할 때는 좀더 까다로운 규칙을 통해 작성해야 합니다.
     1. 첫 번째 행은 추후 삭제될 행으로 지정합니다. 저는 RemovedRowFirst라는 Text를 사용하여 지정하였습니다. 추후 프로그래밍 코드에서 RemovedRowFirst라는 행을 찾아 삭제할 것입니다. (RemovedRow를 굳이 생성하는 이유는 잠시 후에 설명 드리도록 하겠습니다.)
        
     2. 두 번째는 실제 정보가 입력되는 복사될 행을 작성합니다.  저는 Content1, Content2, Content3Date 등의 Text를 사용하여 작성했습니다. 금액 정보의 경우 숫자로만 지정해주어야 추후 Excel로 변환되었을 때 정상적으로 출력이 될 것입니다. (숫자가 아닌 값을 넣으면 Cell Data Type이 Number가 아닌 String로 저장되기 때문에 반드시 숫자로 저장해줍시다.)
        
     3. 세 번째 행으로는 첫 번째 행과 마찬가지로 추후 삭제될 행을 하나 지정합니다. 저는 RemovedRowLast라는 Text를 사용하여 지정하였습니다. 
         
     4. Content6이 지정된 Column 너비보다 길어질 때 옆으로 튀어나오지 않게 하기 위해 Content6의 오른쪽 셀에는 공백을 하나 줍니다.
        
     5. 반복될 행들의 Line를 그어주는 것이 좋겠죠? 여기서 중요한데, 먼저 행의 외곽을 따라가며 외곽선을 먼저 그어줍니다. 반드시 아랫선, 왼쪽선, 오른쪽 선 각각 따로 그려주시기 바랍니다.
        
        1. 먼저 왼쪽편의 외곽선을 긋습니다. 
           
        2. 반대로 오른쪽의 외곽선을 긋습니다. 
           
        3. 마지막으로 아래쪽의 외곽선을 긋습니다. 
           
        4. 여기까지 완료된 모습
            
     6. 반복될 행들의 외곽선을 모두 그렸다면 이제 내부 선을 그립니다. 내부 선은 행들이 반복되었을 때 나타날 내부 선의 모습을 말합니다. 아래 그림과 같이 내부를 선택하고 내부 라인을 선택하여 내부 선을 그립니다. 
        
        (위와 아래로 RemovedRow를 추가하지 않았다면 내부 선을 그릴 수 없었을 것입니다.)
     7. 마지막으로 맨 아래 합계 Cell을 추가해봅시다. 
        
     8. 완성된 모습 
        
     9. 파일을 저장합니다. 반드시 다음과 같이 XML 스프레드시트 2003 (*.xml)로 저장합니다.
        
     10. 이렇게 하면 프로그래밍을 통해서 쉽게 조작할 수 있는 완성된 XML을 얻을 수 있습니다.
   • VisualStudio를 열고 .cs파일에 위에서 작성한 XML을 조작하여 완성된 엑셀문서로 만듭니다. Excel XML도 사실을 별로 어렵지 않으므로 구조를 한번 훑어볼 것을 권장합니다.
     1. 먼저 Excel 문서를 XDocument로 만듭니다.

        //1. XDocument로 만들기
        string strExcelFileXml = File.ReadAllText(Server.MapPath("~/ExcelXML.xml"));
        XDocument xdocParsedExcel = XDocument.Parse(strExcelFileXml);

     2. 그리고 Excel XML은 Namespace가 적용되어있으므로, 앞으로 Element 검색에 사용할 XName를 선언합니다.

        //2. 추후 검색 식에 사용할 XName를 구합니다.
        XElement xWorkBook = xdocParsedExcel.Root;
        string strDefaultNameSpace = xWorkBook.Name.NamespaceName;
        XName xWorkSheetName = XName.Get("Worksheet", strDefaultNameSpace);
        XName xTableName = XName.Get("Table", strDefaultNameSpace);
        XName xRowName = XName.Get("Row", strDefaultNameSpace);
        XName xCellName = XName.Get("Cell", strDefaultNameSpace);
        XName xDataName = XName.Get("Data", strDefaultNameSpace);
        XName xFomulaName = XName.Get("Formula", strDefaultNameSpace);

     3. 반복되지 않는 각 Cell Data를 찾아서 적절한 값으로 지정해줍니다.

        //3. 반복되지 않는 각 Cell Data 지정
        DateTime dtFrom = new DateTime(2010, 12, 01);
        DateTime dtTo = new DateTime(2010, 12, 31);
        string strCompanyName = "테스트회사";
        string strFromToText = dtFrom.ToString("yyyy년 MM월 dd일") + " ~ " + dtTo.ToString("yyyy년 MM월 dd일");
        
        XElement xTable = xWorkBook.Element(xWorkSheetName).Element(xTableName);
        IEnumerable<XElement> xRows = xTable.Elements(xRowName);
        xRows.Single(t => t.Element(xCellName).Element(xDataName) != null && t.Element(xCellName).Element(xDataName).Value == "MainTitle").Element(xCellName).Element(xDataName).SetValue("미정산통장입출금내역");
        XElement xCompanyAndDateRow = xRows.Single(t => t.Element(xCellName).Element(xDataName) != null && t.Element(xCellName).Element(xDataName).Value == "CompanyName");
        xCompanyAndDateRow.Elements(xCellName).Single(t => t.Element(xDataName).Value == "CompanyName").Element(xDataName).SetValue(strCompanyName);
        xCompanyAndDateRow.Elements(xCellName).Single(t => t.Element(xDataName).Value == "DateFrom  ~ DateTo").Element(xDataName).SetValue(strFromToText);
        XElement xHeaderRow = xRows.Single(t => t.Element(xCellName).Element(xDataName) != null && t.Element(xCellName).Element(xDataName).Value == "Header1");
        xHeaderRow.Elements(xCellName).Single(t => t.Element(xDataName).Value == "Header1").Element(xDataName).SetValue("통장명");
        xHeaderRow.Elements(xCellName).Single(t => t.Element(xDataName).Value == "Header2").Element(xDataName).SetValue("계좌번호");
        xHeaderRow.Elements(xCellName).Single(t => t.Element(xDataName).Value == "Header3Date").Element(xDataName).SetValue("발생일");
        xHeaderRow.Elements(xCellName).Single(t => t.Element(xDataName).Value == "Header4Amount").Element(xDataName).SetValue("금액");
        xHeaderRow.Elements(xCellName).Single(t => t.Element(xDataName).Value == "Header5").Element(xDataName).SetValue("비고");
        xHeaderRow.Elements(xCellName).Single(t => t.Element(xDataName).Value == "Header6").Element(xDataName).SetValue("기타");

     4. 반복되는 Row의 Data를 지정합니다. 첫 번째와 세 번째 행에 지정한 RemovedRowFirst, RemovedRowLast를 삭제하고, Content Row를 찾아 지속적으로 Insert합니다.

        //4. 반복되는 Row Data지정
        List<CashInOrOut> lstCashInOut;
        using (CashDataContext db = new CashDataContext() { ObjectTrackingEnabled = false })
        	lstCashInOut = db.CashInOrOuts.Take(100).ToList();
        xRows.Single(t => t.Elements(xCellName).Any(s => s.Element(xDataName) != null && s.Element(xDataName).Value == "RemovedRowFirst")).Remove();
        xRows.Single(t => t.Elements(xCellName).Any(s => s.Element(xDataName) != null && s.Element(xDataName).Value == "RemovedRowLast")).Remove();
        XElement xOriginalRepeadedRow = xRows.Single(t => t.Elements(xCellName).Any(s => s.Element(xDataName) != null && s.Element(xDataName).Value == "Content1"));
        XElement xRepeatedRow;
        List<XElement> xGeneratedRow = new List<XElement>();
        XElement xContentCol1;
        XElement xContentCol2;
        XElement xContentCol3;
        XElement xContentCol4;
        XElement xContentCol5;
        XElement xContentCol6;
        foreach (CashInOrOut item in lstCashInOut)
        {
        	xRepeatedRow = new XElement(xOriginalRepeadedRow);
        	//1) 대상 Element를 찾습니다.
        	xContentCol1 = xRepeatedRow.Elements(xCellName).Single(t => t.Element(xDataName) != null && t.Element(xDataName).Value == "Content1");
        	xContentCol2 = xRepeatedRow.Elements(xCellName).Single(t => t.Element(xDataName) != null && t.Element(xDataName).Value == "Content2");
        	xContentCol3 = xRepeatedRow.Elements(xCellName).Single(t => t.Element(xDataName) != null && t.Element(xDataName).Value == "Content3Date");
        	xContentCol4 = xRepeatedRow.Elements(xCellName).Single(t => t.Element(xDataName) != null && t.Element(xDataName).Value == "4");
        	xContentCol5 = xRepeatedRow.Elements(xCellName).Single(t => t.Element(xDataName) != null && t.Element(xDataName).Value == "Content5");
        	xContentCol6 = xRepeatedRow.Elements(xCellName).Single(t => t.Element(xDataName) != null && t.Element(xDataName).Value == "Content6");
        
        	//2) 값을 지정합니다.
        	xContentCol1.Element(xDataName).SetValue(item.CashName);
        	xContentCol2.Element(xDataName).SetValue(item.CashNumber);
        	xContentCol3.Element(xDataName).SetValue(item.CashDate.ToString("yyyy년 MM월 dd일"));
        	xContentCol4.Element(xDataName).SetValue(item.CashAmount.ToString("0"));
        	xContentCol5.Element(xDataName).SetValue(item.CashNote);
        	xContentCol6.Element(xDataName).SetValue(item.Other);
        	xGeneratedRow.Add(xRepeatedRow);
        }
        if (xGeneratedRow.Count == 0)
        	xOriginalRepeadedRow.Remove();
        else
        	xOriginalRepeadedRow.ReplaceWith(xGeneratedRow);

     5. Excel에서는 전체 행의 개수를 저장하는 부분이 있는데 이 부분을 조정해주지 않으면 엑셀문서가 열리지 않게 되므로 반드시 수정해줍니다.

        //5. 전체 Row 숫자 지정
        int intNumberOfRowCount = lstCashInOut.Count;
        xTable.Attributes().First(t => t.Name.LocalName == "ExpandedRowCount").SetValue(intNumberOfRowCount + 10);

     6. 마지막으로 마지막 행에 Sum()함수를 사용했었는데, 그 Sum함수의 합계 범위를 늘어난 행의 숫자에 맞게 조정해줍니다. 엑셀에서 범위를 지정할 때는 E1:E5 등 셀의 범위를 절대값으로 주었지만, 실제로 저장된 Excel XML은 상대값으로 저장되어 있습니다.

        //6. 행 계수에 따라 합계 정보 Cell Data 조정(Excel의 Sum 함수의 범위는 상대적으로 지정되어 있으므로 참고)
        string strSumFunctionText = "=SUM(R[-3]C:R[-1]C)";
        string strEditedFunctionText = strSumFunctionText.Replace("-3", (-intNumberOfRowCount).ToString());
        XElement xStatisticsRow = xRows.Single(t => t.Elements(xCellName).Any(s => s.Attribute(xFomulaName) != null && s.Attribute(xFomulaName).Value == strSumFunctionText));
        xStatisticsRow.Elements(xCellName).Single(t => t.Attribute(xFomulaName) != null && t.Attribute(xFomulaName).Value == strSumFunctionText).Attribute(xFomulaName).SetValue(strEditedFunctionText);

     7. 완성된 Excel XML을 파일로 저장합니다.

        //7. 저장
        xdocParsedExcel.Save(Server.MapPath("~/xTest/Dk/Output_ExcelXML.xls"));

참 쉽죠~~