如何在 MVC3 中将数据从 Excel 导入到 SQL Server Express 2008
我正在使用 MVC3 和 C# 代码。我的 SQL Server Express 中有一个表,其中包含一些列,并且有一个 Excel 工作表,其中包含相同数量的列、相同的数据类型和名称。
我的要求是我想在 MVC3 应用程序中浏览该 Excel 文件,用户可以在其中选择该文件。我的数据库表和 Excel 工作表中都有一个 RegistrationNo
列。在导入数据库表中的数据之前,应将 Excel 工作表中存在的 RegNo
与数据库表中的 RegNo
进行比较,如果该 RegNo
code> 已存在,则不应插入该 RegNO
,否则如果该 RegNo
不存在于表中,则该 RegNo
的行不存在应插入。
下面是我尝试过的代码,但我遇到了很多问题。
[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();
}
这里CheckRegNo
检查RegNo
是否存在。
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.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
这就是我读取 Excel 并从 Excel 保存数据的方式。
This is how im reading the Excel and saving data from Excel.
我们也可以使用excel库导入。下面是它的代码。
We can also import using excel library. Below is code for it.