随笔-68  评论-89  文章-0 

将xls文件导入数据库

一点点代码,上传xls,然后将它导入到数据库相应的表

protected void Click()
    {
        string postName=file1.PostedFile.FileName;
        if (postName == "")
        {
            Response.Write("<script>alert('请先选择文件.');location='add.aspx';</script>");
        }
        else
        {
            string str = this.UpLoadFile();
            if (str == "Error")
            {
                Response.Write("<script>alert('上传xls文件失败.');location='add.aspx';</script>");
            }
            else
            {
                string mystring = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = "+str+";Extended Properties=Excel 8.0";
                OleDbConnection cnnxls = new OleDbConnection(mystring);
                OleDbDataAdapter myDa = new OleDbDataAdapter("select * from [Sheet1$]", cnnxls);
                DataSet myDs = new DataSet();
                myDa.Fill(myDs);
                if (myDs.Tables[0].Rows.Count > 0)
                {
                    string strSql = "";
                    string CnnString = "Provider=SQLOLEDB;database=testnews;server=(local);uid=sa;pwd=";
                    OleDbConnection conn = new OleDbConnection(CnnString);
                    conn.Open();
                    OleDbCommand myCmd = null;
                    for (int i = 0; i < myDs.Tables[0].Rows.Count; i++)
                    {
                        strSql = "insert into website(sname,surl) values ('";
                        strSql += myDs.Tables[0].Rows[i].ItemArray[0].ToString() + "', '";
                        strSql += myDs.Tables[0].Rows[i].ItemArray[1].ToString() + "')";
                        try
                        {
                            myCmd = new OleDbCommand(strSql, conn);
                            myCmd.ExecuteNonQuery();
                            Response.Write("<script language=javascript>alert('数据导入成功.');location='index.aspx';</script>");
                        }
                        catch
                        {
                            Response.Write("<script language=javascript>alert('数据导入失败.');location='add.aspx';</script>");
                        }
                    }
                }
             }
        }

posted on 2007-10-05 16:17 噢耶游戏 阅读(...) 评论(...) 编辑 收藏