How to export to excel the autoincremented row value of datagridview using VB.NET?

I have datagridview with columns: FullName, Age, Birthday, Degree.

The autoincremented row value displayed/located at the first column of datagridview that is automatically generated by the following code:

Code:

---

Private Sub DataGridView1_CellFormatting(sender As Object, e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles DataGridView1.CellFormatting
        DataGridView1.Rows(e.RowIndex).HeaderCell.Value = CStr(e.RowIndex + 1)
  End Sub

---

This is also the code for databinding in datagridview:

Code:

---

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'ATSDatabaseDataSet.ATS' table. You can move, or remove it, as needed.
        Me.ATSTableAdapter.Fill(Me.ATSDatabaseDataSet.ATS)

    End Sub

---

My problem is that the autoincremented row value is not included in exporting to excel.
I want it to be included in first column(A) in excel.

This is my whole code:

Code:

---

Imports Microsoft.Office.Interop

Public Class Form1

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'ATSDatabaseDataSet.ATS' table. You can move, or remove it, as needed.
        Me.ATSTableAdapter.Fill(Me.ATSDatabaseDataSet.ATS)

    End Sub

    Private Sub DataGridView1_CellFormatting(sender As Object, e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles DataGridView1.CellFormatting
        DataGridView1.Rows(e.RowIndex).HeaderCell.Value = CStr(e.RowIndex + 1)
    End Sub

    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value
        Dim i As Integer
        Dim j As Integer

        xlApp = New Excel.Application
        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("sheet1")

        For i = 0 To DataGridView1.RowCount - 2
            For j = 0 To DataGridView1.ColumnCount - 1
                xlWorkSheet.Cells(i + 18, j + 2) = DataGridView1(j, i).Value.ToString()
            Next
        Next


        xlWorkSheet.SaveAs("C:\Users\Programmer RBP\Desktop\vbexcel.xlsx")
        xlWorkBook.Close()
        xlApp.Quit()

        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)

        MsgBox("You can find the file C:\Users\Programmer RBP\Desktop\vbexcel.xlsx")
    End Sub
End Class

---

he output in excel:

A | B | C | D | E
-------------------------------------------------------------
| Juan Cruz | 31 | 2/24/1988 | Undergraduate
| John Wayne| 31 | 1/05/1988 | Graduate
| Jason Dack| 31 | 2/14/1988 | Undergraduate


What I want or expected output:

A | B | C | D | E
-------------------------------------------------------------
1 | Juan Cruz | 31 | 2/24/1988 | Undergraduate
2 | John Wayne| 31 | 1/05/1988 | Graduate
3 | Jason Dack| 31 | 2/14/1988 | Undergraduate

Attachment 168645

Either you should export the rowheader text or i

somthing like so i would imagine:

xlWorkSheet.Cells(i + 18, j + 1) = (i).ToString()

you can add a Autoincrement Number to Excel after the Export

here a sample

Code:

---

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try
            Dim xlApp As New Microsoft.Office.Interop.Excel.Application()
            Dim xlWb As Microsoft.Office.Interop.Excel.Workbook
            xlWb = xlApp.Workbooks.Open("E:\vbExcel.xlsx")
            Dim xlSt As Microsoft.Office.Interop.Excel.Worksheet = xlWb.ActiveSheet
           
            Dim rowRange = xlSt.UsedRange.Rows.Count 'count the used rows

            'add the running Number Column to A with Text = ID
            'note you only need the next 3 Lines once
            xlSt.Columns("A:A").Insert(-4152)
            xlSt.Range("A1").Value = "ID"
            xlSt.Range("A:A").EntireColumn.NumberFormat = "000"

            With xlSt
                Dim i As Integer, n As Integer
                n = 1
                For i = 2 To rowRange  'start number in row 2 or change to the row you want
                    .Cells(i, "A") = n
                    n += 1
                Next
            End With

            xlWb.Save()
            xlApp.Quit()
            xlApp = Nothing
        Catch g As Exception
            MsgBox(g.ToString)
        End Try
    End Sub

---

Quote:

---

Originally Posted by Goggy

Either you should export the rowheader text or i

somthing like so i would imagine:

xlWorkSheet.Cells(i + 18, j + 1) = (i).ToString()

---

While that would currently work properly, it wont use the right values if the code in _CellFormatting changes, so I'd recommend your other suggestion of exporting the row header:

Code:

---

        xlWorkSheet.Cells(i + 18, j + 1) = DataGridView1.Rows(i).HeaderCell.Value

---

Quote:

---

Originally Posted by si_the_geek

While that would currently work properly, it wont use the right values if the code in _CellFormatting changes, so I'd recommend your other suggestion of exporting the row header:

Code:

---

        xlWorkSheet.Cells(i + 18, j + 1) = DataGridView1.Rows(i).HeaderCell.Value

---

---

This is my revised code:

Code:

---

For i = 0 To DataGridView1.RowCount - 2
            For j = 0 To DataGridView1.ColumnCount - 1
                xlWorkSheet.Cells(i + 18, j + 2) = DataGridView1(j, i).Value.ToString()
                xlWorkSheet.Cells(i + 18, j + 1) = DataGridView1.Rows(i).HeaderCell.Value
            Next
        Next

---

running program:
Attachment 168681

the output becomes this:
Attachment 168687

Remove the AI from the datagridview and add it to the datatable prior to filling it

Quote:

---

Originally Posted by kpmc

Remove the AI from the datagridview and add it to the datatable prior to filling it

---

Thanks..already solved.