Protected Sub Button5_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button5.Click
        'Connessione server        
        Const stCon As String = "OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Data Source=xxx;Initial Catalog=Northwind"
        'query
        Const stSQL As String = "SELECT * FROM ORDERS"

        'creo istanza excel 
        Dim xlApp As Excel.Application

        Try

            xlApp = Marshal.GetActiveObject("Excel.Application")
        Catch ex As COMException

            xlApp = New Excel.Application
        End Try

        'variabili cartella,foglio,range
        Dim xlWBook As Excel.Workbook = xlApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet)
        Dim xlWSheet As Excel.Worksheet = CType(xlWBook.Worksheets(1), Excel.Worksheet)
        Dim xlRange As Excel.Range = CType(xlWSheet, Excel.Worksheet).Range("B2")

        'memoria pivot
        Dim ptCache As Excel.PivotCache = xlWBook.PivotCaches.Add( _
        SourceType:=Excel.XlPivotTableSourceType.xlExternal)

        'comando per pivot      
        With ptCache
            .Connection = stCon
            .CommandText = stSQL
            .CommandType = Excel.XlCmdType.xlCmdSql
        End With

        'set pivot
        Dim ptTable As Excel.PivotTable = _
        xlWSheet.PivotTables.Add( _
        PivotCache:=ptCache, _
        TableDestination:=xlRange, _
        TableName:="PT_Report")


        'creo la pivot (riga,colonna,valore,...ecc)
        With ptTable
            .ManualUpdate = True

            .PivotFields("ShipCity").Orientation = Excel.XlPivotFieldOrientation.xlRowField

            .PivotFields("ShipCountry").Orientation = Excel.XlPivotFieldOrientation.xlColumnField

            .PivotFields("Freight").orientation = Excel.XlPivotFieldOrientation.xlDataField

            .Format(Excel.XlPivotFormatType.xlTable3)
            .ManualUpdate = False
        End With

        'salvo il file xls
        xlWBook.SaveAs("c:\Tmp\Report.xls")

        'apro il file
        With xlApp
            .Visible = True
            .UserControl = True
        End With

        'pulisco
        ptTable = Nothing
        ptCache = Nothing
        xlWSheet = Nothing
        xlWBook = Nothing
        xlApp = Nothing

        ResponseFile("c:\Tmp\Report.xls")
    End Sub
    Private Sub ResponseFile(ByVal filename As String)
        Response.Clear()

        Dim cd = New ContentDisposition()
        cd.FileName = "Report.xls"
        ' questo dovrebbe aprire il programma automaticamente se ci riesce, ma forse con excel non funziona, per i PDF è così che si fa per farglieli aprire nel browser, però almeno explorer se ne accorge e ti fa un bel popup grande chiedentoti conferma se vuoi aprirlo con excel
        cd.Inline = True
        Response.AddHeader("Content-Disposition", cd.ToString())

        Response.ContentType = "application/excel"

        Response.WriteFile(filename)

        Response.[End]()
    End Sub
End Class