执行SQL文，生成EXCEL

''' <summary> ''' SQL文執行結果のEXCEL出力 ''' </summary> ''' <param name="connString">OLEDB接続文字列</param> ''' <param name="sqlString">SQL文</param> ''' <param name="savePath">出力ファイルパス</param> ''' <remarks></remarks> Public Shared Sub ExecSqlToExcel(ByVal connString As String, ByVal sqlString As String, _ ByVal savePath As String) Dim mExcelApplication As Excel.Application = Nothing Dim mWorkbook As Excel.Workbook = Nothing Dim mWorkSheet As Excel.Worksheet = Nothing Dim mQueryTable As Excel.QueryTable = Nothing Try mExcelApplication = New Excel.Application() mWorkbook = mExcelApplication.Workbooks.Add mWorkSheet = mWorkbook.Sheets(1) mQueryTable = mWorkSheet.QueryTables.Add(connString, mWorkSheet.Range("A1"), sqlString) mQueryTable.FieldNames = True mExcelApplication.DisplayAlerts = False mQueryTable.Refresh() mWorkbook.SaveAs(savePath) Catch ex As Exception Throw ex Finally If Not mWorkSheet Is Nothing Then mQueryTable = Nothing mWorkbook.Close() mWorkbook = Nothing mExcelApplication = Nothing End If End Try End Sub

 

说明：

connString="OLEDB;Provider=SQLOLEDB;server=xxx;uid=xxx;pwd=xxx;Initial Catalog=xxx"

savePath应该是完整的路径（含文件名）

导出的excel没有格式要求，如果有必要，可以通过vba对该对象进行格式操作

第一行是select的字段名，如果想要汉字形式，则给每个字段加上汉字别名即可