Click here to Skip to main content
15,886,806 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I create a program for events booking using VB.Net and access as database. So I want to export datagridview to excel for further user's editing. I coded the button called export. But when I click button it open save dialog box for me to save the data. But when I open the saved data in excel, only first column header appear as well as first row data information. The rest of the columns and other rows is not there. I attached the code I have used.


What I have tried:

Imports System.Data.DataTable
Imports Excel = Microsoft.Office.Interop.Excel
Imports Microsoft.Office
Imports Microsoft.Office.Interop
Imports System.IO
Public Class Form1

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

    End Sub

     Private Sub SaveBtn_Click(sender As Object, e As EventArgs) Handles SaveBtn.Click
        SaveToExcel()
    End Sub

    Private Sub SaveToExcel()
        Dim excel As Microsoft.Office.Interop.Excel._Application = New Microsoft.Office.Interop.Excel.Application()
        Dim workbook As Microsoft.Office.Interop.Excel._Workbook = excel.Workbooks.Add(Type.Missing)
        Dim worksheet As Microsoft.Office.Interop.Excel._Worksheet = Nothing

        Try

            worksheet = workbook.ActiveSheet
            worksheet.Name = "ExportedFromDatGrid"

            Dim CellRowIndex As Integer = 1
            Dim cellColumnIndex As Integer = 1

            For J As Integer = 0 To DataGridView1.ColumnCount = -1
                worksheet.cells(CellRowIndex, cellColumnIndex) = DataGridView1.Columns(J).HeaderText
                cellColumnIndex += 1

            Next
            cellColumnIndex = 1
            CellRowIndex += 1

            For i As Integer = 0 To DataGridView1.Rows.Count = -2
                For J As Integer = 0 To DataGridView1.ColumnCount - 1
                    worksheet.cells(CellRowIndex, cellColumnIndex) = DataGridView1.Rows(i).Cells(J).Value.ToString()
                    cellColumnIndex += 1


                Next
                cellColumnIndex = 1
                CellRowIndex += 1
            Next
            Dim SaveDialog As New SaveFileDialog()
            SaveDialog.Filter = "Excel Files(*.xlsx)|*.xlsx|All files (*.*)|*.*"
            SaveDialog.FilterIndex = 2

            If SaveDialog.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
                workbook.saveAs(SaveDialog.FileName)
                MessageBox.Show("Export Successful")
            End If

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            excel.Quit()
            workbook = Nothing
            excel = Nothing
        End Try



    End Sub

    Private Sub SaveFileDialog1_FileOk(sender As Object, e As System.ComponentModel.CancelEventArgs)

    End Sub
End Class
Posted
Updated 30-Sep-20 8:29am

VB.NET
For J As Integer = 0 To DataGridView1.ColumnCount = -1
and
VB.NET
For i As Integer = 0 To DataGridView1.Rows.Count = -2
should be
VB.NET
For J As Integer = 0 To DataGridView1.ColumnCount - 1
and
VB.NET
For i As Integer = 0 To DataGridView1.Rows.Count - 2
respectively.

A quicker way to solve this kind of issues is to use debugging; put a breakpoint at the beginning of the Try block, start a debug session (F5), and execute line-by-line, watching for the program flow. You will quickly find out that your loops may not behave the way they should.
 
Share this answer
 
v2
Comments
NyikoB 6-May-20 4:47am    
Thank you very much it works correctly
phil.o 6-May-20 4:50am    
You're welcome. Please mark the question as answered if that is the case, so that it leaves the unanswered questions queue.
تصدير البيانات الرقميه الى جدوال في الورد


Translation:
Quote:
Export digital data to a table in Word
 
Share this answer
 
v2
Comments
Richard MacCutchan 30-Sep-20 16:06pm    
This is not an answer.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900