vb.net generalized write of data to excel

Question

I need to create an excel file from scratch in VB.NET given a DataTable from asp.net. I can do this for a specific file, but I don't know how to do it for a general database. That is, where I use the "CREATE TABLE ..." I don't know how to tell it what types to use for the data in the table.

The DataTable is derived from a FoxPro database. (I don't know if that matters.)

I invoke the table similar to as follows:

<%
return_value =  make_excel( sql_table, excel_filename)
%>

make_excel is defined as

Option Explicit On 
'Option Strict On

Imports System
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.Page
Imports System.IO
Imports Microsoft.VisualBasic
Imports System.Diagnostics
Imports System.Data
Imports System.Data.OleDb


Public Class clsCommon
    Inherits Page

    ' buncha other stuff defined in here.

    Public Shared Function make_excel(ByVal sqlTable As DataTable, ByVal xls_fn As String) As Boolean
        Dim conn As System.Data.OleDb.OleDbConnection
        Dim ds As System.Data.DataSet
        Dim cmd As New System.Data.OleDb.OleDbCommand()

        conn = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & xls_fn & "';Extended Properties=Excel 8.0;")
        conn.Open()

        cmd.Connection = conn
        cmd.CommandText = "CREATE TABLE MyTable ( Admin  char(20), first_name char(20));"
        cmd.ExecuteNonQuery()

        cmd.CommandText = "INSERT INTO MyTable ( Admin, first_name ) VALUES ('true', 'Bob')"
        cmd.ExecuteNonQuery()

        conn.Close()

        Return True
    End Function

End Class

What I need to be able to do is run through the values in sqlTable above, check their type and then build the sql to write them. Pointers?

One thing I've noticed is that the thing doesn't like the field name "password" . — elbillaf, Mar 16 '11 at 12:37

score 1 · Answer 1 · answered Mar 14 '11 at 22:37

1

Have you looked into the copyfromrecordset function? You'll need to do a bit of work and its a bit of a change of approach but it might be something you can look into. An MS article is available here (Sorry, the article is based around VBA, but it should help as a guide).

answered Mar 14 '11 at 22:37

Jon Egerton

40,401
11
97
129

Thanks for note. I started out trying to use ADO, as I've done similar before with VBA; however, I couldn't figure out what IMPORTS to use to get the methods and so have gone down this other route. That particular method is unfamiliar to me, though - so thanks for that. – elbillaf Mar 15 '11 at 12:27

score 0 · Accepted Answer · answered Mar 17 '11 at 20:29

I have a solution to the problem. I'm not happy about it as a general solution, but it works well enough for the cases I'm currently dealing with.

In this solution, I create a template of the excel file that has the column headings I want to use. When I do the select in the forward code, I change the name of the fields as appropriate (or drop whatever fields I don't want).

Public Shared Function TestXL(ByVal resp As HttpResponse, ByVal sqlTable As DataTable, ByVal xls_template_fn As String, ByVal xls_fn As String) As Boolean Dim conn As System.Data.OleDb.OleDbConnection Dim ds As System.Data.DataSet Dim cmd As New System.Data.OleDb.OleDbCommand()

Dim r As DataRow
Dim c As DataColumn
Dim i As Integer
Dim sql As String
dim str as string

If File.Exists(xls_template_fn) Then
    try 
      If File.Exists(xls_fn) Then
        File.Delete(xls_fn)
      Else
        File.Copy(xls_template_fn, xls_fn)
      End If
    catch ex1 as Exception
      File.Copy(xls_template_fn, xls_fn)
    End Try
Else
    resp.Write("Unable to locate template file: " & xls_template_fn)
    Return False
End If

conn = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & xls_fn & "';Extended Properties=Excel 8.0;")
conn.Open()


cmd.Connection = conn
cmd.CommandText = sql


For Each r In sqlTable.Rows
    sql = "INSERT INTO MyTable ("
    For i = 0 To sqlTable.Columns.Count - 1
        sql = sql & " " & sqlTable.Columns(i).ColumnName & ","
    Next
    sql = Left(sql, sql.Length - 1) & " ) VALUES ( "
    For i = 0 To sqlTable.Columns.Count - 1
    str = r(i).toString()
        dim str2 as string = str.replace("'", "''")
        sql = sql & " '" & str2 & "',"
    Next
    sql = Left(sql, sql.Length - 1) & " );"


    'resp.Write(sql & "<br/>")

    cmd.CommandText = sql
    cmd.ExecuteNonQuery()
Next

conn.Close()

Return True

End Function

Note the name of the the worksheet is the name of the table for the oledb call.

There's a link to other ways of doing this:

http://blogs.msdn.com/b/erikaehrli/archive/2009/01/30/how-to-export-data-to-excel-from-an-asp-net-application-avoid-the-file-format-differ-prompt.aspx

If I have to revisit this problem I'll probably start there.

I notice this problem was already addressed - not sure how I missed it - in another Stackoverflow thread at: http://stackoverflow.com/questions/150339/generating-an-excel-file-in-asp-net — elbillaf, Mar 17 '11 at 20:55

vb.net generalized write of data to excel

2 Answers2