How to read EXCEL data using C#.NET?

Its about how to read a set of data or specific data from EXCEL file using C#.Net. Infact, we can use SQL querying in the EXCEL file data itself.

Step-1: Include the connection string for the EXCEL file containing the filename and Provider settings.

String connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("Filename.xls") + ";" + "Extended Properties=Excel 8.0;";

Step-2: Create a new connection object and open it for processing.

OleDbConnection objConn = newOleDbConnection(connectionString);
objConn.Open();

Step-3: Create a command object for querying and pass that command with the connection object created.

String strConString = "SELECT firstColumnName FROM [Sheet1$]";
//where date = CDate('" + DateTime.Today.ToShortDateString() + "')";
OleDbCommand objCmdSelect = newOleDbCommand(strConString, objConn);

Step-4: Here, in this example i am going to use DataSet for reading and holding the data, for which i am creating a Data Adapter Object.

// Create new OleDbDataAdapter that is used to build a DataSet
// based on the preceding SQL SELECT statement.
OleDbDataAdapterobjAdapter1 = newOleDbDataAdapter();
// Pass the Select command to the adapter.
objAdapter1.SelectCommand = objCmdSelect;
// Create new DataSet to hold information from the worksheet.
DataSetobjDataset1 = newDataSet();

Step-5: Now, fill the data from the EXCEL file by querying with the command object and store them into the Dataset created.

// Fill the DataSet with the information from the worksheet.
objAdapter1.Fill(objDataset1, "ExcelData");

Step-6: Process the data for displaying with the Dataset, which you can set as datasource for many ASP.NET controls.

for (int i = 0; i < objDataset1.Tables[0].Rows.Count; i++)
{
document.Write("<B>Data: </b>" + objDataset1.Tables[0].Rows[i].ItemArray[0].ToString()+"<BR>");
}

Step-7: As we are at the end, important thing is to Close the connection.

// Clean up objects.
objConn.Close();

This is a simple Data reading from an EXCEL file, which doesnt classify the name for the columns to read about. We provide or create an EXCEL file having the Column name as the first row of data.

So our example may contain that as the column name and the sheet name as the table name.

NOTE: See the usage of Sheetname with "[" and "]" for specifying the query syntax properly.