/// <summary>
/// 读取Excel中数据
/// </summary>
/// <param name="strExcelPath"></param>
/// <param name="tableName"></param>
/// <returns></returns>
public static DataTable GetExcelTableByOleDB(string strExcelPath, string tableName)
{
try
{
DataTable dtExcel = new DataTable();
//数据表
DataSet ds = new DataSet();
//获取文件扩展名
string strExtension = System.IO.Path.GetExtension(strExcelPath);
string strFileName = System.IO.Path.GetFileName(strExcelPath);
//Excel的连接
OleDbConnection objConn = null;
switch (strExtension)
{
case ".xls":
objConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strExcelPath + ";" + "Extended Properties=\"Excel 8.0;HDR=yes;IMEX=1;\"");
break;
case ".xlsx":
objConn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strExcelPath + ";" + "Extended Properties=\"Excel 12.0;HDR=yes;IMEX=1;\"");
//此连接可以操作.xls与.xlsx文件 (支持Excel2003 和 Excel2007 的连接字符串) 备注: "HDR=yes;"是说Excel文件的第一行是列名而不是数,"HDR=No;"正好与前面的相反。"IMEX=1 "如果列中的数据类型不一致,使用"IMEX=1"可必免数据类型冲突。
break;
default:
objConn = null;
break;
}
if (objConn == null)
{
return null;
}
objConn.Open();
//获取表名
// string sheet = dtDATA.Rows[0][2].ToString().Trim();
//选择指定表所有数据
string strSql = "select * from [" + tableName + "$]";
//选择表指定列范围
//string strSql = "select * from [" + tableName + "A:Z]";
//获取Excel指定Sheet表中的信息
OleDbCommand objCmd = new OleDbCommand(strSql, objConn);
OleDbDataAdapter myData = new OleDbDataAdapter(strSql, objConn);
myData.Fill(ds, tableName);//填充数据
objConn.Close();
//dtExcel即为excel文件中指定表中存储的信息
dtExcel = ds.Tables[tableName];
return dtExcel;
}
catch (Exception ex)
{
Console.WriteLine(ex.Message + "\r\n" + ex.StackTrace);
return null;
}
}
C#使用 OleDbConnection 连接读取Excel
于 2022-04-22 11:38:51 首次发布