Как я могу создать DataField, который вычисляет значения в EPPlus?

Я переношу код сводной таблицы Excel Interop в EPPlus. Я нахожусь в точке преткновения при создании вычисляемого значения поля. В Excel Interop я могу сделать это следующим образом:

pvt.AddDataField(pvt.PivotFields("TotalQty"), "Total Packages", XlConsolidationFunction.xlSum).NumberFormat = "###,##0";
pvt.AddDataField(pvt.PivotFields("TotalPrice"), "Total Purchases", XlConsolidationFunction.xlSum).NumberFormat = "$#,##0.00"; 
PivotField avg = pvt.CalculatedFields().Add("Average Price", "=TotalPrice/TotalQty", true);
avg.Orientation = XlPivotFieldOrientation.xlDataField;
avg.NumberFormat = "$###0.00";

При этом значение «Средняя цена» в сводной таблице отображает значение TotalPrice, деленное на TotalQty.

Но как это сделать в EPPlus? Я могу создать поля данных «простой ванили» следующим образом:

var totQtyField = pivotTable.Fields["TotalQty"];
pivotTable.DataFields.Add(totQtyField);

var totPriceField = pivotTable.Fields["TotalPrice"];
pivotTable.DataFields.Add(totPriceField);

... но когда дело доходит до вычисления значения, я сбит с толку. Моей отправной точкой был онлайн-пример для таких сумм:

pivotTable.DataFields.Add(pivotTable.Fields["AvgPrice"]).Function = 
    OfficeOpenXml.Table.PivotTable.DataFieldFunctions.Sum();

Итак, я попробовал следующее, но ни один из них не является правильным:

pivotTable.DataFields.Add(pivotTable.Fields["AvgPrice"]).Function = 
    OfficeOpenXml.Table.PivotTable.DataFieldFunctions.Average(totPriceField, totQtyField);
pivotTable.DataFields.Add(pivotTable.Fields["AvgPrice"]).Function = "TotalPrice/TotalQty";
pivotTable.DataFields.Add(pivotTable.Fields["AvgPrice"]) = "TotalPrice/TotalQty";
pivotTable.DataFields.Add(pivotTable.Fields["AvgPrice"]) = totPriceField / totQtyField;
pivotTable.DataFields.Add(pivotTable.Fields["AvgPrice"]).Function =
    OfficeOpenXml.Table.PivotTable.
    DataFieldFunctions.Product(totPriceField/totQtyField);

Ни один из этих взмахов даже не компилируется. Какую корректировку мне нужно внести или какой совершенно новый подход мне нужно использовать?

ОБНОВИТЬ

Я мог бы рассчитать значения, поместить их в лист данных и сослаться на них таким образом; но действительно ли это единственный/лучший способ сделать это?

ОБНОВЛЕНИЕ 2

Я сделал это (добавил vals непосредственно на лист, содержащий исходные данные для сводной таблицы):

var avgCell = rawDataWorksheet.Cells[_lastRowAddedRawData + 1, 9];
if ((TotalPrice < 0.0M) || (Quantity < 1))
{
    avgCell.Value = 0.0;
}
else
{
    avgCell.Value = TotalPrice / Quantity;
}

var prcntgCell = rawDataWorksheet.Cells[_lastRowAddedRawData + 1, 10];
if ((TotalPrice < 0.0M) || (MonthYear == String.Empty))
{
    prcntgCell.Value = 0.0;
}
else
{
    prcntgCell.Value = GetPercentageOfItemForMonthYear(TotalPrice, MonthYear);
}

private double GetPercentageOfItemForMonthYear(decimal totPrice, strin
    monthYear)
{
    decimal totalForMonthYear = monthlySales[monthYear];
    double prcntg = GetPercentageOfItem(totPrice, totalForMonthYear);
    return prcntg;
}

private double GetPercentageOfItem(decimal portionOfTotal, decimal  
    grandTotal)
{
    if ((portionOfTotal <= 0.0M) || (grandTotal <= 0.0M))
    {
        return 0.0;
    }
    if (portionOfTotal == grandTotal)
    {
        return 100.0;
    }
    double d = Convert.ToDouble(portionOfTotal) 
        / Convert.ToDouble(grandTotal) * 100;
    return Math.Round(d, 2);
}

... но все же хотелось бы знать, как это можно сделать с помощью вычисляемых полей во время создания сводной таблицы.


excel average calculated-columns epplus epplus-4
person B. Clay Shannon    schedule 26.10.2016    source источник

Ответы (1)


arrow_upward
5
arrow_downward

В настоящее время EPPlus не поддерживает вычисляемые поля в сводных таблицах. Я использовал следующий обходной путь:

using System.Linq;
using System.Xml;
using OfficeOpenXml.Table.PivotTable;
using ReflectionMagic;

public static ExcelPivotTableField AddCalculatedField(this ExcelPivotTable pivotTable, string calcFieldName, string formula)
{
    var dynamicPivotTable = pivotTable.AsDynamic();
    var maxIndex = pivotTable.Fields.Max(f => f.Index);
    const string schemaMain = @"http://schemas.openxmlformats.org/spreadsheetml/2006/main";
    var cacheTopNode = dynamicPivotTable.CacheDefinition.CacheDefinitionXml.SelectSingleNode("//d:cacheFields", dynamicPivotTable.NameSpaceManager);
    cacheTopNode.SetAttribute("count", (int.Parse(cacheTopNode.GetAttribute("count")) + 1).ToString());
    var cacheFieldNode = dynamicPivotTable.CacheDefinition.CacheDefinitionXml.CreateElement("cacheField", schemaMain);
    cacheFieldNode.SetAttribute("name", calcFieldName);
    cacheFieldNode.SetAttribute("databaseField", "0");
    cacheFieldNode.SetAttribute("formula", formula);
    cacheFieldNode.SetAttribute("numFmtId", "0");
    cacheTopNode.AppendChild(cacheFieldNode);

    var topNode = dynamicPivotTable.PivotTableXml.SelectSingleNode("//d:pivotFields", dynamicPivotTable.NameSpaceManager);
    topNode.SetAttribute("count", (int.Parse(topNode.GetAttribute("count")) + 1).ToString());
    XmlElement fieldNode = dynamicPivotTable.PivotTableXml.CreateElement("pivotField", schemaMain);
    fieldNode.SetAttribute("compact", "0");
    fieldNode.SetAttribute("outline", "0");
    fieldNode.SetAttribute("showAll", "0");
    fieldNode.SetAttribute("defaultSubtotal", "0");
    topNode.AppendChild(fieldNode);

    var excelPivotTableFieldType = typeof(ExcelPivotTableField).AsDynamicType();
    var excelPivotTableField = excelPivotTableFieldType.New((XmlNamespaceManager)dynamicPivotTable.NameSpaceManager, fieldNode, (ExcelPivotTable)dynamicPivotTable, maxIndex + 1, maxIndex + 1);
    excelPivotTableField.SetCacheFieldNode(cacheFieldNode);
    dynamicPivotTable.Fields.AddInternal(excelPivotTableField);

    return pivotTable.Fields.First(f => f.Name == calcFieldName);
}

Пример использования:

var calc1 = pivotTable.AddCalculatedField("Calc1", "BCol*BCol");
var dataField = pivotTable.DataFields.Add(calc1);
dataField.Function = DataFieldFunctions.Sum;
dataField.Name = "Override Name";
person jdx    schedule 23.03.2017
comment
чтобы использовать этот метод, необходимо добавить пакет nuget ReflectionMagic. Ссылка на репозиторий: github.com/ReflectionMagic/ReflectionMagic. - person Oleg Polezky; 26.06.2019
comment
Спасибо jdx, но у меня это работает частично: оно создает вычисляемое поле, но не добавляется в сводную таблицу. Я должен добавить его вручную.... почему? - person sports; 31.03.2020
comment
Хорошо, я решил это. Имя должно быть другим (Calc1 != Override Name), чтобы оно работало. - person sports; 17.04.2020