Get Excel Row Number using C#

Question

I want to get row number of my excel data. Here, I have excel file that has data like below.

I have data :

WSID  Nominal
123   1000
456   2000
789   3000
987   4000
654   5000
321   6000

I want to show row number of my data that is shown in DatagRidView. How can I do that ?

string koneksi = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Program-Pengisian-Uang-ATM-BCA-SOY\Program-Pengisian-Uang-ATM-BCA-SOY\bin\x86\Debug\ATM SLA Surabaya.xlsx; Extended Properties='Excel 12.0 xml;HDR=YES;'";

    private void PrintScheduleBtn_Click(object sender, EventArgs e)
    {
    Excel.Application oApp;
    Excel.Worksheet oSheetCartridge;
    Excel.Workbook oBook;

    oApp = new Excel.Application();
    oApp.Visible = true;
    oApp.DisplayAlerts = false;

    oBook = oApp.Workbooks.Open(lokasifile, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
    oSheetCartridge = oBook.Sheets["Cartridge"];

    OleDbConnection kon2 = new OleDbConnection(koneksi); //database
    OleDbCommand comm = kon2.CreateCommand();

    foreach (DataGridViewRow row in JadwalisiGV.Rows)
    {
        if (!row.IsNewRow)
        {
        kon2.Open();    
        comm.CommandText = "SELECT * FROM [Data$] WHERE [WSID] = '"+ row.Cells["WSID"].Value +"'";
        listBox1.Items.Add(); // I want to show row number of my WSID data that is shown in JadwalisiGV here
        kon2.Close();

        }
    }

    }

Your qus is not clear.. From where u want row no. from datatable? — Kushal Patil
– Kushal Patil, Commented Dec 18, 2016 at 3:23

anis programmer · Accepted Answer · 2016-12-20 05:11:48Z

1

You can try this code

foreach (DataGridViewRow row in JadwalisiGV.Rows)
    {
        if (!row.IsNewRow)
        {
        kon2.Open();    
        comm.CommandText = "SELECT WSID, Nominal,ROW_NUMBER() over (order by WSID) FROM [Data$] WHERE [WSID] = '"+ row.Cells["WSID"].Value +"'";
        row.HeaderCell.Value = (row.Index + 1).ToString();
        listBox1.Items.Add(); // I want to show row number of my WSID data that is shown in JadwalisiGV here
        kon2.Close();

        }
    }

edited Dec 20, 2016 at 5:11

answered Dec 18, 2016 at 3:58

anis programmer

9991 gold badge10 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alfonsus Dhani Over a year ago

Thanks for your answer but I mean I want to show row number of my data that is store in database and shown in datargridview. For eample, my data 1234 is shown in row on of datagridview but it is stored in row five in my database. I want to show 5 to my listbox. Can you help me ?

anis programmer Over a year ago

@Alfonsus Dhani,You can try this way if you use oracle comm.CommandText = "SELECT WSID, Nominal,ROW_NUMBER() over (order by WSID) FROM [Data$] WHERE [WSID] = '"+ row.Cells["WSID"].Value +"'";

Ambrish Pathak · Accepted Answer · 2016-12-18 05:29:23Z

0

Try like this:

  foreach (DataGridViewRow row in JadwalisiGV.Rows)
    {.
     .
      int index = JadwalisiGV.Rows.IndexOf(row);
     .
     .
    }

answered Dec 18, 2016 at 5:29

Ambrish Pathak

3,9882 gold badges17 silver badges30 bronze badges

Collectives™ on Stack Overflow

Get Excel Row Number using C#

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related