-
May 28th, 2019, 04:04 AM
#1
Thread Starter
Member
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
-
May 28th, 2019, 04:25 AM
#2
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
-
May 29th, 2019, 12:31 AM
#3
Thread Starter
Member
Re: How to add paragraph after datagridview data in excel using vb.net?
Originally Posted by si_the_geek
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?
-
May 29th, 2019, 07:17 AM
#4
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|