將Excel中數(shù)據(jù)導(dǎo)入到Access數(shù)據(jù)庫(kù)中的方法

更新時(shí)間：2013年03月19日 20:31:40 作者：

將Excel中數(shù)據(jù)導(dǎo)入到Access數(shù)據(jù)庫(kù)中的方法，需要的朋友可以參考一下

Default.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

        .style1
        {
            height: 16px;
        }
        .style3
        {
            height: 23px;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">

    <div>

    </div>
    <table align="center" border="1" bordercolor="honeydew" cellpadding="0"
        cellspacing="0">
        <tr>
            <td style="FONT-SIZE: 9pt; COLOR: #ff0000; BACKGROUND-COLOR: #ff9933; TEXT-ALIGN: center"
                class="style1">
            </td>
            <td colspan="2"
                style="FONT-SIZE: 9pt; COLOR: #ffffff; HEIGHT: 16px; BACKGROUND-COLOR: #ff9933; TEXT-ALIGN: center">
                將Excel數(shù)據(jù)寫(xiě)入Access數(shù)據(jù)庫(kù)中</td>
        </tr>
        <tr>
            <td style="BACKGROUND-COLOR: #ffffcc; TEXT-ALIGN: center">
            </td>
            <td style="BACKGROUND-COLOR: #ffffcc; TEXT-ALIGN: center">
                <iframe id="I1" name="I1" scrolling="yes" src="學(xué)生成績(jī).xls"
                    style="WIDTH: 407px; HEIGHT: 280px"></iframe>
            </td>
            <td style="WIDTH: 190px; BACKGROUND-COLOR: #ffffcc; TEXT-ALIGN: center">
                <asp:GridView ID="GridView1" runat="server" CellPadding="4" Font-Size="9pt"
                    ForeColor="#333333" GridLines="None" Width="228px">
                    <FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
                    <RowStyle BackColor="#FFFBD6" ForeColor="#333333" />
                    <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
                    <PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
                    <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
                    <AlternatingRowStyle BackColor="White" />
                </asp:GridView>
            </td>
        </tr>
        <tr>
            <td style="BACKGROUND-COLOR: #ff9900; TEXT-ALIGN: center" class="style3">
            </td>
            <td style="HEIGHT: 23px; BACKGROUND-COLOR: #ff9900; TEXT-ALIGN: center"
                valign="top">
                <asp:Button ID="Button3" runat="server" Font-Size="9pt" onclick="Button1_Click"
                    Text="Excel數(shù)據(jù)寫(xiě)入Access數(shù)據(jù)庫(kù)中" />
    <asp:Label ID="Label1" runat="server" Text="Label" Visible="False"
                    style="font-size: x-small"></asp:Label>
            </td>
            <td style="WIDTH: 190px; HEIGHT: 23px; BACKGROUND-COLOR: #ff9900; TEXT-ALIGN: center">
                <asp:Button ID="Button2" runat="server" Font-Size="9pt" onclick="Button2_Click"
                    Text="數(shù)據(jù)庫(kù)中顯示Excel數(shù)據(jù)" />
            </td>
        </tr>
        <tr>
            <td>
                 </td>
        </tr>
    </table>
    </form>
</body>
</html>

Default.aspx.cs

復(fù)制代碼代碼如下:

using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

using System.Data.OleDb;

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

    }
    public OleDbConnection CreateCon()
    {
        string strconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + Server.MapPath("UserScore.mdb") + ";User Id=admin;Password=;";
        OleDbConnection odbc = new OleDbConnection(strconn);
        return odbc;
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        //定義Excel列表
        string StyleSheet = "Sheet1";
        //調(diào)用自定義LoadData方法，將Excel文件中數(shù)據(jù)讀到ASPNET頁(yè)面中
        LoadData(StyleSheet);
        //定義查詢的SQL語(yǔ)句
        string sql = "select ID,用戶姓名,試卷,成績(jī),考試時(shí)間 from Score";
        //創(chuàng)建Oledb數(shù)據(jù)庫(kù)連接
        OleDbConnection con = CreateCon();
        con.Open();//打開(kāi)數(shù)據(jù)庫(kù)連接
        OleDbCommand com = new OleDbCommand(sql, con);
        //開(kāi)始事務(wù)
        OleDbTransaction tran = con.BeginTransaction();
        com.Transaction = tran;
        //創(chuàng)建適配器
        OleDbDataAdapter da = new OleDbDataAdapter(com);
        OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
        //創(chuàng)建DataSet數(shù)據(jù)集
        DataSet ds = new DataSet();
        //填充數(shù)據(jù)集
        da.Fill(ds);
        int curIndex = 0;
        if (ds.Tables[0].Rows.Count > 0)
        {
            curIndex = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
        }
        //創(chuàng)建一個(gè)內(nèi)存表
        DataTable tb = this.getExcelDate();
        string selsql = "";
        for (int i = 0; i < tb.Rows.Count; i++)
        {
            string UserName = tb.Rows[i][0].ToString();
            selsql = "select count(*) from Score where 用戶姓名='" + UserName + "'";
        }
        //判斷Excel文件中是否已經(jīng)導(dǎo)入到Access數(shù)據(jù)庫(kù)中
        if (ExScalar(selsql) > 0)
        {
            Label1.Visible = true;
            Label1.Text = "<script language=javascript>alert('該Excle中的數(shù)據(jù)已經(jīng)導(dǎo)入數(shù)據(jù)庫(kù)中!');location='Default.aspx';</script>";
        }
        else
        {
            //循環(huán)讀取Excel文件中數(shù)據(jù)，并添加到Access事先創(chuàng)建好的數(shù)據(jù)庫(kù)表中
            for (int i = 0; i < tb.Rows.Count; i++)
            {
                DataRow dr = ds.Tables[0].NewRow();
                dr[0] = ++curIndex;
                dr[1] = tb.Rows[i][0];
                dr[2] = tb.Rows[i][1];
                dr[3] = tb.Rows[i][2];
                dr[4] = tb.Rows[i][3];
                ds.Tables[0].Rows.Add(dr);
            }
            try
            {
                da.Update(ds);//執(zhí)行插入操作
                tran.Commit();//事務(wù)提交
                Label1.Visible = true;
                Label1.Text = "<script language=javascript>alert('數(shù)據(jù)導(dǎo)入成功！');location='Default.aspx';</script>";
            }
            catch
            {
                tran.Rollback();//事務(wù)回滾
                Label1.Visible = true;
                Label1.Text = "<script language=javascript>alert('數(shù)據(jù)導(dǎo)入失??！');location='Default.aspx';</script>";
            }
            finally
            {
                con.Close();//關(guān)閉數(shù)據(jù)庫(kù)連接
            }
        }
    }
    protected void Button2_Click(object sender, EventArgs e)
    {
        string sqlstr = "select * from Score";
        OleDbConnection conn = CreateCon();
        conn.Open();
        OleDbCommand mycom = new OleDbCommand(sqlstr, conn);
        OleDbDataReader dr = mycom.ExecuteReader();
        dr.Read();
        if (dr.HasRows)
        {
            GetDataSet(sqlstr);
        }
        else
        {
            Label1.Visible = true;
            Label1.Text = "<script language=javascript>alert('數(shù)據(jù)庫(kù)中沒(méi)有數(shù)據(jù)信息，請(qǐng)先導(dǎo)入再查詢！');location='Default.aspx';</script>";
        }
        dr.Close();
        conn.Close();
    }
    public DataSet GetDataSet(string sqlstr)
    {
        OleDbConnection conn = CreateCon();
        OleDbDataAdapter myda = new OleDbDataAdapter(sqlstr, conn);
        DataSet ds = new DataSet();
        myda.Fill(ds);
        GridView1.DataSource = ds;
        GridView1.DataBind();
        return ds;
    }
    public DataTable getExcelDate()
    {
        string strExcelFileName = Server.MapPath("學(xué)生成績(jī).xls");
        string strcon = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strExcelFileName + ";" + "Extended Properties='Excel 8.0;HDR=YES;IMEX=1';";
        string sql = "select * from [Sheet1$]";
        OleDbDataAdapter da = new OleDbDataAdapter(sql, strcon);
        DataSet ds = new DataSet();
        da.Fill(ds);
        return ds.Tables[0];
    }
    public void LoadData(string StyleSheet)
    {
        //定義數(shù)據(jù)庫(kù)連接字符串 m
        string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" + Server.MapPath("學(xué)生成績(jī).xls") + ";Extended Properties=Excel 8.0";
        //創(chuàng)建數(shù)據(jù)庫(kù)連接
        OleDbConnection myConn = new OleDbConnection(strCon);
        //打開(kāi)數(shù)據(jù)鏈接，得到一個(gè)數(shù)據(jù)集
        myConn.Open();
        //創(chuàng)建DataSet對(duì)象
        DataSet myDataSet = new DataSet();
        //定義查詢的SQL語(yǔ)句
        string StrSql = "select   *   from   [" + StyleSheet + "$]";
        //創(chuàng)建數(shù)據(jù)庫(kù)適配器
        OleDbDataAdapter myCommand = new OleDbDataAdapter(StrSql, myConn);
        //填充數(shù)據(jù)集中的數(shù)據(jù)
        myCommand.Fill(myDataSet, "[" + StyleSheet + "$]");
        //釋放占有的資源
        myCommand.Dispose();
        //關(guān)閉數(shù)據(jù)庫(kù)連接
        myConn.Close();
    }
    public int ExScalar(string sql)
    {
        OleDbConnection conn = CreateCon();
        conn.Open();
        OleDbCommand com = new OleDbCommand(sql, conn);
        return Convert.ToInt32(com.ExecuteScalar());
        conn.Close();
    }
}