Search code examples
c#excelpivot-table

Excel pivot table: move value fields from row to column

I'm developing an excel addin in c# and have to create a pivot table, which I don't know how to organize the data, how it should be.

First of all the datasource:

Pivot Data Source

This is, how the final Pivot Table should looks like:

Pivot should looks like this

But I only get something like this and I have no Idea where I have to how to change my code:

This is what my code does with it -.-

Finally my code to arrange the data source columns:

/* PIVOT RowFields */
Excel.PivotField nameField = pTable.PivotFields("Name");
nameField.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
nameField.Position = 1;

Excel.PivotField monthField = pTable.PivotFields("Monat");
monthField.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
monthField.Position = 2;

/* PIVOT Data */
Excel.PivotField sum200Field = pTable.PivotFields("Summe 1");
sum200Field.Orientation = Excel.XlPivotFieldOrientation.xlDataField;

Excel.PivotField sum700Field = pTable.PivotFields("Summe 2");
sum700Field.Orientation = Excel.XlPivotFieldOrientation.xlDataField;

Excel.PivotField sumDiffField = pTable.PivotFields("Differenz");
sumDiffField.Orientation = Excel.XlPivotFieldOrientation.xlDataField;

I'm using .NET Framework 4 ... I read a lot of articles in the internet, but nothing I read was useful...

Does anyone have an Idea?

UPDATE:

As @MP24 posted, adding these two C#-Lines at the end of my code, the columns will be display right exclusive the grouping of name and month:

Excel.PivotField dataField = pTable.DataPivotField;
dataField.Orientation = Excel.XlPivotFieldOrientation.xlColumnField;

UPDATE2:

To group Month and Name Column, the following code will show how to:

/* PIVOT ZEILEN */
Excel.PivotField monthField = pTable.PivotFields("Monat");
monthField.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
monthField.LayoutCompactRow = true;
monthField.LayoutForm = Excel.XlLayoutFormType.xlOutline;
monthField.set_Subtotals(1, false);

Excel.PivotField nameField = pTable.PivotFields("Name");
nameField.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
nameField.LayoutCompactRow = true;
nameField.set_Subtotals(2, false);
Solution

  • You will have to change the orientation of your data fields:

    The VBA code is

    ActiveSheet.PivotTables("PivotTable1").DataPivotField.Orientation = xlColumnField

    This translates into C# code:

    Excel.PivotField dataField = pTable.DataPivotField;
dataField.Orientation = Excel.XlPivotFieldOrientation.xlColumnField;

    Edit: Note that for understanding the pivot tables, the macro recorder will give you good hints. Just start recording the macro just before you perform your desired action, and stop the macro afterwards. Transferring the insights from the code into C# will then be easy.