I'm using MVC3 with C# code. I have a table in my SQL Server Express containing some columns, and I have an Excel sheet which has the same number of columns with the same datatypes and names.
My requirement is I want to browse that Excel file in my MVC3 application, where the user can select the file. There is a RegistrationNo
column in both my database table as well as in the Excel sheet. Before importing the data in the table of the database the RegNo
present in the Excel sheet should be compared with the RegNo
in the database table and if that RegNo
already exists than no insertion for that RegNO
should take place else if that RegNo
is not present in the table than the row for that RegNo
should be inserted.
Below is my code that I have tried but I'm getting lot of issues with that.
[HttpPost]
public ActionResult AdmissionUpload()
{
string filePath = null;
foreach (string inputTagName in Request.Files)
{
HttpPostedFileBase Infile = Request.Files[inputTagName];
if (Infile.ContentLength > 0 && (Path.GetExtension(Infile.FileName) == ".xls" || Path.GetExtension(Infile.FileName) == ".xlsx" || Path.GetExtension(Infile.FileName) == ".xlsm"))
{
filePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory,
Path.GetFileName(Infile.FileName));
if (System.IO.File.Exists(filePath))
{
System.IO.File.Delete(filePath);
}
Infile.SaveAs(filePath);
//Infile.SaveAs(filePath);
}
if (filePath != null)
{
System.Data.OleDb.OleDbConnection oconn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath.ToString() + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\";");
oconn.Open();
try
{
if (oconn.State == System.Data.ConnectionState.Closed)
oconn.Open();
}
catch (Exception ex)
{
// MessageBox.Show(ex.Message);
}
dynamic myTableName = oconn.GetSchema("Tables").Rows[0]["TABLE_NAME"];
OleDbCommand ocmd = new OleDbCommand("select * from [" + myTableName + "]", oconn);
OleDbDataReader odr = ocmd.ExecuteReader();
if (odr.HasRows)
{
while (odr.Read())
{
if (odr[0].ToString().Trim() != "")
{
if (CheckDepartment(odr[0].ToString().Trim()) == false)
{
var model = new DepartmentMaster();
model.DepartmentName = odr[1].ToString().Trim();
db.DepartmentMasters.AddObject(model);
db.SaveChanges();
FLAG = true;
}
}
}
}
}
}
return View();
}
Here CheckRegNo
checks whether the RegNo
exists.
dynamic myTableName = oconn.GetSchema("Tables").Rows[0]["TABLE_NAME"];
OleDbCommand ocmd = new OleDbCommand("select * from [" + myTableName + "]", oconn);
OleDbDataReader odr = ocmd.ExecuteReader();
if (odr.HasRows)
{
while (odr.Read())
{
var model = new Student();
model.Col1=Convert.ToInt32(odr[0]);
model.Col2 = odr[1].ToString().Trim();
model.col3 = odr[2].ToString().Trim();
model.col4 = odr[3].ToString().Trim();
db.MyTable.AddObject(model);
}
}
This is how im reading the Excel and saving data from Excel.