public class ImportExcel
{
/// <summary>
/// Excel检查版本
/// </summary>
/// <param name="fileName"></param>
/// <returns></returns>
private static string ConnectionString(string fileName)
{
bool isExcel2003 = fileName.EndsWith(".xls");
string connectionString = string.Format(
isExcel2003
? "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES;IMEX=1'"
: "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=YES;IMEX=1'",
fileName);
return connectionString;
}
/// <summary>
/// Excel导入数据源
/// </summary>
/// <param name="sheet">sheet</param>
/// <param name="filename">文件路径</param>
/// <returns></returns>
public static DataTable ExcelToDataTable(string sheet, string filename)
{
OleDbConnection myConn = new OleDbConnection(ConnectionString(filename));
try
{
DataSet ds;
//string strCom = " SELECT * FROM [Sheet1$]";
myConn.Open();
//改成下面的方式
#region 获取sheet表的名称
//返回Excel的架构,包括各个sheet表的名称,类型,创建时间和修改时间等
System.Data.DataTable dtSheetName = myConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
//包含excel中表名的字符串数组
string[] strTableNames = new string[dtSheetName.Rows.Count];
for (int k = 0; k < dtSheetName.Rows.Count; k++)
{
strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString();
}
//从指定的表明查询数据,可先把所有表明列出来供用户选择
string strCom = "select * from[" + strTableNames[0] + "]";
#endregion
OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
ds = new DataSet();
myCommand.Fill(ds);
myConn.Close();
return ds.Tables[0];
}
catch (Exception ex)
{
string err = ex.Message;
myConn.Close();
myConn.Dispose();
throw;
}
}
}
{
/// <summary>
/// Excel检查版本
/// </summary>
/// <param name="fileName"></param>
/// <returns></returns>
private static string ConnectionString(string fileName)
{
bool isExcel2003 = fileName.EndsWith(".xls");
string connectionString = string.Format(
isExcel2003
? "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES;IMEX=1'"
: "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=YES;IMEX=1'",
fileName);
return connectionString;
}
/// <summary>
/// Excel导入数据源
/// </summary>
/// <param name="sheet">sheet</param>
/// <param name="filename">文件路径</param>
/// <returns></returns>
public static DataTable ExcelToDataTable(string sheet, string filename)
{
OleDbConnection myConn = new OleDbConnection(ConnectionString(filename));
try
{
DataSet ds;
//string strCom = " SELECT * FROM [Sheet1$]";
myConn.Open();
//改成下面的方式
#region 获取sheet表的名称
//返回Excel的架构,包括各个sheet表的名称,类型,创建时间和修改时间等
System.Data.DataTable dtSheetName = myConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
//包含excel中表名的字符串数组
string[] strTableNames = new string[dtSheetName.Rows.Count];
for (int k = 0; k < dtSheetName.Rows.Count; k++)
{
strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString();
}
//从指定的表明查询数据,可先把所有表明列出来供用户选择
string strCom = "select * from[" + strTableNames[0] + "]";
#endregion
OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
ds = new DataSet();
myCommand.Fill(ds);
myConn.Close();
return ds.Tables[0];
}
catch (Exception ex)
{
string err = ex.Message;
myConn.Close();
myConn.Dispose();
throw;
}
}
}