//.....................................................................
/// <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();
}
}
