Solved

Asp.net export sql table to excel, missing header row. Need someone to check code!

Posted on 2014-01-15
26
762 Views
Last Modified: 2014-02-04
Hello,
Below code works fine, but excel is missing header row, and have only record lines.
I was try to found solution, and googling all day, but nothing works for me.

So, have can I include also header row into excel file?

Thank you in advance for help!

Protected Sub ImageButtonExportToExcel_Click(sender As Object, e As ImageClickEventArgs) Handles ImageButtonExportToExcel.Click
        Dim cnn As SqlConnection
        Dim connectionString As String
        Dim sql As String
        Dim i, j As Integer

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

        xlApp = New Excel.ApplicationClass
        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("sheet1")

        connectionString = DB
        cnn = New SqlConnection(connectionString)
        cnn.Open()
        sql = "SELECT * FROM Main"
        Dim dscmd As New SqlDataAdapter(sql, cnn)
        Dim ds As New DataSet
        dscmd.Fill(ds)

        For i = 0 To ds.Tables(0).Rows.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("C:\vbexcel.xlsx")
        xlWorkBook.Close()
        xlApp.Quit()

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

        cnn.Close()

        MsgBox("You can find the file C:\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

Open in new window

0
Comment
Question by:dejandejanovic
  • 13
  • 8
  • 5
26 Comments
 
LVL 32

Expert Comment

by:Big Monty
ID: 39782615
i don't see any place where you're building headers, try something like this:

        connectionString = DB
        cnn = New SqlConnection(connectionString)
        cnn.Open()
        sql = "SELECT * FROM Main"
        Dim dscmd As New SqlDataAdapter(sql, cnn)
        Dim ds As New DataSet
        dscmd.Fill(ds)

       j = 0
       For Each column As DataColumn In ds.Columns
           xlWorkSheet.Cells(0, j + 1) =  = column.ColumnName
           j += 1
       Next

        For i = 1 To ds.Tables(0).Rows.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

Open in new window


as for the number of rows returned, are you sure there's more than one record in the database?
0
 
LVL 32

Expert Comment

by:Big Monty
ID: 39782620
if you write out ds.Tables(0).Rows.Count to the screen, what do you get?
0
 

Author Comment

by:dejandejanovic
ID: 39799669
Thank you for replies, and sorry for my late response.
ds.Tables(0).Rows.Count returns counts of each record. Currently I have two records in the table, and returns 2
0
 

Author Comment

by:dejandejanovic
ID: 39799682
Row is giving me an error = Columns' is not a member of 'System.Data.DataSet
For Each column As DataColumn In [b]ds.Columns[/b]

Open in new window


I have try to change, but getting client error = Exception from HRESULT: 0x800A03EC
  j = 0
        For Each column As DataColumn In ds.Tables(0).Columns
           xlWorkSheet.Cells(0, j + 1) = column.ColumnName
            j += 1
        Next

Open in new window

0
 
LVL 32

Expert Comment

by:Big Monty
ID: 39799939
try changing the first FOR loop to:

For Each column As DataColumn In ds.Tables(0).Columns
           Dim colName As String = Row( column.ColumnName).ToString()
           xlWorkSheet.Cells(0, j + 1) =  colName
           j += 1
       Next

Open in new window

0
 

Author Comment

by:dejandejanovic
ID: 39800056
Hm, sorry, but where did define 'Row' in second code row, as:
Systemm.Data.SqlClient.Row' is not accessible in this context because it is 'Friend'.
0
 
LVL 32

Expert Comment

by:Big Monty
ID: 39800063
please post the newly updated code
0
 

Author Comment

by:dejandejanovic
ID: 39803098
Row(column.ColumnName).ToString()  is invalid =  System.Data.SqlClient.Row' is not accessible in this context because it is 'Friend'.

connectionString = DB
        cnn = New SqlConnection(connectionString)
        cnn.Open()
        sql = "SELECT * FROM Main"
        Dim dscmd As New SqlDataAdapter(sql, cnn)
        Dim ds As New DataSet
        dscmd.Fill(ds)
        For Each column As DataColumn In ds.Tables(0).Columns
            Dim colName As String = Row(column.ColumnName).ToString()
            xlWorkSheet.Cells(0, j + 1) = colName
            j += 1
        Next


        For i = 0 To ds.Tables(0).Rows.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

Open in new window

0
 
LVL 32

Assisted Solution

by:Big Monty
Big Monty earned 100 total points
ID: 39803381
try changing

Dim ds As New DataSet

to

Dim ds As DataSet
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39806666
Change line 9 from

Dim colName As String = Row(column.ColumnName).ToString()

to

Dim colName As String = column.ColumnName
0
 

Author Comment

by:dejandejanovic
ID: 39817039
I have change both lines...

Error: Sys.WebForms.PageRequestManagerServerErrorException: Value cannot be null.
Parameter name: dataSet
0
 

Author Comment

by:dejandejanovic
ID: 39817049
If I change back to Dim ds As New DataSet then I get already known error:
Error: Sys.WebForms.PageRequestManagerServerErrorException: Exception from HRESULT: 0x800A03EC
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39817337
Can you show your code?
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:dejandejanovic
ID: 39817732
Dim cnn As SqlConnection
        Dim connectionString As String
        Dim sql As String
        Dim i, j As Integer

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

        xlApp = New Excel.ApplicationClass
        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("sheet1")

        connectionString = DB
        cnn = New SqlConnection(connectionString)
        cnn.Open()
        sql = "SELECT * FROM Main"
        Dim dscmd As New SqlDataAdapter(sql, cnn)
        Dim ds As New DataSet
        dscmd.Fill(ds)
        For Each column As DataColumn In ds.Tables(0).Columns
            Dim colName As String = column.ColumnName
            xlWorkSheet.Cells(0, j + 1) = colName
            j += 1
        Next


        For i = 0 To ds.Tables(0).Rows.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("C:\vbexcel.xlsx")
        xlWorkBook.Close()
        xlApp.Quit()

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

        cnn.Close()

        MsgBox("You can find the file C:\vbexcel.xlsx")

Open in new window

0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39818272
Which line produces the error?
0
 

Author Comment

by:dejandejanovic
ID: 39820105
If Dim ds As New Dataset client error is:
Error: Sys.WebForms.PageRequestManagerServerErrorException: Exception from HRESULT: 0x800A03EC

If Dim ds as Dataset client error is:
Error: Sys.WebForms.PageRequestManagerServerErrorException: Value cannot be null.
Parameter name: dataSet

Line 21 of below code, and visual studio warning error:
Variable 'ds' is used before it has been assigned a value. A null reference exception could result at runtime.

Dim cnn As SqlConnection
        Dim connectionString As String
        Dim sql As String
        Dim i, j As Integer

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

        xlApp = New Excel.ApplicationClass
        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("sheet1")

        connectionString = DB
        cnn = New SqlConnection(connectionString)
        cnn.Open()
        sql = "SELECT * FROM Main"
        Dim dscmd As New SqlDataAdapter(sql, cnn)
        Dim ds As DataSet
        dscmd.Fill(ds)
        For Each column As DataColumn In ds.Tables(0).Columns
            Dim colName As String = column.ColumnName
            xlWorkSheet.Cells(0, j + 1) = colName
            j += 1
        Next


        For i = 0 To ds.Tables(0).Rows.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("C:\vbexcel.xlsx")
        xlWorkBook.Close()
        xlApp.Quit()

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

        cnn.Close()

        MsgBox("You can find the file C:\vbexcel.xlsx")

Open in new window

0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39820472
2nd error is understandable but which line produces the error

Error: Sys.WebForms.PageRequestManagerServerErrorException: Exception from HRESULT: 0x800A03EC


when you use

Dim ds as New dataset
0
 

Author Comment

by:dejandejanovic
ID: 39820539
Yes, correct.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39821112
I missed the question mark at the end. Which line throws the error

Error: Sys.WebForms.PageRequestManagerServerErrorException: Exception from HRESULT: 0x800A03EC

when you use

Dim ds As New DataSet
?
0
 

Author Comment

by:dejandejanovic
ID: 39821616
Yes, correct again. This one = Dim ds As New Dataset.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39823900
WHICH LINE THROWS THE ERROR?
0
 

Author Comment

by:dejandejanovic
ID: 39824098
I'm confused CodeCruiser as I'm receiving only client side error.

Below lines are making upper written errors.
Dim ds As DataSet
        dscmd.Fill(ds)
        For Each column As DataColumn In ds.Tables(0).Columns
            Dim colName As String = column.ColumnName
            xlWorkSheet.Cells(0, j + 1) = colName
            j += 1
        Next

Open in new window

0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39824240
But in this code now, you have

Dim ds As Dataset

instead of

Dim ds As New Dataset

forget about

Dim ds As Dataset

as this will throw a null value exception on following line anyway

dscmd.Fill(ds)



Try following code and see if you still get the error

Dim cnn As SqlConnection
        Dim connectionString As String
        Dim sql As String
        Dim i, j As Integer

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

        xlApp = New Excel.ApplicationClass
        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("sheet1")

        connectionString = DB
        cnn = New SqlConnection(connectionString)
        cnn.Open()
        sql = "SELECT * FROM Main"
        Dim dscmd As New SqlDataAdapter(sql, cnn)
        Dim ds As New DataSet
        dscmd.Fill(ds)
        For Each column As DataColumn In ds.Tables(0).Columns
            Dim colName As String = column.ColumnName
            xlWorkSheet.Cells(1, j + 1) = colName
            j += 1
        Next


        For i = 0 To ds.Tables(0).Rows.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("C:\vbexcel.xlsx")
        xlWorkBook.Close()
        xlApp.Quit()

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

        cnn.Close()

        MsgBox("You can find the file C:\vbexcel.xlsx")

Open in new window

0
 

Author Comment

by:dejandejanovic
ID: 39828897
No errors, but excel is still without Header row.
0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 400 total points
ID: 39832521
And if you change line 31 from

xlWorkSheet.Cells(i + 1, j + 1) = ds.Tables(0).Rows(i).Item(j)

to

xlWorkSheet.Cells(i + 2, j + 1) = ds.Tables(0).Rows(i).Item(j)

then?
0
 

Author Comment

by:dejandejanovic
ID: 39832654
Uf, finally. Yeap, now I have got a column row.
Great job CodeCruiser, and thanks for your pantience.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now