public int?UpdateStore(StoreCharacters store)
{
try
{
ExcelFile file;
using (FileStream stream = new FileStream(_path, FileMode.Open))
{
file = ExcelFile.Load(stream, LoadOptions.XlsxDefault);
var worksheet = file.Worksheets[0];
var row = worksheet.Rows[store.Id];
row.Cells[0].Value = store.Name;
row.Cells[1].Value = store.CountryCode;
row.Cells[2].Value = store.Email;
row.Cells[3].Value = store.StoreManagerName;
row.Cells[4].Value = store.StoreManagerLastName;
row.Cells[5].Value = store.StoreManagerEmail;
row.Cells[6].Value = store.Category;
row.Cells[7].Value = store.StockBackStore;
row.Cells[8].Value = store.StockFrontStore;
row.Cells[9].Value = store.StockShoppingWindow;
row.Cells[10].Value = store.StockAccuracy;
row.Cells[11].Value = store.OnFloorAvailability;
row.Cells[12].Value = store.StockMeanAgeDays;
}
file.Save(_path);
return(store.Id);
}
catch (Exception e)
{
return(null);
}
}
public IEnumerable <StoreCharacters> GetStores()
{
var result = new List <StoreCharacters>();
try
{
using (FileStream stream = new FileStream(_path, FileMode.Open))
{
var file = ExcelFile.Load(stream, LoadOptions.XlsxDefault);
var worksheet = file.Worksheets[0];
// Iterate through all rows in an Excel worksheet.
foreach (var row in worksheet.Rows)
{
if (row.Index == 0)
{
continue;
}
var line = new StoreCharacters()
{
Id = row.Index,
Name = row.AllocatedCells[0].Value.ToString(),
CountryCode = row.AllocatedCells[1].Value.ToString(),
Email = row.AllocatedCells[2].Value.ToString(),
StoreManagerName = row.AllocatedCells[3].Value.ToString(),
StoreManagerLastName = row.AllocatedCells[4].Value.ToString(),
StoreManagerEmail = row.AllocatedCells[5].Value.ToString(),
Category = (int)row.AllocatedCells[6].Value,
StockBackStore = (int)row.AllocatedCells[7].Value,
StockFrontStore = (int)row.AllocatedCells[8].Value,
StockShoppingWindow = (int)row.AllocatedCells[9].Value,
StockAccuracy = Convert.ToDouble(row.AllocatedCells[10].Value),
OnFloorAvailability = Convert.ToDouble(row.AllocatedCells[11].Value),
StockMeanAgeDays = (int)row.AllocatedCells[12].Value
};
line.TotalStock = line.StockBackStore + line.StockFrontStore + line.StockShoppingWindow;
result.Add(line);
}
}
return(result.OrderBy(x => x.Name));
}
catch (Exception e)
{
return(null);
}
}
public ActionResult <int> UpdateStore([FromBody] StoreCharacters store)
{
var errorMessage = ValidationHelper.ValidateStore(store);
if (errorMessage.Any())
{
return(BadRequest(errorMessage));
}
var id = excelDataHandler.UpdateStore(store);
if (id == null)
{
return(BadRequest("Error while updating data in excel file"));
}
return(Ok(id));
}
public static List <object> ValidateStore(StoreCharacters store)
{
var context = new ValidationContext(store, serviceProvider: null, items: null);
var results = new List <ValidationResult>();
Validator.TryValidateObject(store, context, results, validateAllProperties: true);
var errorMessage = new List <object>();
if (results.Any())
{
foreach (var res in results)
{
errorMessage.Add(res.ErrorMessage);
}
}
return(errorMessage);
}
