excel导入

最新推荐文章于 2024-10-15 20:40:36 发布

原创最新推荐文章于 2024-10-15 20:40:36 发布 · 369 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#excel #dataset #string #random #properties #sql

.net 专栏收录该内容

7 篇文章

订阅专栏

本博客介绍了一种将Excel文件中的员工信息批量导入到数据库的方法，包括连接数据库、读取Excel数据、解析数据并更新数据库记录。通过实现一个事务处理流程，确保数据的一致性和准确性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

     TransactionProcess tran = new TransactionProcess();
        ArrayList grouplist = new ArrayList();
        try
        {
            if (FileUpload1.PostedFile != null)
            {
                Random rd = new Random(1);
                string filename = DateTime.Now.Date.ToString("yyyymmdd") + DateTime.Now.ToLongTimeString().Replace(":", "") + rd.Next(9999).ToString() + ".xls";
                FileUpload1.PostedFile.SaveAs(Server.MapPath("../../../File/Upload/") + filename);
                string conn = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + Server.MapPath("../../../File/Upload/") + filename + ";Extended Properties=Excel 8.0";
                OleDbConnection thisconnection = new OleDbConnection(conn);
                thisconnection.Open();
                string Sql = "select * from [Sheet1$]";
                OleDbDataAdapter mycommand = new OleDbDataAdapter(Sql, thisconnection);
                DataSet ds = new DataSet();
                mycommand.Fill(ds, "[Sheet1$]");
                thisconnection.Close();

                int usercount = ds.Tables["[Sheet1$]"].Rows.Count;
                employee_recruitmentHome employee_recruitmenthome = new employee_recruitmentHome();
                employee_recruitmentData employee_recruitmentdata = new employee_recruitmentData();
                //获取所有数据
                for (int i = 0; i < usercount; i++)
                {
                    employee_recruitmentdata.innerid = employee_recruitmenthome.GetSequence();
                    employee_recruitmentdata.name = ds.Tables["[Sheet1$]"].Rows[i][0].ToString().Trim();
                    employee_recruitmentdata.ypzw = ds.Tables["[Sheet1$]"].Rows[i][2].ToString().Trim();
                    employee_recruitmentdata.gzdd = ds.Tables["[Sheet1$]"].Rows[i][3].ToString().Trim();
                    employee_recruitmentdata.yprq = ds.Tables["[Sheet1$]"].Rows[i][4].ToString().Trim();
                    employee_recruitmentdata.gender = ds.Tables["[Sheet1$]"].Rows[i][5].ToString().Trim();
                    employee_recruitmentdata.birthday = ds.Tables["[Sheet1$]"].Rows[i][6].ToString().Trim();
                    employee_recruitmentdata.mqjzd = ds.Tables["[Sheet1$]"].Rows[i][7].ToString().Trim();
                    employee_recruitmentdata.birthplace = ds.Tables["[Sheet1$]"].Rows[i][8].ToString().Trim();
                    employee_recruitmentdata.gznx = ds.Tables["[Sheet1$]"].Rows[i][9].ToString().Trim();
                    employee_recruitmentdata.degree = ds.Tables["[Sheet1$]"].Rows[i][10].ToString().Trim();
                    employee_recruitmentdata.byyx = ds.Tables["[Sheet1$]"].Rows[i][11].ToString().Trim();
                    employee_recruitmentdata.sxzy = ds.Tables["[Sheet1$]"].Rows[i][12].ToString().Trim();
                    employee_recruitmentdata.mobilephone = ds.Tables["[Sheet1$]"].Rows[i][13].ToString().Trim();
                    employee_recruitmentdata.email = ds.Tables["[Sheet1$]"].Rows[i][14].ToString().Trim();
                    employee_recruitmentdata.homeaddress = ds.Tables["[Sheet1$]"].Rows[i][15].ToString().Trim();
                    employee_recruitmentdata.zipcode = ds.Tables["[Sheet1$]"].Rows[i][16].ToString().Trim();
                    employee_recruitmentdata.zjgs = ds.Tables["[Sheet1$]"].Rows[i][17].ToString().Trim();
                    employee_recruitmentdata.zjzw = ds.Tables["[Sheet1$]"].Rows[i][18].ToString().Trim();
                    employee_recruitmentdata.mqnx = ds.Tables["[Sheet1$]"].Rows[i][19].ToString().Trim();
                    employee_recruitmentdata.qwxs = ds.Tables["[Sheet1$]"].Rows[i][20].ToString().Trim();
                    employee_recruitmentdata.sfms = "否";
                    employee_recruitmentdata.sftzms = "否";
                    employee_recruitmentdata.sfcjms = "否";
                    employee_recruitmentdata.sftg = "否";
                    //存在相同的船名和初次登记号的就更新
                    grouplist.Add(employee_recruitmenthome.AddToStr(employee_recruitmentdata));
                }
                //执行Obj.
                if (tran.RunTransaction(grouplist) > 0)
                {
                    //成功
                    JS obj = new JS();
                    obj.Alert("导入成功！", this);
                    string fileName = Server.MapPath("../../../File/Upload/") + filename;
                    File.Delete(fileName);
                }
                else
                {
                    //失败
                    JS obj = new JS();
                    obj.Alert("导入失败！", this);
                }
            }
        }
        catch
        {
            JS obj = new JS();
            obj.Alert("导入失败！", this);
        }