VB.NET

Export DataTable to Excel with variable number of fields

Please support our VB.NET advertiser: Intel Parallel Studio Home

Thread Solved

•

Join Date: Jun 2006

Posts: 45

Reputation: Oxiegen is an unknown quantity at this point

Solved Threads: 3

Oxiegen

Offline

Light Poster

Export DataTable to Excel with variable number of fields

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

•

Join Date: Jun 2006

Posts: 45

Reputation: Oxiegen is an unknown quantity at this point

Solved Threads: 3

Oxiegen

Offline

Light Poster

Re: Export DataTable to Excel with variable number of fields

Dec 5th, 2007

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

By calling the funktion like this:

 Help with Code Tags 
 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:

 Help with Code Tags 
 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