11 Replies - 366 Views - Last Post: 28 May 2019 - 01:44 AM Rate Topic: -----

#1 dpointer   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • 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
        DataGridView1.Rows(e.RowIndex).HeaderCell.Value = CStr(e.RowIndex + 1)
    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
        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



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   User is online

  • Suitor #2
  • member icon



Reputation: 15113
  • View blog
  • 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.

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.
Was This Post Helpful? 0
  • +
  • -

#3 dpointer   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • 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

View Postmodi123_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

Was This Post Helpful? 0
  • +
  • -

#4 maceysoftware   User is offline

  • Member Title
  • member icon

Reputation: 375
  • View blog
  • 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

Your DataSource doesn't contain the Auto-Increment value, instead, you are storing it in your row header using the following 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


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

Was This Post Helpful? 0
  • +
  • -

#5 dpointer   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • 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

View Postmaceysoftware, on 22 May 2019 - 08:53 AM, said:

Your DataSource doesn't contain the Auto-Increment value, instead, you are storing it in your row header using the following 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


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:
Attached Image
Was This Post Helpful? 0
  • +
  • -

#6 maceysoftware   User is offline

  • Member Title
  • member icon

Reputation: 375
  • View blog
  • 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

Was This Post Helpful? 0
  • +
  • -

#7 dpointer   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • 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

View Postmaceysoftware, 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:
Attached Image

This is the output of the revised code:
Attached Image
Was This Post Helpful? 0
  • +
  • -

#8 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15113
  • View blog
  • 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!
Was This Post Helpful? 0
  • +
  • -

#9 dpointer   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • 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

View Postmodi123_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.
Was This Post Helpful? 0
  • +
  • -

#10 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15113
  • View blog
  • 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.
Was This Post Helpful? 0
  • +
  • -

#11 maceysoftware   User is offline

  • Member Title
  • member icon

Reputation: 375
  • View blog
  • 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.
Was This Post Helpful? 0
  • +
  • -

#12 dpointer   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • 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

View Postdpointer, on 23 May 2019 - 02:38 AM, said:

View Postmodi123_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.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1