留着自己以后参考
/// <summary>
/// 获取Excel的内容
/// 备注:把Excel读取到DataTable之后
/// 的列标题
/// </summary>
/// <param name="excelPath"></param>
/// <param name="HasColumn"></param>
/// <returns></returns>
public static System.Data.DataTable GetExcelTable(string excelPath, bool HasColumn)
{
try
{
//打开Excel连接
string connString = "";
OleDbConnection conn = null;
if (HasColumn == true)
{
conn = OpenExcelEx(excelPath, out connString); //连接
}
else
{
conn = OpenExcel(excelPath, out connString); //连接
}
//读取Excel
List<string> lstName = GetFirstSheetNames(connString);
System.Data.DataTable dtTable = null;
foreach (string strSheetName in lstName)
{
string strSql = "select * from [" + strSheetName + "]";
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(strSql, conn); //查询
DataSet dtSet = new DataSet();
dataAdapter.Fill(dtSet);
System.Data.DataTable table = dtSet.Tables[0];
//table.Columns[0].ColumnName = table.Rows[0][table.Columns[0]].ToString();
//table.Rows.RemoveAt(0);
dataAdapter.Dispose();
if (table.Columns.Count > 1)
{
dtTable = table;
break;
}
}
conn.Close(); //cql 进程就不会被占用
return dtTable;
}
catch (Exception ex)
{
return null;
}
}
/// <summary>
/// 获取Excel的内容
/// 备注:把Excel读取到DataTable之后,默认把Excel的第一行作为Datatable
/// 的列标题
/// </summary>
/// <param name="excelPath"></param>
/// <param name="HasColumn">是否把一行做表头</param>
/// <returns></returns>
public static System.Data.DataTable GetExcelTableEX(string excelPath, bool HasColumn)
{
try
{
//打开Excel连接
string connString = "";
OleDbConnection conn = null;
if (HasColumn == true)
{
conn = OpenExcelEx(excelPath, out connString); //连接
}
else
{
conn = OpenExcel(excelPath, out connString); //连接
}
//读取Excel
List<string> lstName = GetFirstSheetNames(connString);
System.Data.DataTable dtTable = null;
foreach (string strSheetName in lstName)
{
string strSql = "select * from [" + strSheetName + "]";
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(strSql, conn); //查询
DataSet dtSet = new DataSet();
dataAdapter.Fill(dtSet);
System.Data.DataTable table = dtSet.Tables[0];
foreach (DataColumn col in table.Columns)
{
col.ColumnName = table.Rows[0][col].ToString();
}
table.Rows.RemoveAt(0);
dataAdapter.Dispose();
if (table.Columns.Count > 1)
{
dtTable = table;
break;
}
}
conn.Close(); //cql 进程就不会被占用
return dtTable;
}
catch (Exception ex)
{
return null;
}
}
private static List<string> GetFirstSheetNames(string connectionString)
{
List<string> lstName = new List<string>();
using (OleDbConnection connection = new
OleDbConnection(connectionString))
{
connection.Open();
System.Data.DataTable schemaTable = connection.GetOleDbSchemaTable(
OleDbSchemaGuid.Tables,
new object[] { null, null, null, "TABLE" });
foreach (DataRow row in schemaTable.Rows)
{
string tableName = row[2].ToString().Trim();
lstName.Add(tableName);
}
connection.Close();
}
return lstName;
}
#region 打开Excel连接 ADO.Net
/// <summary>
/// 打开Excel文件,把一行做表头
/// 解决03版本和07版本Excel的访问驱动不兼容得问题
/// </summary>
/// <param name="excelPath">Excel文件全路径</param>
/// <param name="strConn">连接字符串</param>
/// <returns></returns>
public static OleDbConnection OpenExcelEx(string excelPath, out string strConn)
{
OleDbConnection conn = null;
//先用03版Excel连接方式
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
if (!OpenExcelConnection(strConn, out conn))
{
//在尝试用07的连接方式
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelPath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"";
if (!OpenExcelConnection(strConn, out conn)) return null;
}
return conn;
}
/// <summary>
/// 打开Excel文件
/// 解决03版本和07版本Excel的访问驱动不兼容得问题
/// </summary>
/// <param name="excelPath">Excel文件全路径</param>
/// <param name="strConn">连接字符串</param>
/// <returns></returns>
public static OleDbConnection OpenExcel(string excelPath, out string strConn)
{
OleDbConnection conn = null;
//先用03版Excel连接方式
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelPath + ";Extended Properties=\"Excel 8.0;HDR=No;IMEX=1\"";
if (!OpenExcelConnection(strConn, out conn))
{
//在尝试用07的连接方式
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelPath + ";Extended Properties=\"Excel 12.0;HDR=No;IMEX=1\"";
if (!OpenExcelConnection(strConn, out conn)) return null;
}
return conn;
}
/// <summary>
/// 打开Excel文件
/// </summary>
/// <param name="connectString">连接字符串</param>
/// <param name="conn">连接信息</param>
/// <returns></returns>
private static bool OpenExcelConnection(string connectString, out OleDbConnection conn)
{
conn = new OleDbConnection(connectString);
try
{
conn.Open();
return true;
}
catch (Exception ex)
{
return false;
}
}