/// <summary> /// Data Model To Excel /// </summary> /// <param name="model"></param> /// <param name="data"></param> /// <param name="title"></param> /// <returns></returns> public void DataToExcelEx(List <Dictionary <string, object> > data, string title, List <GridHeaderModel> headers) { if (data != null) { using (System.IO.MemoryStream stream = new System.IO.MemoryStream()) { int colIdx = 0; SpreadsheetDocument spreadsheet = ExcelProc.CreateWorkbook(stream); ExcelProc.AddBasicStyles(spreadsheet); ExcelProc.AddAdditionalStyles(spreadsheet); if (title.Length > 31) { title = title.Substring(0, 28) + "..."; } ExcelProc.AddWorksheet(spreadsheet, title); Worksheet worksheet = spreadsheet.WorkbookPart.WorksheetParts.First().Worksheet; if (data.Count > 0) { // 타이틀 만들기 foreach (KeyValuePair <string, object> header in data[0]) { foreach (var p in header.Value.GetType().GetProperties()) { if (p.GetCustomAttributes(true).Contains(new ExcelExceptDataAttribute()) == false) { var match = headers.Find(c => c.field == p.Name); if (match != null) { ExcelProc.SetColumnHeadingValue(spreadsheet, worksheet, Convert.ToUInt32(colIdx + 1), match.title, false, false); ExcelProc.SetColumnWidth(worksheet, colIdx + 1, 25); colIdx++; } } } } ExcelProc.SetCellValue(spreadsheet, worksheet, 1, 1, title + " (Restricted Document)", 4, false, false); int rowIndex = 1; // 내용 만들기 foreach (var list in data) { colIdx = 0; foreach (KeyValuePair <string, object> body in list) { //colIdx = 0; foreach (var p in body.Value.GetType().GetProperties()) { if (p.GetCustomAttributes(true).Contains(new ExcelExceptDataAttribute()) == false) { var match = headers.Find(c => c.field == p.Name); if (match != null) { ExcelProc.SetCellValue(spreadsheet, worksheet, Convert.ToUInt32(colIdx + 1), Convert.ToUInt32(rowIndex + 2), p.GetValue(body.Value, null) == null ? string.Empty : p.GetValue(body.Value, null).ToString(), false, false); colIdx++; } } } } rowIndex++; } } worksheet.Save(); spreadsheet.Close(); byte[] buffer = stream.ToArray(); string filename = string.Format("{0}.xlsx", title); //return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", filename); } } else { throw new Exception("Datatype Error"); } }
/// <summary> /// Data Model To Excel /// </summary> /// <param name="model"></param> /// <param name="data"></param> /// <param name="title"></param> /// <returns></returns> public byte[] DataToExcel <T>(T data, string title) { if (data is IList) { using (System.IO.MemoryStream stream = new System.IO.MemoryStream()) { SpreadsheetDocument spreadsheet = ExcelProc.CreateWorkbook(stream); ExcelProc.AddBasicStyles(spreadsheet); ExcelProc.AddAdditionalStyles(spreadsheet); if (title.Length > 31) { title = title.Substring(0, 28) + "..."; } ExcelProc.AddWorksheet(spreadsheet, title); Worksheet worksheet = spreadsheet.WorkbookPart.WorksheetParts.First().Worksheet; IList excelData = (data as IList); if (excelData.Count > 0) { IList <PropertyInfo> PropertiesList = new List <PropertyInfo>(); int colIdx = 0; foreach (var p in excelData[0].GetType().GetProperties()) { if (p.GetCustomAttributes(true).Contains(new ExcelExceptDataAttribute()) == false) { PropertiesList.Add(p); // var display = p.GetCustomAttribute <DisplayAttribute>(); ExcelProc.SetColumnHeadingValue(spreadsheet, worksheet, Convert.ToUInt32(colIdx + 1), display.Name, false, false); // 컬럼 헤더 #### //ExcelProc.SetColumnHeadingValue(spreadsheet, worksheet, Convert.ToUInt32(colIdx + 1), p.Name, false, false); // 컬럼 헤더 #### ExcelProc.SetColumnWidth(worksheet, colIdx + 1, 25); colIdx++; } } //ExcelProc.SetCellValue(spreadsheet, worksheet, 1, 1, title + " (Restricted Document)", 4, false, false); //제목 #### int rowIndex = 1; foreach (var d in excelData) { colIdx = 0; foreach (var p in PropertiesList) { ExcelProc.SetCellValue(spreadsheet, worksheet, Convert.ToUInt32(colIdx + 1), Convert.ToUInt32(rowIndex + 1), p.GetValue(d, null) == null ? string.Empty : p.GetValue(d, null).ToString(), false, false); colIdx++; } rowIndex++; } } worksheet.Save(); spreadsheet.Close(); byte[] buffer = stream.ToArray(); string filename = string.Format("{0}.xlsx", title); return(buffer); //return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", filename); } } else { return(null); } }