Solved

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

Posted on 2014-01-15
26
770 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 33

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 33

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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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 33

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 33

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 33

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
 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

726 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