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