public async Task <IActionResult> InsertFromExcelCompany(IFormFile theExcel)
{
if (theExcel == null)
{
ModelState.AddModelError("No files selected", "Please select a file");
return(RedirectToAction(nameof(Index)));
}
ExcelPackage excel;
using (var memoryStream = new MemoryStream())
{
await theExcel.CopyToAsync(memoryStream);
excel = new ExcelPackage(memoryStream);
}
var workSheet = excel.Workbook.Worksheets[0];
var start = workSheet.Dimension.Start;
var end = workSheet.Dimension.End;
//Start a new list to hold imported objects
List <Company> companies = new List <Company>();
List <CompanyVendor> companyVendors = new List <CompanyVendor>();
List <CompanyContractor> companyContractors = new List <CompanyContractor>();
List <CompanyCustomer> companyCustomers = new List <CompanyCustomer>();
for (int row = start.Row + 1; row <= end.Row; row++)
{
//Check if employee exist in database already
string nameCompare = workSheet.Cells[row, 1].Text;
//string tempName = _context.Employees.FirstOrDefault(p => p.Email == emailCompare).Email;
if (_context.Companies.FirstOrDefault(p => p.Name == nameCompare) == null)
{
try
{
Company a = new Company
{
Name = workSheet.Cells[row, 1].Text,
Location = workSheet.Cells[row, 2].Text,
CreditCheck = workSheet.Cells[row, 3].Text == "1",
DateChecked = DateTime.Parse((workSheet.Cells[row, 4].Text == "") ? "1900-01-01" : workSheet.Cells[row, 4].Text),
BillingTermID = _context.BillingTerms.FirstOrDefault(b => b.Terms == workSheet.Cells[row, 5].Text).BillingTermID,
BillingTerm = _context.BillingTerms.FirstOrDefault(b => b.Terms == workSheet.Cells[row, 5].Text),
CurrencyID = _context.Currencies.FirstOrDefault(b => b.CurrencyName == workSheet.Cells[row, 6].Text).CurrencyID,
Currency = _context.Currencies.FirstOrDefault(b => b.CurrencyName == workSheet.Cells[row, 6].Text),
Phone = (workSheet.Cells[row, 7].Text != "") ? Convert.ToInt64(workSheet.Cells[row, 7].Text.Replace(")", "").Replace("(", "").Replace("-", "").Replace(" ", "")) : Convert.ToInt64("0"),
Website = workSheet.Cells[row, 8].Text,
BillingAddress1 = workSheet.Cells[row, 9].Text,
BillingAddress2 = workSheet.Cells[row, 10].Text,
BillingCity = workSheet.Cells[row, 11].Text,
BillingProvinceID = _context.Provinces.FirstOrDefault(b => b.ProvinceName == workSheet.Cells[row, 12].Text).ProvinceID,
BillingPostalCode = workSheet.Cells[row, 13].Text,
BillingCountryID = _context.Countries.FirstOrDefault(b => b.CountryName == workSheet.Cells[row, 14].Text).CountryID,
ShippingAddress1 = workSheet.Cells[row, 15].Text,
ShippingAddress2 = workSheet.Cells[row, 16].Text,
ShippingCity = workSheet.Cells[row, 17].Text,
ShippingProvinceID = _context.Provinces.FirstOrDefault(b => b.ProvinceName == workSheet.Cells[row, 18].Text).ProvinceID,
ShippingPostalCode = workSheet.Cells[row, 19].Text,
ShippingCountryID = _context.Countries.FirstOrDefault(b => b.CountryName == workSheet.Cells[row, 20].Text).CountryID,
Active = workSheet.Cells[row, 27].Text == "1",
Notes = workSheet.Cells[row, 28].Text
};
companies.Add(a);
}
catch (Exception)
{
ModelState.AddModelError("Error", "Error while parsing the file");
return(RedirectToAction(nameof(Index)));
}
}
}
_context.Companies.AddRange(companies);
_context.SaveChanges();
for (int row = start.Row + 1; row <= end.Row; row++)
{
try
{
CompanyCustomer e = new CompanyCustomer
{
CompanyID = _context.Companies.FirstOrDefault(e => e.Name == workSheet.Cells[row, 1].Text).CompanyID,
CustomerTypeID = _context.CustomerTypes.FirstOrDefault(e => e.Type == workSheet.Cells[row, 22].Text).CustomerTypeID
};
companyCustomers.Add(e);
CompanyVendor f = new CompanyVendor
{
CompanyID = _context.Companies.FirstOrDefault(f => f.Name == workSheet.Cells[row, 1].Text).CompanyID,
VendorTypeID = _context.VendorTypes.FirstOrDefault(f => f.Type == workSheet.Cells[row, 24].Text).VendorTypeID
};
companyVendors.Add(f);
CompanyContractor g = new CompanyContractor
{
CompanyID = _context.Companies.FirstOrDefault(g => g.Name == workSheet.Cells[row, 1].Text).CompanyID,
ContractorTypeID = _context.ContractorTypes.FirstOrDefault(g => g.Type == workSheet.Cells[row, 26].Text).ContractorTypeID
};
companyContractors.Add(g);
}
catch (Exception)
{
ModelState.AddModelError("Error", "Error while parsing the file");
return(RedirectToAction(nameof(Index)));
}
}
_context.CompanyCustomers.AddRange(companyCustomers);
_context.CompanyVendors.AddRange(companyVendors);
_context.CompanyContractors.AddRange(companyContractors);
_context.SaveChanges();
return(RedirectToAction(nameof(Index)));
}
public async Task <IActionResult> InsertFromExcel(IFormFile theExcel)
{
if (theExcel == null)
{
ModelState.AddModelError("No files selected", "Please select a file");
return(RedirectToAction(nameof(Index)));
}
ExcelPackage excel;
using (var memoryStream = new MemoryStream())
{
await theExcel.CopyToAsync(memoryStream);
excel = new ExcelPackage(memoryStream);
}
var workSheet = excel.Workbook.Worksheets[0];
var start = workSheet.Dimension.Start;
var end = workSheet.Dimension.End;
//Start a new list to hold imported objects
List <Employee> employees = new List <Employee>();
for (int row = start.Row + 1; row <= end.Row; row++)
{
//Check if employee exist in database already
string emailCompare = workSheet.Cells[row, 3].Text;
//string tempName = _context.Employees.FirstOrDefault(p => p.Email == emailCompare).Email;
if (_context.Employees.FirstOrDefault(p => p.Email == emailCompare) == null)
{
try
{
Employee a = new Employee
{
FirstName = workSheet.Cells[row, 1].Text,
LastName = workSheet.Cells[row, 2].Text,
JobPositionID = _context.JobPositions.FirstOrDefault(p => p.Position == workSheet.Cells[row, 15].Text).JobPositionID,
EmploymentTypeID = _context.EmploymentTypes.FirstOrDefault(t => t.Type == workSheet.Cells[row, 16].Text).EmploymentTypeID,
EmployeeAddress1 = workSheet.Cells[row, 9].Text,
EmployeeAddress2 = workSheet.Cells[row, 10].Text,
EmployeeProvinceID = _context.Provinces.FirstOrDefault(p => p.ProvinceName == workSheet.Cells[row, 13].Text).ProvinceID,
EmployeePostalCode = workSheet.Cells[row, 12].Text,
EmployeeCountryID = _context.Countries.FirstOrDefault(c => c.CountryName == workSheet.Cells[row, 14].Text).CountryID,
CellPhone = (workSheet.Cells[row, 5].Text != "") ? Convert.ToInt64(workSheet.Cells[row, 5].Text.Replace(")", "").Replace("(", "").Replace("-", "").Replace(" ", "")) : Convert.ToInt64("0"),
WorkPhone = (workSheet.Cells[row, 5].Text != "") ? Convert.ToInt64(workSheet.Cells[row, 5].Text.Replace(")", "").Replace("(", "").Replace("-", "").Replace(" ", "")): Convert.ToInt64("0"),
Email = (workSheet.Cells[row, 3].Text == "")?(workSheet.Cells[row, 1].Text + workSheet.Cells[row, 2].Text + "@hagerindustries.com") : workSheet.Cells[row, 3].Text,
DOB = DateTime.Parse((workSheet.Cells[row, 6].Text == "")? "1900-01-01": workSheet.Cells[row, 6].Text),
Notes = workSheet.Cells[row, 6].Text,
Wage = Convert.ToDecimal((workSheet.Cells[row, 7].Text == "")?"0": (workSheet.Cells[row, 7].Text)),
Expense = Convert.ToDecimal((workSheet.Cells[row, 8].Text == "")?"0": workSheet.Cells[row, 8].Text),
DateJoined = DateTime.Parse(workSheet.Cells[row, 17].Text),
KeyFob = int.Parse(workSheet.Cells[row, 24].Text),
EmergencyContactName = workSheet.Cells[row, 22].Text,
EmergencyContactPhone = (workSheet.Cells[row, 23].Text != "")?Convert.ToInt64(workSheet.Cells[row, 23].Text.Replace(")", "").Replace("(", "").Replace("-", "").Replace(" ", "")): Convert.ToInt64("0"),
Active = workSheet.Cells[row, 20].Text == "1"
};
employees.Add(a);
}
catch (Exception)
{
ModelState.AddModelError("Error", "Error while parsing the file");
return(RedirectToAction(nameof(Index)));
}
}
}
_context.Employees.AddRange(employees);
_context.SaveChanges();
return(RedirectToAction(nameof(Index)));
}