/// <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 Create <T>(
string fileName,
List <T> objects,
string sheetName,
List <string> headerNames)
{
//Open the copied template workbook.
using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = myWorkbook.AddWorkbookPart();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
// Create Styles and Insert into Workbook
WorkbookStylesPart stylesPart = myWorkbook.WorkbookPart.AddNewPart <WorkbookStylesPart>();
Stylesheet styles = new CustomStylesheet();
styles.Save(stylesPart);
string relId = workbookPart.GetIdOfPart(worksheetPart);
Workbook workbook = new Workbook();
FileVersion fileVersion = new FileVersion {
ApplicationName = "Microsoft Office Excel"
};
SheetData sheetData = CreateSheetData <T>(objects, headerNames, stylesPart);
Worksheet worksheet = new Worksheet();
int numCols = headerNames.Count;
int width = headerNames.Max(h => h.Length) + 5;
Columns columns = new Columns();
for (int col = 0; col < numCols; col++)
{
Column c = CreateColumnData((UInt32)col + 1, (UInt32)numCols + 1, width);
columns.Append(c);
}
worksheet.Append(columns);
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(sheetData);
worksheetPart.Worksheet = worksheet;
worksheetPart.Worksheet.Save();
myWorkbook.WorkbookPart.Workbook = workbook;
myWorkbook.WorkbookPart.Workbook.Save();
myWorkbook.Close();
}
}
/// <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();
}
}
