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

作者:C/S框架网|www.cscode.ne 发布日期:2021/03/18 18:01:37

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

参考文档：

Winform框架通用导入程序，支持Excel,xls/xlsx格式|C/S开发框架
C#使用SqlBulkCopy快速导入Excel文件(xls/xlsx)-C/S开发框架
在基类窗体扩展一个通用导出Excel文件的按钮(支持导出所有表格的数据到Xlsx文件)
C# OleDbConnection读取加装Excel2003(.xls),Excel2007(.xlsx)文件
CSFrameworkV6旗舰版 | 导入导出Excel文件数据开发教程|C/S开发框架
C# .NET8使用NPOI导出数据到Excel文件
CSFrameworkV6-导出Excel模版、导入数据设计
2022年最新：中国行政区域编码数据表,省市区编号xls文件下载|C#源码
OLEDB创建DbConnection连接Excel文件报错：未在本地计算机上注册ACE.OLEDB
Winform框架通用导入Excel文件源码下载
原创文档-Winform开发框架提供通用导入Excel功能界面|C/S框架网
C#导出数据到Excel 源码大全
C#将数据导出到Excel汇总
C#操作Excel合并多个Excel文件
C#中将数据插入到Excel表中
中国国家统计局省市区划代码数据库下载 - 2021年最新- Excel,SQLLite数据库