public void ImportExcelToPersonnelTest() { IExcelImporter impoerter = new ExcelPersonnelNameImporter(); ExcelStructreTable tbl = impoerter.GetTableTemplate(); var exelUrlWithWrongGroupids = "D:\\temp\\work\\personnel - Copy.xlsx"; Stream stream = File.OpenRead(exelUrlWithWrongGroupids); tbl.Confirmed = true; impoerter.ImportExcel(stream, tbl); impoerter.ValidateModels(); var WorkGroupscount = 0; var personnelcount = 0; using (var db = new EngineContext()) { WorkGroupscount = db.WorkGroups.Count(); personnelcount = db.Personnels.Count(); } impoerter.Save(); // آیا ذخیره می کند using (var db = new EngineContext()) { Assert.True(WorkGroupscount <= db.WorkGroups.Count()); Assert.True(personnelcount <= db.Personnels.Count()); WorkGroupscount = db.WorkGroups.Count(); personnelcount = db.Personnels.Count(); } impoerter.Save(); // باید اگر دوباره ذخیره کردیم ، باید اپدییت کند و جدید ذخیره نکند using (var db = new EngineContext()) { Assert.True(WorkGroupscount == db.WorkGroups.Count()); Assert.True(personnelcount == db.Personnels.Count()); } }
public void ImportExcelToPersonnelTestWrongFileStructure() { try { IExcelImporter impoerter = new ExcelPersonnelNameImporter(); ExcelStructreTable tbl = impoerter.GetTableTemplate(); var exelUrl = "D:\\temp\\work\\wrong.xlsx"; Stream stream = File.OpenRead(exelUrl); impoerter.ImportExcel(stream, tbl); Assert.True(false); } catch (ExcelImporterException E) { Assert.True(true); } }
public void ImportExcelToBiometryDataTestNotConfirmed() { IExcelImporter impoerter = new ExcelPersonnelNameImporter(); ExcelStructreTable tbl = impoerter.GetTableTemplate(); var exelUrl = "D:\\temp\\work\\personnel.xlsx"; Stream stream = File.OpenRead(exelUrl); impoerter.ImportExcel(stream, tbl); try { impoerter.ValidateModels(); Assert.True(false); } catch (ExcelImporterException e) { Assert.True(true); } }
public virtual ExcelStructreTable GetTableTemplate() { ExcelStructreTable tbl = new ExcelStructreTable(); var props = typeof(T).GetProperties() .Where(p => p.PropertyType == typeof(int) || p.PropertyType == typeof(long) || p.PropertyType == typeof(DateTime) || p.PropertyType == typeof(double) || p.PropertyType == typeof(float) || p.PropertyType == typeof(string) || p.PropertyType == typeof(byte) || p.PropertyType == typeof(byte[]) || p.PropertyType == typeof(TimeSpan) || p.PropertyType == typeof(int?) || p.PropertyType == typeof(long?) || p.PropertyType == typeof(DateTime?) || p.PropertyType == typeof(double?) || p.PropertyType == typeof(float?) || p.PropertyType == typeof(string) || p.PropertyType == typeof(byte?) || p.PropertyType == typeof(TimeSpan?) ); var c = 0; foreach (var propertyInfo in props) { tbl.Nodes.Add(new ExcelStructreTableNode { ColumnName = propertyInfo.Name, ColumnTranslate = propertyInfo.GetCustomAttributes(typeof(T), false) .Where(a => a is BaseAttribute) .Select(a => a as BaseAttribute) .Select(a => a.Name) .FirstOrDefault() ?? propertyInfo.Name, NumberInExcel = c++, }); } return(tbl); }
public void ImportExcelToPersonnelWrongGroupIds() { IExcelImporter impoerter = new ExcelPersonnelNameImporter(); ExcelStructreTable tbl = impoerter.GetTableTemplate(); var exelUrlWithWrongGroupids = "D:\\temp\\work\\personnel.xlsx"; Stream stream = File.OpenRead(exelUrlWithWrongGroupids); tbl.Confirmed = true; impoerter.ImportExcel(stream, tbl); try { impoerter.ValidateModels(); Assert.True(false); } catch (ExcelImporterException e) { Assert.True(true); } }
public virtual void ImportExcel(Stream stream, ExcelStructreTable tbl) { Table = tbl; Workbook workbook = new Workbook(); //Load workbook from stream workbook.LoadFromStream(stream); //Append a new sheet to worksheet and then name it int count = workbook.Worksheets.Count + 1; workbook.Worksheets.Create("Sheet" + count); var worksheet = workbook.Worksheets.FirstOrDefault(); if (worksheet == null) { throw new ExcelImporterException("هیچ worksheet ای یافت نشد"); } /// باید ببیند ساختار درخواستی با ساختار واقعی فایل یکسان باشد List <T> models = new List <T>(); foreach (var range in worksheet.Rows) { var model = new T(); for (var i = 0; i < range.Columns.Length; i++) { var node = tbl.Nodes.FirstOrDefault(n => n.NumberInExcel == i); if (node == null) { continue; } var rangeColumn = range.Columns[i]; var propertyInfo = model.GetType().GetProperties().FirstOrDefault(p => p.Name == node.ColumnName); if (propertyInfo == null) { throw new ExcelImporterException("پروپرتی یافت نشد"); } try { if (string.IsNullOrEmpty(rangeColumn.Value)) { if (node.IsRequired) { throw new ExcelImporterException( $@"مقدار نال برای ستون ضروری ارسال شده است - ستون {node.ColumnTranslate} نال است"); } } else { var val = Convert.ChangeType(rangeColumn.Value, propertyInfo.PropertyType); propertyInfo.SetValue(model, val); } } catch (Exception E) { throw new ExcelImporterException(node.ColumnTranslate + "ستون ها با اولویت های اشتباه وارد شده اند - "); } } models.Add(model); } this.Models = models; /*//Save workbook to disk * workbook.Save();*/ }