How to change the font size of a pivot table?

[Matthew Coombe]

Good day. 

I currently have a workbook with a data sheet and a pivot sheet. The pivot sheet contains a pivot table which is populated with the data in the data sheet. I am trying to change the font size of all the entries in the pivot table. However, when trying to use pivotTable.GetCellFormat("A1:A2"); it only formats the first cell (A1) correctly. In addition to this I am experiencing another issue where the pivot table only shows 1 cell in protected view in excel on first launch. Only once enabling editing in excel does the pivot table populate. My code is below.

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

                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;

                pivotTableImpl.Cache.IsRefreshOnLoad = true;

                IPivotCellFormat pivotCellFormat = pivotTable.GetCellFormat("A1:A2");

                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();

            }

There are screenshots attached. One of them shows the pivot table in protected view (just populating one cell). The other shows what it looks like once edit mode has been enabled. As you can see, only the first cell has been formatted, despite me calling it one 2 cells here:  pivotTable.GetCellFormat("A1:A2");. Any help is appreciated.

Thanks,

Matthew

[Konduru Keerthi Konduru Ravichandra Raju]

Hi Matthew, 

Greetings from Syncfusion. 

We are able to reproduce the issue reported with font and validating it currently. We will share the validation details on December 15^th,2021. 

Regarding pivot table being loaded only after enable editing, this issue arises in Microsoft Excel when the file is downloaded in a browser. This is not an issue with Syncfusion XlsIO. 

Regards, 

Keerthi. 

[Matthew Coombe]

Thanks for the reply. Is there any way you know of to avoid the protected view issue that Microsoft Excel causes?

I look forward to seeing the validation details.

Many thanks,

Matthew

[Konduru Keerthi Konduru Ravichandra Raju]

Hi Matthew, 

Files that are downloaded from browsers are opened in Read-Only mode or Protected View, because these files might be unsafe. You can disable this option in your Trust Center Settings in Excel based on your convenience and security. 

Regards, 

Keerthi. 

[Konduru Keerthi Konduru Ravichandra Raju]

Hi Mathew, 

We appreciate your patience. 

We suggest you to first Layout the pivot table before enabling IsRefreshOnLoad property and applying font, to overcome the issue. Please find the code snippet below. 

Code Snippet: 

pivotTable.Layout(); 

PivotTableImpl pivotTableImpl = pivotTable as PivotTableImpl; 

pivotTableImpl.Cache.IsRefreshOnLoad = true; 

IPivotCellFormat pivotCellFormat = pivotTable.GetCellFormat("A1:A2"); 

pivotCellFormat.FontName = "Calibri"; 

pivotCellFormat.FontSize = 8; 

pivotCellFormat.Bold = true; 

Kindly try the code snippet and let us know if this helps. 

Regards, 

Keerthi. 

[Matthew Coombe]

Hi Keerthi,

Thank you again for the reply. 

I have tried your above suggestion and it works perfectly. Many thanks for that! 

Just in regards to the previous issue with the protected view. Now that I have added pivotTable.Layout();, it behaves like this in protected view: 

As you can see, it is as if it mostly loads the dataFields values but not the titles in the first column properly. Is there really no way to ensure it populates everything so that I can open it in protected view and see the pivot table correctly?

Thanks again,

Matthew

[Pandikumar Duraivel]

Hi Matthew, 

 

As per Microsoft Excel behavior, the pivot table will be populated with all fields once the Enable editing option button is clicked. Hence it is not possible to show the pivot table in protected view. 

 

Regards, 

Pandi kumar D. 

[Matthew Coombe]

Hi Pandi,

Thank you for getting back to me and assisting me. I appreciate your time.

Regards,

Matthew

[Konduru Keerthi Konduru Ravichandra Raju]

Hi Matthew, 

You are Welcome. 

Regards, 

Keerthi.