如果大家在google搜C#读取excel数据,一定会搜索出一下的代码:
public DataTable ExcelToDS(string Path)
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
DataTable dt = null;
strExcel = "select * from [sheet1$]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
dt = new DataTable();
myCommand.Fill(dt);
return dt;
}
/// 根据excel的文件的路径提取其中表的数据
/// </summary>
/// <param name="Path"> Excel文件的路径 </param>
private void GetDataFromExcelWithAppointSheetName( string Path)
{
// 连接串
string strConn = " Provider=Microsoft.Jet.OLEDB.4.0; " + " Data Source= " + Path + " ; " + " Extended Properties=Excel 8.0; " ;
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
// 返回Excel的架构,包括各个sheet表的名称,类型,创建时间和修改时间等
DataTable dtSheetName = conn.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();
}
OleDbDataAdapter myCommand = null ;
DataTable dt = new DataTable();
// 从指定的表明查询数据,可先把所有表明列出来供用户选择
string strExcel = " select * from [ " + strTableNames[ 0 ] + " ] " ;
myCommand = new OleDbDataAdapter(strExcel, strConn);
dt = new DataTable();
myCommand.Fill(dt);
dataGridView1.DataSource = dt; // 绑定到界面
}
public DataTable ExcelToDS(string Path)
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
DataTable dt = null;
strExcel = "select * from [sheet1$]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
dt = new DataTable();
myCommand.Fill(dt);
return dt;
}
但是上面的代码只是查询sheet1$表,如果用户把sheet表名改了就是报下面的错:
'sheet1$' 不是一个有效名称。请确认它不包含无效的字符或标点,且名称不太长。
那么怎样才能获得Excel中各个sheet的名称呢?这样就能动态的选中返回哪个sheet的数据,即使用户更改了名称也不怕,完全可以把所有sheet的名称列出来供用户选择:
那么下面就把解决问题的代码列出来,主要用到了OleDbConnection.GetOleDbSchemaTable()方法
/// 根据excel的文件的路径提取其中表的数据
/// </summary>
/// <param name="Path"> Excel文件的路径 </param>
private void GetDataFromExcelWithAppointSheetName( string Path)
{
// 连接串
string strConn = " Provider=Microsoft.Jet.OLEDB.4.0; " + " Data Source= " + Path + " ; " + " Extended Properties=Excel 8.0; " ;
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
// 返回Excel的架构,包括各个sheet表的名称,类型,创建时间和修改时间等
DataTable dtSheetName = conn.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();
}
OleDbDataAdapter myCommand = null ;
DataTable dt = new DataTable();
// 从指定的表明查询数据,可先把所有表明列出来供用户选择
string strExcel = " select * from [ " + strTableNames[ 0 ] + " ] " ;
myCommand = new OleDbDataAdapter(strExcel, strConn);
dt = new DataTable();
myCommand.Fill(dt);
dataGridView1.DataSource = dt; // 绑定到界面
}