asp.net mvc 选择excel 文件并添加到数据库(译,原文有误已更改)
1:描述
单击文件选择按钮,选择多个excel文件,上传,自动同步数据到数据库
步骤:
0打开sqlserver 2012 mamagement studio(我是基于windows认证),连接,新建一个数据库MyDb,新建一个表Person,如图:后面的sql连接串,服务器和用户这些可以直接右击数据库看属性即可.
1新建asp.net mvc 4项目
2新建空控制器TestController.cs
3:在它的Index方法内右击添加视图(不需要强类型),Index.cshtml
4:Index.cshtml的内容如下:
@{
ViewBag.Title = "Index";
}
<h2> Index</h2>
@using (Html.BeginForm("Index","Test", FormMethod.Post, new { enctype = "multipart/form-data" }))
{
<input type="file" name="file" />
<input type="submit" value="OK" />
}
5:给TestController.cs添加一个HttpPost方法
[HttpPost]
public ActionResult Index(HttpPostedFileBase file)
{
DataSet ds = new DataSet();
//首先在内存中存取操作
if (Request.Files["file"].ContentLength > 0)
{
//判断上传文件大小,key是input ,type=file name=file中的name的值,几乎所有get post参数都和Request有关
string fileExtension =
System.IO.Path.GetExtension(Request.Files["file"].FileName);
//获取文件的扩展名,07以前还是07以后
if (fileExtension == ".xls" || fileExtension == ".xlsx")
{
//上传到服务器某个路径,如果该路径已经存在,就覆盖(删除+另存为),从而保持文件最新
string fileLocation = Server.MapPath("~/Content/") + Request.Files["file"].FileName;
if (System.IO.File.Exists(fileLocation))
{
System.IO.File.Delete(fileLocation);
}
Request.Files["file"].SaveAs(fileLocation);
//存储文件到服务器
string excelConnectionString = string.Empty;
//连接com组件,处理excel
excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
//connection String for xls file format.
if (fileExtension == ".xls")
{
excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
}
//connection String for xlsx file format.
else if (fileExtension == ".xlsx")
{
excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
}
//Create Connection to Excel work book and add oledb namespace
OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
excelConnection.Open();
//打开com服务,新建datatable,准备处理表格
DataTable dt = new DataTable();
dt = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dt == null)
{
return null;
}
String[] excelSheets = new String[dt.Rows.Count];
int t = 0;
//excel data saves in temp file here.
foreach (DataRow row in dt.Rows)
{
excelSheets[t] = row["TABLE_NAME"].ToString();
t++;
}
OleDbConnection excelConnection1 = new OleDbConnection(excelConnectionString);
string query = string.Format("Select * from [{0}]", excelSheets[0]);
using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, excelConnection1))
{
dataAdapter.Fill(ds);
}
}
if (fileExtension.ToString().ToLower().Equals(".xml"))
{
string fileLocation = Server.MapPath("~/Content/") + Request.Files["FileUpload"].FileName;
if (System.IO.File.Exists(fileLocation))
{
System.IO.File.Delete(fileLocation);
}
Request.Files["FileUpload"].SaveAs(fileLocation);
XmlTextReader xmlreader = new XmlTextReader(fileLocation);
// DataSet ds = new DataSet();
ds.ReadXml(xmlreader);
xmlreader.Close();
}
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
string conn = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
string connectString = "Data Source=REBGFGNB-PC\\SQLEXPRESS;Initial Catalog=MyDb;user id=rebgfgnb-PC\\rebgfgnb;password=manager123;Integrated"+
"Security=True";
//此处给出两种连接行为,webConfig.中配置,或者直接在代码中使用,要使用第一种,请用这句话替换即可:
/*<connectionStrings>
<add name="DefaultConnection" connectionString="Data Source=REBGFGNB-PC\SQLEXPRESS;Initial Catalog=MyDb;user id=rebgfgnb-PC\rebgfgnb;password=manager123;Integrated Security=True" providerName="System.Data.SqlClient" />
</connectionStrings>
*/
SqlConnection con = new SqlConnection(conn);
string query = "Insert into Person(Name,Email,Mobile) Values('" +
ds.Tables[0].Rows[i][0].ToString() + "','" + ds.Tables[0].Rows[i][1].ToString() +
"','" + ds.Tables[0].Rows[i][2].ToString() + "')";
con.Open();
SqlCommand cmd = new SqlCommand(query, con);
cmd.ExecuteNonQuery();
con.Close();
}
}
return View();
}
新建电子表格(我的 office 2010),确保格式如图:
运行,大功告成
