excel数据导入SQL

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

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            //绑定数据
            Bind();
        }
    }
    /// <summary>
    /// 查询数据并绑定到GridView上
    /// </summary>
    private void Bind()
    {
        /*
         * 该方法用到的是Framework3.5中的Linq进行的数据查找
         */

        DataContext ctx = new DataContext("server=.;database=Test;uid=sa;pwd=;");
        Table<User> objUser = ctx.GetTable<User>();
        Table<UserInfo> objUserInfo = ctx.GetTable<UserInfo>();

        //联表查询
        //GridView1.DataSource = from c in objUser join a in objUserInfo on c.ID equals a.UserID select new { 顾客ID = c.ID, 顾客名 = c.Name, 密码 = c.Pass, 城市 = a.City, 邮编 = a.Zip };

        //单表查询
        GridView1.DataSource = from c in objUser select new { 顾客ID = c.ID, 顾客名 = c.Name, 密码 = c.Pass};
        GridView1.DataBind();
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        try
        {
            string strConnection="";
            //2003以下的版本使用下面的连接字符串
            //strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='d://UserData.xls';Extended Properties=Excel 8.0;";
           
            //2007版的使用下面的连接字符串
            strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='d://UserData.xlsx';Extended Properties=Excel 8.0;";
            OleDbConnection oleConnection = new OleDbConnection(strConnection);

            //设定用于进行excel数据查询的语句
            string strCommondText = " SELECT [Name],[Pass] FROM [Sheet1$]";
            oleConnection.Open();
            DataSet myDataSet = new DataSet();
            //查寻excel中的数据放到DataSet中
            OleDbDataAdapter oleAdper = new OleDbDataAdapter(strCommondText, oleConnection);
            oleAdper.Fill(myDataSet);
            oleConnection.Close();

            if (myDataSet.Tables[0].Rows.Count != 0)
            {
                //如果DataSet中有数据，将数据插入到SQL中
                StringBuilder strSql = new StringBuilder();
                foreach (DataRow dr in myDataSet.Tables[0].Rows)
                {
                    strSql.Append("insert into [User] values('"+dr[0].ToString()+"','"+dr[1].ToString()+"')  ");
                }
                string strCon = "server=.;database=Test;uid=sa;pwd=;";
                SqlConnection con = new SqlConnection(strCon);
                SqlCommand com = new SqlCommand(strSql.ToString(), con);
                con.Open();
                int iCount = com.ExecuteNonQuery();
                con.Close();
            }
            Bind();
        }
        catch (Exception)
        {
            throw;
        }
    }
}

说明：该代码在 vs2008下测试通过， office用的是07版的。