The sheet name or index specified in Excel reads data to the datatable.

Method 1:

Code
1 Public datatable exceltods (string path)
2 {
3 string strconn = "provider = Microsoft. Jet. oledb.4.0;" + "Data Source =" + path + ";" + "extended properties = Excel 8.0 ;";
4 oledbconnection conn = new oledbconnection (strconn );
5 conn. open ();
6 string strexcel = "";
7 oledbdataadapter mycommand = NULL;
8 datatable dt = NULL;
9 strexcel = "select * from [sheet1 $]";
10 mycommand = new oledbdataadapter (strexcel, strconn );
11 dt = new datatable ();
12 mycommand. Fill (DT );
13 return dt;
14}

However, the above Code only queries the table sheet1 $. If you change the table name, the following error is returned: 'sheet1 $ 'is not a valid name. Make sure it does not contain invalid characters or punctuation and the name is not too long. So how can we get the names of sheet in Excel? In this way, you can dynamically select the sheet data to be returned. Even if you have changed the name, you can list all sheet names for the user to choose from: the code to solve the problem is listed below, mainly using OleDbConnection. getOleDbSchemaTable () method
Method 2

Code
1/** // <summary>
2 // extract the table data according to the path of the excel file
3 /// </summary>
4 // <paramname = "Path"> Excel file Path </param>
5 privatevoidGetDataFromExcelWithAppointSheetName (stringPath)
6 {
7 // connection string
8 stringstrConn = "Provider = Microsoft. Jet. OLEDB.4.0;" + "DataSource =" + Path + ";" + "ExtendedProperties = Excel8.0 ;";
9 OleDbConnectionconn = newOleDbConnection (strConn );
10 conn. Open ();
11 // return the Excel schema, including the name, type, Creation Time, and modification time of each sheet.
12 DataTabledtSheetName = conn. GetOleDbSchemaTable (OleDbSchemaGuid. Tables, newobject [] {null, "Table "});
13 // string array containing the table name in excel
14 string [] strTableNames = newstring [dtSheetName. Rows. Count];
15 for (intk = 0; k <dtSheetName. Rows. Count; k ++)
16 {
17 strTableNames [k] = dtSheetName. Rows [k] ["TABLE_NAME"]. ToString ();
18}
19 OleDbDataAdaptermyCommand = null;
20 DataTabledt = newDataTable ();
21 // query data from the specified table. You can list all the table columns for the user to choose from.
22 stringstrExcel = "select * from [" + strTableNames [0] + "]";
23 myCommand = newOleDbDataAdapter (strExcel, strConn );
24 dt = newDataTable ();
25 myCommand. Fill (dt );
26 dataGridView1.DataSource = dt; // bind to the interface
27}