如何使用 Excel Interop 和 C# 向电子表格添加透视表

数据透视操作

在 Excel 中使用数据透视表非常关键；它们允许您从侧面、由内而外和向后查看数据。我不确切知道这意味着什么，但了解如何创建数据透视表有时会派上用场，尤其是在您的雇主或客户要求使用数据透视表时。因此有了这个技巧。

以下技巧假定您已经拥有创建 Excel 电子表格并将数据放置在其上的代码。此技巧仅显示如何使用表格上的现有数据。 那么事不宜迟，以下是您需要执行的操作

添加一些声明（这假定您具有以下 using 子句：“using Excel = Microsoft.Office.Interop.Excel;"）

static readonly object useDefault = Type.Missing;
PivotTable pivotTable;
Range pivotData;
Range pivotDestination;
PivotField descriptionPivotField;
PivotField shortnamePivotField;
PivotField itemcodePivotField;
PivotField pricePivotField;
string pivotTableName = @"SupercalifornialisticXPalbeauxdacious";

PivotField 将根据您的数据而有所不同 - 不仅是它们的名称（您不太可能将它们命名为 “shortnamePivotField” 和 “itemcodePivotField” 等），而且您想要的 PivotField 的数量也可能有所不同。

血腥的细节

现在编写必要的代码，以基于表格上的现有数据创建数据透视表

private void AddPivotTable()
{
    pivotData = _xlSheet.Range["A1:K1600"];
    pivotDestination = _xlSheet.Range["A1605", useDefault];
    _xlBook.PivotTableWizard(
            XlPivotTableSourceType.xlDatabase,
            pivotData,
            pivotDestination,
            pivotTableName,
            true,
            true,
            true,
            true,
            useDefault,
            useDefault,
            false,
            false,
            XlOrder.xlDownThenOver,
            0,
            useDefault,
            useDefault
    );

    // Set variables used to manipulate the Pivot Table.
    pivotTable = (PivotTable)_xlSheet.PivotTables(pivotTableName);
    
    shortnamePivotField = (PivotField)pivotTable.PivotFields(2);
    itemcodePivotField = (PivotField)pivotTable.PivotFields(3);
    descriptionPivotField = (PivotField)pivotTable.PivotFields(4);
    pricePivotField = (PivotField)pivotTable.PivotFields(7);

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

    // Page Field
    shortnamePivotField.Orientation = XlPivotFieldOrientation.xlPageField;
    shortnamePivotField.Position = 1;
    shortnamePivotField.CurrentPage = "(All)";
    
    // Row Fields
    descriptionPivotField.Orientation = XlPivotFieldOrientation.xlRowField;
    descriptionPivotField.Position = 1;
    itemcodePivotField.Orientation = XlPivotFieldOrientation.xlRowField;
    itemcodePivotField.Position = 2;

    // Data Field
    pricePivotField.Orientation = XlPivotFieldOrientation.xlDataField;
    pricePivotField.Function = XlConsolidationFunction.xlSum;
}

当然，必须保存表格，但假设已经在其他地方执行了此操作。

如您所见，上面的代码引用了 pivotData 值的硬编码数据范围（“A1:K1600”）；您需要一个不同的范围，并且可能希望避免硬编码这些值，因为数据量可能会因易失性数据而异。pivotDestination 也是如此（上面显示的行值为 “A1605”），它在数据范围和数据透视表之间留下一行空行。同样，如果您的主数据范围是易失性的，您将不希望使用常量值对其进行硬编码。

当然，您需要在依赖数据写入表格后从代码中调用 AddPivotTable()。

结果

对于上面显示的代码，这就是结果 - 绝不是完美的，但这是一个坚实的起点

...这是页面字段（简称）已下拉，准备好进行过滤的情况

放弃坏脾气的家伙

最后，将您使用的对象设置为 null

shortnamePivotField = null;
itemcodePivotField = null;
descriptionPivotField = null;
pivotDestination = null;
pivotData = null;
pivotTable = null;

关于 XlPivotField"Orientation" 值的说明

依我看，Excel Interop PivotTable 代码工作方式的问题之一是各种字段类型的命名，这似乎具有误导性，或者充其量并非最佳。

Excel.XlPivotFieldOrientation 的可能值（按字母顺序排列）为

• xlColumnField
• xlDataField
• xlHiddenField
• xlPageField
• xlRowField

这些做什么/它们之间有何不同？从给定的名字来看，它并不总是很明显。我建议使用以下替换方式来更好地理解它们的raison d'etre

• xlColumnField = xlDisplayField（在我的测试中，我没有看到它与 xlRowField 有任何区别）
• xlDataField = xlCalculatedField（它确实是一个数据字段，但它们不都是吗？）
• xlHiddenField = xlInitiallyHidden（它在复选框列表中可用，选中它将使其可见）
• xlPageField = xlFilterField
• xlRowField = xlDisplayField（在我的测试中，我没有看到它与 xlColumnField 有任何区别）

正如 Vonnegut 所说，“事情就是这样”

此技巧应该足以生成数据透视表；从那里，您可以调整和扭曲并扭转它以满足您的特定需求。

注意：此代码改编自文章“在 Excel 2007 中创建数据透视表”，可以在此处找到。