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文件到网站，然后在批量插入即可。

 

转载于:https://www.cnblogs.com/lilenfeng/p/3252705.html