Imports System.Runtime.InteropServices
Imports Excel

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        'Connessione server        
        Const stCon As String = "OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Data Source=SERVERName;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\Report3.xls")
        
        'apro il file
        With xlApp
            .Visible = True
            .UserControl = True
        End With
        
        'pulisco
        ptTable = Nothing
        ptCache = Nothing
        xlWSheet = Nothing
        xlWBook = Nothing
        xlApp = Nothing
End sub