Aspose Cells Java : Pivot Table : Font Size

Aspose.Cells Product Family
,
1

Hi Team,

In the earlier Aspose Cells Java version 18.5, we used the following code to set the font size in a pivot table:

Style dxfStyle = workbook.createStyle();
Font font = dxfStyle.getFont();
font.setSize(8);
pivotTable.formatAll(dxfStyle);

However, due to the line pivotTable.formatAll(dxfStyle);, the number formats are not working in the pivot table. We have commented out this line to fix the number formats issue.

So now how can we set the font size in the pivot table? Is there an alternative approach? Please let us know.

2

@koteswaragunda

To set the font size in a pivot table using Aspose.Cells for Java without affecting the number formats, you can customize the styles of the pivot table without using the formatAll method. Instead, you can apply styles to specific areas of the pivot table, such as the header or data cells, individually.

Here’s an alternative approach to set the font size:

  1. Create a Style: Define a new style and set the desired font size.
  2. Apply the Style: Instead of using pivotTable.formatAll(dxfStyle);, apply the style to specific ranges or areas of the pivot table.

Here’s a code example demonstrating this approach:

// Create a workbook and a pivot table
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.getWorksheets().get(0);
PivotTable pivotTable = worksheet.getPivotTables().add("A1", "B1", "C10");

// Create a new style
Style dxfStyle = workbook.createStyle();
Font font = dxfStyle.getFont();
font.setSize(8); // Set the desired font size

// Apply the style to the pivot table header
Range headerRange = pivotTable.getHeaderRange();
headerRange.applyStyle(dxfStyle, new StyleFlag() {{ setFont(true); }});

// Apply the style to the data area
Range dataRange = pivotTable.getDataRange();
dataRange.applyStyle(dxfStyle, new StyleFlag() {{ setFont(true); }});

// Save the workbook
workbook.save("PivotTableWithCustomFontSize.xlsx");

In this example, we create a new style and apply it specifically to the header and data ranges of the pivot table. This way, you can customize the font size without disrupting the number formats in the pivot table.

For more detailed information on customizing pivot table styles, you can refer to the Aspose.Cells documentation on this topic.

Sources:
[1]: Customizing Pivot Table Styles | Aspose.Cells Java Excel Processing API

3

@koteswaragunda
Please use CellsFactory.createStyle() to create a style. Because Workbook.CreateStyle() will copy the default style settings.

The sample code as follows:

CellsFactory factory = new CellsFactory();
Style dxfStyle = factory.createStyle();
4

Hi @John.He, As you mentioned i created the style from cells factory only but how to apply the style to entire pivottable is the concern. I have also checked the AI generated code but the below library methods not exists it seems. please do needful.

getHeaderRange and getDataRange not exists.

// Apply the style to the pivot table header
Range headerRange = pivotTable.getHeaderRange();
headerRange.applyStyle(dxfStyle, new StyleFlag() {{ setFont(true); }})

// Apply the style to the data area
Range dataRange = pivotTable.getDataRange();
dataRange.applyStyle(dxfStyle, new StyleFlag() {{ setFont(true); }});

5

@koteswaragunda
Sorry for any inconvenience caused to you. AI gave you an incorrect response. The two methods mentioned in AI for obtaining ranges do not exist. If you want to obtain the range of the pivot table, please refer to the following document.

Additionally, applying styles through the range of pivot tables is invalid. You need to use the PivotTable.format methods.

6

Thanks @John.He for now i followed the steps advised in the other post. Will wait for your release which has the fix for Row Labels and Number formats in Pivot Table.

7

@koteswaragunda
Thank you for your feedback. You are welcome. Once the issue is fixed, we will notify you promptly.