When you create a pivot table in Excel and add fields to Rows and Values, you get an additional field [Symbol Sigma] Values
in the GUI, that you can drag and drop to Columns.
I am creating a pivot table
using EPPlus
. How can I add this [Symbol Sigma] Values
field to my column fields?
Edit: Here is some code. I dont' see how this is supposed to help, what it was asked for.
private static ExcelWorksheet CreatePivotWorksheet(ExcelPackage excel, ExcelWorksheet dataWorksheet)
{
string worksheetName = "Pivot";
ExcelWorksheet pivotWorksheet = excel.Workbook.Worksheets.Add(worksheetName);
ExcelRangeBase dataRange = dataWorksheet.Cells[dataWorksheet.Dimension.Address];
ExcelPivotTable pivotTable = pivotWorksheet.PivotTables.Add(pivotWorksheet.Cells[1,1], dataRange, "pivotTable");
pivotTable.RowGrandTotals = false;
pivotTable.RowFields.Add(pivotTable.Fields["BaseValue"]);
pivotTable.RowFields.Add(pivotTable.Fields["Remaining Runtime"])
.AddNumericGrouping(0, 500000, 30);
pivotTable.RowFields.Add(pivotTable.Fields["Emittent"]);
pivotTable.RowFields.Add(pivotTable.Fields["CountIfs"]);
pivotTable.RowFields.Add(pivotTable.Fields["ISIN"]);
var stressField = pivotTable.DataFields.Add(pivotTable.Fields["StressScenario - Percent"]);
stressField.Function = DataFieldFunctions.Average;
stressField.Format = "0.00%";
var pessimisticField = pivotTable.DataFields.Add(pivotTable.Fields["PessimisticScenario - Percent"]);
pessimisticField.Function = DataFieldFunctions.Average;
pessimisticField.Format = "0.00%";
var mediumField = pivotTable.DataFields.Add(pivotTable.Fields["MediumScenario - Percent"]);
mediumField.Function = DataFieldFunctions.Average;
mediumField.Format = "0.00%";
var optimisticField = pivotTable.DataFields.Add(pivotTable.Fields["optimisticScenario - Percent"]);
optimisticField.Function = DataFieldFunctions.Average;
optimisticField.Format = "0.00%";
// remove subtotals, this has to be done _after_ adding the field (see https://stackoverflow.com/a/34768357/5909613)
foreach (ExcelPivotTableField rowField in pivotTable.RowFields)
{
rowField.SubTotalFunctions = eSubTotalFunctions.None;
}
return pivotWorksheet;
}
try to change Values Row filed to column. Here 3 is Values filed index.
PivotField row = (PivotField)oPivotTable.RowFields[3];
row.Orientation = XlPivotFieldOrientation.xlColumnField;