VB.NET的写法跟VB大同小异,上月31号严重困扰我的DB连接字符串问题已经解决,三个参数都要写。另外,坚持不懈地试验很重要。 '**************************************************************** ' 名称:fncExporToExcel ' 功能:导出数据到EXCEL ' 用法:ExporToExcel(sql查询字符串, 保存路径) '**************************************************************** Private Function fncExportToExcel(ByVal inSQL As String, ByVal inFileName As String) Dim oleDbComd As New OleDb.OleDbCommand() Dim dataReader As OleDb.OleDbDataReader Dim ldbConn As New OleDb.OleDbConnection() Dim xlApp As Excel.Application Dim xlWorkBook As Excel.Workbook Dim xlWorkSheet As Excel.Worksheet Dim xlQueryTable As Excel.QueryTable On Error Resume Next ldbConn.ConnectionString = gdbConn.ConnectionString ldbConn.Open() xlApp = CreateObject("Excel.Application") xlWorkBook = Nothing xlWorkSheet = Nothing xlWorkBook = xlApp.Workbooks.Add() xlWorkSheet = xlApp.Worksheets("sheet1") xlApp.Visible = False xlQueryTable = xlWorkSheet.QueryTables.Add _ ("OLEDB;Provider=OraOLEDB.Oracle; _ Data Source= User ID= Password= With xlQueryTable .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .“refresh”OnFileOpen = False .BackgroundQuery = True .“refresh”Style = Excel.XlCellInsertionMode.xlInsertDeleteCells .SavePassword = True .SaveData = True .AdjustColumnWidth = True .“refresh”Period = 0 .PreserveColumnInfo = True End With xlQueryTable.FieldNames = True xlQueryTable.“refresh”() xlWorkBook.SaveAs(inFileName) xlApp.Quit() xlApp = Nothing xlWorkBook = Nothing xlWorkSheet = Nothing dataReader.Close() QueryTables.Add()参数说明: 1、Connection QueryTables的数据源。用OLE DB的话,是一个连接字符串。参数配置可以从URL链接中找到。 2、Destination 单元格定位。放置QueryTable的左上角坐标。必须是查询表所在的Worksheet。 3、Sql 可选参数。不过貌似我的程序里是必需的。就是SQL查询语句啦!不多说了。 |
|