asp.net excel数据通过程序导入mysql

最新推荐文章于 2024-07-14 17:30:21 发布

给力地板油

最新推荐文章于 2024-07-14 17:30:21 发布

阅读量986

点赞数

分类专栏：技术类文章标签： asp.net excel dataset string server javascript

本文链接：https://blog.csdn.net/wslyy99/article/details/1886189

版权

技术类专栏收录该内容

107 篇文章 0 订阅

订阅专栏

前台:

<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <title>test</title>
    </head>
<body >
    <form id="form1" runat="server">
    <div align="center" style="font-size: 10px; font-family: Arial; text-decoration: none">
      <table width="100%" background="images/d6.gif">
                 <tr><td style="width:100% ; height: 62px;"><table width="100%" align=center>
            <tr align="left">
                <td class="a" style="height: 17px; width: 40%;">
                   </td>
                <td colspan="2" rowspan="2" align="center" style="width: 20%">
                    <asp:Image ID="Image1" runat="server" Height="42px" ImageUrl="~/images/connect24_logo.gif"
                        Width="45px" /></td>
                <td align="right" colspan="3" rowspan="2" style="width: 40%">                      
                    </td>
            </tr>
            <tr align="left">
                <td class="a" style="height: 18px;width:35%">
                  </td>
            </tr>
      </table></td></tr></table>
      <table width="100%" border="0" cellpadding="0" cellspacing="0">
        <tr>
          <td width="100%" height="20px" align="left" valign="bottom" background="images/d1.gif"> </td>
      </tr>
</table>
        <table style="width: 100%">
            <tr>
                <td style="font-weight: bold; font-size: 9pt; font-family: Arial; width: 763px;" align="center">
                    Please choose a xls file: <input id="File1" runat="server" style="width: 615px"
                        type="file" /> 
                </td>
            </tr>
            <tr>
                <td align="center" style="font-weight: bold; font-size: 9pt; width: 763px; font-family: Arial">
                    Please choose:<asp:DropDownList ID="ddlDealer" runat="server" CssClass="b">
                    </asp:DropDownList></td>
            </tr>
            <tr>
                <td style="height: 21px; font-size: 9pt; width: 763px; font-family: Arial;" align="center">
                    <asp:Button ID="btnUpLoad" runat="server" Text="UpLoad" OnClick="btnUpLoad_Click" />
                    <asp:Button ID="btnClose" runat="server" OnClick="btnClose_Click" Text="Close" /></td>
            </tr>
            <tr>
                <td align="center" style="font-size: 9pt; width: 763px; font-family: Arial; height: 21px">
                    <asp:Label ID="Label1" runat="server" Font-Names="Arial" Font-Size="10px" Text="Label"
                        Visible="False"></asp:Label></td>
            </tr>
            <tr>
                <td align="center" style="font-weight: bold; font-size: 10pt; width: 763px; font-family: Arial;
                    height: 21px">
                    </td>
            </tr>
            <tr>
                <td align="left" style="font-weight: normal; font-size: 10pt; width: 763px; font-family: Arial;
                    height: 21px">
                                </td>
            </tr>
            <tr>
                <td align="left" style="font-weight: normal; font-size: 10pt; width: 763px; font-family: Arial;
                    height: 21px">
                </td>
            </tr>
            <tr>
                <td align="left" style="width: 763px; height: 21px">
                     <asp:GridView ID="GridView1" runat="server">
                    </asp:GridView>
                </td>
            </tr>
            <tr>
                <td style="height: 26px; width: 763px;">
                    <input id="hidsysMsg" runat="server" type="hidden" /></td>
            </tr>
        </table></div>
    </form>
</body>
</html>

后台

protected void Page_Load(object sender, EventArgs e)
    {
        hidsysMsg.Value = "";
        if (!Page.IsPostBack)
        {
             this.GridView1.Visible = false;
        }
    }

       public DataSet GetDS(string filepath)
    {
        string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + filepath + ";Extended Properties=Excel 8.0;";
        OleDbConnection myConn = new OleDbConnection(strCon);
        myConn.Open();
        string aa = filepath;
        try
        {
            string strCom = " Select * FROM [Sheet1$] ";
            OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
            DataSet ds = new DataSet();
            myCommand.Fill(ds, "[Sheet1$]");

            return ds;
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            myConn.Close();
        }
    }

    protected void btnUpLoad_Click(object sender, EventArgs e)
    {
        if (this.File1.PostedFile.FileName == "")
        {
            Response.Write("<script language='javascript'>alert('please choose a xls file')</script>");

        }
        else
        {
            if (this.ddlDealer.SelectedValue.ToString() == "None")
            {
                Response.Write("<script language='javascript'>alert('please choose )</script>");
            }
            else
            {
                string fullfilename = this.File1.PostedFile.FileName;
                string filename = fullfilename.Substring(fullfilename.LastIndexOf("//") + 1);
                this.File1.PostedFile.SaveAs(Server.MapPath("upfile") + "//" + filename);
                string filepath = Server.MapPath("upfile/" + filename);

string shortname = fullfilename.Substring(fullfilename.LastIndexOf(".") + 1);

                if (shortname == "xls")
                {
                    string filepath1 = filepath.Remove(Convert.ToInt32(filepath.LastIndexOf("//") + 1));
                    string filename1 = filepath.Substring(filepath.LastIndexOf("//") + 1);
                    this.GridView1.DataSource = this.GetDS(filepath).Tables[0].DefaultView;
                    this.GridView1.DataBind();
                    GridView1.Visible = true;

                    DataSet mydataset;
                   mydataset = this.GetDS(filepath);
                    string _sqlStr = "";

                    string _conStr = SysConfig.ConStr;
                    OdbcConnection _odbcCon = new OdbcConnection(_conStr);
                    _odbcCon.Open();

                    OdbcTransaction trans = _odbcCon.BeginTransaction();
                    OdbcCommand cmd = new OdbcCommand();
                    cmd.Connection = trans.Connection;
                    cmd.Transaction = trans;

                    try
                    {
                        for (int i = 0; i < mydataset.Tables[0].Rows.Count; i++)
                        {
                            _sqlStr = "";
                            _sqlStr = "insert into TEST(WGL_ACount_ID,WGL_AccountType,W_Description,W_QuickBooksAccountTypee)";
                            _sqlStr += "values (";
                            _sqlStr += "'" + mydataset.Tables[0].Rows[i][0].ToString().Trim() + "',";
                            _sqlStr += "'" + mydataset.Tables[0].Rows[i][3].ToString().Trim() + "',";
                            _sqlStr += "'" + mydataset.Tables[0].Rows[i][1].ToString().Trim().Replace("'","''") + "',";
                            _sqlStr += "'" + mydataset.Tables[0].Rows[i][2].ToString().Trim() + "',";
                          _sqlStr += ")";

                            cmd.CommandText = _sqlStr;
                            cmd.ExecuteNonQuery();
                        }
                    }
                    catch
                    {
                        trans.Rollback();
                        this.Label1.Text = "Data save Fail";
                        return;
                    }
                    trans.Commit();
                    this.Label1.Text = "Data save over";
                    this.Label1.Visible = true;
                    _odbcCon.Close();
                }
                else
                {
                    Response.Write("<script language='javascript'>alert('please choose a xls file')</script>");
                }
            }
        }
    }
    protected void btnClose_Click(object sender, EventArgs e)
    {
        Response.Redirect("Referrals-List.aspx");
    }