Asp.net Excel批量导入数据到SqlServer数据库
下面是自己添加的一个类:在上面的页面中的按钮单击事件中调用。
public class ExcelToMSSql
{
public static int count = 0;
public static string error = "";
public ExcelToMSSql()
{
//
//TODO: 在此处添加构造函数逻辑
//
}
//传入excel文件的路径。
public static void DataTranf(string execlpath)
{
DataTable dtExcel = ExcelToDataTable(@execlpath, "Sheet1");
for (int i = 0; i < dtExcel.Rows.Count; i++)
{
InsertToDB(dtExcel.Rows[i][0].ToString().Trim(), dtExcel.Rows[i][1].ToString().Trim(), dtExcel.Rows[i][2].ToString().Trim(), FormsAuthentication.HashPasswordForStoringInConfigFile(dtExcel.Rows[i][3].ToString().Trim(), "MD5"), dtExcel.Rows[i][4].ToString().Trim(),dtExcel.Rows[i][5].ToString().Trim());
//此处根据excel中字段,自己设定要取用的字段。
}
}
public static DataTable ExcelToDataTable(string strExcelFileName, string strSheetName)
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strExcelFileName + ";" + "Extended Properties=Excel 5.0;";
string strExcel = string.Format("select * from [{0}$]", strSheetName);
DataSet ds = new DataSet();
using (OleDbConnection conn = new OleDbConnection(strConn))
{
conn.Open();
OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, strConn);
adapter.Fill(ds, strSheetName);
conn.Close();
}
return ds.Tables[strSheetName];
}
//插入记录到SqlServer数据库
public static void InsertToDB(string studno, string studname,string studsex,string studpwd,string studclass,string middleschool)
{
string connstr = System.Configuration.ConfigurationSettings.AppSettings["constr"];
SqlConnection conn = new SqlConnection(connstr);
conn.Open();
string str_insertstudent = "insert into student (userno,username,sex,pwd,classname,address) values('" + studno + "','" + studname + "','" + studsex + "','" + studpwd + "','" + studclass + "','" + middleschool + "')";
SqlCommand cmd = new SqlCommand(str_insertstudent, conn);
try
{
cmd.ExecuteNonQuery();
count++;
}
catch(Exception e)
{
error += e.Message.ToString() + "<br />";
}
}
大同小异,导入到其他数据库只要更改下连接串即可。
但是此方法似乎好像只能在网站所在的机子上实现批量插入。远程批量插入会不成功,本人的解决办法是先上传excel文件到网站,然后在批量插入即可。