How to export the results of a stored procedure directly to a MS Excel file?

I found a way to extract the contents of a MS Sql table directly (faster) to excel. But I do not know how to do this with a stored procedure that requires parameters. Is it possible to extract, directly, to an Excel File the results of a stored procedure? I know how to do it indirectly (using a data table) but it is too slow. Thank you very much. PS: This is the method I was using to do some tests. It works with a table, but what I need is to extract the result of a stored procedure: Private Sub SqlToExcelTest2(ByVal excelFilePath As String, _ ByVal nonExistingSheetName As String, _ ByVal sqlServer As String, _ ByVal sqlDatabase As String, _ ByVal sqlUserName As String, _ ByVal sqlPassword As String, _ ByVal sqlTable As String) Const excelConnStrTemplate As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=2"";" Dim connStr As String = String.Format(excelConnStrTemplate, _ excelFilePath) Const adoQueryTemplate As String = "SELECT * INTO {0} FROM [odbc;Driver={{SQL Server}};" & _ "Server={1};Database={2};UID={3};PWD={4}].[{5}] " Dim query As String = String.Format(adoQueryTemplate, _ nonExistingSheetName, _ sqlServer, _ sqlDatabase, _ sqlUserName, _ sqlPassword, _ sqlTable) Using oleConn As New OleDb.OleDbConnection(connStr), oleCmd As New OleDb.OleDbCommand(query, oleConn) oleConn.Open() oleCmd.ExecuteNonQuery() oleConn.Close() End Using End Sub
If you only do this occasionally for testing, can you use the Management Studio functionality for exporting to Excel? Set the query output to go to Excel, then run the sproc in a query window.

以上就是How to export the results of a stored procedure directly to a MS Excel file?的详细内容,更多请关注web前端其它相关文章!

赞(0) 打赏
未经允许不得转载:web前端首页 » excel

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

前端开发相关广告投放 更专业 更精准