Solved

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

Posted on 2014-01-15
26
763 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

831 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