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)); }