//..................................................................... /// <summary> /// /// </summary> /// <typeparam name="T"></typeparam> /// <param name="objects"></param> /// <param name="headerNames"></param> /// <param name="stylesPart"></param> /// <returns></returns> private SheetData CreateSheetData <T>(List <T> objects, List <string> headerNames, WorkbookStylesPart stylesPart) { SheetData sheetData = new SheetData(); if (objects != null) { //Fields names of object List <string> fields = GetPropertyInfo <T>(); var az = new List <Char>(Enumerable.Range('A', 'Z' - 'A' + 1).Select(i => (Char)i).ToArray()); List <Char> headers = az.GetRange(0, fields.Count); int numRows = objects.Count; int numCols = fields.Count; Row header = new Row(); int index = 1; header.RowIndex = (uint)index; for (int col = 0; col < numCols; col++) { //Cell datazell = CreateHeaderCell(headers[col].ToString(), headerNames[col], index, stylesPart.Stylesheet); HeaderCell c = new HeaderCell(headers[col].ToString(), headerNames[col], index, stylesPart.Stylesheet, System.Drawing.Color.DodgerBlue, 12, true); header.Append(c); } sheetData.Append(header); for (int i = 0; i < numRows; i++) { index++; var obj1 = objects[i]; var r = new Row { RowIndex = (uint)index }; for (int col = 0; col < numCols; col++) { string fieldName = fields[col]; PropertyInfo myf = obj1.GetType().GetProperty(fieldName); if (myf != null) { object obj = myf.GetValue(obj1, null); if (obj != null) { if (obj.GetType() == typeof(string)) { // Cell datazell = CreateTextCell(headers[col].ToString(), obj.ToString(), index); TextCell c = new TextCell(headers[col].ToString(), obj.ToString(), index); r.Append(c); } else if (obj.GetType() == typeof(bool)) { string value = (bool)obj ? "Yes" : "No"; //Cell datazell = CreateTextCell(headers[col].ToString(), value, index); TextCell c = new TextCell(headers[col].ToString(), value, index); r.Append(c); } else if (obj.GetType() == typeof(DateTime)) { //string value = GetExcelSerialDate((DateTime) obj).ToString(); string value = ((DateTime)obj).ToOADate().ToString(); // stylesPart.Stylesheet is retrieved reference for the appropriate worksheet. //Cell datazell = CreateDateCell(headers[col].ToString(), value, index, stylesPart.Stylesheet); DateCell c = new DateCell(headers[col].ToString(), (DateTime)obj, index); r.Append(c); } else if (obj.GetType() == typeof(decimal) || obj.GetType() == typeof(double)) { //Cell datazell = CreateDecimalCell(headers[col].ToString(), obj.ToString(), index, stylesPart.Stylesheet); FormatedNumberCell c = new FormatedNumberCell(headers[col].ToString(), obj.ToString(), index); r.Append(c); } else { long value; if (long.TryParse(obj.ToString(), out value)) { //Cell datazell = CreateIntegerCell(headers[col].ToString(), obj.ToString(), index); NumberCell c = new NumberCell(headers[col].ToString(), obj.ToString(), index); r.Append(c); } else { //Cell datazell = CreateTextCell(headers[col].ToString(), obj.ToString(), index); TextCell c = new TextCell(headers[col].ToString(), obj.ToString(), index); r.Append(c); } } } } } sheetData.Append(r); } index++; Row total = new Row(); total.RowIndex = (uint)index; for (int col = 0; col < numCols; col++) { var obj1 = objects[0]; string fieldName = fields[col]; PropertyInfo myf = obj1.GetType().GetProperty(fieldName); if (myf != null) { object obj = myf.GetValue(obj1, null); if (obj != null) { if (col == 0) { //datazell = CreateTextCell(headers[col].ToString(), "Total", index); TextCell c = new TextCell(headers[col].ToString(), "Total", index); c.StyleIndex = 10; total.Append(c); } else if (obj.GetType() == typeof(decimal) || obj.GetType() == typeof(double)) { string headerCol = headers[col].ToString(); string firstRow = headerCol + "2"; string lastRow = headerCol + (numRows + 1); string formula = "=SUM(" + firstRow + " : " + lastRow + ")"; Console.WriteLine(formula); //datazell = CreateFomulaCell(headers[col].ToString(), formula, index, stylesPart.Stylesheet); FomulaCell c = new FomulaCell(headers[col].ToString(), formula, index); c.StyleIndex = 9; total.Append(c); } else { TextCell c = new TextCell(headers[col].ToString(), string.Empty, index); c.StyleIndex = 10; total.Append(c); } } } } sheetData.Append(total); } return(sheetData); }
/// <summary> /// Write excel file of a list of object as T /// Assume that maximum of 24 columns /// </summary> /// <typeparam name="T">Object type to pass in</typeparam> /// <param name="fileName">Full path of the file name of excel spreadsheet</param> /// <param name="objects">list of the object type</param> /// <param name="sheetName">Sheet names of Excel File</param> /// <param name="headerNames">Header names of the object</param> /// public void setColorforHeader <T>(string fileName, //List<T> objects, string sheetName, List <string> headerNames) { using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook)) { WorkbookPart workbookPart = myWorkbook.AddWorkbookPart(); WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>(); SheetData sheetData = new SheetData(); WorkbookStylesPart stylesPart = myWorkbook.WorkbookPart.AddNewPart <WorkbookStylesPart>(); Workbook workbook = new Workbook(); Stylesheet styles = new CustomStylesheet(); string relId = workbookPart.GetIdOfPart(worksheetPart); FileVersion fileVersion = new FileVersion { ApplicationName = "Microsoft Office Excel" }; styles.Save(stylesPart); List <string> fields = GetPropertyInfo <T>(); var az = new List <Char>(Enumerable.Range('A', 'Z' - 'A' + 1).Select(i => (Char)i).ToArray()); List <Char> headers = az.GetRange(0, fields.Count); //int numRows = objects.Count; int numCols = 2;//fields.Count; Row header = new Row(); int index = 1; header.RowIndex = (uint)index; for (int col = 0; col < numCols; col++) { //Cell c = CreateHeaderCell(headers[col].ToString(), headerNames[col], index, stylesPart.Stylesheet); HeaderCell c = new HeaderCell(headers[col].ToString(), headerNames[col], index, stylesPart.Stylesheet, System.Drawing.Color.Aqua, 12, true); header.Append(c); } sheetData.Append(header); Worksheet worksheet = new Worksheet(); int numColumns = headerNames.Count; int width = 0; Columns columns = new Columns(); for (int col = 0; col < numCols; col++) { if (col == 0) { width = 50; } else if (col == 1) { width = 30; } else if (col == 2) { width = 40; } //else if (col == 3) //{ // width = 72; //} //else if (col == 4) //{ // width = 16; //} //else if (col == 5) //{ // width = 12; //} //else if (col == 6) //{ // width = 15; //} Column c = CreateColumnData((UInt32)col + 1, (UInt32)numCols + 1, width); columns.Append(c); } Sheets sheets = new Sheets(); Sheet sheet = new Sheet { Name = sheetName, SheetId = 1, Id = relId }; sheets.Append(sheet); workbook.Append(fileVersion); workbook.Append(sheets); worksheet.Append(columns); worksheet.Append(sheetData); worksheetPart.Worksheet = worksheet; worksheetPart.Worksheet.Save(); myWorkbook.WorkbookPart.Workbook = workbook; myWorkbook.WorkbookPart.Workbook.Save(); myWorkbook.Close(); } }