title: vb.net generalized write of data to excel
body: <p>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.  </p>
  
  <p>The DataTable is derived from a FoxPro database.  (I don't know if that matters.)</p>
  
  <p>I invoke the table similar to as follows:</p>
  
  <pre><code>&lt;%
  return_value =  make_excel( sql_table, excel_filename)
  %&gt;
  </code></pre>
  
  <p>make_excel is defined as </p>
  
  <pre><code>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='" &amp; xls_fn &amp; "';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
  </code></pre>
  
  <p>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?</p>