Export DataTable to Excel with variable number of fields
 

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?

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:

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