Patch to Pivot Table Configuration Example

I have just updated the PowerTools Core (version 2.2.6) to fix a couple of problems with the spreadsheet and pivot examples. I have fixed twoproblems. There were some problems with the GetCellValue method. The other problem was with the pivot table configuration, which did not work when two or more data values were added using the AddDataValue method.

I found the problem with GetCellValue when I tried to use it on a cell with a formula. The problem was that I was using the Value method on the “c” element instead of the “v” element within the “c” element. That worked fine if the only child was the “v” element. Of course, that is not the case when there is a formula. When Value is used for a formula cell, it concatenates the values from both the “v” and “f” elements. The new code now uses “cellValue.Element(S.v).Value” instead of “cellValue.Value.”

The change to AddDataValue is actually two changes. The first problem with adding the second data value was that the code created a new dataField was also creating a new dataFields element to contain it. That was clearly a problem and I added code to look for an existing dataFields element. The more subtle problem is that any pivot table with two or more data fields also needs a special column or row that tells Excel where to display the descriptive headings for the data values and how to group them in relation to the other rows or columns. This is done with a special “field” element that has an “x” attribute equal to -2. It looks like this:

  <colFields count="3">
    <field x="0" />
    <field x="1" />
    <field x="-2" />
  </colFields>

I have added a new method called “AddDataValueLabel” to do just that. Here it is:

public static void AddDataValueLabel(SpreadsheetDocument document, WorksheetPart sheet, PivotAxis axis)
{
    PivotTablePart pivotTablePart = sheet.GetPartsOfType<PivotTablePart>().First();
    XDocument pivotTable = pivotTablePart.GetXDocument();
    switch (axis)
    {
        case PivotAxis.Column:
            // Add to colFields
            {
                XElement fields = pivotTable.Element(S.pivotTableDefinition).Element(S.colFields);
                if (fields == null)
                {
                    fields = new XElement(S.colFields, new XAttribute(NoNamespace.count, 0));
                    XElement rowFields = pivotTable.Element(S.pivotTableDefinition).Element(S.rowFields);
                    if (rowFields == null)
                        pivotTable.Element(S.pivotTableDefinition).Element(S.pivotFields).AddAfterSelf(fields);
                    else
                        rowFields.AddAfterSelf(fields);
                }
                fields.Add(new XElement(S.field, new XAttribute(NoNamespace.x, -2)));
                fields.Attribute(NoNamespace.count).Value = fields.Elements(S.field).Count().ToString();
            }
            break;
        case PivotAxis.Row:
            // Add to rowFields
            {
                XElement fields = pivotTable.Element(S.pivotTableDefinition).Element(S.rowFields);
                if (fields == null)
                {
                    fields = new XElement(S.rowFields, new XAttribute(NoNamespace.count, 0));
                    pivotTable.Element(S.pivotTableDefinition).Element(S.pivotFields).AddAfterSelf(fields);
                }
                fields.Add(new XElement(S.field, new XAttribute(NoNamespace.x, -2)));
                fields.Attribute(NoNamespace.count).Value = fields.Elements(S.field).Count().ToString();
            }
            break;
        case PivotAxis.Page:
            // Add to pageFields
            {
                XElement fields = pivotTable.Element(S.pivotTableDefinition).Element(S.pageFields);
                if (fields == null)
                {
                    fields = new XElement(S.pageFields, new XAttribute(NoNamespace.count, 0));
                    XElement prev = pivotTable.Element(S.pivotTableDefinition).Element(S.colFields);
                    if (prev == null)
                        prev = pivotTable.Element(S.pivotTableDefinition).Element(S.rowFields);
                    if (prev == null)
                        pivotTable.Element(S.pivotTableDefinition).Element(S.pivotFields).AddAfterSelf(fields);
                    else
                        prev.AddAfterSelf(fields);
                }
                fields.Add(new XElement(S.pageField, new XAttribute(NoNamespace.fld, -2)));
                fields.Attribute(NoNamespace.count).Value = fields.Elements(S.field).Count().ToString();
            }
            break;
    }
    pivotTablePart.PutXDocument();
    PivotTableCacheDefinitionPart cacheDefPart = pivotTablePart.GetPartsOfType<PivotTableCacheDefinitionPart>().First();
    ForcePivotRefresh(cacheDefPart);
}

Most of this code is just like the AddPivotAxis method, but without needing to create a pivotField element. Then I changed the AddDataValue method to call AddDataValueLabel to add the special field if it has just added the second data value. For your own application, you may want to take that out of AddDataValue and make the call to AddDataValueLabel yourself. For example, you may want it added as a row instead of a column. The code could also be enhanced to search for an existing element for the data value labels. If that special -2 indexed field already existed, then it would not create another.

Please let me know if you find other problems with the pivot table example. There could be other special cases that I have never tested, so I always welcome feedback.