vba Visual Basic - 将数据表写入 Excel
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17603410/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Visual Basic - Writing a Datatable to Excel
提问by Nick
I am working on a console application that will grab all of the .sql files in a specific folder, use those to query a database, and then export the results of each to an excel file. I've gotten everything down up to the datatable. I am asking for any advice or guidance on getting that datatable to excel. Any help would be appreciated. Thank you.
我正在开发一个控制台应用程序，它将获取特定文件夹中的所有 .sql 文件，使用这些文件查询数据库，然后将每个文件的结果导出到一个 excel 文件。我已经把所有的东西都放到了数据表中。我正在寻求有关使该数据表脱颖而出的任何建议或指导。任何帮助，将不胜感激。谢谢你。
Imports System.Data.OleDb
Module SqlExport
Sub Main()
Dim SQLString As String
Dim SQLDirectory As New IO.DirectoryInfo("\Datastore\scripts\SQL")
Dim SQLQueries As IO.FileInfo() = SQLDirectory.GetFiles("*.sql")
Dim CurrentQuery As IO.FileInfo
Dim dt As New DataTable
For Each CurrentQuery In SQLQueries
SQLString = System.IO.File.ReadAllText(CurrentQuery.FullName)
Using connection As New OleDb.OleDbConnection("provider=advantage ole db provider;data source=\database\dba;advantage server type=ads_remote_server;trimtrailingspaces=true;")
Dim command As New OleDbCommand(SQLString, connection)
Dim da As New OleDb.OleDbDataAdapter(command)
da.Fill(dt)
End Using
Next
End Sub
End Module
回答by Nick
Thank you all for this. I ended up using the following to get the results I was looking for.
谢谢大家。我最终使用以下内容来获得我正在寻找的结果。
Dim da As New OleDb.OleDbDataAdapter(command)
da.Fill(ds)
Dim Excel As Object = CreateObject("Excel.Application")
With Excel
.SheetsInNewWorkbook = 1
.Workbooks.Add()
.Worksheets(1).Select()
.Range("A1").select()
Dim i As Integer = 1
For col = 0 To ds.Tables(0).Columns.Count - 1
If Asc(ds.Tables(0).Columns(col).ColumnName) = 36 Then
.Columns(i).NumberFormat = "General"
.Cells(1, i).value = ds.Tables(0).Columns(col).ColumnName.Substring(1)
Else
.Columns(i).NumberFormat = "@"
.Cells(1, i).value = ds.Tables(0).Columns(col).ColumnName
End If
i += 1
Next
Dim j As Integer = 1
For col = 0 To ds.Tables(0).Columns.Count - 1
i = 2
For row = 0 To ds.Tables(0).Rows.Count - 1
.Cells(i, j).Value = ds.Tables(0).Rows(row).ItemArray(col).ToString
i += 1
Next
j += 1
Next
Dim fileName As String = Path.GetFileNameWithoutExtension(CurrentQuery.Name)
.ActiveWorkbook.SaveAs(Filename:="\reports\" & value & "\" & fileName & DateTime.Now.ToString("yyyy-MM-dd") & ".xlsx", _
WriteResPassword:="123456", _
ReadOnlyRecommended:=False, _
CreateBackup:=False)
.Workbooks.Close()
.Quit()
End With
回答by matzone
In Manual way ..
在手动方式..
First you have to create your XLS file and make colums header like fields in your database
首先，您必须创建您的 XLS 文件并使列标题类似于数据库中的字段
Assumed that is "TRANSFER.XLS" ..
假设是“TRANSFER.XLS”..
Dim sFN = "Transfer.XLS"
Dim cnXLS As OleDbConnection
Dim cmdXLS As OleDbCommand
Dim dr as DataRow
cnXLS = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;" & _
"data source=" & sFN & ";Extended Properties=Excel 8.0;")
cnXLS.Open()
For x as Integer = 0 to dt.Table(0).Rows.Count -1 '---> this is your dt
dr = dt.Table(0).Rows(x)
cmdXLS = New OleDbCommand("INSERT INTO [Sheet1$] ( name, .... ) VALUES ( dr.item("name", ....)", cnXLS)
cmdXLS.ExecuteNonQuery
Next
cnXLS.close()
