private string SetObjValue <T>(T container, DefinedNameVal item, WorkbookPart workbookPart) where T : class, new()
{
// Retrieve a reference to the worksheet part.
WorksheetPart wsPart =
(WorksheetPart)(workbookPart.GetPartById(workbookPart.Workbook.Descendants <Sheet>().First().Id));
// Use its Worksheet property to get a reference to the cell
// whose address matches the address you supplied.
Cell theCell = wsPart.Worksheet.Descendants <Cell>().
Where(c => c.CellReference == item.Reference).FirstOrDefault();
var value = GetCellValue(workbookPart, theCell);
if (!string.IsNullOrEmpty(value) && (item.Parameter != null))
{
try
{
SetObjValue <T>(container, item, value);
}
catch (Exception ex)
{
theCell.CellValue = new CellValue(string.Format("{0}({1})", value, ex.Message));
theCell.StyleIndex = errCellStyleIndex;
HasError = true;
workbookPart.Workbook.Save();
//workbookPart
}
}
return(value);
}
private static WorksheetPart GetWorkSheetPart(WorkbookPart workbookPart, DefinedNameVal definedName)
{
//Get worksheet based on defined name.
string relId = workbookPart.Workbook.Descendants <Sheet>().Where(s => definedName.SheetName.Equals(s.Name)).First().Id;
return((WorksheetPart)workbookPart.GetPartById(relId));
}
private static void GetSheetParameter(WorkbookPart workbookPart, DefinedNameVal defName)
{
defName.Parameter = JsonConvert.DeserializeObject <SheetParameter>(
GetCellValue(GetWorkSheetPart(workbookPart, defName),
workbookPart.GetPartsOfType <SharedStringTablePart>().FirstOrDefault(),
defName.StartColumn, defName.StartRow));
}
private void SetObjValue <T>(T container, DefinedNameVal item, string value) where T : class, new()
{
switch (item.Parameter.Type)
{
case "FK": //Foreignkey
value = GetFKValue(container, item, value);
break;
case "Tbl": //Foreignkey
value = GetTblFKValue(container, item, value);
break;
case "Query": //Foreignkey
value = GetQueryFKValue(container, item, value);
break;
}
if (!string.IsNullOrEmpty(value))
{
// Create the parameter for the ObjectType (typically the 'x' in your expression (x => 'x')
// The "parm" string is used strictly for debugging purposes
ParameterExpression param = Expression.Parameter(container.GetType(), "parm");
MemberExpression member = Expression.Property(param, item.Parameter.Field);
ConstantExpression constant;
if (member.Type.IsGenericType && member.Type.GetGenericTypeDefinition().Equals(typeof(Nullable <>)))
{
if (Nullable.GetUnderlyingType(member.Type) == typeof(DateTime) && !value.Contains("/"))
{
constant = Expression.Constant(Convert.ChangeType(FromExcelSerialDate(Convert.ToInt32(value)), Nullable.GetUnderlyingType(member.Type), cultureInfo));
}
else
{
constant = Expression.Constant(Convert.ChangeType(value, Nullable.GetUnderlyingType(member.Type), cultureInfo));
}
Expression.Assign(member, Expression.Convert(constant, member.Type));
BinaryExpression assignExp = Expression.Assign(member, Expression.Convert(constant, member.Type));
var actionT = typeof(Action <>).MakeGenericType(container.GetType());
//Delegate.CreateDelegate(actionT, container.GetType().GetMethod("Invoke"))
Expression.Lambda <Action <T> >(assignExp, param)
.Compile()
.Invoke(container);
}
else
{
if (member.Type == typeof(DateTime) && !value.Contains("/"))
{
constant = Expression.Constant(Convert.ChangeType(FromExcelSerialDate(Convert.ToInt32(value)), member.Type, cultureInfo));
}
else
{
constant = Expression.Constant(Convert.ChangeType(value, member.Type, cultureInfo));
}
BinaryExpression assignExp = Expression.Assign(member, constant);
Expression.Lambda <Action <T> >(assignExp, param)
.Compile()
.Invoke(container);
}
}
}
private string GetQueryFKValue <T>(T container, DefinedNameVal item, string value) where T : class, new()
{
//if (Db != null)
//{
// var fkValue = Db.Database.SqlQuery<string>(item.Parameter.Query, value).FirstOrDefault();
// if (!string.IsNullOrEmpty(fkValue)) return fkValue;
//}
return(value);
}
private string GetTblFKValue <T>(T container, DefinedNameVal item, string value) where T : class, new()
{
//if (Db != null)
//{
// var fkValue = Db.Database.SqlQuery<string>(string.Format("Select CONVERT(varchar, {0}) From {1} Where CONVERT(nvarchar, {2}) = N'{3}'", item.Parameter.FkKey, item.Parameter.FkObj, item.Parameter.FkText, value)).FirstOrDefault();
// if (!string.IsNullOrEmpty(fkValue)) return fkValue;
//}
return(value);
}
static List <DefinedNameVal> BuildDefinedNamesTable(WorkbookPart workbookPart, int sheetId)
{
//Build a list.
List <DefinedNameVal> definedNames = new List <DefinedNameVal>();
foreach (DefinedName name in workbookPart.Workbook.DefinedNames)
{
if ((!name.LocalSheetId.HasValue) || (name.LocalSheetId.Value == sheetId))
{
var defName = new DefinedNameVal(name);
defName.SheetId = sheetId.ToString();
if ((!string.IsNullOrEmpty(defName.EndColumn)) && (defName.EndColumn != defName.StartColumn))
{
defName.IsRange = true;
if (name.Comment != null)
{
defName.Parameter = JsonConvert.DeserializeObject <SheetParameter>(name.Comment.InnerText);
defName.Columns = new List <DefinedNameVal>();
var row = GetWorkSheetPart(workbookPart, defName).Worksheet.GetFirstChild <SheetData>().Descendants <Row>().FirstOrDefault(r => ((Row)r).RowIndex == Convert.ToInt32(defName.StartRow));
if (row != null)
{
foreach (var cell in row.Descendants <Cell>())
{
var value = GetValue(cell, workbookPart.GetPartsOfType <SharedStringTablePart>().FirstOrDefault());
if (!string.IsNullOrEmpty(value) && value.StartsWith("{") && value.EndsWith("}"))
{
defName.Columns.Add(new DefinedNameVal
{
Key = cell.CellReference,
Reference = cell.CellReference,
SheetId = defName.SheetId,
SheetName = defName.SheetName,
StartRow = defName.StartRow,
StartColumn = GetColumnName(cell.CellReference),
Parameter = JsonConvert.DeserializeObject <SheetParameter>(value)
});
}
}
}
}
}
else
{
GetSheetParameter(workbookPart, defName);
}
definedNames.Add(defName);
}
}
return(definedNames);
}
private string GetFKValue <T>(T container, DefinedNameVal item, string value) where T : class, new()
{
//if (Db != null)
//{
// ParameterExpression param = Expression.Parameter(Db.GetType(), "parm");
// MemberExpression member = Expression.Property(param, item.Parameter.FkObj);
// if ((member.Type != typeof(string)) && typeof(IEnumerable).IsAssignableFrom(member.Type))
// {
// //Db.Set(member.Type).ForEachAsync()
// Type listType = typeof(List<>).MakeGenericType(new[] { member.Type });
// IList list = (IList)Activator.CreateInstance(listType);
// throw new NotSupportedException();
// }
//}
return(value);
}
private int FillRangeObjs <T>(T container, DefinedNameVal item, WorkbookPart workbookPart) where T : class, new()
{
var count = 0;
ParameterExpression param = Expression.Parameter(typeof(T), "parm");
MemberExpression member = Expression.Property(param, item.Parameter.Field);
if ((member.Type != typeof(string)) && typeof(IEnumerable).IsAssignableFrom(member.Type))
{
Type listType = typeof(List <>).MakeGenericType(new[] { member.Type.GetGenericArguments()[0] });
dynamic list = Activator.CreateInstance(listType);
var stop = false;
while (!stop)
{
dynamic obj = Activator.CreateInstance(member.Type.GetGenericArguments()[0]);
var adding = false;
foreach (var col in item.Columns)
{
var value = SetObjValue(obj, col, workbookPart);
if (!string.IsNullOrEmpty(value))
{
adding = true;
}
}
if (adding)
{
count++;
list.Add(obj);
}
stop = !adding;
item.ShiftRows(1);
}
((PropertyInfo)member.Member).SetValue(container, list);
}
return(count);
}
