Editing the pivot table filters changes cell format
Good day,
I hope you are well.
I am just posting with regards to an issue I am experiencing when creating a pivot table with a certain cell format. The issue occurs when I change the filter of the pivot table, or make any edit to the pivot table (such as expanding column fields or changing the sort direction). It seems to undo the formatting that was previously applied to the heading cells. My code and screenshots are below.
As you can see, formatting is applied correctly at the start:
Then I change sort direction as below:
Finally, you can see the font size changes from 8 to 11 below:
Please let me know if there is any fix for this problem.
Many thanks in advance,
Matthew
Code:
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2016;
IWorkbook workbook = application.Workbooks.Create(2);
IWorksheet dataSheet = workbook.Worksheets[1];
dataSheet.Name = "Data";
dataSheet.ImportData(data, 1, 1, true);
IWorksheet pivotSheet = workbook.Worksheets[0];
pivotSheet.Name = "PivotSheet";
IPivotCache pivotCache = workbook.PivotCaches.Add(dataSheet.Range);
IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], pivotCache);
pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium15;
IPivotField pivotFieldRow = pivotTable.Fields["Description"];
pivotFieldRow.Axis = PivotAxisTypes.Row;
pivotFieldRow.AutoSort(PivotFieldSortType.Ascending, 1);
pivotFieldRow.Name = "Description";
IPivotField pivotFieldColumn = pivotTable.Fields["Period"];
pivotFieldColumn.Axis = PivotAxisTypes.Column;
pivotFieldColumn.AutoSort(PivotFieldSortType.Ascending, 0);
pivotFieldColumn.Name = "Period";
IPivotField pivotField = pivotTable.Fields["InclusiveAmount"];
pivotTable.DataFields.Add(pivotField, "Values", PivotSubtotalTypes.Sum);
pivotTable.DataFields[0].NumberFormat = "# ##0.00";
PivotTableImpl pivotTableImpl = pivotTable as PivotTableImpl;
pivotTable.Layout();
pivotTableImpl.Cache.IsRefreshOnLoad = true;
pivotTable.ColumnGrand = false;
pivotTable.RowGrand = false;
var columnCount = pivotTable.Location.LastColumn - 1;
var rowCount = pivotTable.Location.LastRow;
var bottomRightCell = ((char)(columnCount + 64)).ToString() + rowCount;
var formatStr = string.Format("A1:{0}", bottomRightCell);
IPivotCellFormat pivotCellFormat = pivotTable.GetCellFormat(formatStr);
pivotCellFormat.FontName = "Calibri";
pivotCellFormat.FontSize = 8;
Stream stream = new FileStream("pivotTable.xlsx", FileMode.OpenOrCreate, FileAccess.ReadWrite);
workbook.SaveAs(stream, ExcelSaveType.SaveAsXLS);
workbook.Close();
excelEngine.Dispose();
stream.Close();
}
