Get Selected Records from Excel file
In this article we will learn about how can we get records from excel file in c#.
First consider the following function in which we read data from excel file into dataset object with the help of Oledb connection :
public static DataTable GetDataTableExcel(string strFileName, string Table) { System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(“Provider=Microsoft.Jet.OleDb.4.0; Data Source = ” + strFileName + “; Extended Properties = \”Excel 8.0;HDR=Yes;IMEX=1\”;”); conn.Open(); string strQuery = “SELECT * FROM [" + Table + "] Where Name = 'Ankur'“;// This Query will return only those rows of excel file in which Name is Ankur. System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(strQuery, conn); System.Data.DataSet ds = new System.Data.DataSet(); adapter.Fill(ds); return ds.Tables[0]; }
The above method we can use to get selected data from MS Excel in case of header in first row of excel file.But in case no header means your excel file doesn’t contains any header row then we need to change our connection string like as:
“Provider=Microsoft.Jet.OleDb.4.0; Data Source = ” + strFileName + “; Extended Properties = \”Excel 8.0;HDR=NO;IMEX=1\”;”
and in Query we use F1,F2,F3…………Fn in place of Columns Name.
following is the complete example:
public static DataTable GetDataTableExcelWithoutHeaders(string strFileName, string Table) { System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(“Provider=Microsoft.Jet.OleDb.4.0; Data Source = ” + strFileName + “; Extended Properties = \”Excel 8.0;HDR=No;IMEX=1\”;”); conn.Open(); string strQuery = “SELECT * FROM [" + Table + "] Where F1 = 'Ankur'“;// This Query will return only those rows of excel file in which First row contains Ankur. System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(strQuery, conn); System.Data.DataSet ds = new System.Data.DataSet(); adapter.Fill(ds); return ds.Tables[0]; }
try this and let me know if you have any problem…thanks