public void NullableTest() { var excel = new ExcelMapper(@"..\..\products.xlsx"); var products = excel.Fetch<NullableProduct>().ToList(); var nudossi = products[0]; Assert.AreEqual("Nudossi", nudossi.Name); Assert.AreEqual(60, nudossi.Number); Assert.AreEqual(1.99m, nudossi.Price); Assert.IsFalse(nudossi.Offer.Value); nudossi.OfferEnd = null; var halloren = products[1]; Assert.IsTrue(halloren.Offer.Value); Assert.AreEqual(new DateTime(2015, 12, 31), halloren.OfferEnd); halloren.Number = null; halloren.Offer = null; var file = "productsnullable.xlsx"; new ExcelMapper().Save(file, products, "Products"); var productsFetched = new ExcelMapper(file).Fetch<NullableProduct>().ToList(); CollectionAssert.AreEqual(products, productsFetched); }
public void FetchTest() { var products = new ExcelMapper(@"..\..\products.xlsx").Fetch<Product>().ToList(); CollectionAssert.AreEqual(new List<Product> { new Product { Name = "Nudossi", NumberInStock = 60, Price = 1.99m }, new Product { Name = "Halloren", NumberInStock = 33, Price = 2.99m }, new Product { Name = "Filinchen", NumberInStock = 100, Price = 0.99m }, }, products); }
public void FetchNoHeaderTest() { var products = new ExcelMapper(@"..\..\productsnoheader.xlsx") { HeaderRow = false }.Fetch<ProductNoHeader>("Products").ToList(); CollectionAssert.AreEqual(new List<ProductNoHeader> { new ProductNoHeader { Name = "Nudossi", NumberInStock = 60, Price = 1.99m }, new ProductNoHeader { Name = "Halloren", NumberInStock = 33, Price = 2.99m }, new ProductNoHeader { Name = "Filinchen", NumberInStock = 100, Price = 0.99m }, }, products); }
public void DataFormatTest() { var p = new DataFormatProduct { Date = new DateTime(2015, 12, 31), Number = 0.47m }; var file = "productsdataformat.xlsx"; new ExcelMapper().Save(file, new[] { p }); var pfs = new ExcelMapper(file).Fetch<DataFormatProduct>().ToList(); Assert.AreEqual(1, pfs.Count); var pf = pfs[0]; Assert.AreEqual(p.Date, pf.Date); Assert.AreEqual(p.Number, pf.Number); }
public async Task <string> GenerateExcel(string excelFilename, int?refId = null, int?id = null, DateTime?prDateFrom = null, DateTime?prDateTo = null, List <string> prNos = null, List <string> remarkss = null, Dictionary <string, int> exact = null, CancellationToken cancellationToken = default) { try { PurchaseRequestFilterSpecification filterSpec = null; if (id.HasValue) { filterSpec = new PurchaseRequestFilterSpecification(id.Value); } else { filterSpec = new PurchaseRequestFilterSpecification(exact) { Id = id, PrDateFrom = prDateFrom, PrDateTo = prDateTo, PrNos = prNos, Remarkss = remarkss } }.BuildSpecification(); var results = await this.ListAsync(filterSpec, null, true, cancellationToken); cancellationToken.ThrowIfCancellationRequested(); if (ExcelMapper.WriteToExcel <PurchaseRequest>(excelFilename, "purchaseRequest.json", results) == false) { if (refId.HasValue) { await _downloadProcessService.FailedToGenerate(refId.Value, "Failed to generate excel file"); } return(""); } // update database information (if needed) if (refId.HasValue) { excelFilename = Path.GetFileName(excelFilename); await _downloadProcessService.SuccessfullyGenerated(refId.Value, excelFilename); } return(excelFilename); }
public static SettingsData Settings(String property) { var userExcel = new ExcelMapper(@"C:\Users\rache\source\repos\Spec2\Spec2\Data.xls").Fetch <SettingsData>("Settings"); var userExcelData = userExcel.GetEnumerator(); while (userExcelData.MoveNext()) { if (userExcelData.Current.Property.Equals(ConfigurationManager.AppSettings["environment"] + property)) { SettingsData current = userExcelData.Current; return(current); } } Console.Write("Error Data not found for " + property + " #######"); return(null); }
public async Task <ActionResult> ReadMedicinesAsync() { string filePath = Directory.GetCurrentDirectory() + "\\Files\\nomenclator.xlsx"; var medicines = new ExcelMapper(filePath).Fetch <MedicineMapperDto>(); List <MedicineMapperDto> listOfMeds = medicines.ToList(); List <MedicineMapperDto> listOfMedsToInsert = new List <MedicineMapperDto>(); //1400 for (int i = 1; i < 466; i++) { listOfMedsToInsert.Add(listOfMeds[i * 3]); } var orderedList = listOfMedsToInsert.OrderBy(c => c.Id); int index = 1; foreach (var item in orderedList) { var med = new Medicine { Id = index, CimCode = item.CodCIM, CommercialName = item.DenumireComerciala, Name = item.DCI, PharmaceuticalForm = item.FormaFarmaceutica, Concentration = item.Concentratie, Producer = item.FirmaProducatoare, TerapeuticalAction = item.ActiuneTerapeutica, Valability = item.Valabilitate }; _context.Medicines.Add(med); index++; } if (await _context.SaveChangesAsync() > 0) { return(Ok(orderedList)); } else { return(BadRequest()); } }
public void FetchTest() { var products = new ExcelMapper(@"..\..\products.xlsx").Fetch <Product>().ToList(); CollectionAssert.AreEqual(new List <Product> { new Product { Name = "Nudossi", NumberInStock = 60, Price = 1.99m }, new Product { Name = "Halloren", NumberInStock = 33, Price = 2.99m }, new Product { Name = "Filinchen", NumberInStock = 100, Price = 0.99m }, }, products); }
public static UserData GetUserType(String userType) { var userExcel = new ExcelMapper("Data.xls").Fetch <UserData>("UserData"); var userExcelData = userExcel.GetEnumerator(); while (userExcelData.MoveNext()) { if (userExcelData.Current.UserType.Equals(ConfigurationManager.AppSettings["environment"] + userType)) { UserData current = userExcelData.Current; Console.Write("Data " + ConfigurationManager.AppSettings["environment"] + userType); return(current); } } Console.Write("Error Data not found" + userType + " #######"); return(null); }
public void FetchDoubleMap() { // https://github.com/mganss/ExcelMapper/issues/50 var products = new ExcelMapper(@"..\..\..\products.xlsx").Fetch <ProductDoubleMap>().ToList(); CollectionAssert.AreEqual(new List <ProductDoubleMap> { new ProductDoubleMap { Price = "Nudossi", OtherNumber = "60" }, new ProductDoubleMap { Price = "Halloren", OtherNumber = "33" }, new ProductDoubleMap { Price = "Filinchen", OtherNumber = "100" }, }, products); }
public async Task <string> GenerateExcel(string excelFilename, int?refId = null, int?id = null, List <string> names = null, List <int> parentModuleIds = null, Dictionary <string, int> exact = null, CancellationToken cancellationToken = default) { try { ModuleInfoFilterSpecification filterSpec = null; if (id.HasValue) { filterSpec = new ModuleInfoFilterSpecification(id.Value); } else { filterSpec = new ModuleInfoFilterSpecification(exact) { Id = id, Names = names, ParentModuleIds = parentModuleIds } }.BuildSpecification(); var results = await this.ListAsync(filterSpec, null, true, cancellationToken); cancellationToken.ThrowIfCancellationRequested(); if (ExcelMapper.WriteToExcel <ModuleInfo>(excelFilename, "moduleInfo.json", results) == false) { if (refId.HasValue) { await _downloadProcessService.FailedToGenerate(refId.Value, "Failed to generate excel file"); } return(""); } // update database information (if needed) if (refId.HasValue) { excelFilename = Path.GetFileName(excelFilename); await _downloadProcessService.SuccessfullyGenerated(refId.Value, excelFilename); } return(excelFilename); }
public async Task <string> GenerateExcel(string excelFilename, int?refId = null, string id = "", List <string> partNames = null, List <string> descriptions = null, Dictionary <string, int> exact = null, CancellationToken cancellationToken = default) { try { PartFilterSpecification filterSpec = null; if (!string.IsNullOrEmpty(id)) { filterSpec = new PartFilterSpecification(id); } else { filterSpec = new PartFilterSpecification(exact) { Id = id, PartNames = partNames, Descriptions = descriptions } }.BuildSpecification(); var results = await this.ListAsync(filterSpec, null, true, cancellationToken); cancellationToken.ThrowIfCancellationRequested(); if (ExcelMapper.WriteToExcel <Part>(excelFilename, "part.json", results) == false) { if (refId.HasValue) { await _downloadProcessService.FailedToGenerate(refId.Value, "Failed to generate excel file"); } return(""); } // update database information (if needed) if (refId.HasValue) { excelFilename = Path.GetFileName(excelFilename); await _downloadProcessService.SuccessfullyGenerated(refId.Value, excelFilename); } return(excelFilename); }
public void FetchWithTypeUsingStreamAndSheetNameTest() { var stream = new FileStream(@"..\..\..\products.xlsx", FileMode.Open, FileAccess.Read); var products = new ExcelMapper().Fetch(stream, typeof(Product), "Tabelle1"); CollectionAssert.AreEqual(new List <Product> { new Product { Name = "Nudossi", NumberInStock = 60, Price = 1.99m, Value = "C2*D2" }, new Product { Name = "Halloren", NumberInStock = 33, Price = 2.99m, Value = "C3*D3" }, new Product { Name = "Filinchen", NumberInStock = 100, Price = 0.99m, Value = "C5*D5" }, }, products); stream.Close(); }
public static List <TOCScreenshot> ReadTOC(string TOCPath) { if (string.IsNullOrWhiteSpace(TOCPath) || !File.Exists(TOCPath)) { throw new ArgumentException("Blank or non-existant path given", nameof(TOCPath)); } try { var res = new List <TOCScreenshot>(); XSSFWorkbook tocWorkbook; var sheetNames = new List <string>(); using (FileStream file = new FileStream(TOCPath, FileMode.Open, FileAccess.Read)) { tocWorkbook = new XSSFWorkbook(file); for (int i = 1; i < tocWorkbook.NumberOfSheets; i++) { sheetNames.Add(tocWorkbook.GetSheetName(i)); } } var tocChoices = new ExcelMapper(@TOCPath) { TrackObjects = false }; foreach (var sheetName in sheetNames) { var screenshot = new TOCScreenshot() { Filename = $"{sheetName}.png" }; screenshot.Choices = tocChoices.Fetch <TOCChoice>(sheetName).ToList(); res.Add(screenshot); } return(res); } catch (Exception x) { x.Data.Add(nameof(TOCPath), TOCPath); throw; } }
public ExcelMapperTests(DatafileFixture fixture) { _fixture = fixture; _propertyMapFactory = Substitute.For <IExcelPropertyMapFactory>(); _typeConverter = Substitute.For <ICellValueConverter>(); _modelBuilder = Substitute.For <IModelBuilder>(); _propertyInfoExtractor = Substitute.For <IPropertyInfoExtractor>(); _sut = new ExcelMapper(_fixture.GetWorkbook(Workbook), _propertyMapFactory, _typeConverter, _modelBuilder, Substitute.For <ILogger <ExcelMapper> >()); _propertyMapFactory.CreatePropertyMap <Person>(Arg.Any <IDataSheet>()).Returns(GetMappings()); _propertyMapFactory.CreatePropertyMap(Arg.Any <IDataSheet>(), Arg.Any <Action <IMemberSpec <Person> > >()) .Returns(GetMappings()); _propertyMapFactory.CreatePropertyMap <PersonWithMetadata>(Arg.Any <IDataSheet>()) .Returns(GetMappingsWithMetadata()); _propertyMapFactory.CreatePropertyMap(Arg.Any <IDataSheet>(), Arg.Any <Action <IMemberSpec <PersonWithMetadata> > >()) .Returns(GetMappingsWithMetadata()); _modelBuilder.Build <Person>().Returns(new Person()); _modelBuilder.Build <PersonWithMetadata>().Returns(CreateModel(), CreateModel()); }
public void FetchNoHeaderTest() { var products = new ExcelMapper(@"..\..\..\productsnoheader.xlsx") { HeaderRow = false }.Fetch <ProductNoHeader>("Products").ToList(); CollectionAssert.AreEqual(new List <ProductNoHeader> { new ProductNoHeader { Name = "Nudossi", NumberInStock = 60, Price = 1.99m }, new ProductNoHeader { Name = "Halloren", NumberInStock = 33, Price = 2.99m }, new ProductNoHeader { Name = "Filinchen", NumberInStock = 100, Price = 0.99m }, }, products); }
static void Main(string[] args) { ExcelMapper mapper = new ExcelMapper(); Student std1 = new Student(); string excelFileName = Path.Combine(Directory.GetCurrentDirectory(), "Book1.xls"); mapper.Read(std1, excelFileName); double sum = 0.0; int n = 0; foreach (string number in std1.Numbers) { sum += Convert.ToDouble(number); n++; } Console.WriteLine("Student: {0} {1}\nAverage of Numbers:{2}", std1.Name, std1.Family, sum / n); Console.ReadKey(); }
public void JsonMappedTest() { var excel = new ExcelMapper(@"..\..\..\productsjson.xlsx"); excel.AddMapping <ProductJsonMapped>("Product", p => p.Product).AsJson(); var products = excel.Fetch <ProductJsonMapped>().ToList(); CollectionAssert.AreEqual(new List <Product> { new Product { Name = "Nudossi", NumberInStock = 60, Price = 1.99m, Value = "C2*D2" }, new Product { Name = "Halloren", NumberInStock = 33, Price = 2.99m, Value = "C3*D3" }, new Product { Name = "Filinchen", NumberInStock = 100, Price = 0.99m, Value = "C4*D4" }, }, products.Select(p => p.Product)); }
public void FetchMinMaxTest() { var products = new ExcelMapper(@"..\..\..\ProductsMinMaxRow.xlsx") { HeaderRowNumber = 2, MinRowNumber = 6, MaxRowNumber = 9, }.Fetch <Product>().ToList(); CollectionAssert.AreEqual(new List <Product> { new Product { Name = "Nudossi", NumberInStock = 60, Price = 1.99m, Value = "C7*D7" }, new Product { Name = "Halloren", NumberInStock = 33, Price = 2.99m, Value = "C8*D8" }, new Product { Name = "Filinchen", NumberInStock = 100, Price = 0.99m, Value = "C10*D10" }, }, products); }
public void ColumnTestUsingFetchWithType() { var excel = new ExcelMapper(@"..\..\..\dataitems.xlsx") { HeaderRow = false }; var items = excel.Fetch(typeof(DataItem)).OfType <DataItem>().ToList(); var trackedFile = "dataitemstracked1.xlsx"; excel.Save(trackedFile, "DataItems"); var itemsTracked = excel.Fetch(trackedFile, typeof(DataItem), "DataItems").OfType <DataItem>().ToList(); CollectionAssert.AreEqual(items, itemsTracked); var saveFile = "dataitemssave1.xlsx"; new ExcelMapper().Save(saveFile, items, "DataItems"); var itemsSaved = new ExcelMapper().Fetch(saveFile, typeof(DataItem), "DataItems").OfType <DataItem>().ToList(); CollectionAssert.AreEqual(items, itemsSaved); }
public void GetterSetterTest() { var excel = new ExcelMapper(@"..\..\..\productsconvert.xlsx") { TrackObjects = true }; excel.AddMapping <GetterSetterProduct>("Name", p => p.Name); excel.AddMapping <GetterSetterProduct>("OfferEnd", p => p.OfferEnd) .SetCellUsing((c, o) => { if (o == null) { c.SetCellValue("NULL"); } else { c.SetCellValue((DateTime)o); } }) .SetPropertyUsing(v => { if ((v as string) == "NULL") { return(null); } return(Convert.ChangeType(v, typeof(DateTime), CultureInfo.InvariantCulture)); }); var products = excel.Fetch <GetterSetterProduct>().ToList(); var file = @"productsconverttracked.xlsx"; excel.Save(file); var productsFetched = new ExcelMapper(file).Fetch <GetterSetterProduct>().ToList(); CollectionAssert.AreEqual(products, productsFetched); }
public void ColumnTest() { var excel = new ExcelMapper(@"..\..\..\dataitems.xlsx") { HeaderRow = false }; var items = excel.Fetch <DataItem>().ToList(); var trackedFile = "dataitemstracked.xlsx"; excel.Save(trackedFile, "DataItems"); var itemsTracked = excel.Fetch <DataItem>(trackedFile, "DataItems").ToList(); CollectionAssert.AreEqual(items, itemsTracked); var saveFile = "dataitemssave.xlsx"; new ExcelMapper().Save(saveFile, items, "DataItems"); var itemsSaved = new ExcelMapper().Fetch <DataItem>(saveFile, "DataItems").ToList(); CollectionAssert.AreEqual(items, itemsSaved); }
public void SaveTrackedObjectsTest() { var excel = new ExcelMapper(@"..\..\..\products.xlsx") { TrackObjects = true }; excel.AddMapping(typeof(ProductMapped), "Name", "NameX"); excel.AddMapping <ProductMapped>("Number", p => p.NumberX); excel.AddMapping <ProductMapped>("Price", p => p.PriceX); var products = excel.Fetch <ProductMapped>().ToList(); products[1].PriceX += 1.0m; var file = @"productssavetracked.xlsx"; excel.Save(file); var productsFetched = new ExcelMapper(file).Fetch <ProductMapped>().ToList(); CollectionAssert.AreEqual(products, productsFetched); }
public void SaveTest() { var products = new List <Product> { new Product { Name = "Nudossi", NumberInStock = 60, Price = 1.99m, Value = "C2*D2" }, new Product { Name = "Halloren", NumberInStock = 33, Price = 2.99m, Value = "C3*D3" }, new Product { Name = "Filinchen", NumberInStock = 100, Price = 0.99m, Value = "C4*D4" }, }; var file = "productssave.xlsx"; new ExcelMapper().Save(file, products, "Products"); var productsFetched = new ExcelMapper(file).Fetch <Product>().ToList(); CollectionAssert.AreEqual(products, productsFetched); }
private void Settings() { //this.startTimeSpan = TimeSpan.FromSeconds(5); //this.periodTimeSpan = TimeSpan.FromHours(1); this.startTimeSpan = TimeSpan.FromSeconds(5); this.periodTimeSpan = TimeSpan.FromHours(1); this.timerCallback = new TimerCallback( (e) => { //if (DateTime.Now.Hour == 9 || DateTime.Now.Hour == 10) if (DateTime.Now.Hour == 14 || DateTime.Now.Hour == 19) { RunAsync(e); } else { this.Logger.WriteEntry($"On {nameof(Settings)}: It's not time to execute thread", EventLogEntryType.Information); } }); this.mapper = new ExcelMapper <Circuito>(); this.timer = new System.Threading.Timer(this.timerCallback, this.mapper, Timeout.Infinite, Timeout.Infinite); }
private static async Task <bool> RunExportAndUploadTaskAsync <T>(List <T> _list) where T : class { bool exported = false; if (_list != null && _list.Count > 0) { ExcelMapper <T> _excel_mapper = new ExcelMapper <T>(); exported = await _excel_mapper.Export(_list); if (exported) { string exportedFile = _excel_mapper.OldPath; Console.WriteLine(exportedFile); ClienteFTP ftpClient = new ClienteFTP(exportedFile); bool wasUploaded = ftpClient.UploadFile(); if (wasUploaded) { Console.WriteLine($"{Path.GetFileName(exportedFile)} uploaded successfully"); } } } return(exported); }
public IEnumerable <Person> updateFile() { String file = "C:\\Users\\prataps\\source\\repos\\TestExcel.xlsx"; List <Person> persons = ReadStream().ToList(); if (persons == null) { persons = new List <Person>(); } persons.Add(new Person() { Name = "Rahul" + (persons.Count + 1), Gender = "Male" }); var excel = new ExcelMapper() { TrackObjects = true }; excel.Save(file, persons, "Customers"); return(persons); }
public void IgnoreTest() { var excel = new ExcelMapper(@"..\..\products.xlsx"); excel.Ignore<IgnoreProduct>(p => p.Price); var products = excel.Fetch<IgnoreProduct>().ToList(); var nudossi = products[0]; Assert.AreEqual("Nudossi", nudossi.Name); Assert.AreEqual(0, nudossi.Number); Assert.AreEqual(0m, nudossi.Price); Assert.IsFalse(nudossi.Offer); var halloren = products[1]; Assert.IsTrue(halloren.Offer); Assert.AreEqual(new DateTime(2015, 12, 31), halloren.OfferEnd); var file = "productsignored.xlsx"; new ExcelMapper().Save(file, products, "Products"); var productsFetched = new ExcelMapper(file).Fetch<IgnoreProduct>().ToList(); CollectionAssert.AreEqual(products, productsFetched); }
public IEnumerable <Person> Read() { String file_name = "C:\\Users\\prataps\\source\\repos\\TestExcel.xlsx"; //FileStream file_stream = new FileStream(file_name, FileMode.Create); var excel = new ExcelMapper() { TrackObjects = true }; var persons = excel.Fetch <Person>(file_name, "Customers").ToList(); persons[3].Gender = "Female"; //saving first time String fileOut = "C:\\Users\\prataps\\source\\repos\\TestExcel_out.xlsx"; excel.Save(fileOut, "Customers"); //change the values persons[1].Gender = "Unknown"; //saving for the second time excel.Save(fileOut, "Customers"); //changing the value for third time persons[2].Gender = "Dont Care"; //saving for the third time excel.Save(fileOut, persons); return(persons); }
/// <summary> /// Фабрика источников данных. /// </summary> /// <param name="sourceType">Тип источника данных</param> /// <param name="connection">Строка подключения к источнику данных. Может быть как строкой подключения к БД, так и путем к файлу.</param> /// <returns>Интерфейс работы с источником данных</returns> /// <exception cref="ArgumentOutOfRangeException"></exception> public static IEhrmannCore Create(SourceType sourceType, string connection) { IMapper mapper; switch (sourceType) { case SourceType.MSSQL: mapper = new MSSQLMapper(connection); break; case SourceType.Excel: mapper = new ExcelMapper(connection); break; case SourceType.Access: mapper = new AccessMapper(connection); break; default: throw new ArgumentOutOfRangeException(nameof(sourceType), sourceType, null); } return(new Erhmann(mapper)); }
private void StartButton_Click(object sender, RoutedEventArgs e) { // 读取Dxf文件内块的种类和数量 DxfMapper dxf = new DxfMapper(); dxf.ReadFile(dxfPath.Text); Dictionary <string, int> blocks = dxf.BlockCount(); // 读取配置文件中的键值对,把块名替换为编号 ConfigChange dte = new ConfigChange(); dte.MainPage = this; // 把当前页面传给工具类,工具类可以调用本页面内输出窗口 dte.ChangeMap(configPath.Text); Dictionary <string, int> datas = dte.BlockToId(blocks); // 把数据写入到模板中,并生成新文件在D盘根目录下 Dictionary <string, int> haveNo = ExcelMapper.WriteData(formworkPath.Text, datas); if (haveNo.Count != 0) { outputBox.AppendText("=======================\n"); this.outputBox.AppendText(haveNo + "没有在模板中找到对应的编号\n"); } MessageBox.Show("转换完成"); }
public void SaveFetchedTest() { var excel = new ExcelMapper(@"..\..\products.xlsx"); var products = excel.Fetch<Product>().ToList(); products[2].Price += 1.0m; var file = @"productssavefetched.xlsx"; excel.Save(file, products); var productsFetched = new ExcelMapper(file).Fetch<Product>().ToList(); CollectionAssert.AreEqual(products, productsFetched); }
public List <ConsumptionProduct> MassImportProducts() { var products = new ExcelMapper("wwwroot/db.xlsx").Fetch <ConsumptionProduct>(); return(products.Distinct().ToList()); }
public void SaveTest() { var products = new List<Product> { new Product { Name = "Nudossi", NumberInStock = 60, Price = 1.99m }, new Product { Name = "Halloren", NumberInStock = 33, Price = 2.99m }, new Product { Name = "Filinchen", NumberInStock = 100, Price = 0.99m }, }; var file = "productssave.xlsx"; new ExcelMapper().Save(file, products, "Products"); var productsFetched = new ExcelMapper(file).Fetch<Product>().ToList(); CollectionAssert.AreEqual(products, productsFetched); }
public void SaveTrackedObjectsTest() { var excel = new ExcelMapper(@"..\..\products.xlsx") { TrackObjects = true }; excel.AddMapping(typeof(ProductMapped), "Name", "NameX"); excel.AddMapping<ProductMapped>("Number", p => p.NumberX); excel.AddMapping<ProductMapped>("Price", p => p.PriceX); var products = excel.Fetch<ProductMapped>().ToList(); products[1].PriceX += 1.0m; var file = @"productssavetracked.xlsx"; excel.Save(file); var productsFetched = new ExcelMapper(file).Fetch<ProductMapped>().ToList(); CollectionAssert.AreEqual(products, productsFetched); }
public static string ConvertBins(Stream from, bool indent = false) { var bin = new ExcelMapper(from).Fetch <Bin>(); return(JsonConvert.SerializeObject(bin, GetFormatting(indent))); }
public static string ConverLocations(Stream from, bool indent = false) { var locations = new ExcelMapper(from).Fetch <Location>(); return(JsonConvert.SerializeObject(locations, GetFormatting(indent))); }
/// <summary> /// Import file danh mục /// </summary> /// <param name="stream"></param> /// <param name="createdBy"></param> /// <returns></returns> public virtual async Task <AppDomainImportResult> ImportTemplateFile(Stream stream, string createdBy) { AppDomainImportResult appDomainImportResult = new AppDomainImportResult(); var dataTable = SetDataTable(); using (var package = new ExcelPackage(stream)) { int totalFailed = 0; int totalSuccess = 0; var ws = package.Workbook.Worksheets.FirstOrDefault(); if (ws == null) { throw new Exception("Sheet name không tồn tại"); } var existItems = Queryable.Where(e => !e.Deleted); var catalogueMappers = new ExcelMapper(stream) { HeaderRow = false, MinRowNumber = 1 }.Fetch <CatalogueMapper>().ToList(); if (catalogueMappers != null && catalogueMappers.Any()) { List <string> codeImports = new List <string>(); foreach (var catalogueMapper in catalogueMappers) { int index = catalogueMappers.IndexOf(catalogueMapper); int resultIndex = index + 2; IList <string> errors = new List <string>(); if (string.IsNullOrEmpty(catalogueMapper.Code)) { errors.Add("Vui lòng nhập mã"); } if (existItems.Any(x => x.Code == catalogueMapper.Code) || codeImports.Any(x => x == catalogueMapper.Code)) { errors.Add("Mã đã tồn tại"); } if (string.IsNullOrEmpty(catalogueMapper.Name)) { errors.Add("Vui lòng nhập tên"); } if (errors.Any()) { ws.Cells["D" + resultIndex].Value = string.Join(", ", errors); totalFailed += 1; } else { ws.Cells["D" + resultIndex].Value = "Thành công"; totalSuccess += 1; E item = new E() { Code = catalogueMapper.Code, Name = catalogueMapper.Name, Description = catalogueMapper.Description, Deleted = false, Active = true, Created = DateTime.Now, CreatedBy = createdBy, }; codeImports.Add(catalogueMapper.Code); dataTable = AddDataTableRow(dataTable, item); } } } ws.Column(4).Hidden = false; ws.Cells.AutoFitColumns(); appDomainImportResult.Data = new { TotalSuccess = totalSuccess, TotalFailed = totalFailed, }; appDomainImportResult.ResultFile = package.GetAsByteArray(); } if (dataTable.Rows.Count > 0) { var connectionString = string.Format(configuration.GetConnectionString("MedicalDbContext")); using (SqlBulkCopy bc = new SqlBulkCopy(connectionString)) { bc.DestinationTableName = this.GetTableName(); bc.BulkCopyTimeout = 4000; await bc.WriteToServerAsync(dataTable); } } appDomainImportResult.Success = true; return(appDomainImportResult); }