-
May 22nd, 2019, 03:34 AM
#1
Thread Starter
Member
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
-
May 22nd, 2019, 04:15 AM
#2
Addicted Member
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
-
May 22nd, 2019, 05:12 AM
#3
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.
-
May 22nd, 2019, 06:27 AM
#4
Re: How to export to excel the autoincremented row value of datagridview using VB.NET
Originally Posted by Goggy
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
-
May 22nd, 2019, 08:03 PM
#5
Thread Starter
Member
Re: How to export to excel the autoincremented row value of datagridview using VB.NET
Originally Posted by si_the_geek
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:
the output becomes this:
Last edited by ronelpisan; May 22nd, 2019 at 08:22 PM.
-
May 22nd, 2019, 08:25 PM
#6
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
-
May 28th, 2019, 03:33 AM
#7
Thread Starter
Member
Re: How to export to excel the autoincremented row value of datagridview using VB.NET
Originally Posted by kpmc
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|