Results 1 to 4 of 4

Thread: How to add paragraph after datagridview data in excel using vb.net?

  1. #1

    Thread Starter
    Member
    Join Date
    Apr 2019
    Posts
    54

    How to add paragraph after datagridview data in excel using vb.net?

    I want to add a text or paragraph after exporting datagridview data. I don't know how to add because there is no specific cell where I can put the text/paragraph or value and also because the data from datagridview is consuming rows more rows depending on the number of data.
    This is my 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

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

    Re: How to add paragraph after datagridview data in excel using vb.net?

    You are using For loops to put the data in, and based on that code you can tell what the last row (and column) the data will be placed in.

    The row that the For loops put data in to is i + 18 , and the highest value of i is DataGridView1.RowCount - 2

    So, if you want to put something below it (with a one line gap), you could do this:
    Code:
            Dim lastRowForGrid as Integer = (DataGridView1.RowCount - 1) + 18
            xlWorkSheet.Cells(lastRowForGrid + 2, 1) = "something here"
    Last edited by si_the_geek; May 30th, 2019 at 06:34 AM. Reason: accidental use of Column instead of Row - mentioned to ronelpisan in another thread

  3. #3

    Thread Starter
    Member
    Join Date
    Apr 2019
    Posts
    54

    Re: How to add paragraph after datagridview data in excel using vb.net?

    Quote Originally Posted by si_the_geek View Post
    You are using For loops to put the data in, and based on that code you can tell what the last row (and column) the data will be placed in.

    The row that the For loops put data in to is i + 18 , and the highest value of i is DataGridView1.RowCount - 2

    So, if you want to put something below it (with a one line gap), you could do this:
    Code:
            Dim lastRowForGrid as Integer = (DataGridView1.ColumnCount - 1) + 18
            xlWorkSheet.Cells(lastRowForGrid + 2, 1) = "something here"
    Thank you very much man it works! I forgot to mention on merging cells in this cell "xlWorkSheet.Cells(lastRowForGrid + 2, 1) = "something here"". How to merge cells from range of 1st column(A) to 11th columns(K)? Any idea?

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

    Re: How to add paragraph after datagridview data in excel using vb.net?

    When it comes to working out bits of code you need to automate Office programs, the best way is to get the Office program (Excel in this case) to write the code for you... just open it and record a macro while you perform the task manually. You can then look at the code for the macro in the built-in VBA Editor.

    The code it produces isn't quite right for a .Net program, but it is close enough for you to work it out fairly easily. For a general guide on converting the code, see the relevant section of my Excel automation tutorial: http://www.vbforums.com/showthread.php?t=391665 (but note that this is for VB6 rather than VB.Net, so some minor details may differ).

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