OpenXML example for filtering pivot table?

Question

I've been tasked with populating an Excel template with data (on sheet 1). The data in that sheet will be used to update 5 or 6 pivot tables on other sheets. That much is working so far with the code below. However, I'm trying to figure out how to remove $0 amounts from the final pivot table result.

        public void PivotTableTesting(int recCount)
    {
        PivotTableCacheDefinitionPart ptp =  book.PivotTableCacheDefinitionParts.FirstOrDefault();
        PivotTableDefinition def = sheetPart.PivotTableParts.FirstOrDefault().PivotTableDefinition;
        ptp.PivotCacheDefinition.RefreshOnLoad = true;
        ptp.PivotCacheDefinition.RecordCount = UInt32Value.ToUInt32((uint)recCount);
        string val = ptp.PivotCacheDefinition.CacheSource.WorksheetSource.Reference.Value;
        ptp.PivotCacheDefinition.CacheSource.WorksheetSource.Reference = @"A1:AO" + (recCount + 1).ToString();
        ptp.PivotTableCacheRecordsPart.PivotCacheRecords.RemoveAllChildren();
        ptp.PivotTableCacheRecordsPart.PivotCacheRecords.Count = 0;
    }

Does anyone have any example code on creating a filter and applying it to a pivot table definition?

OpenXML example for filtering pivot table?

0 Answers0