将EXCEL表格导入数据库

     /// <summary>
        /// 获取导入文件路径,判断导入文件的格式,并且返回文件路径
        /// </summary>
        /// <param name="fileloads"></param>
        /// <returns></returns>
        private String ExcelFilePath(FileUpload fileloads)
        {
            string fullfilename = this.ExcelFile.PostedFile.FileName;
            string filename = fullfilename.Substring(fullfilename.LastIndexOf("//") + 1);
            string type = fullfilename.Substring(fullfilename.LastIndexOf(".") + 1);
            string murl = "";
            if (type == "xls" || type == "xlsx")
            {
                this.ExcelFile.PostedFile.SaveAs(AppDomain.CurrentDomain.BaseDirectory + "Pages//Manager//WorkListManager//ExcelFiles" + "//" + filename);
                murl = AppDomain.CurrentDomain.BaseDirectory + "Pages//Manager//WorkListManager//ExcelFiles" + "//" + filename;
            }
            else
            {
                Alert("导入文件的格式不正确!!!");
            }
            return murl;
        }

 /// <summary>
        /// 把Excel中的数据把导入到数据库中,导入按钮触发领取事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void lblSave_Click(object sender, EventArgs e)
        {
            if (this.ExcelFile.PostedFile != null)
            {
                try
                {
                    string fileurl = ExcelFilePath(ExcelFile);
                    AttendanceMonitor vws = new AttendanceMonitor();
                    DataSet ds = vws.ExcelDate(fileurl);
                    int errorcount = 0;
                    int insertcount = 0;
                    int repeatcount = 0;
                    //循环获取Excel文件中的数据
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        string WorkTime = ds.Tables[0].Rows[i][0].ToString();
                        string StartTime = DateTime.Parse(WorkTime).ToString("yyyy-MM-dd ") + DateTime.Parse(ds.Tables[0].Rows[i][1].ToString()).ToString("HH:mm:ss");
                        string EndTime = DateTime.Parse(WorkTime).ToString("yyyy-MM-dd ") + DateTime.Parse(ds.Tables[0].Rows[i][2].ToString()).ToString("HH:mm:ss");
                        string Username = ds.Tables[0].Rows[i][3].ToString();
                        string Shopname = ds.Tables[0].Rows[i][4].ToString();
                        //判断获取的Excel文件中是否有空的数据
                        if (WorkTime != "" && StartTime != "" && EndTime != "" && Username != "" && Shopname != "")
                        {
                            //检查员工名称是否存在
                            bool bu = vws.CheckUserName(Username);
                            if (bu)
                            {
                                //检查体验店名称是否存在
                                bool bs = vws.CheckShopName(Shopname);
                                if (bs)
                                {
                                    insertcount++;
                                }
                                else
                                {
                                    errorcount++;
                                    Alert("第" + (i + 1) + "行体验店名称暂时不存在,请检查后再进行导入!!!");
                                }
                            }
                            else
                            {
                                errorcount++;
                                Alert("第" + (i + 1) + "行员工名称暂时不存在,请检查后再进行导入!!!");
                            }
                        }
                        else
                        {
                            errorcount++;
                            Alert("第" + (i + 1) + "行数据部分信息为空,请检查后再进行导入!!!");
                        }
                    }
                    //如果所有的数据全部合法,开始循环进行导入
                    if (errorcount == 0)
                    {
                        for (int j = 0; j < ds.Tables[0].Rows.Count; j++)
                        {
                            DateTime WorkTime = Convert.ToDateTime(ds.Tables[0].Rows[j][0].ToString());
                            DateTime StartTime = Convert.ToDateTime(DateTime.Parse(ds.Tables[0].Rows[j][0].ToString()).ToString("yyyy-MM-dd ") +
                                DateTime.Parse(ds.Tables[0].Rows[j][1].ToString()).ToString("HH:mm:ss"));
                            DateTime EndTime = Convert.ToDateTime(DateTime.Parse(ds.Tables[0].Rows[j][0].ToString()).ToString("yyyy-MM-dd ") +
                                DateTime.Parse(ds.Tables[0].Rows[j][2].ToString()).ToString("HH:mm:ss"));
                            string Username = ds.Tables[0].Rows[j][3].ToString();
                            string Shopname = ds.Tables[0].Rows[j][4].ToString();
                            vws.WorkTime = WorkTime;
                            vws.WorkStartTime = StartTime;
                            vws.WorkEndTime = EndTime;
                            User u = new User();
                            u.CName = Username;
                            vws.UserID = Convert.ToInt32(u.GetUserEntityInfo().ID);
                            VWShop s = new VWShop();
                            s.Name = Shopname;
                            vws.ShopID = Convert.ToString(s.GetInfo().ID);
                            //检查所导入Excel文件中的数据是否跟数据库中有重复的数据
                            bool b = vws.CheckExcelData(vws.WorkTime.Value, vws.WorkStartTime.Value, vws.WorkEndTime.Value, vws.UserID.Value, vws.ShopID);
                            if (!b)
                            {
                                vws.AddKQPersonInfo();
                            }
                            else
                            {
                                repeatcount++;
                            }
                        }
                        if (insertcount == repeatcount)
                        {
                            Alert("共" + insertcount + "条数据," + repeatcount + "条数据已全部存在无法重复导入!");
                        }
                        else
                        {
                            if (repeatcount == 0)
                            {
                                Alert("共" + insertcount + "条数据," + insertcount  + "条数据已全部导入成功!");
                            }
                            else
                            {
                                Alert("共" + insertcount + "条数据," + (insertcount - repeatcount) + "条数据已导入成功," + repeatcount + "条数据已存在无法重复导入!");
                            }
                        }

                        hidePopWin();
                    }
                }
                catch
                {
                    Alert("数据导入失败!!!");
                }
            }
        }

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值