用DATETABLE往excel里导数据

Question

    'Sql导出Excel
    Private Sub Export2Excel()
        Try
            Dim o_intRow As Integer, o_intCol As Integer
            Dim o_strPath As String, o_strConnString As String
            Dim o_cnnConnect As OleDbConnection, o_cmdComm As OleDbCommand
            Dim o_strCreatedField As String = "", o_strParaField As String = ""
            Dim o_strPara As String, o_intItem As Integer            o_strPath = sfdExcel.FileName
            If o_strPath.Trim.Length > 0 Then
                Me.Cursor = Cursors.WaitCursor
                tsBar.Enabled = False
                If My.Computer.FileSystem.FileExists(o_strPath) Then My.Computer.FileSystem.DeleteFile(o_strPath)
                With dgvJiaoDai
                    For o_intItem = 0 To CT_LastColumn
                        If o_intItem = 0 Then
                            o_strCreatedField = .Columns(o_intItem).HeaderText.Trim & " varchar"
                            o_strParaField = "@" & .Columns(o_intItem).HeaderText.Trim
                        Else
                            o_strCreatedField = o_strCreatedField & ", " & .Columns(o_intItem).HeaderText.Trim & " varchar"
                            o_strParaField = o_strParaField & ", @" & .Columns(o_intItem).HeaderText.Trim
                        End If
                    Next
                End With                o_strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & o_strPath & ";Extended Properties=Excel 8.0;"
                o_cnnConnect = New OleDbConnection(o_strConnString)
                o_cmdComm = New OleDbCommand()
                With o_cmdComm
                    .Connection = o_cnnConnect
                    .Connection.Open()
                    .CommandText = "CREATE TABLE Sheet1( " & o_strCreatedField & ")"    '建立表结构
                    .ExecuteNonQuery()
                    .CommandText = "INSERT INTO Sheet1 VALUES (" & o_strParaField & ")" '建立插入动作的Command
                    .Parameters.Clear()
                    For o_intItem = 0 To CT_LastColumn                                             '建立参数
                        o_strPara = "@" & dgvJiaoDai.Columns(o_intItem).HeaderText.Trim
                        .Parameters.Add(New OleDbParameter(o_strPara, OleDbType.VarChar))
                    Next
                    '遍历DataSet将数据插入新建的Excel文件中
                    For o_intRow = 0 To m_dsJiaoDai.Tables("Detail").Rows.Count - 1
                        System.Windows.Forms.Application.DoEvents()
                        For o_intCol = 0 To CT_LastColumn
                            .Parameters.Item(o_intCol).Value = m_dsJiaoDai.Tables("Detail").Rows(o_intRow)(o_intCol).ToString() & ""
                        Next
                        .ExecuteNonQuery()
                    Next                    .Connection.Close()
                End With
                tsBar.Enabled = True
                Me.Cursor = Cursors.Default
                MsgBox("引出Excel成功...", MsgBoxStyle.OkOnly, Me.Text)
            Else
            End If
        Catch ex As Exception
            WriteLog(Me.Name & ":Export2Excel", Err.Number, Err.Description, ex.ToString)
        End Try
    End Sub