public IList <Customers> ImportCustomer() { string rootFolder = _hostingEnvironment.WebRootPath; string fileName = @"ImportCustomers.xlsx"; FileInfo file = new FileInfo(Path.Combine(rootFolder, fileName)); using (ExcelPackage package = new ExcelPackage(file)) { ExcelWorksheet workSheet = package.Workbook.Worksheets["Customer"]; int totalRows = workSheet.Dimension.Rows; List <Customers> customerList = new List <Customers>(); for (int i = 2; i <= totalRows; i++) { customerList.Add(new Customers { CustomerName = workSheet.Cells[i, 1].Value.ToString(), CustomerEmail = workSheet.Cells[i, 2].Value.ToString(), CustomerCountry = workSheet.Cells[i, 3].Value.ToString() }); } _db.Customers.AddRange(customerList); _db.SaveChanges(); return(customerList); } }
public IActionResult Post(List <IFormFile> files) { try { long size = files.Sum(f => f.Length); var filePath = Path.GetTempFileName(); var test = Path.GetDirectoryName(filePath) + Path.DirectorySeparatorChar; var fileName = Path.GetFileName(filePath); var result = Path.ChangeExtension(fileName, ".xlsx"); foreach (var formFile in files) { if (formFile.Length <= 0) { continue; } using (var stream = new FileStream(filePath, FileMode.Create)) { formFile.CopyToAsync(stream); } } FileInfo x = new FileInfo(filePath); x.MoveTo(Path.ChangeExtension(filePath, ".xlsx")); Debug.Write(filePath); FileInfo file = new FileInfo(Path.Combine(test, result)); //DEBUG NAMEN NAAR CONSOLE //Debug.WriteLine("\n"); //Debug.WriteLine("!!!!!!!!!!!!!!!!!!!!!!"); //Debug.WriteLine(filePath); //Debug.WriteLine(test); //Debug.WriteLine(result); //Debug.WriteLine("!!!!!!!!!!!!!!!!!!!!!! \n\n"); using (ExcelPackage package = new ExcelPackage(file)) { response model = new response(); ExcelWorksheet workSheet = package.Workbook.Worksheets[0]; int totalRows = workSheet.Dimension.Rows; //Kijkt of het ease excel template word gebruikt string check = workSheet.Cells[1, 1].Value.ToString(); if (check != "ease_import_sheet") { model.answer = "De sheet die u heeft geselecteerd is niet geldig."; return(View(model)); } List <Issue> issuelist = new List <Issue>(); List <Data> datas = new List <Data>(); List <Doubles> dubbel = new List <Doubles>(); SqlConnection connection = new SqlConnection(@"Data Source=BTO;Initial Catalog=CoreDb;Integrated Security=True"); // SqlConnection connection = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=Importexcel;Integrated Security=True;Pooling=False"); connection.Open(); Debug.Write(connection.State.ToString()); SqlCommand cmd = new SqlCommand("SELECT * FROM Issue", connection); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { Data item = new Data(); item.Gereed = reader.GetString(0); item.Project_Code = reader.GetDouble(1); item.Organisatie_Code = reader.GetDouble(2); item.Input_Bron = reader.GetDouble(3); item.AardId = reader.GetDouble(4); item.Categorie = reader.GetString(5); item.Actiehouder = reader.GetString(6); item.Prioriteit = reader.GetString(7); item.Kenmerk = reader.GetString(8); item.Issues = reader.GetString(9); item.Antwoord = reader.GetString(10); item.Opmerking = reader.GetString(11); item.Aangever = reader.GetString(12); item.Man_Uren = reader.GetDouble(13); item.Datum_Ingedied = reader.GetString(14); item.Datum_Gepland = reader.GetString(15); item.Datum_Gereed = reader.GetString(16); item.Status = reader.GetString(17); datas.Add(item); //PRINT ALLE ENTRIES IN DE DEBUG CONSOLE Debug.WriteLine("\n\n", item.id); Debug.WriteLine(item.Gereed); Debug.WriteLine(item.Project_Code); Debug.WriteLine(item.Organisatie_Code); Debug.WriteLine(item.Input_Bron); Debug.WriteLine(item.AardId); Debug.WriteLine(item.Categorie); Debug.WriteLine(item.Prioriteit); Debug.WriteLine(item.Kenmerk); Debug.WriteLine(item.Issues); Debug.WriteLine(item.Antwoord); Debug.WriteLine(item.Opmerking); Debug.WriteLine(item.Aangever); Debug.WriteLine(item.Man_Uren); Debug.WriteLine(item.Datum_Ingedied); Debug.WriteLine(item.Datum_Gepland); Debug.WriteLine(item.Datum_Gereed); Debug.WriteLine(item.Status); Debug.Write("\n"); } connection.Close(); int dubbele_data = 0; for (int i = 4; i <= totalRows; i++) { Issue issue = new Issue(); issue.Gereed = workSheet.Cells[i, 1].Value.ToString(); issue.Project_Code = (Double)workSheet.Cells[i, 2].Value; issue.Organisatie_Code = (Double)workSheet.Cells[i, 3].Value; issue.Input_Bron = (Double)workSheet.Cells[i, 4].Value; issue.AardId = (Double)workSheet.Cells[i, 5].Value; issue.Categorie = workSheet.Cells[i, 6].Value.ToString(); issue.Actiehouder = workSheet.Cells[i, 7].Value.ToString(); issue.Prioriteit = workSheet.Cells[i, 8].Value.ToString(); issue.Kenmerk = workSheet.Cells[i, 9].Value.ToString(); issue.Issues = workSheet.Cells[i, 10].Value.ToString(); issue.Antwoord = workSheet.Cells[i, 11].Value.ToString(); issue.Opmerking = workSheet.Cells[i, 12].Value.ToString(); issue.Aangever = workSheet.Cells[i, 13].Value.ToString(); issue.ManUren = (Double)workSheet.Cells[i, 14].Value; issue.Datum_Ingediend = workSheet.Cells[i, 15].Value.ToString(); issue.Datum_Gepland = workSheet.Cells[i, 16].Value.ToString(); issue.Datum_Gereed = workSheet.Cells[i, 17].Value.ToString(); issue.Status = workSheet.Cells[i, 18].Value.ToString(); foreach (var item in datas) { if (issue.Actiehouder == item.Actiehouder) { dubbele_data += 1; // Response.WriteAsync("<script>alert('DUBBELE DATA GEVONDEN!');</script>"); Doubles doubles = new Doubles(); doubles.rij = dubbele_data + 3; dubbel.Add(doubles); issue.Status = null; } } if (issue.Status != null) { Debug.WriteLine("\n\n ISSUE Lijnnummer " + i + " Added \n "); issuelist.Add(issue); } _db.Issue.AddRange(issuelist); } if (dubbele_data != 0) { model.answer = "Er is/zijn " + dubbele_data + " dubbele rijen gevonden. De rest is toegevoegd. Het gaat om rij : "; foreach (var item in dubbel) { model.answer += " "; model.answer += item.rij.ToString(); } } else { model.answer = "Succesvol toegevoegd"; } _db.SaveChanges(); return(View(model)); } } catch (Exception error) { response model = new response(); model.answer = "Er is een fout opgetreden. Mogelijk wordt dit bestand niet ondersteund." + error; return(View(model)); } }