Results 1 to 2 of 2

Thread: Export to Excel From Datagridview

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Jun 2013
    Posts
    26

    Export to Excel From Datagridview

    hi, I'm new to Vb.net and I need a little help. I have this Code to Export All Data from Datagridview to Excel and it works well, Problem 1 :

    But what I want to do is To export only Specific columns, not all columns from Datagridview (Example I want to Export only Column 3 and 5) Not all Columns.

    And Problem 2:
    is It is possible to export in an existing template example I have an excel template with Name And ID to export in this existing template not in new.

    Thank You.

    Code:
       ' Creating a Excel object.
            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" '
                worksheet.Name = Me.Text ' 
                Dim cellRowIndex As Integer = 1
                Dim cellColumnIndex As Integer = 1
    
                'Loop through each row and read value from each column.
                For i As Integer = -1 To dgw.Rows.Count - 1
                    For j As Integer = 0 To dgw.Columns.Count - 1
                        ' Excel index starts from 1,1. As first Row would have the Column headers, adding a condition check.
                        If cellRowIndex = 1 Then
                            worksheet.Cells(cellRowIndex, cellColumnIndex) = dgw.Columns(j).HeaderText
                        Else
                            worksheet.Cells(cellRowIndex, cellColumnIndex) = dgw.Rows(i).Cells(j).Value.ToString()
                        End If
                        cellColumnIndex += 1
                    Next
                    cellColumnIndex = 1
                    cellRowIndex += 1
                Next
    
                'Getting the location and file name of the excel to save from user.
                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)
                    MsgBox("Export Successful", vbInformation)
                End If
            Catch ex As System.Exception
                MessageBox.Show(ex.Message) 'e paraqet probleim ne mesazh
            Finally
                excel.Quit()
                workbook = Nothing
                excel = Nothing
            End Try

  2. #2
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    Re: Export to Excel From Datagridview

    This is simple to export columns in a DataGridView when using the DataSource of the DataGridView with a DataTable using SpreadSheetLight via NuGet.

    To get only needed columns, Crate a new DataTable as follows where you replace C1, C2 etc with column names from your DataTable

    Code:
    Dim view As New DataView(dataTableFromDataGridView)
    Dim selected As DataTable = view.ToTable("Selected", False, "C1", "C2")
    Simple export code

    Code:
    Public Shared Sub ExportTable(pFileName As String, pSheetName As String, pDataTable As DataTable, pColumnHeaders As Boolean)
    
        Using doc As New SLDocument()
            doc.SelectWorksheet(pSheetName)
            doc.ImportDataTable(1, SLConvert.ToColumnIndex("A"), pDataTable, pColumnHeaders)
            doc.RenameWorksheet(SLDocument.DefaultFirstSheetName, pSheetName)
            doc.SaveAs(pFileName)
        End Using
    
    End Sub
    Styling can be done via SpreadSheetLight's SLStyle class e.g.

    Code:
    Using doc As New SLDocument(pFileName)
    
        doc.SelectWorksheet(pSheetName)
        doc.ImportDataTable(pStartRow, SLConvert.ToColumnIndex("A"), pDataTable, pColumnHeaders)
    
        Dim style As New SLStyle With {.FormatCode = "MM/dd//yyyy"}
    
    
        If pDataTable.Columns.Contains("ModifiedDate") Then
            doc.SetColumnStyle(pDataTable.Columns("ModifiedDate").Ordinal + 1, style)
        End If
    
        doc.Save()
    
        Return True
    
    End Using

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
  •  



Click Here to Expand Forum to Full Width