Export excel file via Asp.NET MVC, using OpenXml

개요

Web에서 고전적인 형태로 엑셀 파일을 내려주는 경우(Html table element 활용), 정확한 excel 파일 포맷을 준수하지 않기때문에, viewer가 이를 후 보정하여 보여주는 형태로 동작하게 됩니다. 이를 방지하기 위해서 정확하게 format을 준수하는 excel 파일을 생성하여 제공하기 위해 ExcelDocument와 ExcelResult를 정리하였습니다.

아래의 코드는 References에서 제공하는 것을 살짝 modify한 것입니다.

개발하던 중 막히는 부분을 찾아보다가, 거의 그대로 유용할 수 있는 sample을 확보해서...

Install Packages

OpenXml SDK를 이용하기 위해서는 WindowsBase.dll이 필요하며, SDK를 손쉽게 활용하기 위하여 SimpleOOXML(SDK Extension)를 설치합니다.

WindowsBase.dll must be included. (Assemblies > Framework)

Install-Package DocumentFormat.OpenXml

Install-Package SimpleOOXML

Create excel document 

일반적으로 사용하는 .Net 데이터 구조인 DataTable, DataSet을 소스로 Excel 파일을 생성하는 것을 목표로 합니다. DataTable을 하나의 sheet에 mapping하고, columnHeader를 1st row에 작성합니다. DataTable.TableName을 sheet name으로 설정합니다. 이를 위해 아래의 기능을 개발하였습니다. 이를 지원하기 위해서 기능적으로 Sheet를 추가/삭제하고 rename할 수 있어야 합니다. Data를 sheet에 등록하고, cell style을 설정할 수 있어야 합니다.

public class ExcelDocument { public static Stream GetStream(DataTable dataTable) { MemoryStream stream = SpreadsheetReader.Create(); using (SpreadsheetDocument document = SpreadsheetDocument.Open(stream, true)) { CreateSheet(document, dataTable, (uint)0); //Remove first 3 default tabs (Sheet1 ~ Sheet3) document.WorkbookPart.Workbook.Sheets.FirstChild.Remove(); document.WorkbookPart.Workbook.Sheets.FirstChild.Remove(); document.WorkbookPart.Workbook.Sheets.FirstChild.Remove(); }; stream.Position = 0; return stream; } public static Stream GetStream(DataSet dataSet) { MemoryStream stream = SpreadsheetReader.Create(); using (SpreadsheetDocument document = SpreadsheetDocument.Open(stream, true)) { for (int i = 0; i < dataSet.Tables.Count; i++) CreateSheet(document, dataSet.Tables[i], (uint)i); //Remove first 3 default tabs (Sheet1 ~ Sheet3) document.WorkbookPart.Workbook.Sheets.FirstChild.Remove(); document.WorkbookPart.Workbook.Sheets.FirstChild.Remove(); document.WorkbookPart.Workbook.Sheets.FirstChild.Remove(); }; stream.Position = 0; return stream; } private static SpreadsheetDocument CreateSheet(SpreadsheetDocument document , DataTable table , uint sheetId) { WorksheetPart newWorkSheetPart = document.WorkbookPart.AddNewPart<WorksheetPart>(); newWorkSheetPart.Worksheet = new Worksheet(new SheetData()); newWorkSheetPart.Worksheet.Save(); Sheets sheets = document.WorkbookPart.Workbook.GetFirstChild<Sheets>(); string relationshipId = document.WorkbookPart.GetIdOfPart(newWorkSheetPart); if (sheets.Elements<Sheet>().Any()) { sheetId = sheets.Elements<Sheet>() .Max(s => s.SheetId.Value) + 1; } Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = table.TableName }; sheets.Append(sheet); // styling for header WorksheetWriter writer = new WorksheetWriter(document, newWorkSheetPart); SpreadsheetStyle style = SpreadsheetReader.GetDefaultStyle(document); style.SetBorder("000000", BorderStyleValues.Thin); style.SetHorizontalAlignment(HorizontalAlignmentValues.Center); style.IsBold = true; for (int i = 0; i < table.Columns.Count; i++) writer.PasteText(GetExcelColumnValue(i + 1) + "1", table.Columns[i].ColumnName, style); // styling for body style.SetHorizontalAlignment(HorizontalAlignmentValues.General); style.IsBold = false; // paste data writer.PasteDataTable(table, "A2", style); document.WorkbookPart.Workbook.Save(); return document; } private static string GetExcelColumnValue(int columnNumber) { if (columnNumber <= 26) return ((char)(columnNumber + 64)).ToString(); columnNumber--; return GetExcelColumnValue(columnNumber / 26) + GetExcelColumnValue((columnNumber % 26) + 1); } }

Export excel document

앞서 생성한 Excel 파일을 사용자에게 제공하기 위해, ActionResult를 상송받는 ExcelResult class를 생성합니다. 앞서 생성한 ExcelDocument에서 확보한 stream값을 ExcelResult의 생성자를 통해서 전달케 합니다.

public class ExcelResult : ActionResult { private const string fileExtension = ".xlsx"; private readonly string _fileName; private readonly DataSet _dataSet; private ExcelResult(string fileName) : base() { _fileName = fileName + fileExtension; } public ExcelResult(DataTable table) : this(table.TableName) { _dataSet = new DataSet(table.TableName); _dataSet.Tables.Add(table); } public ExcelResult(DataSet dataSet) : this(dataSet.DataSetName) { _dataSet = dataSet; } /// <summary> /// Execute the Excel Result. /// </summary> /// <param name="context">Controller context.</param> public override void ExecuteResult(ControllerContext context) { MemoryStream stream = (MemoryStream)ExcelDocument.GetStream(_dataSet); WriteStream(stream, _fileName); } /// <summary> /// Writes the memory stream to the browser. /// </summary> /// <param name="memoryStream">Memory stream.</param> /// <param name="excelFileName">Excel file name.</param> private static void WriteStream(MemoryStream memoryStream, string excelFileName) { HttpContext context = HttpContext.Current; context.Response.Clear(); context.Response.AddHeader("content-disposition", string.Format("attachment;filename={0}", excelFileName)); memoryStream.WriteTo(context.Response.OutputStream); memoryStream.Close(); context.Response.End(); } }

Issues

두 번째 link의 댓글에 보면, 데이터 양이 많은 경우 지옥문이 열린다고 합니다.

이는 Extension dll의 기능을 통해 데이터를 paste하는 형태로 구현한 것이 원인일 것으로 추정되는데, 이를 보안하기 위해서는 OpenXml SDK를 통해 직접 구현해야 할 필요가 있어 보입니다.

References

OpenXml => https://ko.wikipedia.org/wiki/오피스_오픈_XML OpenXml

Sample code => http://johnlivingstontech.blogspot.kr/2010/06/create-excel-workbook-from-dataset.html

ExcelResult => http://www.dotnetcurry.com/ShowArticle.aspx?ID=484