数据透视表(pivot table)的编程

   在上一个的项目中，由于之前客户的报表使用的是pivot table，与我们一贯使用的水晶报表不一致，所以研究了一下，受益不少。

   首先介绍一下pivot table，“使用数据透视表可以汇总、分析、浏览和提供摘要数据。使用数据透视图可以在数据透视表中可视化此摘要数据，并且可以方便地查看比较、模式和趋势。数据透视表和数据透视图都能使您做出有关企业中关键数据的决策。以下部分提供了数据透视表和数据透视图的概述。”，以上是Office Online的概述，通俗的说可以看作是一个微型的数据库，原始数据隐式存储在excel文档之中，通过用户的设置显示，类似与于sqlserver的视图功能。
   
   在进行pivot table的编程之前我们需要安装Microsoft OFFICE、MS QUERY(一个excel专用的外部数据导入工具)，这个工具是OFFICE的工具组件之一，默认安装模式是不会安装这个工具的，同时我们在工程项目中需要引用excel.dll对象。MS Query貌似不支持直接连接SQLServer，我们仍然需要建立一个ODBC连接在你的电脑上，到这里准备工作宣告完成。

   以下是代码：
   '--------------------------------------------------------------------------
   '--Function:   CreatePivotExcel
   '--Description:Create a pivot table in Excel 
   '--Input:        cnnsr                                  ODBC Connection
   '--                 rptPath                               generate excel path
   '--                 SP                                     Stored Procedure name     
   '--Output:      Pivot Table Excel
   '---------------------------------------------------------------------------
    Protected Sub CreatePivotExcel(ByVal cnnsr As String, ByVal rptPath As String, ByVal SP As String)
        Dim excel As Excel.Application
        Dim workbook As Excel.Workbook
        Dim sheet As Excel.Worksheet
        Dim PivotCache As Excel.PivotCache
        Dim PivotTable As Excel.PivotTable
       
       '----   configuration property  ----
        Dim dt As DataTable
        Dim obj As New Reports

        excel = New Excel.Application

       Try
            workbook = excel.Workbooks.Add(True)
            sheet = workbook.ActiveSheet

            PivotCache = workbook.PivotCaches.Add(SourceType:=2)
            PivotCache.Connection = cnnsr
            PivotCache.CommandType = Global.Excel.XlCmdType.xlCmdSql

            PivotCache.CommandText = SP  
            PivotTable = PivotCache.CreatePivotTable(sheet.Range("A4"), "sheet1", 2)

            
            PivotTable.PivotFields(field_name1).Orientation = Global.Excel.XlPivotFieldOrientation.xlDataField
            PivotTable.PivotFields(field_name2).Orientation = Global.Excel.XlPivotFieldOrientation.xlRowField
            PivotTable.PivotFields(field_name3).Orientation = Global.Excel.XlPivotFieldOrientation.xlColumnField


            workbook.Saved = True
            excel.Visible = True

            excel.ActiveWorkbook.SaveCopyAs(rptPath)
            excel.Quit()

        Catch ex As Exception
            workbook.Saved = True
            excel.Quit()
            Response.Write("Genarate Report error...")
            Response.End()
        Finally

        End Try

    End Sub