Export DataTable to Excel with variable number of fields

Expand Post »

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 10:51 am.

Oxiegen

Reputation Points: 87

Solved Threads: 128

Practically a Master Poster

Offline

652 posts
since Jun 2006

Permalink

Dec 5th, 2007

Re: Export DataTable to Excel with variable number of fields

Problem solved.
I simply added a funktion that returns the string containing the values.

By calling the funktion like this:

VB.NET Syntax (Toggle Plain Text)
values = DataValues(row, dt.Columns.Count - 1)
values = DataValues(row, dt.Columns.Count - 1)

The information is returned like this:

VB.NET Syntax (Toggle Plain Text)
   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
   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

Oxiegen

Reputation Points: 87

Solved Threads: 128

Practically a Master Poster

Offline

652 posts
since Jun 2006

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in VB.NET Forum Timeline: Printing HTML from VB.Net

Next Thread in VB.NET Forum Timeline: Code generator that produces SQL Stored Procedures

DaniWeb Message

Cancel Changes

User Name
Password