从Excel文件中读取内容

时间：2023-01-03 05:37:55

从Excel文件中读取内容

　　　　　　 global::System.Web.HttpPostedFileBase file = Request.Files["txtFile"];

            string FileName;

            string savePath;

            if (file == null || file.ContentLength <= )

            {

                ViewBag.error = "文件不能为空";

                return View();

            }

            else

            {

                string filename = global::System.IO.Path.GetFileName(file.FileName);

                int filesize = file.ContentLength; //获取上传文件的大小单位为字节byte

                string fileEx = global::System.IO.Path.GetExtension(filename); //获取上传文件的扩展名

                string NoFileName = global::System.IO.Path.GetFileNameWithoutExtension(filename); //获取无扩展名的文件名

                int Maxsize = *; //定义上传文件的最大空间大小为4M

                string FileType = ".xls,.xlsx"; //定义上传文件的类型字符串

                FileName = NoFileName + global::System.DateTime.Now.ToString("yyyyMMddhhmmss") + fileEx;

                if (!FileType.Contains(fileEx))

                {

                    ViewBag.error = "文件类型不对，只能导入xls和xlsx格式的文件";

                    return View();

                }

                if (filesize >= Maxsize)

                {

                    ViewBag.error = "上传文件超过4M，不能上传";

                    return View();

                }

                string path = global::System.AppDomain.CurrentDomain.BaseDirectory + "Download/excel/";//路径

                savePath = global::System.IO.Path.Combine(path, FileName);

                file.SaveAs(savePath);

            }

            string strConn;

            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + savePath + ";" + "Extended Properties=Excel 8.0";

            var conn = new global::System.Data.OleDb.OleDbConnection(strConn);

            conn.Open();

            var myCommand = new global::System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", strConn);//查找表名为Sheet1表内的值

            var myDataSet = new global::System.Data.DataSet();

            try

            {

                myCommand.Fill(myDataSet, "ExcelInfo");

            }

            catch (global::System.Exception ex)

            {

                ViewBag.error = ex.Message;

                return View();

            }

            var table = myDataSet.Tables["ExcelInfo"].DefaultView.ToTable();

            var pBll = new PersonnelBLL();

            var msg = string.Empty;

            for (int i = ; i < table.Rows.Count; i++)

            {

                var perModel = new Personnel();

                perModel.RjPerId = table.Rows[i][].ToString();//第一列

                perModel.LoginName = table.Rows[i][].ToString();

                perModel.Name = table.Rows[i][].ToString();

                perModel.Gender = table.Rows[i][].ToString().Equals("男") ?  : ;

                perModel.Positional = table.Rows[i][].ToString();

                perModel.Birthday = string.IsNullOrEmpty(table.Rows[i][].ToString().Trim())

                    ? DateTime.Now.ToString()

                    : table.Rows[i][].ToString().Trim();

                perModel.IDCard = table.Rows[i][].ToString();

                perModel.Mobile = table.Rows[i][].ToString();

                perModel.Status = table.Rows[i][].ToString().Equals("有效") ?  : ;

                perModel.Password = null;

                perModel.Sort = +i;

                perModel.AddTime = DateTime.Now;

                perModel.ID = null;

                perModel.Description = "数据导入";

                perModel.DepartmentID = "";

                var returnNum = pBll.Insert(perModel, new string[] {"9a0e6c1860aa4b22a57fb847b87fcaf7"});

　　　　　　　　　}

相关文章