EXCEL表的读取与插入数据库（利用dataset更新数据库）

本文链接：https://blog.csdn.net/ZLHZHJ/article/details/7565076

1、.aspx页面
<html xmlns=" http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>

</head>
<body style="background-color: #e0e8f6;">
    <form id="form1" runat="server">
    <div style="width:100%">
        <table class="style1" width="100%">
            <tr>
                <td align="center">
                    <span lang="zh-cn">商家接口ID录入</span>
                </td>
            </tr>
            <tr>
                <td align="center" class="style2">
                    <span lang="zh-cn">选择数据文件：</span><asp:FileUpload ID="FileUpload1" runat="server" />
                    <span lang="zh-cn"> </span>
                    <asp:Button ID="btnShow" runat="server" Text="查看链接分配" οnclick="btnShow_Click" />
                </td>
            </tr>
            <tr>
                <td align="center">
                    <asp:GridView ID="GridView1" runat="server">
                    </asp:GridView>
                </td>
            </tr>
            <tr>
                <td align="center">
                    <asp:Button ID="btnImport" runat="server" Text="导入数据库"
                        οnclick="btnImport_Click" Height="21px"/>
                </td>
            </tr>
        </table>
    </div>
    </form>
</body>
</html>
2、.cs页面
protected void Page_Load(object sender, EventArgs e)
    {
        btnImport.Visible = false;
    }
    string constr = System.Configuration.ConfigurationManager.ConnectionStrings["DatabaseConn"].ConnectionString;// "Data Source=oradb9i;User Id=admaster;Password=admaster5703";//

    protected void btnShow_Click(object sender, EventArgs e)
    {
        try
        {
            //判断是否选中文件
            if (FileUpload1.FileName == string.Empty)
            {
                Response.Write("<script>alert('请选择文件！')</script>");
                return;
            }
            //源文件路径
            string strPath = ConfigurationManager.AppSettings["Upload"] + "LinkAssign/";
           // string strPath = "/UnionSky/upload/LinkAssign/";
            string strFileName = Server.MapPath(@"~" + strPath + FileUpload1.FileName);
            Response.Write(strFileName);
            //保存文件
             FileUpload1.SaveAs(strFileName);
              //string strFileName = FileUpload1.PostedFile.FileName;
              //文件读取链接字符串
              string strConn = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + strFileName + ";Extended Properties=Excel 8.0";
              OleDbConnection conn = new OleDbConnection(strConn);
              OleDbCommand cmd = new OleDbCommand();
              OleDbDataAdapter da = new OleDbDataAdapter(cmd);
              cmd.Connection = conn;
              cmd.CommandText = "select * from [Sheet1$]";
              cmd.CommandType = CommandType.Text;
              conn.Open();
              DataTable dtSource = new DataTable();
              da.Fill(dtSource);
              conn.Close();
              conn.Dispose();
              cmd.Dispose();

              GridView1.DataSource = dtSource;
              GridView1.DataBind();
              btnImport.Visible = true;
        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
        }
    }
    protected void btnImport_Click(object sender, EventArgs e)
    {
        string strConn = constr;
        OracleConnection conn = new OracleConnection(strConn);
        OracleCommand cmd = new OracleCommand();
        cmd.Connection = conn;
        cmd.CommandText = "select * from ad_interfacelink";
        cmd.CommandType = CommandType.Text;
        OracleDataAdapter da = new OracleDataAdapter(cmd);
        OracleCommandBuilder cb = new OracleCommandBuilder(da);
        DataTable dtLink = new DataTable();
        try
        {
            conn.Open();
            da.Fill(dtLink);

            foreach (GridViewRow row in GridView1.Rows)
            {
                string strAdId = row.Cells[0].Text.Trim();
                string strLinkId = row.Cells[1].Text.Trim();
                if (strLinkId == " ")
                {
                    continue;
                }

                if (dtLink.Select("adid = '" + strAdId + "' and linkid = '" + strLinkId + "'").Length == 0)
                {
                    DataRow newRow = dtLink.NewRow();
                    newRow["adid"] = strAdId;
                    newRow["linkid"] = strLinkId;
                    dtLink.Rows.Add(newRow.ItemArray);
                }
            }
            da.Update(dtLink);
            conn.Close();
            conn.Dispose();
            cmd.Dispose();
            Response.Write("<script>alert('导入成功！')</script>");
        }
        catch (OracleException ex)
        {
            Response.Write("<script>alert('导入出错！'" + ex.Message + "'')</script>");
        }
    }