Solved

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

Posted on 2014-01-15
26
760 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
Comment Utility
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
Comment Utility
if you write out ds.Tables(0).Rows.Count to the screen, what do you get?
0
 

Author Comment

by:dejandejanovic
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
please post the newly updated code
0
 

Author Comment

by:dejandejanovic
Comment Utility
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
Comment Utility
try changing

Dim ds As New DataSet

to

Dim ds As DataSet
0
 
LVL 83

Expert Comment

by:CodeCruiser
Comment Utility
Change line 9 from

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

to

Dim colName As String = column.ColumnName
0
 

Author Comment

by:dejandejanovic
Comment Utility
I have change both lines...

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

Author Comment

by:dejandejanovic
Comment Utility
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
Comment Utility
Can you show your code?
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:dejandejanovic
Comment Utility
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
Comment Utility
Which line produces the error?
0
 

Author Comment

by:dejandejanovic
Comment Utility
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
Comment Utility
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
Comment Utility
Yes, correct.
0
 
LVL 83

Expert Comment

by:CodeCruiser
Comment Utility
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
Comment Utility
Yes, correct again. This one = Dim ds As New Dataset.
0
 
LVL 83

Expert Comment

by:CodeCruiser
Comment Utility
WHICH LINE THROWS THE ERROR?
0
 

Author Comment

by:dejandejanovic
Comment Utility
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
Comment Utility
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
Comment Utility
No errors, but excel is still without Header row.
0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 400 total points
Comment Utility
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
Comment Utility
Uf, finally. Yeap, now I have got a column row.
Great job CodeCruiser, and thanks for your pantience.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
JavaScript error 1 33
Export data with an insert trigger to a linked server 20 19
getting id from database 5 24
Report Builder 9 25
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

743 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

17 Experts available now in Live!

Get 1:1 Help Now