Question How to export datatable to Excel for Windows App and using XML?

[superservo15]

I have a question that google hasn't helped me figure out yet.

I have an old VB.net windows app, framework is 2.0. I am needing to take a datatable (and subsequently a data set of tables) and export this data to a spreadsheet.

One catch, can't use the interop library. Is there a way to do it without the Interop? Can I use XML perhaps?

The only way I done this before was for a web app and just set the response type...

Thanks and any help is much appreciated... I can't figure this one out.

 

[MattP]

If you can't use Interop then ADO.NET is going to be your best bet.

How To Use ADO.NET to Retrieve and Modify Records in an Excel Workbook With Visual Basic .NET

 

[Tom]

As long as your keeping a full table structure you dont need to automate (interop) Excel file.

The following example will export table structures to an excel file without the need to automate Excel or make any reference to it. However you do lose some formatting options such as being able to resize columns, bold the header etc... Overall though I have a few subs & functions defined and its easy now to pass any datatable or entire dataset right to these functions for export.

Add the below subs/functions to a module then just past your datatable to the ExportDataTableToExcel sub

ExportDataTableToExcel(dsMyDataset.Tables("TableName"), strYourFileName&PathHere)

Public Sub ExportDataTableToExcel(ByVal dtSource As DataTable, ByVal strFile As String, Optional ByVal blnIncludeHeaderRow As Boolean = True)

        Dim strDbCon As String = ""

        If IO.File.Exists(strFile) Then IO.File.Delete(strFile)

        'Connection string to Excel file
        strDbCon = CreateExcelConnectionString(strFile, blnIncludeHeaderRow)

        Using con As New OleDbConnection(strDbCon)

            Dim bldrSql As New OleDbCommandBuilder
            Dim cmdCreateTable As New OleDbCommand
            Dim daXls As OleDbDataAdapter
            Dim dtTarget As New DataTable
            Dim strCreateTable As String = ""

            'Generate Create Table statement
            strCreateTable = CreateExcelTable(dtSource)

            'Create file and table structure
            con.Open()
            With cmdCreateTable
                .Connection = con
                .CommandText = strCreateTable
                .ExecuteNonQuery()
            End With
            con.Close()

            'Get table structure to generate insert statement
            daXls = New OleDbDataAdapter("Select * From [" & dtSource.TableName & "]", con)
            daXls.Fill(dtTarget)

            'Create Insert command
            bldrSql.DataAdapter = daXls
            daXls.InsertCommand = bldrSql.GetInsertCommand

            'Loop through each row and add to target dataset
            For Each rowSource As DataRow In dtSource.Rows
                Dim rowTarget As DataRow = Nothing

                rowTarget = dtTarget.NewRow

                'Loop thru each column
                For intIndex = 0 To dtSource.Columns.Count - 1
                    rowTarget.Item(intIndex) = rowSource.Item(intIndex)
                Next intIndex

                dtTarget.Rows.Add(rowTarget)
            Next rowSource

            'Export data to file
            daXls.Update(dtTarget)
        End Using 'con

    End Sub

Private Function CreateExcelTable(ByVal dtCreate As DataTable) As String

        Dim bldrSql As New StringBuilder
        Dim intTotalCols As Integer = 0

        intTotalCols = dtCreate.Columns.Count

        'Begin Create Table statement
        bldrSql.Append("Create Table [")
        bldrSql.Append(dtCreate.TableName)
        bldrSql.Append("] (")

        'Loop thru each of the source table's columns
        For Each col As DataColumn In dtCreate.Columns

            bldrSql.Append(col.ColumnName)

            'Format Excel column datatypes to match source table
            Select Case col.DataType.Name
                Case "SByte", "Byte", "Int16", "Int32", "Int64", _
                     "Decimal", "Double", "Single"
                    bldrSql.Append(" Number, ")
                Case "Boolean"
                    bldrSql.Append(" Bit, ")
                Case "Char", "String"
                    bldrSql.Append(" Memo, ")
                Case "DateTime"
                    bldrSql.Append(" DateTime, ")
                Case Else
                    bldrSql.Append(" Text, ")
            End Select 'col.DataType.Name
        Next col

        'Remove extra comma from end
        bldrSql.Remove(bldrSql.Length - 2, 2)
        bldrSql.Append(")")

        Return bldrSql.ToString

    End Function

Private Function CreateExcelConnectionString(ByVal strFile As String, Optional ByVal blnHeader As Boolean = True) As String

        Dim bldrCon As New OleDb.OleDbConnectionStringBuilder
        bldrCon("Provider") = "Microsoft.Jet.OLEDB.4.0"
        bldrCon("Data Source") = strFile

        'blnHeader specifies whether a header row is included or not
        'IMEX=1; - Tells the driver to read everything as intermixed text

        If blnHeader = True Then
            bldrCon("Extended Properties") = "Excel 8.0;HDR=YES"
        Else
            bldrCon("Extended Properties") = "Excel 8.0;HDR=NO"
        End If

        Return bldrCon.ConnectionString

    End Function

 

[superservo15]

Thanks guys. The first post lead me to a few new ideas and then I found a thread that had a similiar solution to the second post, which is how I did it.

Thanks again Such a big help