# How to export to excel the autoincremented row value of datagridview?

Page 1 of 1

## 11 Replies - 366 Views - Last Post: 28 May 2019 - 01:44 AMRate Topic: //<![CDATA[ rating = new ipb.rating( 'topic_rate_', { url: 'https://www.dreamincode.net/forums/index.php?app=forums&module=ajax&section=topics&do=rateTopic&t=416188&amp;s=0cc355f175af16602fdba2fb90be8001&md5check=' + ipb.vars['secure_hash'], cur_rating: 0, rated: 0, allow_rate: 0, multi_rate: 1, show_rate_text: true } ); //]]>

### #1 dpointer

Reputation: 0
• Posts: 42
• Joined: 06-March 09

# How to export to excel the autoincremented row value of datagridview?

Posted 20 May 2019 - 07:27 PM

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:

Private Sub DataGridView1_CellFormatting(sender As Object, e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles DataGridView1.CellFormatting
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 the whole code of my simple program:
Imports Microsoft.Office.Interop

Public Class Form1

Private Sub DataGridView1_CellFormatting(sender As Object, e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles DataGridView1.CellFormatting
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
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



The 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

Is This A Good Question/Topic? 0

## Replies To: How to export to excel the autoincremented row value of datagridview?

### #2 modi123_1

• Suitor #2

Reputation: 15113
• Posts: 60,463
• Joined: 12-June 08

## Re: How to export to excel the autoincremented row value of datagridview?

Posted 20 May 2019 - 08:22 PM

You wouldn't want to be writing from the datagrid, but instead the dataset that backs the grid.

On the dataset define your table, columns, etc.

Build

In the toolbox you'll see your dataset in the the project components. Drag on to your designer for your form.

Click on your datatable and see the little arrow? Click that and chose dataset you dragged on to the form.

From there it's as simple as using a streamwriter for a CSV. All the columns are there.

            Using foo As New StreamWriter("C:\temp\text.csv")
For Each item As DataRow In DataSet11.Tables(0).Rows
foo.WriteLine($"{item("lVal")},{item("sVal")}") Next End Using  Remember - the datagrid is just the UI.. you should _NOT_ be counting on it like a data set that would back up that UI. Was This Post Helpful? 0 ### #3 dpointer • New D.I.C Head Reputation: 0 • Posts: 42 • Joined: 06-March 09 ## Re: How to export to excel the autoincremented row value of datagridview? Posted 22 May 2019 - 01:15 AM modi123_1, on 21 May 2019 - 03:22 AM, said: You wouldn't want to be writing from the datagrid, but instead the dataset that backs the grid. Right click on your project -> add -> new -> dataset. On the dataset define your table, columns, etc. Build In the toolbox you'll see your dataset in the the project components. Drag on to your designer for your form. Click on your datatable and see the little arrow? Click that and chose dataset you dragged on to the form. From there it's as simple as using a streamwriter for a CSV. All the columns are there.  Using foo As New StreamWriter("C:\temp\text.csv") For Each item As DataRow In DataSet11.Tables(0).Rows foo.WriteLine($"{item("lVal")},{item("sVal")}")
Next
End Using


Remember - the datagrid is just the UI.. you should _NOT_ be counting on it like a data set that would back up that UI.

i have already bound the data to datagridview using this code:
 Me.ATSTableAdapter.Fill(Me.ATSDatabaseDataSet.ATS)


but still the first column with autoincremented rownumber in datagridview did not included when i export to excel

This post has been edited by dpointer: 22 May 2019 - 01:17 AM

### #4 maceysoftware

• Member Title

Reputation: 375
• Posts: 1,607
• Joined: 07-September 13

## Re: How to export to excel the autoincremented row value of datagridview?

Posted 22 May 2019 - 01:53 AM

Private Sub DataGridView1_CellFormatting(sender As Object, e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles DataGridView1.CellFormatting
End Sub


When you are looping around your DataGridView, you are only going through rows and columns and not the column header.
   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


So when you say:

Quote

but still the first column with autoincremented rownumber in datagridview did not included when i export to excel

You are incorrect the auto-incremented value is not in a column it is the row header, which is why your export is not including it.

What Modi was meaning is your Auto Increment number should actually be in your DataTable and either shown in a column or still in the Row Header, either way when you get to Exporting the data you can then loop around your DataTable instead to get all the values instead of coding around retrieving information from a row header.

This post has been edited by maceysoftware: 22 May 2019 - 01:56 AM

### #5 dpointer

Reputation: 0
• Posts: 42
• Joined: 06-March 09

## Re: How to export to excel the autoincremented row value of datagridview?

Posted 22 May 2019 - 05:45 AM

maceysoftware, on 22 May 2019 - 08:53 AM, said:

Private Sub DataGridView1_CellFormatting(sender As Object, e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles DataGridView1.CellFormatting
End Sub


When you are looping around your DataGridView, you are only going through rows and columns and not the column header.
   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


So when you say:

Quote

but still the first column with autoincremented rownumber in datagridview did not included when i export to excel

You are incorrect the auto-incremented value is not in a column it is the row header, which is why your export is not including it.

What Modi was meaning is your Auto Increment number should actually be in your DataTable and either shown in a column or still in the Row Header, either way when you get to Exporting the data you can then loop around your DataTable instead to get all the values instead of coding around retrieving information from a row header.

Ah ok. It is in row header. Sorry a beginner here. So what can i do to copy or export those autoincremented row numbers in a row header to excel? Is there other way to get onto it? Thanks.

Sample illustration:

### #6 maceysoftware

• Member Title

Reputation: 375
• Posts: 1,607
• Joined: 07-September 13

## Re: How to export to excel the autoincremented row value of datagridview?

Posted 22 May 2019 - 06:04 AM

If you're going to keep exporting from a datagridview you will need to do the following.
For i = 0 To DataGridView1.RowCount - 2
' Export the Row Header first before doing the loop
' you can get at the row like: Rows(e.RowIndex).HeaderCell.Value.ToString()

' Then continue on and export the other columns
For j = 0 To DataGridView1.ColumnCount - 1
xlWorkSheet.Cells(i + 18, j + 2) = DataGridView1(j, i).Value.ToString()
Next
Next


This post has been edited by maceysoftware: 22 May 2019 - 06:05 AM

### #7 dpointer

Reputation: 0
• Posts: 42
• Joined: 06-March 09

## Re: How to export to excel the autoincremented row value of datagridview?

Posted 22 May 2019 - 06:56 PM

maceysoftware, on 22 May 2019 - 01:04 PM, said:

If you're going to keep exporting from a datagridview you will need to do the following.
For i = 0 To DataGridView1.RowCount - 2
' Export the Row Header first before doing the loop
' you can get at the row like: Rows(e.RowIndex).HeaderCell.Value.ToString()

' Then continue on and export the other columns
For j = 0 To DataGridView1.ColumnCount - 1
xlWorkSheet.Cells(i + 18, j + 2) = DataGridView1(j, i).Value.ToString()
Next
Next


Thanks for the idea.
This is my revised code:
For i = 0 To DataGridView1.RowCount - 2
xlWorkSheet.Cells(i + 18, j + 1) = DataGridView1.Rows(i).HeaderCell.Value
For j = 0 To DataGridView1.ColumnCount - 1
xlWorkSheet.Cells(i + 18, j + 2) = DataGridView1(j, i).Value.ToString()
Next
Next


It works but needs a little refinement. The row number does not continue to number 2.

This is the running program:

This is the output of the revised code:

### #8 modi123_1

• Suitor #2

Reputation: 15113
• Posts: 60,463
• Joined: 12-June 08

## Re: How to export to excel the autoincremented row value of datagridview?

Posted 22 May 2019 - 07:14 PM

Refresh me - why are you putting the ID in the row headers? Outside of the obvious pain point above it also takes that column out of contention to sort on!

### #9 dpointer

Reputation: 0
• Posts: 42
• Joined: 06-March 09

## Re: How to export to excel the autoincremented row value of datagridview?

Posted 22 May 2019 - 07:38 PM

modi123_1, on 23 May 2019 - 02:14 AM, said:

Refresh me - why are you putting the ID in the row headers? Outside of the obvious pain point above it also takes that column out of contention to sort on!

I need to put those numbers to identify easily how many records and on what sequence that record is.

### #10 modi123_1

• Suitor #2

Reputation: 15113
• Posts: 60,463
• Joined: 12-June 08

## Re: How to export to excel the autoincremented row value of datagridview?

Posted 22 May 2019 - 07:44 PM

Yes.. but putting that in a column, and not in the header, accomplishes the exact same thing but with more benefits.

### #11 maceysoftware

• Member Title

Reputation: 375
• Posts: 1,607
• Joined: 07-September 13

## Re: How to export to excel the autoincremented row value of datagridview?

Posted 23 May 2019 - 01:17 AM

Why are you attempting to use 'j' before the loop? if you want it to always be in column one, put it in the column.

My guess is you have declared 'j' somewhere else in your code above so after the first loops ends it is actually at value x instead of 0, therefore your index isn't being put at the start of the rows but rather at the end. I could be wrong and it might just not work, but I can't see why it would work the first time and not again.

### #12 dpointer

Reputation: 0
• Posts: 42
• Joined: 06-March 09

## Re: How to export to excel the autoincremented row value of datagridview?

Posted 28 May 2019 - 01:44 AM

dpointer, on 23 May 2019 - 02:38 AM, said:

modi123_1, on 23 May 2019 - 02:14 AM, said:

Refresh me - why are you putting the ID in the row headers? Outside of the obvious pain point above it also takes that column out of contention to sort on!

I need to put those numbers to identify easily how many records and on what sequence that record is.

Thanks..I already put the autonumber in datagridview in the first place.