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));
}
}