Create Pivot Table Filters With EPPLUS

Question

I am using EPPLUS excel library. Do you have an idea how to create pivot table with report filters, row label and values ? Some simple example will be excellent.

Answer 1

Here is a simple example. Note that EEPlus does not have the ability to do page filters so you will have to use (in order of difficulty) VBA, Excel Interop, or XML manipulation (I used XML below). You can put this code into a unit test of the EPPlus source download for easy testing:

const string FORMATCURRENCY = "#,###;[Red](#,###)";

var file = new FileInfo(@"c:\temp\temp.xlsx");
if (file.Exists)
    file.Delete();

var pck = new ExcelPackage(file);
var workbook = pck.Workbook;
var worksheet = workbook.Worksheets.Add("newsheet");

//The data
worksheet.Cells["A20"].Value = "Col1";
worksheet.Cells["A21"].Value = "sdf";
worksheet.Cells["A22"].Value = "wer";
worksheet.Cells["A23"].Value = "ghgh";
worksheet.Cells["A24"].Value = "sdf";
worksheet.Cells["A25"].Value = "wer";
worksheet.Cells["A26"].Value = "ghgh";
worksheet.Cells["A27"].Value = "sdf";
worksheet.Cells["A28"].Value = "wer";
worksheet.Cells["A29"].Value = "ghgh";

worksheet.Cells["B20"].Value = "Col2";
worksheet.Cells["B21"].Value = "Group A";
worksheet.Cells["B22"].Value = "Group B";
worksheet.Cells["B23"].Value = "Group A";
worksheet.Cells["B24"].Value = "Group C";
worksheet.Cells["B25"].Value = "Group A";
worksheet.Cells["B26"].Value = "Group B";
worksheet.Cells["B27"].Value = "Group C";
worksheet.Cells["B28"].Value = "Group C";
worksheet.Cells["B29"].Value = "Group A";

worksheet.Cells["C20"].Value = "Col3";
worksheet.Cells["C21"].Value = 453;
worksheet.Cells["C22"].Value = 634;
worksheet.Cells["C23"].Value = 274;
worksheet.Cells["C24"].Value = 453;
worksheet.Cells["C25"].Value = 634;
worksheet.Cells["C26"].Value = 274;
worksheet.Cells["C27"].Value = 453;
worksheet.Cells["C28"].Value = 634;
worksheet.Cells["C29"].Value = 274;

worksheet.Cells["D20"].Value = "Col4";
worksheet.Cells["D21"].Value = 686468;
worksheet.Cells["D22"].Value = 996440;
worksheet.Cells["D23"].Value = 185780;
worksheet.Cells["D24"].Value = 686468;
worksheet.Cells["D25"].Value = 996440;
worksheet.Cells["D26"].Value = 185780;
worksheet.Cells["D27"].Value = 686468;
worksheet.Cells["D28"].Value = 996440;
worksheet.Cells["D29"].Value = 185780;

//The pivot table
var pivotTable = worksheet.PivotTables.Add(worksheet.Cells["A4"], worksheet.Cells["A20:D29"], "test");

//The label row field
pivotTable.RowFields.Add(pivotTable.Fields["Col1"]);
pivotTable.DataOnRows = false;

//The data fields
var field = pivotTable.DataFields.Add(pivotTable.Fields["Col3"]);
field.Name = "Sum of Col2";
field.Function = DataFieldFunctions.Sum;
field.Format = FORMATCURRENCY;

field = pivotTable.DataFields.Add(pivotTable.Fields["Col4"]);
field.Name = "Sum of Col3";
field.Function = DataFieldFunctions.Sum;
field.Format = FORMATCURRENCY;

//The page field
pivotTable.PageFields.Add(pivotTable.Fields["Col2"]);
var xdCacheDefinition = pivotTable.CacheDefinition.CacheDefinitionXml;
var xeCacheFields = xdCacheDefinition.FirstChild["cacheFields"];
if (xeCacheFields == null)
    return;

//To filter, add items to the Cache Definition via XML
var count = 0;
var assetfieldidx = -1;

foreach (XmlElement cField in xeCacheFields)
{
    var att = cField.Attributes["name"];
    if (att != null && att.Value == "Col2" )
    {
        assetfieldidx = count;

        var sharedItems = cField.GetElementsByTagName("sharedItems")[0] as XmlElement;
        if(sharedItems == null)
            continue;

        //set the collection attributes
        sharedItems.RemoveAllAttributes();
        att = xdCacheDefinition.CreateAttribute("count");
        att.Value = "3";
        sharedItems.Attributes.Append(att);

        //create and add the item
        var item = xdCacheDefinition.CreateElement("s", sharedItems.NamespaceURI);
        att = xdCacheDefinition.CreateAttribute("v");
        att.Value = "Group A";
        item.Attributes.Append(att);
        sharedItems.AppendChild(item);

        item = xdCacheDefinition.CreateElement("s", sharedItems.NamespaceURI);
        att = xdCacheDefinition.CreateAttribute("v");
        att.Value = "Group B";
        item.Attributes.Append(att);
        sharedItems.AppendChild(item);

        item = xdCacheDefinition.CreateElement("s", sharedItems.NamespaceURI);
        att = xdCacheDefinition.CreateAttribute("v");
        att.Value = "Group C";
        item.Attributes.Append(att);
        sharedItems.AppendChild(item);

        break;
    }

    count++;
}

//Now go back to the main pivot table xml and add the cross references to complete filtering
var xdPivotTable = pivotTable.PivotTableXml;
var xdPivotFields = xdPivotTable.FirstChild["pivotFields"];
if (xdPivotFields == null)
    return;

count = 0;
foreach (XmlElement pField in xdPivotFields)
{
    //Find the asset type field
    if (count == assetfieldidx)
    {
        var att = xdPivotTable.CreateAttribute("multipleItemSelectionAllowed");
        att.Value = "1";
        pField.Attributes.Append(att);

        var items = pField.GetElementsByTagName("items")[0] as XmlElement;
        items.RemoveAll();

        att = xdPivotTable.CreateAttribute("count");
        att.Value = "4";
        items.Attributes.Append(att);
        pField.AppendChild(items);

        //Add the classes to the fields item collection
        for (var i = 0; i < 3; i++)
        {
            var item = xdPivotTable.CreateElement("item", items.NamespaceURI);
            att = xdPivotTable.CreateAttribute("x");
            att.Value = i.ToString(CultureInfo.InvariantCulture);
            item.Attributes.Append(att);

            //Turn of the Cash class in the fielder
            if (i == 1)
            {
                att = xdPivotTable.CreateAttribute("h");
                att.Value = "1";
                item.Attributes.Append(att);
            }

            items.AppendChild(item);

        }

        //Add the default
        var defaultitem = xdPivotTable.CreateElement("item", items.NamespaceURI);
        att = xdPivotTable.CreateAttribute("t");
        att.Value = "default";
        defaultitem.Attributes.Append(att);
        items.AppendChild(defaultitem);

        break;
    }
    count++;
}

pck.Save();

---

Sorry for all the edit but I have been working on this for a little while when I stumbled on this question. I created an extension method just for applying a filter. Give it the field name (it assumes there is a header line contining the column names), the filters you want to apply, and the worksheet containing the data or it will just the Pivot Table worksheet if no data worksheet is passed in. It have done basic testing so you should QA:

public static bool FilterField(this ExcelPivotTable pivotTable, string pageFieldName, IEnumerable<object> filters, ExcelWorksheet dataWorksheet = null)
{
    //set the worksheet
    var ws = dataWorksheet ?? pivotTable.WorkSheet;

    //Set the cache definitions and cache fields
    var xdCacheDefinition = pivotTable.CacheDefinition.CacheDefinitionXml;
    var xeCacheFields = xdCacheDefinition.FirstChild["cacheFields"];
    if (xeCacheFields == null)
        return false;

    //Go the field list in the definitions, note the field idx and valuesfor 
    var count = 0;
    var fieldIndex = -1;
    List<object> fieldValues = null;

    foreach (XmlElement cField in xeCacheFields)
    {
        var att = cField.Attributes["name"];
        if (att != null && att.Value.Equals(pageFieldName, StringComparison.OrdinalIgnoreCase))
        {
            //store the field data
            fieldIndex = count;
            var dataddress = new ExcelAddress(pivotTable.CacheDefinition.SourceRange.Address);

            var valueHeader = ws
                .Cells[dataddress.Start.Row, dataddress.Start.Column, dataddress.Start.Row, dataddress.End.Column]
                .FirstOrDefault(cell => cell.Value.ToString().Equals(pageFieldName, StringComparison.OrdinalIgnoreCase));

            if (valueHeader == null)
                return false;

            //Get the range minus the header row
            var valueObject = valueHeader.Offset(1, 0, dataddress.End.Row - dataddress.Start.Row, 1).Value;
            var values = (object[,])valueObject;

            fieldValues = values
                .Cast<object>()
                .Distinct()
                .ToList();

            //kick back if the types are mixed
            if (fieldValues.FirstOrDefault(v => v is string) != null && fieldValues.FirstOrDefault(v => !(v is string)) != null)
                throw new NotImplementedException("Filter function does not (yet) support mixed parameter types");

            //fill in the shared items for the field
            var sharedItems = cField.GetElementsByTagName("sharedItems")[0] as XmlElement;
            if (sharedItems == null)
                continue;

            //Reset the collection attributes
            sharedItems.RemoveAllAttributes();

            //Handle numerics - assume all or nothing
            var isNumeric = fieldValues.FirstOrDefault(v => v is string) == null;
            if (isNumeric)
            {
                att = xdCacheDefinition.CreateAttribute("containsSemiMixedTypes");
                att.Value = "0";
                sharedItems.Attributes.Append(att);

                att = xdCacheDefinition.CreateAttribute("containsString");
                att.Value = "0";
                sharedItems.Attributes.Append(att);

                att = xdCacheDefinition.CreateAttribute("containsNumber");
                att.Value = "1";
                sharedItems.Attributes.Append(att);

                att = xdCacheDefinition.CreateAttribute("containsInteger");
                att.Value = fieldValues.Any(v => !(v is int || v is long)) ? "0" : "1";
                sharedItems.Attributes.Append(att);
            }

            //add the count
            att = xdCacheDefinition.CreateAttribute("count");
            att.Value = fieldValues.Count.ToString(CultureInfo.InvariantCulture);
            sharedItems.Attributes.Append(att);

            //create and add the item
            foreach (var fieldvalue in fieldValues)
            {
                var item = xdCacheDefinition.CreateElement(isNumeric ? "n" : "s", sharedItems.NamespaceURI);
                att = xdCacheDefinition.CreateAttribute("v");
                att.Value = fieldvalue.ToString();
                item.Attributes.Append(att);
                sharedItems.AppendChild(item);
            }

            break;
        }

        count++;
    }

    if (fieldIndex == -1 || fieldValues == null)
        return false;

    //Now go back to the main pivot table xml and add the cross references to complete filtering
    var xdPivotTable = pivotTable.PivotTableXml;
    var xdPivotFields = xdPivotTable.FirstChild["pivotFields"];
    if (xdPivotFields == null)
        return false;

    var filtervalues = filters.ToList();
    count = 0;
    foreach (XmlElement pField in xdPivotFields)
    {
        //Find the asset type field
        if (count == fieldIndex)
        {
            var att = xdPivotTable.CreateAttribute("multipleItemSelectionAllowed");
            att.Value = "1";
            pField.Attributes.Append(att);

            var items = pField.GetElementsByTagName("items")[0] as XmlElement;
            if (items == null)
                return false;

            items.RemoveAll();

            att = xdPivotTable.CreateAttribute("count");
            att.Value = (fieldValues.Count + 1).ToString(CultureInfo.InvariantCulture);
            items.Attributes.Append(att);
            pField.AppendChild(items);

            //Add the classes to the fields item collection
            for (var i = 0; i < fieldValues.Count; i++)
            {
                var item = xdPivotTable.CreateElement("item", items.NamespaceURI);
                att = xdPivotTable.CreateAttribute("x");
                att.Value = i.ToString(CultureInfo.InvariantCulture);
                item.Attributes.Append(att);

                if (filtervalues.Contains(fieldValues[i]))
                {
                    att = xdPivotTable.CreateAttribute("h");
                    att.Value = "1";
                    item.Attributes.Append(att);
                }

                items.AppendChild(item);
            }

            //Add the default
            var defaultitem = xdPivotTable.CreateElement("item", items.NamespaceURI);
            att = xdPivotTable.CreateAttribute("t");
            att.Value = "default";
            defaultitem.Attributes.Append(att);
            items.AppendChild(defaultitem);

            break;
        }
        count++;
    }

    return true;

}

To use it in the above example, you would do something like this:

pivotTable.FilterField("Col2", new List<string> { "Group B" });