Export XL to Html - Getting an exception after refreshing pivot tables

Aspose.Cells Product Family
,
1

Hi There,

I am using latest version of Aspose.Cells (2024.8.0).
I have an excel input file(‘input.xlsx’ attached), which contains:
The file contains 2 sheets:

  1. Output Dashboards - contains the range to export to html.
  2. Pivots - contains a pivot table.

Using the below code to export the range to an html, I get an exception on:
worksheet.PageSetup.PrintArea = range.Address;
I do need to call:
wbc.RefreshPivotTables();
as it is part of our process.

 private static HtmlSaveOptions _htmlSaveOptions =>
       new()
       {
           ExportImagesAsBase64 = true,
           ExportPrintAreaOnly = true,
           ExportHiddenWorksheet = false,
           ExcludeUnusedStyles = true,
           ExportActiveWorksheetOnly = true,
           Encoding = Encoding.UTF8
       };

   public static void ExportHtml(string inputPath, string htmlOutputPath)
   {
       Workbook wbc = null;
       using (FileStream ms = new FileStream(inputPath, FileMode.Open))
       {
           wbc = new Workbook(ms, new LoadOptions(LoadFormat.Xlsx));
       }

       //This code here is mandatory 
       wbc.RefreshPivotTables();

       var range = wbc.GetRangeByNamedRange("To_Publish");
       var worksheet = range.Worksheet;
       worksheet.PageSetup.PrintArea = range.Address;
       wbc.Worksheets.ActiveSheetIndex = worksheet.Index;
       using var html = new MemoryStream();
       wbc.Save(html, _htmlSaveOptions);
       File.WriteAllBytes(htmlOutputPath, html.ToArray());
   }

   public static void RefreshPivotTables(this Workbook workbook)
   {
       foreach (var ws in workbook.Worksheets)
       {
           foreach (var pivot in ws.PivotTables)
           {
               try
               {
                   pivot.ShowEmptyCol = false;
                   pivot.ShowEmptyRow = false;
                   pivot.RefreshData();
                   pivot.CalculateData();
               }
               catch (Exception e) { /*Log error*/ }
           }
       }
   }

input.zip (140.6 KB)

Please advise,

Thanks,
Shlomi

2

@shlomi.z
We can reproduce the issue by testing on the latest version v24.8 using sample files and the following sample code. An exception occurred while saving the file to HTML after refreshing the pivot tables.

The sample code as follows:

HtmlSaveOptions _htmlSaveOptions = new HtmlSaveOptions()
            {
                ExportImagesAsBase64 = true,
                ExportPrintAreaOnly = true,
                ExportHiddenWorksheet = false,
                ExcludeUnusedStyles = true,
                ExportActiveWorksheetOnly = true,
                Encoding = Encoding.UTF8
            };

Workbook wbc = null;
using (FileStream ms = new FileStream(filePath + "input.xlsx", FileMode.Open))
{
    wbc = new Workbook(ms, new LoadOptions(LoadFormat.Xlsx));
}

//This code here is mandatory 
RefreshPivotTables(wbc);

var range = wbc.Worksheets.GetRangeByName("To_Publish");
var worksheet = range.Worksheet;
worksheet.PageSetup.PrintArea = range.Address;
wbc.Worksheets.ActiveSheetIndex = worksheet.Index;
var html = new MemoryStream();
wbc.Save(html, _htmlSaveOptions);

public static void RefreshPivotTables(Workbook workbook)
{
    foreach (var ws in workbook.Worksheets)
    {
        foreach (var pivot in ws.PivotTables)
        {
            try
            {
                pivot.ShowEmptyCol = false;
                pivot.ShowEmptyRow = false;
                pivot.RefreshData();
                pivot.CalculateData();
            }
            catch (Exception e) { /*Log error*/ }
        }
    }
}

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-56575

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.

3

@shlomi.z
We are pleased to inform you that your issue has been resolved. The fix will be included in our upcoming release (Aspose.Cells for .NET 24.9) that we plan to release in the first half of September 2024. You will be notified when the next version is released.

4

Hi @John.He,
Thank you for the quick reply!

  1. What is approximate ETA for version 24.9 release date?
  2. Is it possible to get an intermediate version to verify the solution?
5

@shlomi.z

Our next official version 24.9 is scheduled to be released in the second week of September. You will be notified when it is published. However, according to the policy of our company, we cannot share internal fix except the paid support services. Thanks for your understanding.

6

The issues you have found earlier (filed as CELLSNET-56575) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi