Creation of dynamic measure as well as data model in C#

Aspose.Cells Product Family
,
1

Hi all,

I have an excel sheet that has 3 columns, all if which contain string data and I need to create pivot table out of those 3. By default when we drag sting column in values section for creating a pivot, excel aggregates it to show count(numbers), but I need to show values(actual values in that column). I googled it and found out that we can do it in excel in this tutorial - How to Show Text in Pivot Table Values Area

Also while creating pivot, if we check on ‘Add this data to Data Model’ checkbox, then it allows us to create a new measure with DAX formula. I used the following formula for creating a custom measure on the column that I’ll add in values -
= CONCATENATEX(Range, Range[column3])

Adding this newly created measure to values section made excel show values in pivot and not count.

So my doubt is, can we automate all of this in C# code? I mean when I create a worksheet with data, can I also create a pivot table from that worksheet and add it to data model, create a dynamic measure and add it to values?

Sorry for such a long question and please let me know we anything else is needed from my side.

Thanks,
Pratik Ahire

2

@pratikahire,
Thank you for your query.
Could you please share your sample Excel file where you have created the Pivot Table and used CONCATENATEX(Range, Range[column3]) formula to achieve your desired results. We will analyze your sample file and share our feedback accordingly.

3

To Aspose.zip (40.9 KB)

Hi,

Sorry for late response.

I’ve attached a sample file as required, please check.

Thanks,
Pratik

4

@pratikahire,
We have understood your requirement but we need to look into it more. We have logged the issue in our database for investigation and for a sample code. Once, we will have some news for you, we will update you in this topic.

This issue has been logged as

CELLSNET-47335 – Show text in Pivot Table Values Area

5

@pratikahire,

We evaluated your issue in details. For now, we cannot support PivotTable with DataModel. So you cannot add custom PivotField such as:

“= CONCATENATEX(Range, Range[column3])”

If you want to show text values like the tutorial How to Show Text in Pivot Table Values Area,
you can use the following code to implement it:
e.g
Sample code:

Workbook wb = new Workbook(filePath + "To Aspose.xlsx");
Worksheet sheet = wb.Worksheets.Add("Test");
int pivotIndex = sheet.PivotTables.Add("=ExportPOC2_AggregateData7_M!$A$1:$D$24", "A3", "TestPivot");
PivotTable pivot = sheet.PivotTables[pivotIndex];
pivot.AddFieldToArea(PivotFieldType.Row, 0);
pivot.AddFieldToArea(PivotFieldType.Column, 1);
pivot.AddFieldToArea(PivotFieldType.Data, 3);

pivot.PivotTableStyleType = PivotTableStyleType.PivotTableStyleLight16;

pivot.RefreshDataFlag = true;
pivot.RefreshData();
pivot.CalculateData();
pivot.RefreshDataFlag = false;

PivotFormatConditionCollection pfcc = pivot.PivotFormatConditions;
int pIndex = pfcc.Add();
PivotFormatCondition pfc = pfcc[pIndex];
FormatConditionCollection fcc = pfc.FormatConditions;
CellArea dataBodyRange = pivot.DataBodyRange;

string startCell = CellsHelper.CellIndexToName(dataBodyRange.StartRow, dataBodyRange.StartColumn);
fcc.AddArea(dataBodyRange);

//you can add other format conditions
//now we will replace 1 with "Direct" 
int idx = fcc.AddCondition(FormatConditionType.Expression);
FormatCondition fc = fcc[idx];
fc.Formula1 = "=" + startCell + "=1";
fc.Operator = OperatorType.Equal;
fc.Style.Custom = "[=1]\"Direct\";;";

//replace 8 with "Direct8" 
idx = fcc.AddCondition(FormatConditionType.Expression);
fc = fcc[idx];
fc.Formula1 = "=" + startCell + "=8";
fc.Operator = OperatorType.Equal;
fc.Style.Custom = "[=8]\"Direct8\";;";

wb.Save(filePath + "out.xlsx");

Please try it and let us know your feedback.