如何更改数据透视表，使其以所需的方式显示数据? [英] How can I alter my PivotTable so that it displays the data in the desired way?

问题描述

我已经能够将数据透视表拖到舞池上，但无法将它放到舞池中-看来我们一直在踩着对方的脚趾.我已经在用于构建数据透视表的工作表上有了数据.此类描述了该数据:

public class PriceVarianceData
{
    public String Unit { get; set; }
    public String ShortName { get; set; }
    public String ItemCode { get; set; }
    public String Description { get; set; }
    public String PriceWeek { get; set; }
    public String Week { get; set; }
    public String Price { get; set; }
    public String Variance { get; set; }
    public String VarianceAverage { get; set; }
    public int RegionOrder { get; set; }
    public int ContractPrice { get; set; }
}

工作表的第一列("A")包含单位值，第二列("B")显示ShortName值，等等.

我正在使用以下代码从此数据创建数据透视表:

private void AddPivotTable()
{
    pivotData = _xlSheet.Range["A1:K1600"];
    pivotDestination = _xlSheet.Range["A1602", useDefault];

    _xlBook.PivotTableWizard(
        Excel.XlPivotTableSourceType.xlDatabase,
        pivotData,
        pivotDestination,
        pivotTableName,
        true,
        true,
        true,
        true,
        useDefault,
        useDefault,
        false,
        false,
        Excel.XlOrder.xlDownThenOver,
        0,
        useDefault,
        useDefault
    );

    // Set variables used to manipulate the Pivot Table.
    pivotTable = (Excel.PivotTable)_xlSheet.PivotTables(pivotTableName);

    shortnamePivotField = (Excel.PivotField)pivotTable.PivotFields(2);
    itemcodePivotField = (Excel.PivotField)pivotTable.PivotFields(3);
    descriptionPivotField = (Excel.PivotField)pivotTable.PivotFields(4);
    priceweekPivotField = (Excel.PivotField)pivotTable.PivotFields(5);
    weekPivotField = (Excel.PivotField)pivotTable.PivotFields(6);
    regionorderPivotField = (Excel.PivotField)pivotTable.PivotFields(10);

    // Format the Pivot Table.
    pivotTable.Format(Excel.XlPivotFormatType.xlReport2);
    pivotTable.InGridDropZones = false;
    pivotTable.SmallGrid = false;
    pivotTable.ShowTableStyleRowStripes = true;
    pivotTable.TableStyle2 = "PivotStyleLight1";

    // The PivotFields have the following interesting properties:
    // Orientation, Function, Calculation

    // Page Field
    //shortnamePivotField.Orientation = Excel.XlPivotFieldOrientation.xlPageField;

    // Column Field
    priceweekPivotField.Orientation = Excel.XlPivotFieldOrientation.xlColumnField;

    // Row Fields
    descriptionPivotField.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
    //regionorderPivotField.Orientation = Excel.XlPivotFieldOrientation.xlRowField;

    // Data Fields
    //contractPricePivotField.Orientation = Excel.XlPivotFieldOrientation.xlDataField;
    //contractPricePivotField.Function = Excel.XlConsolidationFunction.xlSum;
    itemcodePivotField.Orientation = Excel.XlPivotFieldOrientation.xlDataField;
    itemcodePivotField.Function = Excel.XlConsolidationFunction.xlSum;
}

诚然，范围值的硬编码有点令人费解，但这是一个实验项目，它将始终返回相同(数量)的数据，因此在这种情况下，可以在这种情况下使用.

上面的代码的确向工作表中添加了数据透视表，但是由于它没有什么意义(原始数据的最后几行显示在顶部；数据透视表从第1602行开始)，因此它提供了查看者不足的值: /p>

我意识到存在问题是因为我没有将正确的PivotFields指定为xlPageField，xlColumnField [s]，xlRowField [s]和xlDataField [s].问题是我的无聊之一.而不是让我全神贯注于这些数据，我的灰色物质是沿着数据透视表的城墙以休止角虚弱地躺着的.

要更改数据透视表的外观以使其显示，我需要做什么:

每个ItemCode(例如"110051")的总计，与ShortName无关.

...，也许在另一个数据透视表中:

每个短名称(例如"T& T")的总计，与ItemCode无关.

???

更新

受评论的启发

更新3

这是我最好的猜测:

// Page Field
shortnamePivotField.Orientation = Excel.XlPivotFieldOrientation.xlPageField;

// Row Fields
descriptionPivotField.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
itemcodePivotField.Orientation = Excel.XlPivotFieldOrientation.xlRowField;

// Data Fields
pricePivotField.Orientation = Excel.XlPivotFieldOrientation.xlDataField;
pricePivotField.Function = Excel.XlConsolidationFunction.xlSum;

...但是比在墙上扔代码更好，因为它不起作用:

解决方案

我已经到那儿了；我对另一个问题的自动回答中显示了如何开始完成我所需要的工作

I am using the following code to create a PivotTable from this data:

private void AddPivotTable()
{
    pivotData = _xlSheet.Range["A1:K1600"];
    pivotDestination = _xlSheet.Range["A1602", useDefault];

    _xlBook.PivotTableWizard(
        Excel.XlPivotTableSourceType.xlDatabase,
        pivotData,
        pivotDestination,
        pivotTableName,
        true,
        true,
        true,
        true,
        useDefault,
        useDefault,
        false,
        false,
        Excel.XlOrder.xlDownThenOver,
        0,
        useDefault,
        useDefault
    );

    // Set variables used to manipulate the Pivot Table.
    pivotTable = (Excel.PivotTable)_xlSheet.PivotTables(pivotTableName);

    shortnamePivotField = (Excel.PivotField)pivotTable.PivotFields(2);
    itemcodePivotField = (Excel.PivotField)pivotTable.PivotFields(3);
    descriptionPivotField = (Excel.PivotField)pivotTable.PivotFields(4);
    priceweekPivotField = (Excel.PivotField)pivotTable.PivotFields(5);
    weekPivotField = (Excel.PivotField)pivotTable.PivotFields(6);
    regionorderPivotField = (Excel.PivotField)pivotTable.PivotFields(10);

    // Format the Pivot Table.
    pivotTable.Format(Excel.XlPivotFormatType.xlReport2);
    pivotTable.InGridDropZones = false;
    pivotTable.SmallGrid = false;
    pivotTable.ShowTableStyleRowStripes = true;
    pivotTable.TableStyle2 = "PivotStyleLight1";

    // The PivotFields have the following interesting properties:
    // Orientation, Function, Calculation

    // Page Field
    //shortnamePivotField.Orientation = Excel.XlPivotFieldOrientation.xlPageField;

    // Column Field
    priceweekPivotField.Orientation = Excel.XlPivotFieldOrientation.xlColumnField;

    // Row Fields
    descriptionPivotField.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
    //regionorderPivotField.Orientation = Excel.XlPivotFieldOrientation.xlRowField;

    // Data Fields
    //contractPricePivotField.Orientation = Excel.XlPivotFieldOrientation.xlDataField;
    //contractPricePivotField.Function = Excel.XlConsolidationFunction.xlSum;
    itemcodePivotField.Orientation = Excel.XlPivotFieldOrientation.xlDataField;
    itemcodePivotField.Function = Excel.XlConsolidationFunction.xlSum;
}

Admittedly, the hardcoding of the range values is a bit doofusesque, but this is an experimental project which will always return the same (amount of) data, so it works in this case with this somewhat contrived case.

The code above does add a PivotTable to the sheet, but it provides the viewer scant value, as it makes little sense (the last few rows of raw data are shown at the top; the PivotTable begins on row 1602):

I realize the problem exists because I'm not designating the correct PivotFields as xlPageField, xlColumnField[s], xlRowField[s], and xlDataField[s]. The problem is one of groklessness on my part; instead of wrapping my mind around this data, my grey matter is lying flaccid at an angle of repose along the ramparts of the PivotTable.

What do I need to do in order to alter the PivotTable's appearance so that it shows:

Totals for each ItemCode (such as "110051") regardless of ShortName.

...and perhaps in another PivotTable:

Totals for each ShortName (such as "T&T") regardless of ItemCode.

???

UPDATE

Inspired by a comment here, I added column headings, which changed the PivotTable a bit:

UPDATE 2

Here is a fakely-generated (with the GUI, not in code) PivotTable that I want to create in code:

What code is needed to specify "SHORT NAME" as the "Report Filter", so that it will generate a drop-down as shown above with the various values of ShortName selectable?

UPDATE 3

This was my best guess:

// Page Field
shortnamePivotField.Orientation = Excel.XlPivotFieldOrientation.xlPageField;

// Row Fields
descriptionPivotField.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
itemcodePivotField.Orientation = Excel.XlPivotFieldOrientation.xlRowField;

// Data Fields
pricePivotField.Orientation = Excel.XlPivotFieldOrientation.xlDataField;
pricePivotField.Function = Excel.XlConsolidationFunction.xlSum;

...but was no better than throwing code at the wall, since it didn't work:

解决方案

I've gotten partway there; what I did to get a start on how to accomplish what I need is shown in my auto-answer to another question here.

这篇关于如何更改数据透视表，使其以所需的方式显示数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！