public static string GetExcelFirstTableName(string connectstring)
{
string excelFirstTableName;
using (OleDbConnection oleDbConnection = new OleDbConnection(connectstring))
{
excelFirstTableName = MdExcelHelper.GetExcelFirstTableName(oleDbConnection);
}
return(excelFirstTableName);
}
/// <summary>
/// 返回Excel工作表名
/// </summary>
/// <param name="connectstring"></param>
/// <returns></returns>
public static List <string> GetExcelTablesName(string connectstring)
{
List <string> excelTablesName;
using (OleDbConnection oleDbConnection = new OleDbConnection(connectstring))
{
excelTablesName = MdExcelHelper.GetExcelTablesName(oleDbConnection);
}
return(excelTablesName);
}
/// <summary>
/// EXCEL导入DataSet
/// </summary>
/// <param name="connectstring"></param>
/// <param name="table"></param>
/// <returns></returns>
public static DataSet ExcelToDataSet(string connectstring, string table)
{
DataSet result;
using (OleDbConnection oleDbConnection = new OleDbConnection(connectstring))
{
DataSet dataSet = new DataSet();
if (MdExcelHelper.IsExistExcelTableName(oleDbConnection, table))
{
OleDbDataAdapter oleDbDataAdapter = new OleDbDataAdapter("SELECT * FROM [" + table + "]", oleDbConnection);
oleDbDataAdapter.Fill(dataSet, table);
}
result = dataSet;
}
return(result);
}
/// <summary>
/// 获取Excel文件中指定工作表的列
/// </summary>
/// <param name="excelPath"></param>
/// <param name="eType"></param>
/// <param name="table"></param>
/// <returns></returns>
public static List <string> GetColumnsList(string excelPath, MdExcelHelper.ExcelType eType, string table)
{
string excelConnectstring = MdExcelHelper.GetExcelConnectstring(excelPath, true, eType);
DataTable readerSchema;
using (OleDbConnection oleDbConnection = new OleDbConnection(excelConnectstring))
{
oleDbConnection.Open();
readerSchema = MdExcelHelper.GetReaderSchema(table, oleDbConnection);
}
return((from DataRow dr in readerSchema.Rows
let columnName = dr["ColumnName"].ToString()
let datatype = ((OleDbType)dr["ProviderType"]).ToString()
let netType = dr["DataType"].ToString()
select columnName).ToList <string>());
}
/// <summary>
/// EXCEL所有工作表导入DataSet
/// </summary>
/// <param name="connectstring"></param>
/// <returns></returns>
public static DataSet ExcelToDataSet(string connectstring)
{
DataSet result;
using (OleDbConnection oleDbConnection = new OleDbConnection(connectstring))
{
DataSet dataSet = new DataSet();
List <string> excelTablesName = MdExcelHelper.GetExcelTablesName(oleDbConnection);
foreach (string current in excelTablesName)
{
OleDbDataAdapter oleDbDataAdapter = new OleDbDataAdapter("SELECT * FROM [" + current + "]", oleDbConnection);
oleDbDataAdapter.Fill(dataSet, current);
}
result = dataSet;
}
return(result);
}
/// <summary>
/// 返回Excel工作表名
/// </summary>
/// <param name="excelPath"></param>
/// <param name="eType"></param>
/// <returns></returns>
public static List <string> GetExcelTablesName(string excelPath, MdExcelHelper.ExcelType eType)
{
string excelConnectstring = MdExcelHelper.GetExcelConnectstring(excelPath, true, eType);
return(MdExcelHelper.GetExcelTablesName(excelConnectstring));
}
/// <summary>
/// 返回Excel 连接字符串 [IMEX=1]
/// </summary>
/// <param name="excelPath"></param>
/// <param name="header"></param>
/// <param name="eType"></param>
/// <returns></returns>
public static string GetExcelConnectstring(string excelPath, bool header, MdExcelHelper.ExcelType eType)
{
return(MdExcelHelper.GetExcelConnectstring(excelPath, header, eType, MdExcelHelper.IMEXType.ImportMode));
}
/// <summary>
/// EXCEL所有工作表导入DataSet
/// </summary>
/// <param name="excelPath"></param>
/// <param name="header"></param>
/// <param name="eType"></param>
/// <returns></returns>
public static DataSet ExcelToDataSet(string excelPath, bool header, MdExcelHelper.ExcelType eType)
{
string excelConnectstring = MdExcelHelper.GetExcelConnectstring(excelPath, header, eType);
return(MdExcelHelper.ExcelToDataSet(excelConnectstring));
}
/// <summary>
/// 判断是否存在该数据表
/// </summary>
/// <param name="connection"></param>
/// <param name="table"></param>
/// <returns></returns>
private static bool IsExistExcelTableName(OleDbConnection connection, string table)
{
List <string> excelTablesName = MdExcelHelper.GetExcelTablesName(connection);
return(excelTablesName.Any((string tName) => tName.ToLower() == table.ToLower()));
}
/// <summary>
/// 返回Excel第一个工作表表名
/// </summary>
/// <param name="excelPath"></param>
/// <param name="eType"></param>
/// <returns></returns>
public static string GetExcelFirstTableName(string excelPath, MdExcelHelper.ExcelType eType)
{
string excelConnectstring = MdExcelHelper.GetExcelConnectstring(excelPath, true, eType);
return(MdExcelHelper.GetExcelFirstTableName(excelConnectstring));
}
