RSS Forums RSS

Export DataTable to Excel with variable number of fields

Please support our VB.NET advertiser: DiscountASP.NET – 3 Months Free on VB.NET Web Hosting
Thread Solved
Reply
Posts: 45
Reputation: Oxiegen is an unknown quantity at this point 
Solved Threads: 3
Oxiegen Oxiegen is offline Offline
Light Poster

Help Export DataTable to Excel with variable number of fields

  #1  
Dec 4th, 2007
Hi!
Sorry about my bad grammer and/or spelling. English is not my native language.

I don't know if this already have been asked and answered, however...

I'm currently developing a program in which it will be possible to select table fields from a database and set various criteria using dropdown-lists. This will then dynamically generate an SQL-query string.
I then convert the datareader I use for this query into a datatable.

The above part works great.

Now to the problem.
I have other functions that perform an export to excel, but those has fixed number of fields and it's then easy, for example, to use VALUES ('" & row.Item(0) & "','" & row.Item(2) & "','" & row.Item(3) & "'..... to retrieve and export the information.

But because this latest function has fields that varies in number, the above code won't exactly work.
I need a way to retrieve the information into a single string where:
str &= "'" & row.Item(<dynamic number 0 to field.count - 1>) & "'," .

And here's the crux. All this has to be performed inside a single For Each row AS DataRow In DataTable.Rows because the data is exported into excel row by row.

Any ideas?
Last edited by Oxiegen : Dec 4th, 2007 at 9:51 am.
AddThis Social Bookmark Button
Reply With Quote  
Posts: 45
Reputation: Oxiegen is an unknown quantity at this point 
Solved Threads: 3
Oxiegen Oxiegen is offline Offline
Light Poster

Solution Re: Export DataTable to Excel with variable number of fields

  #2  
Dec 5th, 2007
Problem solved.
I simply added a funktion that returns the string containing the values.

By calling the funktion like this:
values = DataValues(row, dt.Columns.Count - 1)
The information is returned like this:
   Private Function DataValues(ByVal DRow As DataRow, ByVal Max As Integer) As String
        Dim retVal As String

        retVal = "'" & DRow.Item(0) & "',"
        For i As Integer = 1 To Max - 1
            retVal &= "'" & DRow.Item(i) & "',"
        Next
        retVal &= "'" & DRow.Item(Max) & "'"

        Return retVal
    End Function
Reply With Quote  
Reply

Only community members can participate in forum threads. You must register or log in to contribute.



Other Threads in the VB.NET Forum
Views: 6142 | Replies: 1 | Currently Viewing: 1 (0 members and 1 guests)

 

Thread Tools Display Modes
Forums | Blogs | Tutorials | Code Snippets | Whitepapers | RSS Feeds | Advertising
All times are GMT -4. The time now is 10:01 pm.
Newsletter Archive - Sitemap - Privacy Statement - Acceptable Use Policy - Contact Us
Forum system based on vBulletin Copyright ©2000 - 2009, Jelsoft Enterprises Ltd.
©2003 - 2008 DaniWeb® LLC