Aspose.Cells Pivot PrintArea

Aspose.Cells Product Family
1

When shifting from Aspose Cells v 25.3.0 to 25.4.0, the PrintArea behavior changed.

Background: given an excel spreadsheet like this:

Year Product Gross Net - - -
2001 Apples 100 30 Row Labels Sum of Net
Bananas 200 60 Apples 4350
Cranberries 300 90 Bananas 4650
2002 Apples 400 120 Cranberries 4950
Bananas 500 150 Grand Total 13950
…

Pivot table is F2:G6

The following code

private static HtmlSaveOptions _pivotSaveOptions = new HtmlSaveOptions(SaveFormat.Html)
{
    ExportPrintAreaOnly = true,
    ExportActiveWorksheetOnly = true,
};

// omitted for brevity

pivot.RefreshData();
pivot.CalculateData();
sheet.Workbook.Worksheets.ActiveSheetIndex = sheet.Index;
sheet.PageSetup.PrintArea = pivot.TableRange1.ToString().SubstringAfter("(").SubstringBefore(")");
sheet.Workbook.Save(stream, _pivotSaveOptions);

Yields an empty 2x5 table.

However, in debug mode, if I set the PrintArea to be:

  • A2:G6 => the pivot table HTML includes all pivot table data
  • B2:G6 => the pivot table HTML includes all pivot table data
  • C2:G6 => the pivot table Cranberries total is empty
  • D2:G6 => all pivot table Sums are empty; Cranberries label is missing
  • E2:G6 => a 3x5 empty table
  • F2:G6 => a 2x5 empty table

Our workaround is to revert to version 25.3.0.

Any word on a bug fix or other workaround?

Also, how do I upload a spreadsheet here? The source is available at:

https://github.com/quandis/qbo3-Documentation/raw/refs/heads/master/Charts.xlsx

2

@epatrick

It seems that you are experiencing issues with the PrintArea behavior when transitioning from Aspose.Cells version 25.3.0 to 25.4.0. The problem appears to be related to how the PrintArea is set for the pivot table, resulting in an empty output when exporting to HTML.

To address this, you can try the following steps:

  1. Verify the PrintArea: Ensure that the PrintArea is correctly set to encompass the entire range of the pivot table. In your case, it seems that setting the PrintArea to specific ranges (like A2:G6) yields different results. You may want to experiment with the exact range that includes all necessary data.

  2. Use HtmlSaveOptions: Make sure you are using the HtmlSaveOptions correctly. You have already set ExportPrintAreaOnly to true, which is good. This option ensures that only the specified PrintArea is exported.

  3. Check for Updates: Since you mentioned reverting to version 25.3.0 as a workaround, it might be worth checking if there are any patches or updates for version 25.4.0 that address this issue. Sometimes, bugs are fixed in subsequent releases.

  4. Example Code: Here’s a simplified example of how to set the PrintArea and export to HTML, which you can compare with your implementation:

    Workbook workbook = new Workbook("path_to_your_file.xlsx");
Worksheet worksheet = workbook.Worksheets[0];

// Set the PrintArea to the range of the pivot table
worksheet.PageSetup.PrintArea = "A2:G6"; // Adjust as necessary

HtmlSaveOptions saveOptions = new HtmlSaveOptions
{
    ExportPrintAreaOnly = true,
    ExportActiveWorksheetOnly = true
};

workbook.Save("output.html", saveOptions);

  5. Contact Support: If the issue persists, consider reaching out to Aspose support directly with your specific use case and the code you are using. They may provide insights or a fix for the behavior change.

By following these steps, you should be able to troubleshoot the PrintArea issue with your pivot table in the newer version of Aspose.Cells. If you find any specific behavior that seems incorrect, documenting it and sharing it with Aspose support could help them address the problem in future updates.

3

@epatrick
We can reproduce the issue by testing on the latest version v25.4 using sample files and the following sample code. HTML file data loss when setting the print area to pivot table range. Please refer to the attachment. result.zip (5.7 KB)

License license = new License();
license.SetLicense(filePath + "Aspose.Cells.NET.lic");
Workbook workbook = new Workbook(filePath + "Charts.xlsx");

HtmlSaveOptions _pivotSaveOptions = new HtmlSaveOptions(SaveFormat.Html)
{
    ExportPrintAreaOnly = true,
    ExportActiveWorksheetOnly = true,
};
             
Worksheet sheet = workbook.Worksheets[0];
PivotTable pivot = sheet.PivotTables[0];
// omitted for brevity

pivot.RefreshData();
pivot.CalculateData();
Console.WriteLine(sheet.Name);
sheet.Workbook.Worksheets.ActiveSheetIndex = sheet.Index;


sheet.PageSetup.PrintArea = "F2:G6";
sheet.Workbook.Save(filePath + "out_f2g6.html", _pivotSaveOptions);

sheet.PageSetup.PrintArea = "E2:G6";
sheet.Workbook.Save(filePath + "out_e2g6.html", _pivotSaveOptions);

sheet.PageSetup.PrintArea = "A2:G6";
sheet.Workbook.Save(filePath + "out_a2g6.html", _pivotSaveOptions);

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): CELLSNET-58227

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.

4

@epatrick ,

We are pleased to inform you that your issue has been resolved. The fix will be included in an upcoming release (Aspose.Cells v25.5) that we plan to release in the first half of May 2025. You will be notified when the next version is released.
Here is the result after fix for your reference: output.zip (12.2 KB)

1 Like