asp.net中处理excel的导入问题

一、方法一：

 //导入
        public static string into(string filename,string sqlinsert)
        {
            try
            {
                string mystring = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + filename + ";Extended Properties=Excel 8.0";//获取excel文件
                OleDbConnection cnnxls = new OleDbConnection(mystring);//用数据库连接excel
                cnnxls.Open();

                Microsoft.Office.Interop.Excel.ApplicationClass oExcel = new Microsoft.Office.Interop.Excel.ApplicationClass();
                object oMissing = System.Reflection.Missing.Value;

                oExcel.Workbooks.Add(oMissing);
                Microsoft.Office.Interop.Excel.Workbook oBook = oExcel.Workbooks[1];
                Microsoft.Office.Interop.Excel.Worksheet oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oBook.Sheets[1];
                string sheetname = oSheet.Name;
                OleDbDataAdapter myDa = new OleDbDataAdapter("select * from [Sheet1$]", cnnxls);//创建匹配器
                DataSet myDs = new DataSet();
                myDa.Fill(myDs);
                if (myDs.Tables[0].Rows.Count > 0)
                {
                    SqlConnection strcon = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["Trafficdb"]);
                    strcon.Open();
                    SqlCommand myCmd = null;
                    for (int i = 1; i < myDs.Tables[0].Rows.Count; i++)
                    {
                        int j;
                        for (j = 0; j < myDs.Tables[0].Columns.Count - 1; j++)
                        {
                            sqlinsert += myDs.Tables[0].Rows[i].ItemArray[j].ToString() + "', '";
                        }
                        sqlinsert += myDs.Tables[0].Rows[i].ItemArray[j].ToString() + "' )";
                        myCmd = new SqlCommand(sqlinsert, strcon);
                        myCmd.ExecuteNonQuery();//写入SQL数据库
                        sqlinsert = sqlinsert.Substring(1, sqlinsert.IndexOf("'"));
                    }
                    strcon.Close();
                    strcon.Dispose();
                }
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            return null;

 

 

 

二、方法二：

  protected void Imagedaoru_Click(object sender, ImageClickEventArgs e)//导入
    {
      try
       {
        string filename = Path.GetFileName(FileUpload1.PostedFile.FileName);//获得文件名
        FileUpload1.PostedFile.SaveAs(@Server.MapPath(filename));//保存文件
        //string mystring = "Provider=Microsoft.Jet.OLEDB.4.0 ;Data Source='E:/test.xls';Extended Properties=Excel 8.0";

      string mystring = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + Server.MapPath(filename) + ";Extended       Properties=Excel 8.0";//获取excel文件
      OleDbConnection cnnxls = new OleDbConnection(mystring);//用数据库连接excel
      cnnxls.Open();
      OleDbDataAdapter myDa = new OleDbDataAdapter("select * from [Sheet1$]", cnnxls);//创建匹配器
      DataSet myDs = new DataSet();
      myDa.Fill(myDs);
      if (myDs.Tables[0].Rows.Count > 0)
       {
         string strSql = "";
         SqlConnection strcon = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["constr"]);
         strcon.Open();
         SqlCommand myCmd = null;
        for (int i = 3; i < myDs.Tables[0].Rows.Count; i++)
        {
          strSql = "insert into YouFu_YF_ Handicapped(姓名,性别,身份证号,入伍时间,退伍时间,残疾军人,伤残人民警察,伤残国家机关          工作人员,伤残民兵民工,因战,因公,因病,证号,户籍地址,抚恤金,护理费,银行账号,备注) values ('";
          strSql += myDs.Tables[0].Rows[i].ItemArray[0].ToString() + "', '";
          strSql += myDs.Tables[0].Rows[i].ItemArray[1].ToString() + "','";
          strSql += myDs.Tables[0].Rows[i].ItemArray[2].ToString() + "', '";
          strSql += myDs.Tables[0].Rows[i].ItemArray[3].ToString() + "','";
          strSql += myDs.Tables[0].Rows[i].ItemArray[4].ToString() + "', '";
          strSql += myDs.Tables[0].Rows[i].ItemArray[5].ToString() + "','";
                        strSql += myDs.Tables[0].Rows[i].ItemArray[6].ToString() + "', '";
                        strSql += myDs.Tables[0].Rows[i].ItemArray[7].ToString() + "','";
                        strSql += myDs.Tables[0].Rows[i].ItemArray[8].ToString() + "','";
                        strSql += myDs.Tables[0].Rows[i].ItemArray[9].ToString() + "','";
                        strSql += myDs.Tables[0].Rows[i].ItemArray[10].ToString() + "','";
                        strSql += myDs.Tables[0].Rows[i].ItemArray[11].ToString() + "', '";
                        strSql += myDs.Tables[0].Rows[i].ItemArray[12].ToString() + "','";
                        strSql += myDs.Tables[0].Rows[i].ItemArray[13].ToString() + "', '";
                        strSql += myDs.Tables[0].Rows[i].ItemArray[14].ToString() + "','";
                        strSql += myDs.Tables[0].Rows[i].ItemArray[15].ToString() + "', '";
                        strSql += myDs.Tables[0].Rows[i].ItemArray[16].ToString() + "','";
                        strSql += myDs.Tables[0].Rows[i].ItemArray[17].ToString() + "' )";
                        myCmd = new SqlCommand(strSql, strcon);
                        myCmd.ExecuteNonQuery();//写入SQL数据库

                    }
                    strcon.Close();
                    strcon.Dispose();
                    Response.Write("<script language=javascript>alert('数据导入成功')</script>");

                }
            }
            catch
            {
                Response.Write("<script language=javascript>alert('导入数据失败!')</script>");

            }
        }