#region 获取Excel数据
/// <summary>
/// 根据Excel物理路径、表名(Sheet名)获取数据集
/// </summary>
/// <param name="FileFullPath">excel文件完整路径</param>
/// <param name="SheetName">工作簿名称</param>
/// <returns></returns>
public DataTable GetExcelToDataTableBySheet(string FileFullPath, string SheetName)
{
string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + FileFullPath + ";Extended Properties='Excel 12.0; HDR=YES; IMEX=1'"; //此连接可以操作.xls与.xlsx文件,【HDR为YES表示首行为字段,为NO表示首行为数据】
//string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + FileFullPath + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";//旧版连接
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
DataSet ds = new DataSet();
OleDbDataAdapter odda = new OleDbDataAdapter(string.Format("SELECT * FROM [{0}]", SheetName), conn);//("select * from [Sheet1$]", conn);
odda.Fill(ds);
conn.Close();
return ds.Tables[0];
}
/// <summary>
/// 根据Excel物理路径获取Excel文件中所有表名
/// </summary>
/// <param name="FileFullPath">excel文件完整路径</param>
/// <returns></returns>
public String[] GetExcelSheetNames(string FileFullPath)
{
OleDbConnection objConn = null;
System.Data.DataTable dt = null;
try
{
string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + FileFullPath + ";Extended Properties='Excel 12.0; HDR=YES; IMEX=1'"; //此连接可以操作.xls与.xlsx文件
objConn = new OleDbConnection(strConn);
objConn.Open();
dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dt == null)
{
return null;
}
String[] excelSheets = new String[dt.Rows.Count];
int i = 0;
foreach (DataRow row in dt.Rows)
{
excelSheets[i] = row["TABLE_NAME"].ToString();
i++;
}
return excelSheets;
}
catch
{
return null;
}
finally
{
if (objConn != null)
{
objConn.Close();
objConn.Dispose();
}
if (dt != null)
{
dt.Dispose();
}
}
}
#endregion
/// <summary>
/// 获取CSV导入的数据
/// </summary>
/// <param name="filePath">文件所在路径(不包含文件名)</param>
/// <param name="fileName">文件名称</param>
/// <returns></returns>
public DataTable GetCsvData(string filePath, string fileName)
{
string path = Path.Combine(filePath, fileName);
string connString = @"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + filePath + ";Extensions=asc,csv,tab,txt;";
try
{
using (OdbcConnection odbcConn = new OdbcConnection(connString))
{
odbcConn.Open();
OdbcCommand oleComm = new OdbcCommand();
oleComm.Connection = odbcConn;
oleComm.CommandText = "select * from [" + fileName + "]";
OdbcDataAdapter adapter = new OdbcDataAdapter(oleComm);
DataSet ds = new DataSet();
adapter.Fill(ds);
odbcConn.Close();
return ds.Tables[0];
}
}
catch (Exception ex)
{
throw ex;
}
}
C#读取Excel和CSV为DataTable
最新推荐文章于 2024-06-12 09:46:16 发布