C # Read the specified sheet name or index in 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;
}

 

 

HoweverCodeOnly query 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, and the oledbconnection. getoledbschematable () method is used.

Code

[Copy to clipboard]

/// <Summary>
/// Extract the table data according to the path of the Excel file
/// </Summary>
/// <Param name = "path"> Excel file path </param>
Private void getdatafromexcelwithappointsheetname (string path)
{
// Connection string
String strconn = "provider = Microsoft. Jet. oledb.4.0;" + "Data Source =" + path + ";" + "extended properties = Excel 8.0 ;";
Oledbconnection conn = new oledbconnection (strconn );

Conn. open ();

// Return the Excel schema, including the name, type, Creation Time, and modification time of each sheet.
Datatable dtsheetname = conn. getoledbschematable (oledbschemaguid. Tables, new object [] {null, "table "});

// String array containing the table name in 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 ();

// Query data from the specified table. All the table columns can be selected first.
String strexcel = "select * from [" + strtablenames [0] + "]";
Mycommand = new oledbdataadapter (strexcel, strconn );
Dt = new datatable ();
Mycommand. Fill (DT );

Datagridview1.datasource = DT; // bind to the interface
}

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/lee576/archive/2009/08/10/4431328.aspx