We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

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

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 - https://www.contextures.com/pivottabletextvalues.html

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.

Pratik Ahire

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.

To Aspose.zip (40.9 KB)


Sorry for late response.

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


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


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 https://www.contextures.com/pivottabletextvalues.html,
you can use the following code to implement it:
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.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);

//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.