vs2005中Excel 数据倒入SQL数据库

完整示例：

ImportDetail.aspx代码如下 ：

 

<% @ Page Language = " C# "  AutoEventWireup = " true "  Codebehind = " ImportDetail.aspx.cs "  Inherits = " ExcelImportDetail._Default "   %>

<! DOCTYPE html PUBLIC  " -//W3C//DTD XHTML 1.0 Transitional//EN "   " http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd " >
< html xmlns = " http://www.w3.org/1999/xhtml " >
< head >
     < title > Excel Import  </ title >
</ head >
< body >
     < form runat = " server " >
         < table width = " 100% " >
             < tr style = " height: 100px " >
             </ tr >
             < tr align = " center " >
                 < td >
                     < asp:GridView ID = " GridView2 "  runat = " server "  OnPageIndexChanging = " GridView2_PageIndexChanging "
                        AllowPaging = " true " >
                         < FooterStyle BackColor = " #FFFFCC "  ForeColor = " #330099 "   />
                         < RowStyle BackColor = " #fbffff "  ForeColor = " #333333 "   />
                         < SelectedRowStyle BackColor = " #FFCC66 "  Font - Bold = " True "  ForeColor = " #663399 "   />
                         < PagerStyle BackColor = " #FFFFCC "  ForeColor = " #330099 "   />
                         < HeaderStyle BackColor = " #5D96d5 "  Height = " 20px "  Font - Bold = " True "  ForeColor = " White "   />
                     </ asp:GridView >
                 </ td >
             </ tr >
             < tr >
              
             </ tr >
         </ table >
     </ form >
</ body >
</ html >

ImportDetail.aspx.cs 代码如下：

using  System;
using  System.Data;
using  System.Configuration;
using  System.Collections;
using  System.Web;
using  System.Web.Security;
using  System.Web.UI;
using  System.Web.UI.WebControls;
using  System.Web.UI.WebControls.WebParts;
using  System.Web.UI.HtmlControls;
using  System.Data.OleDb;
using  System.Data.SqlClient;
namespace  ExcelImportDetail
... {
    public partial class _Default : System.Web.UI.Page
    ...{
        private readonly string AddressBookConnString = System.Configuration.ConfigurationManager.ConnectionStrings["AddressBookConnectionString"].ToString();

        protected void Page_Load(object sender, EventArgs e)
        ...{
            if (!IsPostBack)
            ...{   
                InsertData();
                GridView2.DataSource = GetDataSet();
                GridView2.DataBind();
            }
        }
        private DataSet CreateDataSet()
        ...{
            string strConn;
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
            "Data Source=d:/CollectedErrors.xls;" +
            "Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);
            DataSet myDataSet = new DataSet();
            myCommand.Fill(myDataSet);
            return myDataSet;
        }
        private void InsertData()
        ...{
            SqlConnection Conn = new SqlConnection(AddressBookConnString);
            SqlDataAdapter Adapter = new SqlDataAdapter("Select * From part1_detail", Conn);
            SqlCommandBuilder builder = new SqlCommandBuilder(Adapter);
            builder.QuotePrefix = "[";
            builder.QuoteSuffix = "]";
            DataSet Tempdataset = CreateDataSet();
            for (int i = 0; i < Tempdataset.Tables[0].Rows.Count; i++)
            ...{
                Tempdataset.Tables[0].Rows[i].SetAdded();
            }
            try
            ...{
                // test builder commandtext  
              string tmp = builder.GetUpdateCommand().CommandText;
                int Rows = Adapter.Update(Tempdataset);
                if (Rows > 0)
                ...{
                   ClientScript.RegisterStartupScript(this.GetType(), "Import Message"," alert("" + Rows.ToString() + " Rows Import The Database !")",true);
                }
                else
                ...{

                    ClientScript.RegisterStartupScript(this.GetType(), "Import Message", " alert("Import Data Failuer !")", true);
                }
            }
            catch (Exception ex)
            ...{
                throw new Exception("Exception", ex);
            }              
        }
        private DataSet GetDataSet()
        ...{
            DataSet ds = new DataSet();
            String cmdtext = " Select * FROM part1_detail ";
            SqlConnection Conn = new SqlConnection(AddressBookConnString);
            SqlDataAdapter Adapter = new SqlDataAdapter(cmdtext, Conn);  
            try
            ...{
                Adapter.Fill(ds);
            }
            catch (Exception ex)
            ...{
                throw new Exception("Exception", ex);
            }
             return ds;
        }

        protected void GridView2_PageIndexChanging(object sender, GridViewPageEventArgs e)
        ...{
            this.GridView2.PageIndex = e.NewPageIndex;
            GridView2.DataSource = this.GetDataSet();
            GridView2.DataBind();
        }
    }
}

web.config 如下：

    < connectionStrings >
         < add name = " AddressBookConnectionString "  connectionString = " Data Source=C1030SQLEXPRESS;Initial Catalog=Test;Integrated Security=True "
            providerName = " System.Data.SqlClient "   />
     </ connectionStrings >

数据库创建如下：

CREATE TABLE [dbo].[part1_detail](
    [LIST] [ float ] NOT NULL,
    [Tracking_NO] [nvarchar]( 255 ) COLLATE Chinese_PRC_CI_AS NULL,
    [DCN] [nvarchar]( 255 ) COLLATE Chinese_PRC_CI_AS NULL,
    [Fields_with_Error] [nvarchar]( 255 ) COLLATE Chinese_PRC_CI_AS NULL,
    [Error_Explanation] [nvarchar]( 255 ) COLLATE Chinese_PRC_CI_AS NULL,
    [UCID] [ float ] NULL,
    [DeDupe] [ float ] NULL,
    [Validating] [ float ] NULL,
    [keystroke_in_error] [ float ] NULL,
    [No_Error] [ float ] NULL,
    [Web_Entered] [ float ] NULL,
    [Total_Keystrokes] [ float ] NULL,
    [Batch_NO] [nvarchar]( 255 ) COLLATE Chinese_PRC_CI_AS NULL,
 CONSTRAINT [PK_part1_detail] PRIMARY KEY CLUSTERED 
(
    [LIST] ASC
)WITH (IGNORE_DUP_KEY  =  OFF) ON [PRIMARY]
) ON [PRIMARY]