Click here to Skip to main content
15,894,907 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
How to export excel file from datagridview by having the user choose it's destination file and it's filename?

What I have tried:

VB
Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value

        Dim i As Int16, j As Int16

        Dim xlApp As Excel.Application = New Microsoft.Office.Interop.Excel.Application()
        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("sheet1")

        xlWorkSheet.Cells(1, 1) = "DATE HANDLED"
        xlWorkSheet.Cells(1, 2) = "ROUTE"
        xlWorkSheet.Cells(1, 3) = "ITIN NO"
        xlWorkSheet.Cells(1, 4) = "METER NUMBER"
        xlWorkSheet.Cells(1, 5) = "L.I.N"
        xlWorkSheet.Cells(1, 6) = "ADDRESS"
        xlWorkSheet.Cells(1, 7) = "FF DESCRIPTION AND REMARKS"
        xlWorkSheet.Cells(1, 8) = "RDG"



        For i = 0 To dt_data.RowCount - 2
            For j = 0 To dt_data.ColumnCount - 1
                xlWorkSheet.Cells(i + 3, j + 1) = dt_data(j, i).Value.ToString()
            Next
        Next

        xlWorkBook.SaveAs("f:\vb.net-informations.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue,
         Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue)
        xlWorkBook.Close(True, misValue, misValue)
        xlApp.Quit()

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

        MessageBox.Show("Saved")

and here is the releaseObject class
VB
Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
            MessageBox.Show("Exception Occured while releasing object " + ex.ToString())
        Finally
            GC.Collect()
        End Try
    End Sub
Posted
Updated 12-Nov-19 22:38pm
Comments
EricERankin 15-Apr-20 0:49am    
Here is the simplest was I know how to export DataGridView to an Excel file in VB.NET:

-------
Dim saveFileDialog = New SaveFileDialog()
If (saveFileDialog.ShowDialog() = DialogResult.OK) Then

Dim workbook = New ExcelFile()
Dim worksheet = workbook.Worksheets.Add("Sheet1")

' Export data from DataGridView to ExcelFile.
DataGridViewConverter.ImportFromDataGridView(worksheet, Me.dataGridView1, New ImportFromDataGridViewOptions() With {.ColumnHeaders = True})

workbook.Save(saveFileDialog.FileName)

End If
-------

This doesn't use Office Interop, instead it uses an Excel library for VB.NET.

 
Share this answer
 
See the SaveFileDialog example here: epplus - Save to disk with SaveFileDialog | epplus Tutorial[^]

This example uses the EPPlus library which means you don't need Office interop: GitHub - JanKallman/EPPlus: Create advanced Excel spreadsheets using .NET[^]
 
Share this 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