/// <summary>
/// Applies a function specified by the <paramref name="dataFieldFunction"/>
/// over the specified collection of <paramref name="values"/>.
/// </summary>
/// <param name="dataFieldFunction">The dataField function to be applied.</param>
/// <param name="values">The values to apply the function to.</param>
/// <returns>The result of the function.</returns>
public object Calculate(DataFieldFunctions dataFieldFunction, List <object> values)
{
string function = this.GetCorrespondingExcelFunction(dataFieldFunction);
if (values == null || values.Count == 0 || values.All(v => v == null))
{
return(null);
}
if (function.IsEquivalentTo("PRODUCT") && values.All(v => v == null || string.IsNullOrWhiteSpace(v.ToString())))
{
return(0);
}
// Write the values into a temp worksheet.
int row = 1;
for (int i = 0; i < values.Count; i++)
{
row = i + 1;
this.TempWorksheet.Cells[row, TotalsFunctionHelper.DataColumn].Value = values[i];
}
var resultCell = this.TempWorksheet.Cells[row + 1, TotalsFunctionHelper.DataColumn];
resultCell.Formula = $"={function}(A1:A{row})";
resultCell.Calculate();
return(resultCell.Value);
}
public PivotDataField(string fieldName, string displayName = null, DataFieldFunctions funtion = DataFieldFunctions.Average)
{
this.FieldName = fieldName;
if (string.IsNullOrWhiteSpace(displayName))
{
this.DisplayName = fieldName;
}
else
{
this.DisplayName = displayName;
}
this.Function = funtion;
}
private string GetCorrespondingExcelFunction(DataFieldFunctions dataFieldFunction)
{
switch (dataFieldFunction)
{
case DataFieldFunctions.Count:
return("COUNTA");
case DataFieldFunctions.CountNums:
return("COUNT");
case DataFieldFunctions.None: // No function specified, default to sum.
case DataFieldFunctions.Sum:
return("SUM");
case DataFieldFunctions.Average:
return("AVERAGE");
case DataFieldFunctions.Max:
return("MAX");
case DataFieldFunctions.Min:
return("MIN");
case DataFieldFunctions.Product:
return("PRODUCT");
case DataFieldFunctions.StdDev:
return("STDEV.S");
case DataFieldFunctions.StdDevP:
return("STDEV.P");
case DataFieldFunctions.Var:
return("VAR.S");
case DataFieldFunctions.VarP:
return("VAR.P");
default:
throw new InvalidOperationException($"Invalid data field function: {dataFieldFunction}.");
}
}
/// <summary>
/// Applies a function specified by the <paramref name="dataFieldFunction"/>
/// over the specified collection of <paramref name="values"/>.
/// </summary>
/// <param name="dataFieldFunction">The dataField function to be applied.</param>
/// <param name="values">The values to apply the function to.</param>
/// <returns>The result of the function.</returns>
public object Calculate(DataFieldFunctions dataFieldFunction, List <object> values)
{
if (values == null || values.Count == 0)
{
return(null);
}
// Write the values into a temp worksheet.
int row = 1;
for (int i = 0; i < values.Count; i++)
{
row = i + 1;
this.TempWorksheet.Cells[row, TotalsFunctionHelper.DataColumn].Value = values[i];
}
var resultCell = this.TempWorksheet.Cells[row + 1, TotalsFunctionHelper.DataColumn];
resultCell.Formula = $"={this.GetCorrespondingExcelFunction(dataFieldFunction)}(A1:A{row})";
resultCell.Calculate();
return(resultCell.Value);
}
public EPPlusColumnFormatAttribute(
EPPlusColumnFormats format = EPPlusColumnFormats.Default,
EPPlusColumnTotalFormula total_formula = EPPlusColumnTotalFormula.None,
string date_format = @"dd/mm/yyyy",
string date_time_format = @"dd/mm/yyyy hh:mm",
string currency_format = @"#,##0.00 €",
string percent_format = @"#0.00%",
EPPPlusPivotTablePosition pivottable_position = EPPPlusPivotTablePosition.none,
DataFieldFunctions pivottable_function = DataFieldFunctions.Count,
ExcelHorizontalAlignment horizontal_alignment = ExcelHorizontalAlignment.General
)
{
this.format = format;
this.total_formula = total_formula;
this.date_format = date_format;
this.date_time_format = date_time_format;
this.currency_format = currency_format;
this.percent_format = percent_format;
this.pivottable_position = pivottable_position;
this.pivottable_function = pivottable_function;
this.horizontal_alignment = horizontal_alignment;
}
internal static void addDataFieldToPivot(ExcelPivotTable pivot, string fieldName, DataFieldFunctions function, string displayName)
{
ExcelPivotTableDataField fieldD = pivot.DataFields.Add(pivot.Fields[fieldName]);
fieldD.Function = function;
if (displayName.Length != 0)
{
fieldD.Name = displayName;
}
}
internal static void addDataFieldToPivot(ExcelPivotTable pivot, string fieldName, DataFieldFunctions function)
{
addDataFieldToPivot(pivot, fieldName, function, String.Empty);
}
public PivotDataField(string fieldName, string displayName = null, DataFieldFunctions funtion = DataFieldFunctions.Average)
{
this.FieldName = fieldName;
if (string.IsNullOrWhiteSpace(displayName))
{
this.DisplayName = fieldName;
}
else
{
this.DisplayName = displayName;
}
this.Function = funtion;
}
