How to get the records from excel based on particular cell value using c#

See more: C# hello every body iam curently working on windows project .i want to import the excel data into database using c#.

my SpreadSheet is

  ExcelSheet
  |--------------------------------------------------|
  |                                                                           |
  |--------------------------------------------------|
  |                                                                           |
  |--------------------------------------------------|
  | Merchant no:12345                                                |
  |--------------------------------------------------|
  | merchant no|         Id No |Amount   | branch name |   
  |------------|-------------|---------|-------------|
  |      12345   |            101 |   10000   | Hyd            |   
  |------------| ------------| --------|-------------|
  |      12345   |            102 |   20000   |   Bombay      | 
  |------------|-------------|---------|-------------|
  |      12345   |            103 |   30000   |      Delhi            |
  |---------------------------------------------------

Now i want to read total excel sheet based on cell value=12345 (here starting two rows are empty and my code read all records ignore the empteis ) and stored all records into database.This is my task. i already implement the code like below

private void btnImport_Click(object sender, EventArgs e)
{
    try
    {
        DataTable dtExcel=new DataTable();
        string SourceConstr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + txtExcelFile.Text + "';Extended Properties= 'Excel 8.0;HDR=Yes;IMEX=1'";
        OleDbConnection con = new OleDbConnection(SourceConstr);
        string query="Select * from [Sheet1$]";
        OleDbDataAdapter data=new OleDbDataAdapter(query,con);
        data.Fill(dtExcel);
        //dgvExcelData.DataSource = dtExcel;
        //dgvExcelData.ColumnHeadersVisible=false;
        string DestConstr = @"Data Source=COMPUTER-8EB749;Initial Catalog=TRMSDB;Integrated Security=true";
        SqlConnection connection = new SqlConnection(DestConstr);
        connection.Open();

        string Mno = "";
        foreach (DataRow rowExcel in dtExcel.Rows)
        {
            foreach (DataColumn colExcel in dtExcel.Columns)
            {
                Mno = rowExcel[colExcel].ToString().Trim();
                if (Mno != "")
                {
                    string Mno1 = Mno.Substring(16, 10);
       开发者_如何学运维             Mno =Mno1.ToString();
                    //Int32 MerchNo = Convert.ToInt32(Mno);
                }
                break;
            }
        if(Mno!="")// Mno contains the exact MerchantNo.
        {
            for(int i=0;i<dtExcel.Rows.Count;i++)
            {
                if (dtExcel.Rows.Contains("MerchantNo=1105393011"))
                {
                     string str = dtExcel.Rows[i][0].ToString().Trim();
                     string str1 = dtExcel.Rows[i][1].ToString().Trim();
                }
            }
        }
    }
}

Yes i have this type of knowledge for import the excel into data table and data table to data base.

My requirements on this excel sheet are 1. ignore the empty rows. 2.not read the first two rows and treated as empty rows. 3.read records based on cell value(12345)

my Queries are 1.i want to read total record at a time or read cell values based on row no and and store into variable?

I am in confusion. any body give me good sugition for import data and give good examples on this or modify my code. plz........

 using Microsoft.Office.Interop.Excel;

  ApplicationClass app = null;
  Workbook workBook = null;
  CultureInfo ci = Thread.CurrentThread.CurrentCulture;
  Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
  app = new ApplicationClass();
  workBook = app.Workbooks.Open(pathToExcelFile, Type.Missing, true, Type.Missing,      Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

  Range range = ((Microsoft.Office.Interop.Excel.Worksheet)workBook.Sheets[sheet.Index]).UsedRange;

  for (int i = 0; i < range.Rows.Count - 1; i++)
  {
      Index++;
      string val  = ((Range)range.Cells[rowIndex, colIndex]).Value2;
  }

Something like this. At least it works for me. Sorry, but try/catch, checking for null etc is up to you. It works for net 3.5