Click here to Skip to main content
14,330,512 members
Rate this:
Please Sign up or sign in to vote.
See more:
I have created a datagridveiw to display access database. I need to create a export function from datagrideview to MS Excel. I have tried to save all data to export from datagrideview to MS Excel it worked.


What i really need is i need user to select records (Rows) and selected rows needs to be stored on MS Excel. How i can change below coding to save only selected rows?


to select rows i have created a row column on datagridview using below command
Dim checkBoxColumn As New DataGridViewCheckBoxColumn()
        checkBoxColumn.HeaderText = ""
        checkBoxColumn.Width = 30
        checkBoxColumn.Name = "checkBoxColumn"
        DataGridView1.Columns.Insert(0, checkBoxColumn)


What I have tried:

<pre>Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
    Try
        Button3.Text = "Please Wait..."
        Button3.Enabled = False

        SaveFileDialog1.Filter = "Excel Document (*.xlsx)|*.xlsx"
        If SaveFileDialog1.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
            Dim xlApp As Microsoft.Office.Interop.Excel.Application
            Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook
            Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
            Dim misValue As Object = System.Reflection.Missing.Value
            Dim i As Integer
            Dim j As Integer

            xlApp = New Microsoft.Office.Interop.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
                    For k As Integer = 1 To DataGridView1.Columns.Count
                        xlWorkSheet.Cells(1, k) = DataGridView1.Columns(k - 1).HeaderText
                        xlWorkSheet.Cells(i + 2, j + 1) = DataGridView1(j, i).Value.ToString()
                    Next
                Next
            Next

            xlWorkSheet.SaveAs(SaveFileDialog1.FileName)
            xlWorkBook.Close()
            xlApp.Quit()

            releaseObject(xlApp)
            releaseObject(xlWorkBook)
            releaseObject(xlWorkSheet)

            MsgBox("Successfully saved" & vbCrLf & "File are saved at : " & SaveFileDialog1.FileName, MsgBoxStyle.Information, "Information")

            Button3.Text = "Export To MS Excel"
            Button3.Enabled = True
        End If
    Catch ex As Exception
        MessageBox.Show("Failed to save !!!", "Error Message", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Return
    End Try
Posted
Updated 6-Oct-19 1:00am
v2

1 solution

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

Solution 1

For i = 0 To DataGridView1.RowCount - 2
This iterates over all rows but last one.

Change this to
For i = 0 To DataGridView1.SelectedRows.Count - 1
and it will only iterate over selected rows.

Then you will have to tweak the code inside your three loops to work on actual selected row:
For k As Integer = 1 To DataGridView1.Columns.Count
   DataGridViewRow row = DataGridView1.SelectedRows.Item(i)
   '' TODO change implementation to take actual row into consideration
   
Comments
Mi Shan 6-Oct-19 6:56am
   
I have created a check box on the datagridview with below command

Dim checkBoxColumn As New DataGridViewCheckBoxColumn()
checkBoxColumn.HeaderText = ""
checkBoxColumn.Width = 30
checkBoxColumn.Name = "checkBoxColumn"
DataGridView1.Columns.Insert(0, checkBoxColumn)

how i can select only checked rows from Datagridview? any idea idea please
phil.o 6-Oct-19 7:00am
   
- Create an empty list of rows
- Iterate over the rows
- Add current row to list if it is checked
- Return the list
- Act on rows of formerly obtained list

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