将Excel中的数据传到数据库中

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BnbCRM.Models;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;
using System.Threading;
using System.Configuration;

{
public partial class Survey_FormtbOBCustListImport : System.Web.UI.Page
    {
  protected void Button1_Click(object sender, EventArgs e)
    {
        string filename = FileUpload1.FileName.ToLower();
        if (!filename.EndsWith(".xls"))
        {
            msgLabel.Text = "只能接收.xls文件";
            return;
        }

        try
        {
            DateTime dt1 = DateTime.Now;
            if (!Directory.Exists("D://web"))
            {
                Directory.CreateDirectory("D://web");
            }
            string path = "D://web//" + filename;

            FileUpload1.SaveAs(path);
              getExcel(project_id, path);

            File.Delete("D://web//"+ filename);

}

 

      public static int getExcel(int project_id,string path)
      {

      using (SqlConnection sConn = new SqlConnection(ConfigurationManager.ConnectionStrings["CommonEcrmDBConnectionString"].ConnectionString))
            {
            string[] name = GetTablesFromOleDb(path);
            string a=name[0].Replace("'","");
                string OLEDBConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                                    "Extended Properties=/"Excel 8.0;HDR=YES;IMEX=0/";" + // 指定扩展属性为 Microsoft Excel 8.0 (97) 9.0 (2000) 10.0 (2002)，并且第一行作为数据返回，且以文本方式读取
                                    "data source=" + path;

                sConn.Open();
              
                OleDbDataAdapter oda = new OleDbDataAdapter("select * from [" + a + "]", OLEDBConnStr);
                DataTable sourceDataTable = new DataTable();
                oda.Fill(sourceDataTable);
                oda.Dispose();

                int importCount = sourceDataTable.Rows.Count;
                for (int i = sourceDataTable.Rows.Count - 1; i >= 0; i--)
                {
                    
                    try
                    {
                        int ordid = Convert.ToInt32(sourceDataTable.Rows[i]["订单号"].ToString().Trim());
                        decimal dzys = Convert.ToDecimal(sourceDataTable.Rows[i]["对账代收"].ToString().Trim());
                        if(dzys==0 || ordid==0)
                        {
                            sourceDataTable.Rows.RemoveAt(i);
                        }
                    }
                    catch
                    {
                        sourceDataTable.Rows.RemoveAt(i);
                    }
         
                }
              
                int result=0;
                foreach (DataRow item in sourceDataTable.Rows)
                {
                    string order_id = Convert.ToString(item["订单号"]);
                    DateTime pay_dt = Convert.ToDateTime(item["日期"]);
                    decimal fee_sum = Convert.ToDecimal(item["对账代收"]);
                    decimal service_fee = Convert.ToDecimal(item["服务费"]);
                    decimal insurance_fee = Convert.ToDecimal(item["保险费"]);

                    delete(order_id, project_id);

                    string sql = "insert into tbFinCompareCod(project_id,ecrm_orderid,pay_dt,fee_sum,service_fee,insurance_fee)values(@project_id,@id,@pay_dt,@fee_sum,@service_fee,@insurance_fee)";
                  
                    SqlParameter[] param = new SqlParameter[]
                   {
                       new SqlParameter("@id",order_id),
                       new SqlParameter("@pay_dt",pay_dt),
                       new SqlParameter("@fee_sum",fee_sum),
                       new SqlParameter("@project_id",project_id),
                       new SqlParameter("@service_fee",service_fee),
                       new SqlParameter("@insurance_fee",insurance_fee)

                   };
  
              result+= SqlHelper.SqlHelper.ExecuteNonQuery(sConn, System.Data.CommandType.Text, sql, param);
                }

              

                sConn.Close();

                return result;

            }

        }

      private static void delete(string ids, int project_id)
      {
          SqlConnection sConn = new SqlConnection(ConfigurationManager.ConnectionStrings["CommonEcrmDBConnectionString"].ConnectionString);

          string select = "select * from tbFinCompareCod where ecrm_orderid='" + ids + "' and project_id=" + project_id;

          using (SqlDataReader reader = SqlHelper.SqlHelper.ExecuteReader(Connstring.ConnStr, CommandType.Text, select, null))
          {

              if (reader.HasRows)
              {
                  string s = "delete from tbFinCompareCod where ecrm_orderid='" + ids + "' and project_id=" + project_id;
                  SqlHelper.SqlHelper.ExecuteNonQuery(sConn, System.Data.CommandType.Text, s, null);
              }

          }
          sConn.Close();

      }

      public static string[] GetTablesFromOleDb(string path)
      {

          string[] result = null;
          string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                                  "Extended Properties=/"Excel 8.0;HDR=YES;IMEX=0/";" + // 指定扩展属性为 Microsoft Excel 8.0 (97) 9.0 (2000) 10.0 (2002)，并且第一行作为数据返回，且以文本方式读取
                                  "data source=" + path;
          // string sql_F = "SELECT * FROM [{0}]";

          OleDbConnection conn = null;
          //OleDbDataAdapter da = null;
          DataTable tblSchema = null;
          //IList<string> tblNames = null;

          // 初始化连接，并打开
          conn = new OleDbConnection(connStr);
          try
          {
              conn.Open();
              // 获取数据源的表定义元数据                       
              //tblSchema = conn.GetSchema("Tables");

              tblSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
              result = new string[tblSchema.Rows.Count];
              for (int i = 0; i < tblSchema.Rows.Count; i++)
              {
                  result[i] = tblSchema.Rows[i][2].ToString();
              }
          }
          catch (Exception e)
          {
              return null;
          }

          finally
          {
              // 关闭连接
              conn.Close();
          }
          return result;
      }

   

    }
}