C# Excel xls文件导入程序Excel数据库通用类XlsFileDB

  C# Excel xls文件导入程序Excel数据库通用类XlsFileDB

C# Code:

/// <summary>
/// XLS文件数据库通用类
/// </summary>
public class XlsFileDB
{
  
  protected DbConnection _DbConnection = null;
  
  private string _FileName = "";
  
  /// <summary>
  /// 构造器
  /// </summary>
  /// <param name="xlsFileName">xls文件名</param>
   public XlsFileDB(string xlsFileName)
  {
    _FileName = xlsFileName;
    
    if (File.Exists(xlsFileName))
    {
      _DbConnection = CreateConnection();
    }
    else
    throw new Exception("文件不存在！" + xlsFileName);
  }
  
  /// <summary>
  /// 取XLS文件所有表名(SHEET)
  /// </summary>
  /// <returns></returns>
   public List<string> GetTables()
  {
    List<string> list = new List<string>();
    try
    {
      if (_DbConnection.State == ConnectionState.Closed) _DbConnection.Open();
      DataTable dt = _DbConnection.GetSchema("Tables");
      foreach (DataRow row in dt.Rows)
      {
        if ((row[3].ToString() == "TABLE") || (row[3].ToString() == "BASE TABLE"))
        list.Add(row[2].ToString());
      }
    }
    finally
    {
      if (_DbConnection.State != ConnectionState.Closed) _DbConnection.Close();
    }
    return list;
  }
  
  /// <summary>
  /// 创建数据链接
  /// </summary>
  /// <returns></returns>
   public DbConnection CreateConnection()
  {
    // IMEX=1 可把混合型作为文本型读取，避免null值
     string conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + _FileName + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
    return new OleDbConnection(conn);
  }
  
  /// <summary>
  /// 获取Sheet数据,表名如：Sheet1$
  /// </summary>
  /// <returns></returns>
   public DataTable GetData(string tableName)
  {
    string sql = "SELECT * FROM [" + tableName + "] ";
    return this.GetDataBySQL(sql);
  }
  
  /// <summary>
  /// 获取Xls数据，转换为DataTable
  /// </summary>
  /// <param name="sqlSelect">SELECT * FROM [Sheet1$]</param>
  /// <returns></returns>
   public DataTable GetDataBySQL(string sqlSelect)
  {
    DataTable dt = null;
    try
    {
      if (_DbConnection.State == ConnectionState.Closed) _DbConnection.Open();
      OleDbCommand cmd = _DbConnection.CreateCommand() as OleDbCommand;
      cmd.CommandText = sqlSelect;
      dt = new DataTable();
      DbDataAdapter adp = new OleDbDataAdapter(cmd);
      adp.Fill(dt);
    }
    finally
    {
      if (_DbConnection.State != ConnectionState.Closed) _DbConnection.Close();
    }
    return dt;
  }
  
}

//来源:C/S框架网 | www.csframework.com | QQ:23404761

---

扫一扫加微信