Click here to Skip to main content
15,867,869 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
In my desktop application i want to Export data directly from a SQL database to MS Excel with customize heading.I want to export my database which contains many tables to the excel 2007.
When user clicks on Export button, it will directly export data into Excel using VB.Net.How can i achieve that? please help me..
Posted
Comments
Archana K 4-May-13 6:08am    
I solved my problem my self...
Maciej Los 5-May-13 7:29am    
Please, share your solution and (after that) mark the question as "solved".

1 solution

This code exports from DataGridView to Excel
VB
Private Sub Export_Button_Click(ByVal sender As System.Object, ByVal e As 
System.EventArgs) Handles VIEW_Button.Click
 
    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.ApplicationClass
    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("D:\vbexcel.xlsx")
    xlWorkBook.Close()
    xlApp.Quit()
 
    releaseObject(xlApp)
    releaseObject(xlWorkBook)
    releaseObject(xlWorkSheet)
 
    MsgBox("You can find the file D:\vbexcel.xlsx")
End Sub
 
Private Sub releaseObject(ByVal obj As Object)
    Try
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
        obj = Nothing
    Catch ex As Exception
        obj = Nothing
    Finally
        GC.Collect()
    End Try
End Sub
 
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