June 7, 2012
Posted by on
Extending the previous discussion on PivotTables, in this blog post I will show how you can create a PivotTable and PivotChart using Visual Studio Tools for Office (VSTO). After populating data into excel worksheet, generating PivotTables is quite a common task for VSTO developers. This can be easily broken down into below steps:
a) Create Table Range where Pivot Table would be created
b) Create Chart Range where Pivot Chart would be placed
c) Determine Data Range that would act as input for Pivot (generally you might want to create many pivot tables out of this range)
d) Create a PivotCache object using Data Range as Input
e) Determine Data Range that would act as input for a pivot table
f) Generate Pivot Table and set the orientation of Pivot fields
g) Create chart with using Pivot Table as input
Let’s have a quick look at the C# source code below:
//Step A, B
Worksheet worksheet = Globals.Factory.GetVstoObject(
var chartRange = worksheet.Range["G3", "J15"];
var pivotTableRange = worksheet.Range["G20", "J30"];
var listObject = FindListObject("demoDataList");
var end = listObject.DataBodyRange.get_End(Microsoft.Office.Interop.Excel.XlDirection.xlDown).Row;
var dataRangeForPivot = worksheet.Range["A1", "W" + end.ToString()];
Excel.PivotCache pivotCache = this.Application.ActiveWorkbook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlDatabase, dataRangeForPivot);
Microsoft.Office.Interop.Excel.Range dataRangeForPivotTable = worksheet.Range["H35", "H" + end.ToString()];
Microsoft.Office.Interop.Excel.PivotTable pivotTable = pivotCache.CreatePivotTable(dataRangeForPivotTable, @"DTable", dataRange, Type.Missing);
Microsoft.Office.Interop.Excel.PivotField demoField = ((Microsoft.Office.Interop.Excel.PivotField)pivotTable.PivotFields(8));
demoField.Function = Microsoft.Office.Interop.Excel.XlConsolidationFunction.xlCount;
Chart chart = worksheet.Controls.AddChart(chartRange, "DChart");
chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlColumnClustered;
chart.ChartTitle.Text = "Demo Analysis";
Confusing? I hope not 🙂
June 2, 2012
Posted by on
OK, I am not a data guy. Or let me say I am not an Excel (MS-EXCEL) guy. I have always believed that if you are good at PowerPoint (along with Visio) you probably are an architect and if you are good at Excel you are most likely to be a manager 🙂 . But not knowing Excel well, has always given me a feeling of – “something is missing”. After all, it’s the same data, but the way few people present it, they just make it look so good. While it takes time to develop those skills, understanding Pivot and related features is usually a good step in that direction.
Excel primarily is a spreadsheet tool in which you can dump some data. At times though, data is not everything. For e.g. your retail transactional system captures the sales across product lines, but if a manager needs to predict his stocks for next fiscal based on historic sales, he got to make some sense out of that data. That’s where you got to Pivot the data. The word ‘Pivot’ as Wiki generally describes it – is center point of any rotational system. You have seen them in action while helping your kids on a seesaw. Pivot table on other hand is a data summarization tool found in spreadsheet softwares like Excel. If you have to bridge this tool and the word ‘Pivot’ – you can think you are bending your data to a specific viewpoint using functions like sort, total, average, count, etc. Let’s see it with help of a trivial example. Consider below the sample data in Excel. To bend it to monthly stock view you can click on Insert tab and insert a Pivot Table. This would give you an empty pivot table and a PivotTable field list. Drag and drop your fields to specific areas to bend your data to a viewpoint (N.B. you can have multiple fields for each area and control them via expand / collapse buttons and all your charting knowledge works as is with Pivot tables).
PowerPivot as the name suggests elevates the Pivot table feature to next level making it full-fledged self-service BI tool. An Excel Add-In, PowerPivot connects to almost any external data sources including SQL Server Analysis Services. Once fetched, data stays inside the workbook and using excellent compression techniques the file size on the disk is still relatively small. You can share your PowerPivot workbooks via SharePoint and configure refresh cycles for your workbook to ensure your team always makes their decisions based on recent data. The experience of pivoting though remains same as shown earlier.
Hope that helps 🙂 .