[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 788
  • Last Modified:

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

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
dejandejanovic
Asked:
dejandejanovic
  • 13
  • 8
  • 5
2 Solutions
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
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
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
if you write out ds.Tables(0).Rows.Count to the screen, what do you get?
0
 
dejandejanovicAuthor Commented:
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
Technology Partners: 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!

 
dejandejanovicAuthor Commented:
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
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
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
 
dejandejanovicAuthor Commented:
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
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
please post the newly updated code
0
 
dejandejanovicAuthor Commented:
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
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
try changing

Dim ds As New DataSet

to

Dim ds As DataSet
0
 
CodeCruiserCommented:
Change line 9 from

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

to

Dim colName As String = column.ColumnName
0
 
dejandejanovicAuthor Commented:
I have change both lines...

Error: Sys.WebForms.PageRequestManagerServerErrorException: Value cannot be null.
Parameter name: dataSet
0
 
dejandejanovicAuthor Commented:
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
 
CodeCruiserCommented:
Can you show your code?
0
 
dejandejanovicAuthor Commented:
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
 
CodeCruiserCommented:
Which line produces the error?
0
 
dejandejanovicAuthor Commented:
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
 
CodeCruiserCommented:
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
 
dejandejanovicAuthor Commented:
Yes, correct.
0
 
CodeCruiserCommented:
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
 
dejandejanovicAuthor Commented:
Yes, correct again. This one = Dim ds As New Dataset.
0
 
CodeCruiserCommented:
WHICH LINE THROWS THE ERROR?
0
 
dejandejanovicAuthor Commented:
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
 
CodeCruiserCommented:
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
 
dejandejanovicAuthor Commented:
No errors, but excel is still without Header row.
0
 
CodeCruiserCommented:
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
 
dejandejanovicAuthor Commented:
Uf, finally. Yeap, now I have got a column row.
Great job CodeCruiser, and thanks for your pantience.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 13
  • 8
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now