Click here to Skip to main content
14,667,169 members
Rate this:
Please Sign up or sign in to vote.
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

Rate this:
Please Sign up or sign in to vote.

Solution 1

For J As Integer = 0 To DataGridView1.ColumnCount = -1
and
For i As Integer = 0 To DataGridView1.Rows.Count = -2
should be
For J As Integer = 0 To DataGridView1.ColumnCount - 1
and
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.
   
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.
Rate this:
Please Sign up or sign in to vote.

Solution 2

تصدير البيانات الرقميه الى جدوال في الورد


Translation:
Quote:
Export digital data to a table in Word
   
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, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100