EPPlus: Values in column of pivot table

1
votes

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;
}

What I want to achieve

3
c#epplusepplus-4
That's the symbol for Sum/Total. The symbol doesn't matter anyway, values only appear as aggregates in a pivot table's cells. The GUI simply selects an appropriate default depending on the value type, eg sum for numbers, count for text etc. You'll have to specify this explicitly in your code – Panagiotis Kanavos
BTW where is the code? – Panagiotis Kanavos
@PanagiotisKanavos I added code, but this won't help with my question. – Jerome Reinländer
There's no Σ column or field in the GUI. That's just the values region in the pivot table. It uses Σ because that's the math symbol for sums. The fields you set there are set throu the DataFields collection in the API. – Panagiotis Kanavos
That's not a regular field. Check this explanation. In this simple case it doesn't really matter. If you add more column fields though it does - it controls whether the totals will appear inside each column group or vice versa. Which means the important question is how to change its order. – Panagiotis Kanavos

3 Answers

1
votes

try to change Values Row filed to column. Here 3 is Values filed index.

  PivotField row = (PivotField)oPivotTable.RowFields[3];
  row.Orientation = XlPivotFieldOrientation.xlColumnField;
3
votes

Try to set pivotTable.DataOnRows = false to get such pivot table behaviour

0
votes

I have found solution for your case. You should add a couple strings in your code for datafields. Example for datafield "stressField":

    stressField.Field.Outline = true;
    stressField.Field.ShowInFieldList=true;

As well as for other:

    pessimisticField.Field.Outline = true;
    pessimisticField.Field.ShowInFieldList=true;

    mediumField.Field.Outline = true;
    mediumField.Field.ShowInFieldList=true;

    optimisticField.Field.Outline = true;
    optimisticField.Field.ShowInFieldList=true;