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); }