, " OLEDB Provider=SQLOLEDB.1 Integrated Security=SSPI Persist Security Info=True Data Source=. With ActiveSheet.PivotTables( " PivotTable1 ").PivotFields( " ProductModelID ")Įxcel.Application _app = new Excel.Application() Įxcel.Workbook _book = _() Įxcel.WorkbookConnection _conn = _( " Test ", " PivotTable Test " With ActiveSheet.PivotTables( " PivotTable1 ").PivotFields( " Size ") " PivotTable1 ").PivotFields( " ListPrice "), " Sum of ListPrice ", xlSum
With ActiveSheet.PivotTables( " PivotTable1 ").PivotFields( " ProductID ")ĪctiveSheet.PivotTables( " PivotTable1 ").AddDataField ActiveSheet.PivotTables( _ TableName:= " PivotTable1 ", DefaultVersion:=xlPivotTableVersion14 XlPivotTableVersion14).CreatePivotTable TableDestination:= " Sheet1!R1C1 ", _ " ""AdventureWorks2012"".""Production"".""Product"" "), 3Ī(SourceType:=xlExternal, SourceData:= _ĪctiveWorkbook.Connections( ". , " tial Catalog=AdventureWorks2012 "), Array( _ " =True Packet Size=4096 Workstation ID=WINDOWS8NB Use Encryption for Data=False Tag with column collation when possible=False Ini " _ Use Procedure for Prepare=1 Auto Translate " _ " OLEDB Provider=SQLOLEDB.1 Integrated Security=SSPI Persist Security Info=True Data Source=. AdventureWorks2012 Product ", "", Array _ Workbooks( " 123.xlsx ").Connections.Add ".
Right can change the chart type, such as pie charts, diagrams cone:ġ2, I intend to PivotTable functions integrated into the system, needed to generate the code.īy tracking the macro code, the code can be easily used to generate: Select the row dimension product model and product, select the column dimension size, the final price statistics.ĭouble-click the cell you can see the price breakdown, the equivalent of drill-through reports:Ĩ, in addition to tables show, also supports a variety PivotTable chart.Ĭlick in in the data:ĩ, select the database connection you just created:ġ1, just as similar to dragging a field, a chart is generated, it is not very strong? Click to complete the wizard:ħ, on the right drag the fields that we want to analyze: Of course, you can write your own SQL statements to filter data:Ĥ. I downloaded the official SQL Server 2012 sample database from the CodePlex: AdventureWorks2012ĬodePlex site also revised a large number of Metro-style Jģ, currently only a test, select a table. PivotTable is a completely self-reporting, very powerful, for extraction, data analysis is very convenient.ġ, in the, choose to extract data from SQL Server: Calculating data related performed with the arrangement of the PivotTable. PivotTable is an interactive table that certain calculations, such as counting and summation.
The original system is written using VBA, difficult to maintain, and does not support new operating systems such as Windows 7,64-bit architecture, I am going to be rewritten with VSTO. Since the company before the system has a similar query using Excel PivotTables done, so I intend to learn from it. Recent house doing a general query report, facilitate human resource analysis, statistical data. : Original Excel pivot tables and PowerPivot VSTO study notes (xiv)