Pivot table using c#

Pivot table using c#

• Bill Libert

  I was able to create pivot table and row /column fields , part of the code is shown below, but I wanted to get rid of the subtotal which automatically comes up if I add 2 RowFields, I posted the question in this as well as Office PIA forums without any help

  I created a Pivot Table in Excel using c# and was able to specifi RowFiled,ColumnField and Datafield as:
  
  Xl.PivotField pvtRow1 =((Xl.PivotField)pvt.PivotFields("Description"));
  pvtRow1.Orientation = Xl.XlPivotFieldOrientation.xlRowField;
  
  Xl.PivotField pvtCol =((Xl.PivotField)pvt.PivotFields("PortName"));
  pvtCol.Orientation = Xl.XlPivotFieldOrientation.xlColumnField ;
  
  Xl.PivotField pvtFld1 =((Xl.PivotField)pvt.PivotFields("DurContrib"));
  pvtFld1.Orientation = Xl.XlPivotFieldOrientation.xlDataField;
  
  It is working but my question is: is this the right way of doing it and next 
  
  a) how can I take out the automatic subtotal for a particular RowField  
  b) how can I increase the number of columnfields/rowfields in the pivottable 
  
  c) is there any book which describes how to convert VBA macros to C# 
  
  Any help or code snippet will be helpful.
  
  Thanks
  

  
  

• WUSHH2000

  If you're still having problems after going through the samples etc then let us know and we'll see what we can do.

  Ade

  
  
  

• learningdba

  The full code for the funtion is as follows;

  public void stgPivot()

  {

  Xl.Application xlApp;

  xlApp = (Xl.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");

  Xl.Workbook wb = xlApp.ActiveWorkbook as Xl.Workbook ;

  Xl.Worksheet ws = xlApp.ActiveWorkbook.Worksheets["Sheet2"] as Xl.Worksheet;

  Xl.PivotCaches pch = wb.PivotCaches() ;

  pch.Add(Xl.XlPivotTableSourceType.xlDatabase,"Sheet1!R3C2:R28C9").CreatePivotTable(ws.Cells[8,3],"PivTab1",Type.Missing, Type.Missing);

  Xl.PivotTable pvt = ws.PivotTables("PivTab1") as Xl.PivotTable;

  Xl.PivotField fld =((Xl.PivotField)pvt.PivotFields("Tkr"));

  fld.Orientation = Xl.XlPivotFieldOrientation.xlRowField;

  fld =((Xl.PivotField)pvt.PivotFields("Description"));

  fld.Orientation = Xl.XlPivotFieldOrientation.xlRowField;

  fld =((Xl.PivotField)pvt.PivotFields("PortName"));

  fld.Orientation = Xl.XlPivotFieldOrientation.xlColumnField ;

  fld =((Xl.PivotField)pvt.PivotFields("DurContrib"));

  fld.Orientation = Xl.XlPivotFieldOrientation.xlDataField;

  fld =((Xl.PivotField)pvt.PivotFields("Notional"));

  fld.Orientation = Xl.XlPivotFieldOrientation.xlDataField;

  pvt.DataPivotField.Orientation = Xl.XlPivotFieldOrientation.xlColumnField;

  pvt.DataPivotField.Position =1;

  pvt.RowGrand = false;

  wb.ShowPivotTableFieldList = false;

  ws.get_Range("E10","G80").NumberFormat = "#,##0.00_);[Red](#,##0.00)";

  return ;

  }

  This code automatically generates subtotal for the RowField "Tkr", in VBA it can be supressed but I don't know how to do it in C#, can someone help

  Also how can I increase the number of rowfields / columnfields allowed in a pivottable (more than what is allowed as a default)

  Is there a good book which deals with the transition from VBA to C#

  As always thanks for taking the time to read through this, any code sinippets or help will he appreciated.

  Thanks

  
  

• Per-Ove R. J.

  I'm not massively familiar with Excel VSTO, but I did have these samples bookmarked. They look promising for what you are looking for, especially the Data Analysis sample...

  http://msdn2.microsoft.com/en-us/library/8x19fbw1.aspx

  
  
  

• Shehzad Sheikh

  Thanks David, seems like this may be helpful, although doesn't have exactly the things that I am looking for but the sample codes can be a good learning tool.
  
• Pivot table using c#