c# execl 导入数据库

最新推荐文章于 2024-10-21 15:06:42 发布

原创最新推荐文章于 2024-10-21 15:06:42 发布 · 3.2k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#c# #string #dataset #button #excel #insert

asp.net 同时被 2 个专栏收录

60 篇文章

订阅专栏

41 篇文章

订阅专栏

本文介绍了一种从Excel文件中读取数据并将其导入到数据库的方法。具体实现了Excel文件的上传、验证、读取，并将数据写入到指定的数据库表中。同时，文章还涉及了对已有数据的检查及更新策略。

1. default.aspx.cs

using System.Data.OleDb;
using System.mySQLConnection;
using System.Data.SqlClient;

public 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);

            OleDbDataAdapter odda = new OleDbDataAdapter("select * from [外地$]",conn);

DataSet ds = new DataSet();

            odda.Fill(ds,table);

            return ds;

   }
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void Button1_ServerClick(object sender, EventArgs e)
    {

        string IsXls = System.IO.Path.GetExtension(FileUpload1.FileName.ToString().ToLower());
        if (IsXls != ".xls")
        {
          Page.ClientScript.RegisterStartupScript(Page.GetType(), "", "<script type='text/javascript' language='javascript'>alert('只可以选择Excel文件')</script>");
          return;//当选择的不是Excel文件时,返回
        }
       string error = null;

       SqlConnections cn = new SqlConnections();

       string strpath = FileUpload1.PostedFile.FileName.ToString();

        //获取Execle文件路径
       string filename = FileUpload1.FileName;
        //获取Execle文件名
       DataSet ds = ExecleDs(strpath,filename);

       DataRow[] dr = ds.Tables[0].Select();
        //定义一个DataRow数组
      int rowsnum = ds.Tables[0].Rows.Count;
      if (rowsnum == 0)
      {
           Page.ClientScript.RegisterStartupScript(Page.GetType(), "", "<script type='text/javascript' language='javascript'>alert('Excel表为空表,无数据!')</script>");

            return;
       }
     else
      {
         for (int i = 0; i < dr.Length; i++)
           {
               string YHMC = dr[i]["通知书号"].ToString();
               string YHMM = dr[i]["姓名"].ToString();
               string DQRQ = dr[i]["身份证号"].ToString();
               string ZT = dr[i]["性别"].ToString();
               string TJSJ = dr[i]["专业"].ToString();
               string JXDM = dr[i]["住址"].ToString();
               string youbian = dr[i]["邮编"].ToString();
               string dianhua = dr[i]["联系电话"].ToString();
               string shouji = dr[i]["手机"].ToString();
               string tuijianren = dr[i]["推荐人"].ToString();

//还缺少，学生ID号，学生报名号

string sqlcheck = "select f_StudentID from t_studentInfo where f_Name='" + YHMM + "'And f_Identify='" + DQRQ + "'"; //检查用户是否存在

DataTable CH = System.mySQLConnection.SqlConnections.GetDataTable(sqlcheck, "aa");

//得到专业ID

string sql = "select o_id from t_setclass where o_outname='" + TJSJ + "'";

string zhuanyeid = System.mySQLConnection.SqlConnections.GetNumOneField(sql);

               if (CH != null && CH.Rows.Count > 0)
               {
                   error += "<em style='color:red;font-sixe:25px'>" + YHMM + "</em>用户基本信息已存在,此行记录无法插入!<br>";   //若用户存在,将已存在用户信息打出,并提示此用户无法插入

string baomingId = System.mySQLConnection.SqlConnections.GetNumOneField("select dbo.myFun_StudentInfo_Flowid(getDate())");

string sqltext = "insert into t_StudentAdmit(f_FlowID,f_StudentID,o_id,t_tuijianname,f_zhengshu) values('" + baomingId + "','" + CH.Rows[i]["f_StudentID"].ToString() + "','" + zhuanyeid + "','" + tuijianren + "','" + YHMC + "')";

System.mySQLConnection.SqlConnections.UpdateOrDelete(sqltext);

                   continue;
               }
               else
               {
                  string studentId = System.mySQLConnection.SqlConnections.GetNumOneField("select dbo.myFun_StudentInfo_XueHao(getDate())");

string sqltest = "insert into t_studentInfo(f_StudentID,f_Name,f_Gender,f_Identify,f_detail,f_youbian,f_zuoji,f_shouji,f_tuijianren) values('" + studentId + "','" + YHMM + "','" + ZT + "','" + DQRQ + "','" + JXDM + "','" + youbian + "','" + dianhua + "','" + shouji + "','" + tuijianren + "')";

System.mySQLConnection.SqlConnections.UpdateOrDelete(sqltest);

string baomingId = System.mySQLConnection.SqlConnections.GetNumOneField("select dbo.myFun_StudentInfo_Flowid(getDate())");

string sqltext1 = "insert into t_StudentAdmit(f_FlowID,f_StudentID,o_id,t_tuijianname,f_zhengshu) values('" + baomingId + "','" + studentId + "','" + zhuanyeid + "','" + tuijianren + "','" + YHMC + "')";

                  System.mySQLConnection.SqlConnections.UpdateOrDelete(sqltext1);

               }
           }
           Response.Write("<script>alert('Excle表导入成功!')</script>");

           Label1.Text = error;
         }
    }

2.default.aspx