Asp.net中把Excel資料存儲至SQL Server中

操作圖

excelwrapper

        /// <summary>

        /// 查詢excel電子表格添加到dataset

        /// </summary>

        /// <param name="filenameurl">檔案路徑</param>

        /// <param name="table">dataset中的表名(并不是要和資料庫中的表一樣)</param>

        /// <returns></returns>

        public static dataset execleds(string filenameurl, string table)

        {

            string strconn = "provider=microsoft.jet.oledb.4.0;"

                                         + "data source=" + filenameurl + ";extended properties='excel 8.0; hdr=yes; imex=1'";

            oledbconnection conn = new oledbconnection(strconn);

            conn.open();

            dataset ds = new dataset();

            oledbdataadapter odda = new oledbdataadapter("select * from [sheet1$]", conn);

            odda.fill(ds, table);

            return ds;

        }

.cs

       // 送出按鈕

        protected void imgbtnsubmit_click(object sender, imageclickeventargs e)

            try

            {

                if (!fileupload1.hasfile)

                {

                    jshelper.alert("請您選擇excel檔案", this);

                    return;

                }

                // 取得檔案字尾名

                string extension = system.io.path.getextension(fileupload1.filename).tostring().tolower();

                if (extension != ".xls" && extension != ".xlsx")

                    jshelper.alert("隻可以選擇excel檔案", this);

                //  構造exel存在伺服器相對路徑的檔案名，并saveas 将上傳的檔案内容儲存在伺服器上

                string filename = datetime.now.tostring("yyyymmddhhmmss") + fileupload1.filename;

                string savepath = server.mappath(("~\\upfiles\\") + filename);

                fileupload1.saveas(savepath);

                dataset ds = excelwrapper.execleds(savepath, filename);

                datarow[] dr = ds.tables[0].select();

                int rowsnum = ds.tables[0].rows.count;

                list<string> lstmsg = new list<string>();

                if (rowsnum == 0)

                    jshelper.alert("excel表為空表,無資料", this);

                else

                    for (int i = 0; i < dr.length; i++)

                    {

                        string error = "";

                        // excel列名不能變

                        string num = dr[i]["學号"].tostring();

                        string name = dr[i]["姓名"].tostring();

                        string pwd = dr[i]["密碼"].tostring();

                        string collegenum = dr[i]["學院編号"].tostring();

                        string birth = dr[i]["生日"].tostring();

                        if (!bll.m_collegebll.getallcollegenum().contains(collegenum))

                        {

                            error += "所屬學院不存 ";

                        }

                        if (string.isnullorempty(collegenum))

                            error += "請選擇該學生所在院系 ";

                        if (string.isnullorempty(num))

                            error += "學号不能為空 ";

                        else if (!utility.isletterthansomelength(num, 25))

                            error += "學号的長度過長 ";

                        if (string.isnullorempty(name))

                            error += "姓名不能為空 ";

                        else if (!utility.isletterthansomelength(name, 25))

                            error += "姓名的長度過長 ";

                         if (string.isnullorempty(birth))

                            error += "出生日期不能為空 ";

                        else if (!utility.isdatetime(birth))

                            error += "出生日期格式不正确 ";

                        if (string.isnullorempty(sex))

                            error += "性别不能為空 ";

                        if (string.isnullorempty(error))

                            m_student stu = new m_student();

                            stu.num = num;

                            stu.name = name;

                            stu.pwd = pwd;

                            stu.collegenum = collegenum;

                            stu.birthday = convert.todatetime(birth);

                            // 該學号不存在

                            if (!bll.m_studentbll.getallstunum().contains(num))

                            {

                                bll.m_studentbll.add(stu);

                            }

                            else

                                bll.m_studentbll.modify(stu);

                        else

                            lstmsg.add("學号為" + num + "未導入成功，" + "原因:" + error + "。");

                    }

                this.lblhint.text = "導入完成。";

                if (null != lstmsg)

                    this.lblhint.text += "共有" + lstmsg.count() + "條記錄未成功。<br /><br />";

                    foreach (string s in lstmsg)

                        this.lblhint.text += s;

            }

            catch

                this.lblhint.text = "程式出錯，請您檢查需要導入的表！";

效果圖