none
How to display data in Pivot Tables in seperate columns using C#

    Question

  • Hi,

    I am creating pivot table using C#. I am able to create the table in excel.

    I am stuck with the problem that I am geting all the Row fields data in 1 column one under other.

    Like this:

    Column A

      Maximus            Items       Total Hours

         Company

               Name

    I want columns Maximus and Comp should some in seperate columns, like below:

      Maximus     Company    Name      Items       Total Hours

    This is my code what I am using to create pivot table.

    Excel.

    PivotCachepivotCache = (Excel.PivotCache)workbook.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, dataRange);

    Excel.

    PivotTablepivotTable = sheet.PivotTables().Add((PivotCache: pivotCache), TableDestination: "Sheet1!R3C1", TableName: "PivotTable1");

                pivotTable.SubtotalHiddenPageItems =

    false;

                pivotTable.AllowMultipleFilters =

    true;

                pivotTable.LayoutRowDefault = Excel.

    XlLayoutRowType.xlOutlineRow;

                pivotTable.InGridDropZones =

    false;

                pivotTable.EnableDrilldown =

    false;

    Excel.

    PivotFieldPivotField1 = (Excel.PivotField)pivotTable.PivotFields("Location");

                PivotField1.Orientation = Excel.

    XlPivotFieldOrientation.xlRowField;

                PivotField1.Position = 1;

                PivotField1.Name =

    "Location";

    PivotField1 = (Excel.

    PivotField)pivotTable.PivotFields("Date");

    PivotField1.Orientation = Excel.

    XlPivotFieldOrientation.xlRowField;

    PivotField1.Name =

    "Date Worked";

    PivotField1 = (Excel.

    PivotField)pivotTable.PivotFields("Function");

    PivotField1.Orientation = Excel.

    XlPivotFieldOrientation.xlRowField;

    PivotField1.Name =

    "Function";

    PivotField1 = (Excel.

    PivotField)pivotTable.PivotFields("Customer ID");

                PivotField1.Orientation = Excel.

    XlPivotFieldOrientation.xlRowField;

    "Customer ID";

    PivotField1 = (Excel.

    PivotField)pivotTable.PivotFields("Name");

    PivotField1.Orientation = Excel.

    XlPivotFieldOrientation.xlRowField;

    PivotField1.Name =

    "Name";

    PivotField1 = (Excel.

    PivotField)pivotTable.PivotFields("Total Hours");

                 PivotField1.Orientation = Excel.

    XlPivotFieldOrientation.xlDataField;

                 PivotField1 = (Excel.

    PivotField)pivotTable.PivotFields("Total Items");

                 PivotField1.Orientation = Excel.

    XlPivotFieldOrientation.xlDataField;

    PivotField1 = (Excel.

    PivotField)pivotTable.PivotFields("Items Per Hour");

                 PivotField1.Orientation = Excel.

    XlPivotFieldOrientation.xlDataField;

    Any help will be really appreciated.

    Thanks.

    PivotField1.Name =

    Friday, January 24, 2014 7:51 AM

Answers

  • Record a macro while setting up the pivot table the way that you want.
    Sunday, January 26, 2014 5:14 PM
  • Hi Bernie,

    Thanks for your time and suggestions. I am able to generate the report. I have changed the way I was generating it.

    I have used PivotTableWizard mothod to create pivot table and added Pivot fields.

    Thanks.

    Tuesday, January 28, 2014 6:49 AM

All replies

  • For those items, instead of

    XlPivotFieldOrientation.xlRowField;

    use

    XlPivotFieldOrientation.xlColumnField;

    Friday, January 24, 2014 5:16 PM
  • Thanks Bernie for the reply.

    But when I am using XlPivotFieldOrientation.xlColumnField, thw complete data is moved in new column.

    I don't want that. I want only the Column Names as column and rest of the fields as rows.

    Please suggest.

    Friday, January 24, 2014 5:40 PM
  • Try just

    pivotTable.DataPivotField.Orientation = xlColumnField

    Friday, January 24, 2014 6:11 PM
  • Hi Bernie,

    I tried the above but its still not working.

    I think either I am creating the Pivot table in wrong way or I am missing anything.

    Sunday, January 26, 2014 3:10 AM
  • Record a macro while setting up the pivot table the way that you want.
    Sunday, January 26, 2014 5:14 PM
  • Hi Bernie,

    Thanks for your time and suggestions. I am able to generate the report. I have changed the way I was generating it.

    I have used PivotTableWizard mothod to create pivot table and added Pivot fields.

    Thanks.

    Tuesday, January 28, 2014 6:49 AM