public RevenueActuals Update(RevenueActuals revenueActuals)
{
var newRevenueActuals = _context.RevenueActuals.Update(revenueActuals);
if (newRevenueActuals != null && newRevenueActuals.State == EntityState.Added)
{
var affectedRows = _context.SaveChanges();
if (affectedRows > 0)
{
return(newRevenueActuals.Entity);
}
}
return(null);
}
public IActionResult RevenueActuals()
{
IFormFile file = Request.Form.Files[0];
string folderName = "UploadExcel";
string webRootPath = _hostingEnvironment.WebRootPath;
string newPath = Path.Combine(webRootPath, folderName);
//StringBuilder sb = new StringBuilder();
if (!Directory.Exists(newPath))
{
Directory.CreateDirectory(newPath);
}
if (file.Length > 0)
{
string sFileExtension = Path.GetExtension(file.FileName).ToLower();
ISheet sheet;
string fullPath = Path.Combine(newPath, file.FileName);
using (var stream = new FileStream(fullPath, FileMode.Create))
{
file.CopyTo(stream);
stream.Position = 0;
if (sFileExtension == ".xls")
{
HSSFWorkbook hssfwb = new HSSFWorkbook(stream); //This will read the Excel 97-2000 formats
sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook
}
else
{
XSSFWorkbook hssfwb = new XSSFWorkbook(stream); //This will read 2007 Excel format
sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook
}
IRow headerRow = sheet.GetRow(0); //Get Header Row
int cellCount = headerRow.LastCellNum;
//test only 3-4 row
for (int i = 1; i <= 685; i++)
{
var x = new RevenueActuals()
{
};
IRow row = sheet.GetRow(i);
if (row == null)
{
continue;
}
if (row.Cells.All(d => d.CellType == CellType.Blank))
{
continue;
}
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
//columns += row.GetCell(j).ToString();
//if (j == 0)
//{
// Name = row.GetCell(j).ToString();
//}
switch (j)
{
case 1:
x.Quarter = row.GetCell(j).ToString();
break;
case 5:
x.BusinessUnitL5 = row.GetCell(j).ToString();
break;
case 10:
x.EndCustomerName = row.GetCell(j).ToString();
break;
case 13:
x.RevKSadj = Convert.ToDouble(row.GetCell(j).ToString());
break;
case 14:
x.CosKSadj = Convert.ToDouble(row.GetCell(j).ToString());
break;
case 15:
x.Units = Convert.ToInt32(row.GetCell(j).ToString());
break;
default:
break;
}
}
}
_revenueActualsRepository.Add(x);
}
}
}
return(View("RevenueActuals", "Successfully uploaded revenue actuals"));
}