C# Excel xls文件导入程序Excel数据库通用类XlsFileDB
作者:C/S框架网|www.cscode.ne  发布日期:2021-03-18 18:01:37
  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



C/S框架网|原创精神.创造价值.打造精品

扫一扫加微信
C/S框架网作者微信 C/S框架网|原创作品.质量保障.竭诚为您服务

上一篇 下一篇