Click here to Skip to main content
14,732,921 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello everyone,
i want create export database table items to excel that include the columns name of the items.

this code export that items successfully but excluding the columns name. any help please?

'lib
Imports System.Data
Imports System.Data.OleDb
'add references called microsoft.office.interop.excel 11
Imports Excel = Microsoft.Office.Interop.Excel

'btn export
Dim cnn As OleDbConnection
        Dim cnnst As String
        Dim sql As String
        Dim i, j As Integer

        'excel
        Dim xlapp As Excel.Application
        Dim xlworkbook As Excel.workbook
        Dim xlworksheet As Excel.worksheet
        Dim misvalue As Object = Reflection.Missing.Value
        xlapp = New Excel.Application
        xlworkbook = xlapp.workbooks.add(misvalue)
        xlworksheet = xlworkbook.sheets("sheet1")
        cnnst = "Provider=Microsoft.jet.OLEDB.4.0;Persist Security Info=False;Data Source=..\..\..\indigeneDB.mdb;"
        cnn = New OleDbConnection(cnnst)
        cnn.Open()
        sql = "select * from lga"
        Dim cmd As New OleDbDataAdapter(sql, cnn)
        Dim ds As New DataSet
        cmd.Fill(ds)

        For i = 0 To ds.Tables(0).Columns.Count - 1
            For j = 0 To ds.Tables(0).Columns.Count - 1
                xlworksheet.Cells(i + 1, j + 1) = ds.Tables(0).Rows(i).Item(j)
            Next
        Next
        xlworksheet.SaveAs("Sample_District.xlsx")
        xlworkbook.close()
        xlapp.Quit()

        Myobject(xlapp)
        Myobject(xlworkbook)
        Myobject(xlworksheet)
        cnn.Close()
        MessageBox.Show("The sample database was exported to 'Documents folder'")

thank you.
Posted
Comments
PIEBALDconsult 17-Jan-16 10:41am
   
Exporting to CSV is easier and more portable. Excel can read CSV; lots of things can read CSV.
As a recipient of many types of files, I prefer CSV over Excel.
When I do read or write Excel, I use the ACE engine and ADO.net -- never interop.
What are you going to do with the file after you create it?
Engr. S.M. Inuwa 17-Jan-16 11:47am
   
Actually, i want have the column names to explain to client-users the name of data column because if there is no columns name one may not understand what a particular column in exported excel stand for.
PIEBALDconsult 17-Jan-16 12:56pm
   
I understand why you want the column names; I want to know why you are writing to Excel; it's probably not the best solution to whatever you are trying to do.

1 solution

You are only saving the data. You need to extract the column names from the table you are trying to export, by calls of the form:
ds.Tables(0).Columns(i).ColumnName;
   

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