|
The following is my program snippet from DataTable to Excel, see if it helps
Public Sub DStoXls(ByVal Table As DataTable, ByVal DefFileName As String)
Dim MyOleDbCn As New System.Data.OleDb.OleDbConnection
Dim MyOleDbCmd As New System.Data.OleDb.OleDbCommand
Dim MyTable As New DataTable
Dim intRowsCnt, intColsCnt As Integer
Dim strSql As String, strFlName As String
Dim Fso As New System.Object
If Table Is Nothing Then
MessageBox.Show("Data not obtained, unable to export", "Export error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Exit Sub
End If
MyTable = Table
If MyTable.Rows.Count = 0 Then
MessageBox.Show("Data not obtained, unable to export", "Export error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Exit Sub
End If
Dim FileName As String
Dim SaveFileDialog As New SaveFileDialog
SaveFileDialog.InitialDirectory = My.Computer.FileSystem.SpecialDirectories.MyDocuments
SaveFileDialog.Title = "Save as"
SaveFileDialog.Filter = ".xls|*.xls"
SaveFileDialog.FileName = DefFileName
If (SaveFileDialog.ShowDialog() = System.Windows.Forms.DialogResult.OK) Then
FileName = SaveFileDialog.FileName
'TODO: Add the code to open the file here.
End If
If FileName = "" Then Exit Sub
strFlName = FileName
If Dir(FileName) <> "" Then
Kill(FileName)
End If
Try
Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
MyOleDbCn.ConnectionString = "Provider=Microsoft.Jet.OleDb.4.0;"&_
"Data Source="&strFlName&";"&_
"Extended ProPerties=""Excel 8.0;HDR=Yes;"""
MyOleDbCn.Open()
MyOleDbCmd.Connection = MyOleDbCn
MyOleDbCmd.CommandType = CommandType.Text
'Insert column headers in the first row
strSql = "CREATE TABLE "&DefFileName&"("
For intColsCnt = 0 To MyTable.Columns.Count-1
If intColsCnt <> MyTable.Columns.Count-1 Then
strSql = strSql&ChangeChar(MyTable.Columns(intColsCnt).Caption)&"text,"
Else
strSql = strSql&ChangeChar(MyTable.Columns(intColsCnt).Caption)&"text)"
End If
Next
MyOleDbCmd.CommandText = Replace(strSql, ".", "_")'When creating a table, "." field names are not allowed, so use "ˍ" to replace
MyOleDbCmd.ExecuteNonQuery()
'Insert rows
For intRowsCnt = 0 To MyTable.Rows.Count-1
strSql = "INSERT INTO "&DefFileName&" VALUES('"
For intColsCnt = 0 To MyTable.Columns.Count-1
If intColsCnt <> MyTable.Columns.Count-1 Then
strSql = strSql&ChangeChar(MyTable.Rows(intRowsCnt).Item(intColsCnt))&"','"
Else
strSql = strSql&ChangeChar(MyTable.Rows(intRowsCnt).Item(intColsCnt))&"')"
End If
Next
MyOleDbCmd.CommandText = strSql
MyOleDbCmd.ExecuteNonQuery()
Next
MessageBox.Show("Data has been successfully imported into EXCEL file"&strFlName, "Data Export", MessageBoxButtons.OK, MessageBoxIcon.Information)
Catch ErrCode As Exception
MsgBox("Error message:"&ErrCode.Message&vbCrLf&vbCrLf&_
"Event raised:"&ErrCode.TargetSite.ToString, MsgBoxStyle.OkOnly + MsgBoxStyle.Information, "Error source:"&ErrCode.Source)
Exit Sub
'Finally
MyOleDbCmd.Dispose()
MyOleDbCn.Close()
MyOleDbCn.Dispose()
End Try
End Sub |
|