/// <summary>
/// 获取连接字符串
/// </summary>
/// <param name="path">位置</param>
/// <param name="fromat">excel格式</param>
/// <param name="hasHeadText">是否把第一行作为表头</param>
/// <param name="mode">读写模式</param>
/// <returns></returns>
private static string GetConnectString(string path, ExcelFormat fromat, bool hasHeadText, IMEX mode)
{
string provider = XLSProvider;
if (fromat == ExcelFormat.Excel2010)
{
provider = XLSXProvider;
}
string connString = null;
string hdr = "YES";
if (!hasHeadText)
{
hdr = "NO";
}
string strmode = ((int)mode).ToString();
connString = string.Format(provider, path, hdr, strmode);
return(connString);
}
/// <summary>
/// 读取Excel到DataSet
/// </summary>
/// <param name="path">路径</param>
/// <param name="fromat">格式</param>
/// <param name="showFilter">是否显示废弃表</param>
/// <param name="hasHeadText">是否把第一行作为表头</param>
/// <param name="mode">打开模式</param>
/// <returns></returns>
public static DataSet LoadXLS(string path, ExcelFormat fromat, bool showFilter, bool hasHeadText, IMEX mode)
{
DataSet ds = new DataSet();
string connString = GetConnectString(path, fromat, hasHeadText, mode);
using (OleDbConnection conn = new OleDbConnection(connString))
{
conn.Open();
DataTable dtTables = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
if (dtTables == null)
{
return(ds);
}
foreach (DataRow row in dtTables.Rows)
{
string tableName = row["TABLE_NAME"].ToString();
if (!showFilter)
{
int index = tableName.LastIndexOf(FilterString, StringComparison.CurrentCultureIgnoreCase);
if (tableName.Length - FilterString.Length == index)
{
continue;
}
}
if (!string.IsNullOrEmpty(tableName))
{
OleDbDataAdapter oda = new OleDbDataAdapter("select * from [" + tableName + "]", conn);
oda.Fill(ds, tableName);
}
}
}
return(ds);
}