如何获取此数据透视表以在所需的位置显示所需的值? [英] How can I get this PivotTable to display the values I want it to, in the location I want them to be?

问题描述

经过大量的牙齿咬牙之后，我通过生成数据透视表获得了一定的成功.现在，它同时具有行过滤器"(在说明"上)和列过滤器"(在月年"列上)，就像这样:

After much gnashing of teeth, &c, I was able to achieve a modicum of success with generating a PivotTable. It now sports both a "row filter" (on the Descriptions) and a "column filter" (on the "month year" columns), like so:

这是通过以下代码完成的:

This is accomplished with the following code:

var pch = _xlBook.PivotCaches();
Range sourceData = _xlBook.Worksheets["PivotData"].Range["A1:G318"]; // TODO: Make range dynamic

PivotCache pc = pch.Create(XlPivotTableSourceType.xlDatabase, sourceData);
PivotTable pvt = pc.CreatePivotTable(_xlPivotTableSheet.Range["A8"], "PivotTable");

pvt.PivotFields("Description").Orientation = XlPivotFieldOrientation.xlRowField;
pvt.PivotFields("MonthYr").Orientation = XlPivotFieldOrientation.xlColumnField;

源数据("PivotData")如下所示:

The source data ("PivotData") looks like this:

数据透视表应该看起来是这样的:

What the PivotTable should look like, when all is said and coded, is this:

我需要做些什么才能实现这种外观(此数据以及在这些位置)?我认为部分原因与将更多XlPivotFieldOrientation值分配给更多PivotFields有关，但不知道它们应该是什么.

What do I need to do to achieve this look (this data, and in these locations)? I reckon part of it is has to do with the assignments of more XlPivotFieldOrientation values to more PivotFields, but don't know just what they should be.

首先但可能最不重要(但仍然很重要)，行标签"应显示说明"，列标签"应显示月"

And first but possibly least (but still important), "Row Labels" should say "Description" and "Column Labels" should say "Month"

我正在尝试逐段添加显示各个列所需的代码.我试过了:

I am trying to add, piece by piece, the code needed to make the various columns display. I tried this:

pvt.AddDataField(pvt.PivotFields("TotalQty")，总包数"，XlConsolidationFunction.xlSum).NumberFormat ="###，## 0";

pvt.AddDataField(pvt.PivotFields("TotalQty"), "Total Packages", XlConsolidationFunction.xlSum).NumberFormat = "###,##0";

...希望源数据的"TotalQty"列中的数据将显示在标题为"Total Packages"的列中

...hoping that the data from the source data's "TotalQty" column would display in a column headed "Total Packages"

它确实显示了，但是标签"Total Packages"出现在一个奇数/偏僻的地方:

It does indeed display, but the label "Total Packages", appears in an odd/out-of-the-way place:

如何获取总包装"标签以显示在模型"屏幕截图中的位置?

How can I get the "Total Packages" label to display where it does in the "model" screenshot?

Hambone在回答中提到了我的待办事项；我回到它，并以这种方式使其动态化:

Hambone mentioned my TODO in his answer; I got back to it and made that dynamic this way:

int rowsUsed = _xlBook.Worksheets["PivotData"].UsedRange.Rows.Count;
int colsUsed = _xlBook.Worksheets["PivotData"].UsedRange.Columns.Count;
string colsUsedAsAlpha = GetExcelColumnName(colsUsed);
string endRange = string.Format("{0}{1}", colsUsedAsAlpha, rowsUsed);
Range sourceData = _xlBook.Worksheets["PivotData"].Range[string.Format("A1:{0}",
  endRange)];

// Pass "1", get "A", etc.; from http://stackoverflow.com/questions/181596/how-  
to-convert-a-column-number-eg-127-into-an-excel-column-eg-aa
public static string GetExcelColumnName(int columnNumber)
{
    int dividend = columnNumber;
    string columnName = String.Empty;
    while (dividend > 0)
    {
        var modulo = (dividend - 1) % 26;
        columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
        dividend = (dividend - modulo) / 26;
    }
    return columnName;
}

顺便说一句，我找不到我的青铜徽章.

BTW, I can't find my bronze badges.

推荐答案

有几件事……您没有特别要求，但是在您的代码中，您具有使动态范围动态的"TODO".一种非常好的方法是将范围转换为表格.完成此操作后，您可以传递表格而不是范围.您可能从以前的帖子中知道该表是:

A couple of things... you didn't ask this specifically, but in your code you had a "TODO" as making the range dynamic. A really nice way to do this is to convert your range to a table. Once you do this, you can pass the table instead of the range. The table, as you probably know from previous posts would be:

ListObject tab = _xlPivotTableSheet.ListObjects["Table1"];
PivotCache pc = pch.Create(XlPivotTableSourceType.xlDatabase, tab);

如果执行此操作，则边界无关紧要-该表将全部作为一个数据源使用.

If you do this, boundaries don't matter -- the table goes all as one data source.

回到您眼前的问题.您可以添加数据字段了:

Back to your issue at hand. You added your data fields okay:

pvt.AddDataField(pvt.PivotFields("TotalQty"), "Total Packages",
    XlConsolidationFunction.xlSum);
pvt.AddDataField(pvt.PivotFields("TotalSales"), "Total Purchases",
     XlConsolidationFunction.xlSum);

计算出的字段比较棘手.我不太了解总数的百分比"的计算方式，但是出于说明目的，这是平均价格的计算方式:

The calculated field is a bit more tricky. I didn't quite understand the calculation for "% of total," but for illustration purposes this is how you would do average price:

PivotField avg = pvt.CalculatedFields().Add("Average Price", "=TotalSales/TotalQty", true);
avg.Orientation = XlPivotFieldOrientation.xlDataField;

您说得对-方向不是您所期望的.此设置是在数据透视表级别上完成的-它可以是行或列.要获取所需的视图，可以将其从默认列更改为行:

And you're right -- the orientation is not what you expect. This setting is done at the pivot table level -- it's either rows or columns. To get the view you're looking for you would change it from the default columns to rows:

pvt.DataPivotField.Orientation = XlPivotFieldOrientation.xlRowField;

完成此操作后，我认为数据透视表将看起来更像您期望的样子.

Once you do this, I think the pivot table will look more the way you expect.

这篇关于如何获取此数据透视表以在所需的位置显示所需的值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！