public void AddSaleFromFile(Stream inputStream, string fileName)
{
IWorkbook _iWorkbook = null;
if (fileName.Contains(".xlsx"))
{
_iWorkbook = new XSSFWorkbook(inputStream);
}
else if (fileName.Contains(".xls"))
{
_iWorkbook = new HSSFWorkbook(inputStream);
}
if (_iWorkbook != null)
{
string saleCodeTabName = ConfigurationManager.AppSettings["SaleCodeTabName"];
for (int i = 0; i < _iWorkbook.NumberOfSheets; i++)
{
if (_iWorkbook.GetSheetName(i).ToLower().Contains(saleCodeTabName.ToLower()))
{
ISheet sheet = _iWorkbook.GetSheetAt(i);
List<Sale> listProgram = new List<Sale>();
for (int j = 3; j <= sheet.LastRowNum; j++)
{
var row = sheet.GetRow(j);
if (row.GetCell(0) != null && !string.IsNullOrEmpty(row.GetCell(0).NumericCellValue.ToString()))
{
for (int k = 2; k <= row.LastCellNum; k++)
{
if (sheet.GetRow(2).GetCell(k) != null && row.GetCell(0).NumericCellValue.ToString() != "" && row.GetCell(0).NumericCellValue.ToString() != "0")
{
Sale sale = new Sale();
sale.ProductCode = Convert.ToInt32(row.GetCell(0).NumericCellValue.ToString());
sale.Date = Convert.ToDateTime(sheet.GetRow(2).GetCell(k).DateCellValue.ToString());
sale.Quantity = row.GetCell(k) != null ? Convert.ToInt32(row.GetCell(k).NumericCellValue.ToString()) : 0;
_iSaleRepository.InsertOrUpdate(sale);
}
}
}
}
_iSaleRepository.Save();
}
}
}
}
public void InsertOrUpdate(Sale Sale)
{
if (Sale.Id == default(int))
{
// New entity
context.Sales.Add(Sale);
}
else
{
var InDb = context.Programs.Find(Sale.Id);
// Activity does not exist in database and it's new one
if (InDb == null)
{
context.Sales.Add(Sale);
return;
}
// Activity already exist in database and modify it
context.Entry(InDb).CurrentValues.SetValues(Sale);
context.Entry(InDb).State = EntityState.Modified;
}
}