Few Pivot tables are not rendering

Aspose.Cells Product Family
,
1

Hi,

We use Aspose.Cells 24.10.0 to create pivot tables and charts for out .net 8 project
The excel template which we use to generate report was working fine for a long period.
But from the past 2 months we observed Pivot tables are not rendered correctly (Data source is being deleted after mailMerge)

I tried creating a dummy new pivot table in the same template.
The new pivot table loads correctly, but old pivot tables were empty.

Not sure what has caused this error, But old pivot table template which was working for few years are now not working.

Below is the customized template we use
pivot table Report.zip (5.4 MB)

DotNet code

  private static void RenderDocument(Workbook document, CancellationToken cancellationToken = default(CancellationToken))
  {
  	DocumentGenerationModel generationModel = new DocumentGenerationModel();
  	generationModel.Parameters = new Dictionary<string, object> { { "PERSONNAME", "Jagan" } };

  	List<TemplateSequenceParameter> templateSequenceParameters = new List<TemplateSequenceParameter>
  	{
  		new TemplateSequenceParameter
  		{
  			Key = "<<[INPUT.SEQUENCE.TICKETID]>>",
  			Values = new List<string> { "323", "324", "325" }
  		},
  		new TemplateSequenceParameter
  		{
  			Key = "<<[INPUT.SEQUENCE.TICKETSTATUS]>>",
  			Values = new List<string> { "New", "Closed", "Open" }
  		}
  	};

  	generationModel.SequenceParameters = templateSequenceParameters;


  	foreach (Worksheet worksheet in document.Worksheets)
  	{
  		foreach (KeyValuePair<string, object> parameter in generationModel.Parameters)
  		{
  			cancellationToken.ThrowIfCancellationRequested();

  			Cell parameterCell = worksheet.Cells.Find(string.Format(ParameterFormat, parameter.Key), null);
  			if (parameterCell != null)
  			{
  				parameterCell.PutValue(parameter.Value);
  			}
  		}

  		foreach (Core.Models.TemplateSequenceParameter sequenceParameter in generationModel.SequenceParameters)
  		{
  			cancellationToken.ThrowIfCancellationRequested();

  			Cell sequenceParameterCell = worksheet.Cells.Find(string.Format(SequenceParameterFormat, sequenceParameter.Key), null);
  			if (sequenceParameterCell != null)
  			{
  				worksheet.Cells.ImportObjectArray(sequenceParameter.Values.ToArray(), sequenceParameterCell.Row, sequenceParameterCell.Column, true);
  			}
  		}
  	}

  	foreach (Worksheet worksheet in document.Worksheets)
  	{
  		foreach (PivotTable pt in worksheet.PivotTables)
  		{
  			for (int i = 0; i < pt.RowFields.Count; i++)
  			{
  				pt.RowFields[i].HideDetail(false);
  			}

  			pt.RefreshDataFlag = false;
  			pt.CalculateData();
  			pt.RefreshDataFlag = true;
  		}
  	}
  }
2

@mjjagan

It seems that you are experiencing issues with rendering old pivot tables in your Excel template after performing a mail merge. Here are some steps and considerations that might help you troubleshoot and resolve the problem:

  1. Refresh and Calculate Pivot Tables: Ensure that you are refreshing and calculating the pivot tables after the mail merge operation. In your code, you are already iterating through the pivot tables and calling CalculateData() and setting RefreshDataFlag. However, make sure that the data source for the old pivot tables is still valid after the mail merge. If the data source is deleted or altered, the pivot tables may not render correctly.

    Here’s a simplified example of how to refresh and calculate pivot tables:

    foreach (Worksheet worksheet in document.Worksheets)
{
    foreach (PivotTable pt in worksheet.PivotTables)
    {
        pt.RefreshData();
        pt.CalculateData();
    }
}

  2. Check Data Source: Verify that the data source for the old pivot tables is intact and accessible. If the data source is being deleted during the mail merge, you may need to adjust your mail merge logic to preserve the data required for the pivot tables.

  3. Load Options: If you are loading the workbook with specific options, consider enabling the option to parse cached records. This can be done by setting ParsingPivotCachedRecords to true when loading the workbook. This might help in retaining the necessary data for the pivot tables.

    Example of setting load options:

    LoadOptions options = new LoadOptions();
options.ParsingPivotCachedRecords = true;
Workbook wb = new Workbook("yourfile.xlsx", options);

  4. Testing with a New Template: Since you mentioned that a new pivot table works correctly, it might be beneficial to compare the configurations of the old and new pivot tables. Check for any differences in the data source, fields, or settings that might affect rendering.

  5. Review Documentation and Examples: You can refer to the Aspose documentation and examples for more insights on handling pivot tables. For instance, the example on refreshing and calculating pivot tables can provide additional context on how to manage them effectively [1].

If the issue persists after trying these steps, consider sharing more details about the specific configurations of the old pivot tables or any error messages you might be encountering.

Sources:
[1]: Refresh and Calculate Items

3

@mjjagan
We have reviewed the sample code you provided, which includes some unrecognized classes. Would you like to provide runnable test code? We will check it soon.

Additionally, please refer to the following example code to refresh and calculate pivot tables.

foreach (Worksheet worksheet in document.Worksheets)
{
    foreach (PivotTable pt in worksheet.PivotTables)
    {
        for (int i = 0; i < pt.RowFields.Count; i++)
        {
            pt.RowFields[i].HideDetail(false);
        }

        pt.RefreshDataFlag = true;
        pt.RefreshData();
        pt.CalculateData();
        pt.RefreshDataFlag = false;
    }
}
4

Thanks for your suggestion.

We were facing this issue when using Aspose cells 24.10.0 version.
however after upgrading it to latest version 25.1.2 it is now working.

I guess we have some issue in 24.1.0
But our pivot tables are working with latest version of Aspose Cells.

5

@mjjagan
You are welcome. Thank you for your feedback. I’m glad you solved the issue by using the latest version v25.1.2. If you have any questions, please feel free to contact us at any time.