C#将Excel表格中的数据导入SQL数据库

OGeek|极客世界-中国程序员成长平台 › 门户 › 编程› Node.js›Node.js教程

原作者: [db:作者] 来自: [db:来源] 收藏邀请

private static string _filePath=string.Empty ;
#region Excel导入SQL数据库
  /// <summary>
  /// 获取Excel数据表列表
  /// </summary>
  /// <returns></returns>
  public static ArrayList GetExcelTables()
  {
   //将Excel架构存入数据里
   System.Data.DataTable dt = new System.Data.DataTable();
   ArrayList TablesList=new ArrayList();
   if (File.Exists(FilePath))
   {
    using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet."+
         "OLEDB.4.0;Extended Properties=\"Excel 8.0\";Data Source=" + FilePath))
    {
     try
     {
      conn.Open();
      dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
     }
     catch (Exception exp)
     {
      Log.StrFileName ="查询Excel表单名";
      Log.StrDepartment ="Excel架构存入数据";
      Log.StrDescription =exp.ToString ();
      Log.WriteLog ();
     }     //获取数据表个数
     int tablecount=dt.Rows.Count;
     for (int i=0;i<tablecount;i=i+2)
     {
      string tablename=dt.Rows[2].ToString().Trim().TrimEnd('$');
      if(TablesList.IndexOf(tablename)<0)
      {
       TablesList.Add(tablename);
      }

     }
    }
   }
   return TablesList;
  }
  /// <summary>
  /// 导入Excel数据表至DataTable（第一行作为表头）
  /// </summary>
  /// <returns></returns>
  public static System.Data.DataSet FillDataSet()
  {
   if (!File.Exists(FilePath))
   {
    throw new Exception("Excel文件不存在！");
   }
   ArrayList TableList=new ArrayList();
   TableList = GetExcelTables();
   if(TableList.Count <=0)
   {
     return null;
   }

   System.Data.DataTable table ;
            System.Data .DataSet ds=new DataSet ();
   OleDbConnection dbcon = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePath + ";Extended Properties=Excel 8.0");
   try
   {
    if (dbcon.State == ConnectionState.Closed)
    {
     dbcon.Open();
    }
    for(int i=0;i<TableList.Count ;i++)
    {
     string dtname=TableList.ToString ();
     try
     {
      OleDbCommand cmd = new OleDbCommand("select * from [" + dtname + "$]", dbcon);
      OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
      table=new DataTable (dtname);
      adapter.Fill(table);
      ds.Tables .Add (table);
     }
     catch (Exception exp)
     {
      Log.StrFileName =dtname;
      Log.StrDepartment ="将Excel表格导入DataSet";
      Log.StrDescription =exp.ToString ();
      Log.WriteLog ();
     }
    }
   }
   finally
   {
    if (dbcon.State == ConnectionState.Open)
    {
     dbcon.Close();
    }
   }
   return ds;
  }

  /// <summary>
  /// Excel导入数据库
  /// </summary>
  /// <returns></returns>
  public static DataSet ImportFromExcel()
  {
   return FillDataSet();
  }

#endregion