将Excel中数据导入到Access数据库中的方法

2020-01-18 00:06:28

字体：大中小

来源：转载

供稿：网友

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数据写入Access数据库中</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="学生成绩.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数据写入Access数据库中" />
    <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="数据库中显示Excel数据" />
            </td>
        </tr>
        <tr>
            <td>
                </td>
        </tr>
    </table>
    </form>
</body>
</html>

Default.aspx.cs

复制代码代码如下:

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";
        //调用自定义LoadData方法，将Excel文件中数据读到ASPNET页面中
        LoadData(StyleSheet);
        //定义查询的SQL语句
        string sql = "select ID,用户姓名,试卷,成绩,考试时间 from Score";
        //创建Oledb数据库连接
        OleDbConnection con = CreateCon();
        con.Open();//打开数据库连接
        OleDbCommand com = new OleDbCommand(sql, con);
        //开始事务
        OleDbTransaction tran = con.BeginTransaction();
        com.Transaction = tran;
        //创建适配器
        OleDbDataAdapter da = new OleDbDataAdapter(com);
        OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
        //创建DataSet数据集
        DataSet ds = new DataSet();
        //填充数据集
        da.Fill(ds);
        int curIndex = 0;
        if (ds.Tables[0].Rows.Count > 0)
        {
            curIndex = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
        }
        //创建一个内存表
        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文件中是否已经导入到Access数据库中
        if (ExScalar(selsql) > 0)
        {
            Label1.Visible = true;
            Label1.Text = "<script language=javascript>alert('该Excle中的数据已经导入数据库中!');location='Default.aspx';</script>";
        }
        else
        {
            //循环读取Excel文件中数据，并添加到Access事先创建好的数据库表中
            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);//执行插入操作
                tran.Commit();//事务提交
                Label1.Visible = true;
                Label1.Text = "<script language=javascript>alert('数据导入成功！');location='Default.aspx';</script>";
            }
            catch
            {
                tran.Rollback();//事务回滚
                Label1.Visible = true;
                Label1.Text = "<script language=javascript>alert('数据导入失败！');location='Default.aspx';</script>";
            }
            finally
            {
                con.Close();//关闭数据库连接
            }
        }
    }
    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('数据库中没有数据信息，请先导入再查询！');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("学生成绩.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)
    {
        //定义数据库连接字符串 m
        string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" + Server.MapPath("学生成绩.xls") + ";Extended Properties=Excel 8.0";
        //创建数据库连接
        OleDbConnection myConn = new OleDbConnection(strCon);
        //打开数据链接，得到一个数据集
        myConn.Open();
        //创建DataSet对象
        DataSet myDataSet = new DataSet();
        //定义查询的SQL语句
        string StrSql = "select   *   from   [" + StyleSheet + "$]";
        //创建数据库适配器
        OleDbDataAdapter myCommand = new OleDbDataAdapter(StrSql, myConn);
        //填充数据集中的数据
        myCommand.Fill(myDataSet, "[" + StyleSheet + "$]");
        //释放占有的资源
        myCommand.Dispose();
        //关闭数据库连接
        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();
    }
}