Results 1 to 7 of 7

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

  1. #1

    Thread Starter
    Member
    Join Date
    Apr 2019
    Posts
    54

    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

    Name:  expected output.jpg
Views: 677
Size:  44.5 KB

  2. #2
    Addicted Member Goggy's Avatar
    Join Date
    Oct 2017
    Posts
    196

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

    Either you should export the rowheader text or i

    somthing like so i would imagine:

    xlWorkSheet.Cells(i + 18, j + 1) = (i).ToString()
    Utterly useless, but always willing to help

    As a finishing touch god created the dutch

  3. #3
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,042

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

    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
    Last edited by ChrisE; May 22nd, 2019 at 05:16 AM.
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

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

    Quote Originally Posted by Goggy View Post
    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

  5. #5

    Thread Starter
    Member
    Join Date
    Apr 2019
    Posts
    54

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

    Quote Originally Posted by si_the_geek View Post
    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:
    Name:  revised.PNG
Views: 649
Size:  6.7 KB

    the output becomes this:
    Name:  revisedoutput.PNG
Views: 563
Size:  3.4 KB
    Last edited by ronelpisan; May 22nd, 2019 at 08:22 PM.

  6. #6
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

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

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

  7. #7

    Thread Starter
    Member
    Join Date
    Apr 2019
    Posts
    54

    Resolved Re: How to export to excel the autoincremented row value of datagridview using VB.NET

    Quote Originally Posted by kpmc View Post
    Remove the AI from the datagridview and add it to the datatable prior to filling it
    Thanks..already solved.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width