Asp.net中Excel数据导入到SQL Server

1、首先在前台添加一个upfileload文件上传控件和一个button按钮，upfileload上传控件用于读取Excel文件！

2、GetExcelData()函数用于读取Excel文件并填写到一个DateSet中，由于微软对07以后的Excel的数据结构作了修改，所以针对07以前的版本和07及其以后的版本使用不同的连接串。

        string connStr03 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0;"; ;
        string connStr07 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0;HDR=YES'";

由于Excel文件可能无法读取到，在读取之前最好先将文件上传：

inputFile.SaveAs(Server.MapPath("~/" + System.IO.Path.GetFileName(inputFile.PostedFile.FileName)));

string filePath = Server.MapPath("~/" + System.IO.Path.GetFileName(inputFile.PostedFile.FileName));

3、InsertDB(）函数用于将存储Excel数据的DateSet中的数据导入到SQL Server中。

具体代码如下：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Data.OleDb;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }


    /// <summary>
    /// 该方法实现从Excel中导出数据到DataSet中，其中filepath为Excel文件的绝对路径，sheetname为表示那个Excel表,此用Sheet1；
    /// </summary>
    /// <param name="ds">ds</param>
    private void InsertDB(DataSet ds)
    {
        string CONNECTION_STRING = @"Data Source=（local）; Initial Catalog=test;Integrated Security=false;User ID=sa;Password=sa;";
        SqlConnection _con = new SqlConnection(CONNECTION_STRING);
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = _con;
        StringBuilder sb = new StringBuilder();
        if (ds.Tables[0].Rows.Count > 0)
        {
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                sb.Append(" insert into ynzt6(time,cul1,cul2,cul3,cul4) values('");
                sb.Append(ds.Tables[0].Rows[i].ItemArray[0].ToString() + "','");
                sb.Append(ds.Tables[0].Rows[i].ItemArray[1].ToString() + "','");
                sb.Append(ds.Tables[0].Rows[i].ItemArray[2].ToString() + "','");
                sb.Append(ds.Tables[0].Rows[i].ItemArray[3].ToString() + "','");
                sb.Append(ds.Tables[0].Rows[i].ItemArray[4].ToString() + "' ) ");
                cmd.CommandText = sb.ToString();
            }
        }
        _con.Open();
        int j = cmd.ExecuteNonQuery();
        _con.Close();
        if (j > 0)
        {
            lblMessage.Text = "Insert into PDB_YN_BEF table Sucessfully!";
        }
    }
    /// <summary>
    /// get data source from excel file
    /// </summary>
    /// <returns>dataset ds</returns>
    private DataSet GetExcelData()
    {
        DataSet ds = new DataSet();
        inputFile.SaveAs(Server.MapPath("~/" + System.IO.Path.GetFileName(inputFile.PostedFile.FileName)));
        string filePath = Server.MapPath("~/" + System.IO.Path.GetFileName(inputFile.PostedFile.FileName));
        string connStr03 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0;"; ;
        string connStr07 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0;HDR=YES'";
        string queryStr = "SELECT * FROM [Sheet1$]";
        OleDbConnection conn03 = new OleDbConnection(connStr03);
        OleDbConnection conn07 = new OleDbConnection(connStr07);
        if (inputFile.HasFile)
        {
            string fileExt = System.IO.Path.GetExtension(inputFile.FileName);
            if (fileExt == ".xls")
            {
                OleDbDataAdapter myAdapter = new OleDbDataAdapter(queryStr, conn03);
                myAdapter.Fill(ds);
            }
            else if (fileExt == ".xlsx")
            {
                OleDbDataAdapter myAdapter = new OleDbDataAdapter(queryStr, conn07);
                myAdapter.Fill(ds);
            }
            else
            {
                lblMessage.Text = "The file is not exist!";
            }

        }
        return ds;
    }
    protected void btnUpload_Click1(object sender, EventArgs e)
    {
        DataSet ds = GetExcelData();
        InsertDB(ds);

    }
}