//private SheetData CreateSheetData<T>(List<T> objects, ExcelHeaderList headerTitles, WorkbookStylesPart stylesPart)
//{
// SheetData sheetData = new SheetData();
// if (objects != null)
// {
// List<string> fields = ObjUtility.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);
// Row header = new Row();
// int index = 1;
// header.RowIndex = (uint)index;
// foreach (ExcelHeader keyValuePair in headerTitles.Where(i => i.HeaderType == ExcelHeaderType.SingleProperty))
// {
// HeaderCell c = new HeaderCell(headers[headerTitles.ToList().IndexOf(keyValuePair)].ToString(),
// keyValuePair.Value,
// index,
// stylesPart.Stylesheet,
// System.Drawing.Color.DodgerBlue,
// 12,
// true);
// header.Append(c);
// }
// sheetData.Append(header);
// for (int i = 0; i < objects.Count; i++)
// {
// index++;
// var selectedObj = objects[i];
// var r = new Row { RowIndex = (uint)index };
// int headerIndex = 0;
// foreach (ExcelHeader keyValuePair in headerTitles.OrderBy(h => (byte)h.HeaderType))
// {
// PropertyInfo myf = selectedObj.GetType().GetProperty(keyValuePair.Key);
// if (myf != null)
// {
// object obj = myf.GetValue(selectedObj, null);
// if (obj != null)
// {
// if (obj.GetType() == typeof(string))
// {
// TextCell c = new TextCell(headers[headerIndex].ToString(),
// obj.ToString(),
// index);
// r.Append(c);
// }
// else if (obj.GetType() == typeof(bool))
// {
// string value = (bool)obj ? "Yes" : "No";
// TextCell c = new TextCell(headers[headerIndex].ToString(),
// value,
// index);
// r.Append(c);
// }
// else if (obj.GetType() == typeof(DateTime))
// {
// string value = ((DateTime)obj).ToOADate().ToString();
// DateCell c = new DateCell(headers[headerIndex].ToString(),
// (DateTime)obj,
// index);
// r.Append(c);
// }
// else if (obj.GetType() == typeof(decimal) || obj.GetType() == typeof(double))
// {
// FormatedNumberCell c = new FormatedNumberCell(headers[headerIndex].ToString(),
// obj.ToString(),
// index);
// r.Append(c);
// }
// else if (obj.GetType() == typeof(Dictionary<string, string>) && keyValuePair.HeaderType == ExcelHeaderType.ListProperty)
// {
// Dictionary<string, string> objList = (obj as Dictionary<string, string>);
// foreach (var VARIABLE in objList)
// {
// TextCell c = new TextCell(
// headers[headerIndex].ToString(),
// VARIABLE.Value,
// index);
// r.Append(c);
// HeaderCell hc = new HeaderCell(
// headers[headerIndex].ToString(),
// VARIABLE.Key,
// 1,
// stylesPart.Stylesheet,
// System.Drawing.Color.Brown,
// 12,
// true);
// header.Append(hc);
// headerIndex++;
// }
// }
// else
// {
// long value;
// if (long.TryParse(obj.ToString(), out value))
// {
// NumberCell c = new NumberCell(headers[headerIndex].ToString(),
// obj.ToString(),
// index);
// r.Append(c);
// }
// else
// {
// TextCell c = new TextCell(headers[headerIndex].ToString(),
// obj.ToString(),
// index);
// r.Append(c);
// }
// }
// }
// }
// headerIndex++;
// }
// sheetData.Append(r);
// }
// index++;
// }
// return sheetData;
//}
private SheetData CreateSheetData(IReadOnlyList <object> objects, ExcelHeaderList headerTitles, WorkbookStylesPart stylesPart)
{
var sheetData = new SheetData();
if (objects != null)
{
var header = new Row();
var index = 1;
header.RowIndex = (uint)index;
foreach (ExcelHeader keyValuePair in headerTitles.Where(i => i.HeaderType == ExcelHeaderType.SingleProperty))
{
HeaderCell c = new HeaderCell(Utility.IntToAlpha(headerTitles.ToList().IndexOf(keyValuePair) + 1),
keyValuePair.Value,
index,
stylesPart.Stylesheet,
System.Drawing.Color.DodgerBlue,
12,
true);
header.Append(c);
}
sheetData.Append(header);
for (int i = 0; i < objects.Count; i++)
{
index++;
var selectedObj = objects[i];
var row = new Row {
RowIndex = (uint)index
};
int headerIndex = 1;
foreach (ExcelHeader keyValuePair in headerTitles.OrderBy(h => (byte)h.HeaderType))
{
PropertyInfo myf = selectedObj.GetType().GetProperty(keyValuePair.Key);
if (myf != null)
{
object obj = myf.GetValue(selectedObj, null);
if (obj != null)
{
CreateDataCell(row, obj, headerIndex, ref index);
headerIndex++;
}
}
}
sheetData.Append(row);
}
index++;
}
return(sheetData);
}
public static List <T> ReadObjFromExel <T>(Stream stream, ExcelHeaderList pattern) where T : new()
{
return(ReadObjFromExel <T>(stream, pattern, ""));
}
// key : custom header name
// value : property original name
public static List <T> ReadObjFromExel <T>(Stream stream, ExcelHeaderList pattern, string sheetName) where T : new()
{
var result = new List <T>();
var columnHeaders = GetRowValues(stream, sheetName, "1");
var hasHeader = columnHeaders.Any(header => pattern != null && pattern.Any(i => i.Key.ToLower().Trim() == header.ToLower().Trim()));
var excelHeaders = new List <string>();
for (var i = 1; i <= columnHeaders.Count; i++)
{
excelHeaders.Add(Utility.IntToAlpha(i));
}
var rowsCount = excelHeaders.Select(header => GetColumnValues(stream, sheetName, header.ToString()).Count).Concat(new[] { 0 }).Max();
for (var i = hasHeader ? 1 : 0; i < rowsCount; i++)
{
var obj = new T();
#region has header
if (hasHeader)
{
foreach (var propName in columnHeaders.Where(p => pattern.Any(c => c.Key.ToLower() == p.ToLower())))
{
var prop = "";
var singleOrDefault = pattern.SingleOrDefault(k => k.Key.ToLower() == propName.ToLower());
if (singleOrDefault != null)
{
prop = singleOrDefault.Value;
}
var value = GetCellData(stream, sheetName,
string.Format("{0}{1}", excelHeaders[columnHeaders.IndexOf(propName)],
i + 1));
if (string.IsNullOrEmpty(prop))
{
continue;
}
try
{
var propertyInfo = obj.GetType().GetProperty(prop);
propertyInfo.SetValue(obj, value, null);
}
catch (Exception)
{ }
}
}
#endregion
#region has not hrader
else
{
var values = GetRowValues(stream, sheetName, (i + 1).ToString());
for (var j = 0; j < pattern.Count; j++)
{
var prop = pattern[j].Value;
try
{
var propertyInfo = obj.GetType().GetProperty(prop);
propertyInfo.SetValue(obj, values[j], null);
}
catch (Exception) { }
}
}
#endregion
result.Add(obj);
}
return(result);
}
/// <summary>
///
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="objects"></param>
/// <param name="sheetName">generated sheet name, Do not set empty value for this parameters</param>
/// <param name="headerNames"></param>
/// <returns></returns>
public Stream Do <T>(List <T> objects, string sheetName, ExcelHeaderList headerNames)
{
var stream = new MemoryStream();
sheetName = string.IsNullOrEmpty(sheetName) ? "Mayhedi_Sheet" : sheetName;
using (var document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook))
{
var workbookPart = document.AddWorkbookPart();
var worksheetPart = workbookPart.AddNewPart <WorksheetPart>(sheetName);
// Create Styles and Insert into Workbook
var stylesPart = document.WorkbookPart.AddNewPart <WorkbookStylesPart>();
Stylesheet styles = new CustomStylesheet();
styles.Save(stylesPart);
var relId = workbookPart.GetIdOfPart(worksheetPart);
var workbook = new Workbook();
var fileVersion = new FileVersion {
ApplicationName = "Microsoft Office Excel"
};
var data = objects.Select(o => (object)o).ToList();
if (headerNames == null)
{
headerNames = new ExcelHeaderList();
foreach (var o in ObjUtility.GetPropertyInfo(objects[0]))
{
headerNames.Add(o, o);
}
}
var sheetData = CreateSheetData(data, headerNames, stylesPart);
var worksheet = new Worksheet();
var numCols = headerNames.Count;
var width = 20;//headerNames.Max(h => h.Length) + 5;
var columns = new Columns();
for (var col = 0; col < numCols; col++)
{
var c = CreateColumnData((UInt32)col + 1, (UInt32)numCols + 1, width);
if (c != null)
{
columns.Append(c);
}
}
worksheet.Append(columns);
var sheets = new Sheets();
var 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();
document.WorkbookPart.Workbook = workbook;
document.WorkbookPart.Workbook.Save();
document.Close();
}
return(stream);
}
//private SheetData CreateSheetData<T>(List<T> objects, ExcelHeaderList headerTitles, WorkbookStylesPart stylesPart)
//{
// SheetData sheetData = new SheetData();
// if (objects != null)
// {
// List<string> fields = ObjUtility.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);
// Row header = new Row();
// int index = 1;
// header.RowIndex = (uint)index;
// foreach (ExcelHeader keyValuePair in headerTitles.Where(i => i.HeaderType == ExcelHeaderType.SingleProperty))
// {
// HeaderCell c = new HeaderCell(headers[headerTitles.ToList().IndexOf(keyValuePair)].ToString(),
// keyValuePair.Value,
// index,
// stylesPart.Stylesheet,
// System.Drawing.Color.DodgerBlue,
// 12,
// true);
// header.Append(c);
// }
// sheetData.Append(header);
// for (int i = 0; i < objects.Count; i++)
// {
// index++;
// var selectedObj = objects[i];
// var r = new Row { RowIndex = (uint)index };
// int headerIndex = 0;
// foreach (ExcelHeader keyValuePair in headerTitles.OrderBy(h => (byte)h.HeaderType))
// {
// PropertyInfo myf = selectedObj.GetType().GetProperty(keyValuePair.Key);
// if (myf != null)
// {
// object obj = myf.GetValue(selectedObj, null);
// if (obj != null)
// {
// if (obj.GetType() == typeof(string))
// {
// TextCell c = new TextCell(headers[headerIndex].ToString(),
// obj.ToString(),
// index);
// r.Append(c);
// }
// else if (obj.GetType() == typeof(bool))
// {
// string value = (bool)obj ? "Yes" : "No";
// TextCell c = new TextCell(headers[headerIndex].ToString(),
// value,
// index);
// r.Append(c);
// }
// else if (obj.GetType() == typeof(DateTime))
// {
// string value = ((DateTime)obj).ToOADate().ToString();
// DateCell c = new DateCell(headers[headerIndex].ToString(),
// (DateTime)obj,
// index);
// r.Append(c);
// }
// else if (obj.GetType() == typeof(decimal) || obj.GetType() == typeof(double))
// {
// FormatedNumberCell c = new FormatedNumberCell(headers[headerIndex].ToString(),
// obj.ToString(),
// index);
// r.Append(c);
// }
// else if (obj.GetType() == typeof(Dictionary<string, string>) && keyValuePair.HeaderType == ExcelHeaderType.ListProperty)
// {
// Dictionary<string, string> objList = (obj as Dictionary<string, string>);
// foreach (var VARIABLE in objList)
// {
// TextCell c = new TextCell(
// headers[headerIndex].ToString(),
// VARIABLE.Value,
// index);
// r.Append(c);
// HeaderCell hc = new HeaderCell(
// headers[headerIndex].ToString(),
// VARIABLE.Key,
// 1,
// stylesPart.Stylesheet,
// System.Drawing.Color.Brown,
// 12,
// true);
// header.Append(hc);
// headerIndex++;
// }
// }
// else
// {
// long value;
// if (long.TryParse(obj.ToString(), out value))
// {
// NumberCell c = new NumberCell(headers[headerIndex].ToString(),
// obj.ToString(),
// index);
// r.Append(c);
// }
// else
// {
// TextCell c = new TextCell(headers[headerIndex].ToString(),
// obj.ToString(),
// index);
// r.Append(c);
// }
// }
// }
// }
// headerIndex++;
// }
// sheetData.Append(r);
// }
// index++;
// }
// return sheetData;
//}
private SheetData CreateSheetData(IReadOnlyList<object> objects, ExcelHeaderList headerTitles, WorkbookStylesPart stylesPart)
{
var sheetData = new SheetData();
if (objects != null)
{
var header = new Row();
var index = 1;
header.RowIndex = (uint)index;
foreach (ExcelHeader keyValuePair in headerTitles.Where(i => i.HeaderType == ExcelHeaderType.SingleProperty))
{
HeaderCell c = new HeaderCell(Utility.IntToAlpha(headerTitles.ToList().IndexOf(keyValuePair) + 1),
keyValuePair.Value,
index,
stylesPart.Stylesheet,
System.Drawing.Color.DodgerBlue,
12,
true);
header.Append(c);
}
sheetData.Append(header);
for (int i = 0; i < objects.Count; i++)
{
index++;
var selectedObj = objects[i];
var row = new Row { RowIndex = (uint)index };
int headerIndex = 1;
foreach (ExcelHeader keyValuePair in headerTitles.OrderBy(h => (byte)h.HeaderType))
{
PropertyInfo myf = selectedObj.GetType().GetProperty(keyValuePair.Key);
if (myf != null)
{
object obj = myf.GetValue(selectedObj, null);
if (obj != null)
{
CreateDataCell(row, obj, headerIndex, ref index);
headerIndex++;
}
}
}
sheetData.Append(row);
}
index++;
}
return sheetData;
}
