Hi ,
I have a use case where I am saving all the pivot table details like the field name and its orientation to a hidden sheet. Then I am clearing the pivot table to remove all the data and saving back the workbook as a template.
Now when opening this template I am getting the pivot table details from the hidden sheet and reconstructing the pivot table.
The below function mostly achieve this functionality.
private void SelectFieldsFromDetails(PivotTable pt, string fieldDetails)
{
string[] detailsArray = fieldDetails.Split(',');
foreach (string detail in detailsArray)
{
string[] fieldInfo = detail.Split(':');
string fieldName = fieldInfo[0];
string orientation = fieldInfo[1];
XlPivotFieldOrientation pivotOrientation = (XlPivotFieldOrientation)Enum.Parse(typeof(XlPivotFieldOrientation), orientation, ignoreCase: true);
if (pivotOrientation == XlPivotFieldOrientation.xlDataField)
{
pt.AddDataField(pt.CubeFields[fieldName]);
// After making field changes manual update is being reset, so setting it again
pt.ManualUpdate = true;
}
else
{
pt.CubeFields[fieldName].Orientation = pivotOrientation;
// After making field changes manual update is being reset, so setting it again
pt.ManualUpdate = true;
}
}
}
The issue is for OLAP pivot table when trying to set orientation to DataField or PageField using
pt.CubeFields[fieldName].Orientation = pivotOrientation; it gives an error. I was able to use pivottable.AddDataField() method to add the datafield to the pivot table. How to add the pageField to the pivot table also if I have the details of items that was selected in the page field how would I select them as well
