private List <ExcelDep> GetFileData(ExcelWorksheet worksheet, int rowCount, int colCount, List <Deparment> successDeps)
{
List <ExcelDep> data = new List <ExcelDep>();
for (int row = 2; row <= rowCount; row++)
{
ExcelDep excelDep = new ExcelDep();
for (int col = 1; col <= colCount; col++)
{
switch (col)
{
case 1:
excelDep.Company = Convert.ToString(worksheet.Cells[row, col].Value); break;
case 2:
excelDep.Manager = Convert.ToString(worksheet.Cells[row, col].Value); break;
case 3:
excelDep.Name = Convert.ToString(worksheet.Cells[row, col].Value); break;
case 4:
excelDep.WorkerCount = Convert.ToInt32(worksheet.Cells[row, col].Value); break;
case 5:
excelDep.Code = Convert.ToString(worksheet.Cells[row, col].Value); break;
default: break;
}
;
}
foreach (var item in successDeps)
{
if (item.Name == excelDep.Name)
{
excelDep.IsSuccess = true;
}
else
{
excelDep.IsSuccess = false;
}
}
data.Add(excelDep);
}
return(data);
}
public object SaveDepToDB(ExcelWorksheet worksheet)
{
int rowCount = worksheet.Dimension.Rows;
int ColCount = worksheet.Dimension.Columns;
int successCount = 0, badCount = 0;
List <Deparment> successDeparments = new List <Deparment>();
List <ExcelDep> data = new List <ExcelDep>();
var result = new object();
for (int row = 2; row <= rowCount; row++)
{
Deparment deparment = new Deparment();
ExcelDep excelDep = new ExcelDep();
for (int col = 1; col <= ColCount; col++)
{
switch (col)
{
case 1:
deparment.CompanyId = _commonAppService.GetCompId(worksheet.Cells[row, col].Value.ToString());
excelDep.Company = worksheet.Cells[row, col].Value.ToString(); break;
case 2: deparment.ManagerId = _commonAppService.GetUserId(worksheet.Cells[row, col].Value.ToString());
excelDep.Manager = worksheet.Cells[row, col].Value.ToString(); break;
case 3: deparment.Name = worksheet.Cells[row, col].Value.ToString();
excelDep.Name = worksheet.Cells[row, col].Value.ToString(); break;
case 4: deparment.WorkerCount = Convert.ToInt32(worksheet.Cells[row, col].Value);
excelDep.WorkerCount = Convert.ToInt32(worksheet.Cells[row, col].Value); break;
case 5: deparment.Code = worksheet.Cells[row, col].Value.ToString();
excelDep.Code = worksheet.Cells[row, col].Value.ToString(); break;
default: break;
}
;
}
if (deparment.CompanyId != 0 && deparment.ManagerId != 0)
{
if (!_commonAppService.IsExitDep(deparment.Name, deparment.CompanyId))
{
excelDep.IsSuccess = true;
successDeparments.Add(deparment);
successCount++;
}
else
{
excelDep.IsSuccess = false;
badCount++;
}
}
else
{
excelDep.IsSuccess = false;
badCount++;
}
data.Add(excelDep);
}
_ctx.Deparment.AddRange(successDeparments);
Company company = _ctx.Company.Find(successDeparments[0].CompanyId);
company.DeparmentCount = company.DeparmentCount + successCount;
_ctx.SaveChanges();
result = new
{
successCount,
badCount,
data
};
return(result);
}
