How to get selected records from Excel file in c#

 
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
 

Author: Ankur

Have worked primarily in the domain of Calling, CRM and direct advertisers services. My technological forte is Microsoft Technologies especially Dot Net (Visual Studio 2003, 2005, 2008, 2010 and 2012) and Microsoft SQL Server 2000,2005 and 2008 R2. My Area of Expertise is in C#. Net, VB.Net, MS-SQL Server, ASP. Net, Silverlight, HTML, XML, Crystal Report, Active Reports, Infragistics, Component Art, ComponeOne, Lead Tools etc.