Excel - Pivot issue : Row Labels and Number formats not working when Summary fields added to Pivot table

Aspose.Cells Product Family
,
1

Hi Team,
A Very Happy New Year 2025!

I am facing an issue with excel generated output having summary fields in pivot table. First column of the field name is getting overwritten by “Row Labels” and Number formats not applied to the summary field.

FYI, aspose excel version used is, 24.10

I have enclosed the sample program, input/output excel files. Also enclosed the screenshot of the issue (highlighted the issue portions in RED circles).

Please do me a favor ASAP.

input file name: 42043.xlsx
output file name: 42043-pivot.xlsx

Thank You.
excel-pivot-summary-field-issue.zip (167.9 KB)

excel-pivot-summary-field-issue.png (56.9 KB)

2

@koteswaragunda

It seems you are experiencing issues with the generated Excel output from a pivot table, specifically with the first column being labeled as “Row Labels” and the number formats not being applied to the summary fields. Here are some steps you can take to address these issues:

  1. Row Labels Issue: If the first column of your pivot table is being overwritten with “Row Labels,” you can customize the pivot table to set specific names for your row fields. This can be done by accessing the pivot table’s fields and renaming them as needed.

  2. Number Formatting: To ensure that number formats are applied correctly to your summary fields, you can explicitly set the number format for each data field in the pivot table. For example, you can use a format like “$#,##0.00” for currency values.

Here is a sample code snippet that demonstrates how to create a pivot table and apply number formatting:

using Aspose.Cells;

public class CreatePivotTableExample
{
    public static void Run()
    {
        // Create a workbook and add data
        Workbook workbook = new Workbook();
        Worksheet sheet = workbook.Worksheets[0];
        Cells cells = sheet.Cells;

        // Add data to the worksheet
        cells["A1"].PutValue("Category");
        cells["B1"].PutValue("Sales");
        cells["A2"].PutValue("A");
        cells["B2"].PutValue(100);
        cells["A3"].PutValue("B");
        cells["B3"].PutValue(200);

        // Create a pivot table
        int index = sheet.PivotTables.Add("=A1:B3", "D1", "PivotTable1");
        PivotTable pivotTable = sheet.PivotTables[index];

        // Add fields to the pivot table
        pivotTable.AddFieldToArea(PivotFieldType.Row, 0); // Category
        pivotTable.AddFieldToArea(PivotFieldType.Data, 1); // Sales

        // Set number format for the data field
        pivotTable.DataFields[0].NumberFormat = "$#,##0.00";

        // Save the workbook
        workbook.Save("PivotTableOutput.xlsx");
    }
}

Make sure to adjust the range and field indices according to your specific data structure. This code will create a pivot table and apply the specified number format to the sales data.

If you continue to face issues, please check the version of Aspose.Cells you are using, as there may be updates or bug fixes in newer versions that could resolve your problem.

Feel free to reach out if you need further assistance!

Sources:
[1]: CreatePivotTableWithFormatting.cs
[2]: CreatePivotTable.cs

3

@koteswaragunda
1,“Row Labels”
Please try the latest version 24.12. We have fixed this issue.

2,Please comment the line 588
//pt.formatAll(dxfStyle);

If you set pivot table format, all settings of pivot field will be covered .

4

Hi @simon.zhao

  1. “Row Labels” issue still facing in 24.12 version too. I have observed that “Row Labels” issue is coming when i add summary fields only.
  2. Number formats now applied after commenting “pt.formatAll(dxfStyle)” but it worked fine in older aspose cells version i.e. 18.5. We have used this portion of the code to set the font size in pivot table. As I have commented the line, now the font size is not applied. How to set the font size in pivot table?

Please suggest. Its P1 case for us. We have to address the customer ASAP.

5

@koteswaragunda,

Thanks for your feedback.

We will evaluate your issues and get back to you soon.

6

Thanks @amjad.sahi I am waiting for your response.

7

@koteswaragunda
By testing with sample files and code on the latest version v24.12, we can reproduce the issue. Found that Row Labels and Number formats are not working when Summary fields added to Pivot table .

We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): 
CELLSJAVA-46216: Row Labels not working when Summary fields added to Pivot table
CELLSJAVA-46225: Number formats not working after calling PivotTable.formatAll(dxfStyle)

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.

8

Thanks, @John.He, for the information. However, it worked fine with Aspose Cells Java older version 18.5. Until the issue on your side is fixed, we would like to downgrade to version 18.5. Could you please let us know if the newer license supports the downgraded version? Are there any alternatives? FYI, this is a P1 case for us. Please let us know any alternatives to use the downgraded version with the new license key.

9

@koteswaragunda
Thanks for your feedback. The new license is signed by sha256, but the old license is signed by SHA1. Older versions of the API cannot use the sha256 license. You can request an updated (New) license (to the sales team). This license must be signed in SHA1. If so, the license is compatible with Asose.Cells old version.

10

@koteswaragunda

1, Row Labels:
We have fixed this issue and will release the next version within 1 - 2 week.
With current version, please make the field not show as compact form as the following:
asposePivotTable.addCalculatedField(name, “=” + formula, true);
PivotFieldCollection bFields = asposePivotTable.getBaseFields();
bFields.get(bFields.getCount() -1).setShowCompact(false);

2,Number formats:
Please create style with CellsFactory.createStyle(). Workbook.CreateStyle() will copy default style setting.
com.aspose.cells.CellsFactory factory = new com.aspose.cells.CellsFactory();
Style dxfStyle = factory.createStyle();

11

Thanks @simon.zhao seems issue is resolved by following the mentioned steps. Thanks all for your prompt advises. Just wanted to check, is it a temporary fix? can i remove this advise once the fix is in place in the newer version?

12

@koteswaragunda
Thank you for your feedback. The issues mentioned will be fixed in the new version. You will no longer need this temporary solution. But workbook.createStyle() must be changed. You can use CellsFactory.createStyle() or newly added Workbook.createStyle(false). Once the version is released, we will notify you immediately.

13

Thanks @John.He and Thank you All

14

@koteswaragunda
Thank you for your feedback. You are welcome. If you have any questions, please feel free to contact us at any time.