[Office] XElement를 이용하여 Excel XML 작성하기
2011. 1. 24. 19:10ㆍOthers/C# 일반
Microsoft Office Excel 문서를 작성하는 방법 중에 쉽게 프로그래밍으로 구현할 수 있는 방법이 바로 Excel XML을 이용하는 방법입니다. 여기에서는 Microsoft Excel 2003부터 지원하는 Excel XML을 이용하여 프로그래밍으로 Excel 문서를 쉽게 출력하는 방법을 안내 드립니다.
- Excel 문서를 작성합니다. 프린트 될 때의 형태, 비율, 폰트 명, 색상 등 자유롭게 작성합니다.
작성 규칙
- 위 사진과 같이 Header1, CompanyName, MainTitle 등의 텍스트는 모두 프로그래밍에 의해서 실제 데이터로 대체될 것이므로 적당히 알아서 지정합니다. 제목과 Header 등 리스트 행 정보같이 반복되는 정보가 아닌 부분을 모두 완성합니다.
- 프로그래밍 시 반복될 부분을 지정할 때는 좀더 까다로운 규칙을 통해 작성해야 합니다.
- 첫 번째 행은 추후 삭제될 행으로 지정합니다. 저는 RemovedRowFirst라는 Text를 사용하여 지정하였습니다. 추후 프로그래밍 코드에서 RemovedRowFirst라는 행을 찾아 삭제할 것입니다. (RemovedRow를 굳이 생성하는 이유는 잠시 후에 설명 드리도록 하겠습니다.)
- 두 번째는 실제 정보가 입력되는 복사될 행을 작성합니다. 저는 Content1, Content2, Content3Date 등의 Text를 사용하여 작성했습니다. 금액 정보의 경우 숫자로만 지정해주어야 추후 Excel로 변환되었을 때 정상적으로 출력이 될 것입니다. (숫자가 아닌 값을 넣으면 Cell Data Type이 Number가 아닌 String로 저장되기 때문에 반드시 숫자로 저장해줍시다.)
- 세 번째 행으로는 첫 번째 행과 마찬가지로 추후 삭제될 행을 하나 지정합니다. 저는 RemovedRowLast라는 Text를 사용하여 지정하였습니다.
- Content6이 지정된 Column 너비보다 길어질 때 옆으로 튀어나오지 않게 하기 위해 Content6의 오른쪽 셀에는 공백을 하나 줍니다.
- 반복될 행들의 Line를 그어주는 것이 좋겠죠? 여기서 중요한데, 먼저 행의 외곽을 따라가며 외곽선을 먼저 그어줍니다. 반드시 아랫선, 왼쪽선, 오른쪽 선 각각 따로 그려주시기 바랍니다.
- 먼저 왼쪽편의 외곽선을 긋습니다.
- 반대로 오른쪽의 외곽선을 긋습니다.
- 마지막으로 아래쪽의 외곽선을 긋습니다.
- 여기까지 완료된 모습
- 먼저 왼쪽편의 외곽선을 긋습니다.
- 반복될 행들의 외곽선을 모두 그렸다면 이제 내부 선을 그립니다. 내부 선은 행들이 반복되었을 때 나타날 내부 선의 모습을 말합니다. 아래 그림과 같이 내부를 선택하고 내부 라인을 선택하여 내부 선을 그립니다.
(위와 아래로 RemovedRow를 추가하지 않았다면 내부 선을 그릴 수 없었을 것입니다.) - 마지막으로 맨 아래 합계 Cell을 추가해봅시다.
- 완성된 모습
- 파일을 저장합니다. 반드시 다음과 같이 XML 스프레드시트 2003 (*.xml)로 저장합니다.
- 이렇게 하면 프로그래밍을 통해서 쉽게 조작할 수 있는 완성된 XML을 얻을 수 있습니다.
- 첫 번째 행은 추후 삭제될 행으로 지정합니다. 저는 RemovedRowFirst라는 Text를 사용하여 지정하였습니다. 추후 프로그래밍 코드에서 RemovedRowFirst라는 행을 찾아 삭제할 것입니다. (RemovedRow를 굳이 생성하는 이유는 잠시 후에 설명 드리도록 하겠습니다.)
- VisualStudio를 열고 .cs파일에 위에서 작성한 XML을 조작하여 완성된 엑셀문서로 만듭니다. Excel XML도 사실을 별로 어렵지 않으므로 구조를 한번 훑어볼 것을 권장합니다.
- 먼저 Excel 문서를 XDocument로 만듭니다.
//1. XDocument로 만들기 string strExcelFileXml = File.ReadAllText(Server.MapPath("~/ExcelXML.xml")); XDocument xdocParsedExcel = XDocument.Parse(strExcelFileXml);
- 그리고 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);
- 반복되지 않는 각 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("기타");
- 반복되는 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);
- Excel에서는 전체 행의 개수를 저장하는 부분이 있는데 이 부분을 조정해주지 않으면 엑셀문서가 열리지 않게 되므로 반드시 수정해줍니다.
//5. 전체 Row 숫자 지정 int intNumberOfRowCount = lstCashInOut.Count; xTable.Attributes().First(t => t.Name.LocalName == "ExpandedRowCount").SetValue(intNumberOfRowCount + 10);
- 마지막으로 마지막 행에 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);
- 완성된 Excel XML을 파일로 저장합니다.
//7. 저장 xdocParsedExcel.Save(Server.MapPath("~/xTest/Dk/Output_ExcelXML.xls"));
- 먼저 Excel 문서를 XDocument로 만듭니다.
- 위 사진과 같이 Header1, CompanyName, MainTitle 등의 텍스트는 모두 프로그래밍에 의해서 실제 데이터로 대체될 것이므로 적당히 알아서 지정합니다. 제목과 Header 등 리스트 행 정보같이 반복되는 정보가 아닌 부분을 모두 완성합니다.
'Others > C# 일반' 카테고리의 다른 글
[C#] File, Directory 보안 설정 (0) | 2011.04.23 |
---|---|
[C#] Version 자동 설정 및 BuildDateTime 구하기 (0) | 2011.04.15 |
[Office] Excel Html을 이용하여 Excel 문서 작성하기 (0) | 2011.02.28 |
[Office.Interop] Server에서 Microsoft.Interop.Excel.dll 설정 (0) | 2011.01.20 |
[Office] Application에서 Excel 파일 읽기 (0) | 2011.01.20 |