Excel Dynamic Array Formula Issues in XLSX to PDF Conversion (Aspose.Cells for Java 22.12)

Aspose.Cells Product Family
1

Dear Aspose.Cells Support Team,

We are encountering a critical issue when converting an XLSX workbook to PDF using Aspose.Cells. Specifically, several modern Excel dynamic array formulas, which work correctly in the source XLSX file, are displaying errors (#NAME? or #VALUE!) in the generated PDF output instead of their calculated values. This significantly impacts the usability of our PDF documents.

Our source XLSX workbook contains the following formulas, and here’s their observed behavior in the PDF output:

  • OFFSET: Appears to work correctly.
  • SEQUENCE: Appears to work correctly.
  • INDEX + SEQUENCE: Appears to work correctly.
  • FILTER: Appears to work correctly.
  • UNIQUE: Appears to work correctly.
  • SORT: Shows #VALUE! errors in the PDF.
  • SORTBY: Appears to work correctly.
  • VSTACK/HSTACK: Shows #NAME? errors in the PDF.
  • TRANSPOSE: Appears to work correctly.
  • CHOOSECOLS/CHOOSEROWS: Shows #NAME? errors in the PDF.
  • TOCOL/TOROW: Shows #NAME? errors in the PDF.
  • TEXTBEFORE/TEXTAFTER: Shows #NAME? errors in the PDF.
  • TEXTSPLIT: Shows #NAME? errors in the PDF.
  • RANDARRAY: Appears to work correctly.

This indicates that Aspose.Cells might not be evaluating or rendering these specific dynamic array formulas correctly during the conversion process from XLSX to PDF.

Important Note: We have tested this same conversion process with Aspose.Cells for Java version 24.5, and the issue does not occur; the formulas are rendered correctly in the PDF.

Given this, could you please advise on how to resolve this issue in version 22.12? Is there a known workaround or configuration change we can apply to make these formulas work in version 22.12, or is upgrading to a newer version (like 24.5) the only solution?

We urgently need the PDF output to accurately reflect the calculated values from these dynamic array formulas as they appear in the original XLSX file.

To help you troubleshoot, here are the details of our setup and the code snippet we are using:

  • Aspose.Cells Version: Aspose.Cells for Java 22.12 (Issue observed)
  • Operating System: macOS 15.5
  • Development Environment: IntelliJ IDEA
  • Programming Language: Groovy

Code Snippet:


Workbook workbook = new Workbook("formulas.xlsx");

for (int i = 0; i < workbook.getWorksheets().getCount(); i++) {
    Worksheet sheet = workbook.getWorksheets().get(i);
    PivotTableCollection pivots = sheet.getPivotTables();

    for (int j = 0; j < pivots.getCount(); j++) {
        PivotTable pivot = pivots.get(j);

        pivot.refreshData();
        pivot.calculateData();

        System.out.println("Refreshed pivot: " + pivot.getName());
    }
}

workbook.calculateFormula();
workbook.refreshDynamicArrayFormulas(true);

for (int i = 0; i < workbook.getWorksheets().getCount(); i++) {
    if (!workbook.getWorksheets().get(i).getName().startsWith("_")) {
        workbook.getWorksheets().get(i).setVisible(true);
        System.out.println(workbook.getWorksheets().get(i).getName());
    } else {
        workbook.getWorksheets().get(i).setVisible(false);
    }
}

workbook.save("output.pdf", SaveFormat.PDF);

We have attached the problematic PDF output and the xlsx file in the following zip:
pdf_and_excel.zip (87.6 KB)

Thank you for your time and urgent assistance.

Sincerely,
Kiran Karki

2

@kiran.karki
Please call the refresh dynamic array formula before calculating the formula. Please refer to the attachment. out_java.pdf (54.1 KB)

Workbook workbook = new Workbook(filePath + "excel_formulas.xlsx");

for (int i = 0; i < workbook.getWorksheets().getCount(); i++) {
    Worksheet sheet = workbook.getWorksheets().get(i);
    PivotTableCollection pivots = sheet.getPivotTables();

    for (int j = 0; j < pivots.getCount(); j++) {
        PivotTable pivot = pivots.get(j);

        pivot.refreshData();
        pivot.calculateData();

        System.out.println("Refreshed pivot: " + pivot.getName());
    }
}

workbook.refreshDynamicArrayFormulas(true);
workbook.calculateFormula();


for (int i = 0; i < workbook.getWorksheets().getCount(); i++) {
    if (!workbook.getWorksheets().get(i).getName().startsWith("_")) {
        workbook.getWorksheets().get(i).setVisible(true);
        System.out.println(workbook.getWorksheets().get(i).getName());
    } else {
        workbook.getWorksheets().get(i).setVisible(false);
    }
}

workbook.save(filePath + "out_java.pdf", SaveFormat.PDF);
3

@kiran.karki

For those formulas that show #NAME?, the used functions are not supported by old version 22.12. There are also some other bugs in 22.12 but they have been fixed in later versions, such as the generated #VALUE!.

We also found another bug in current version(25.5, and all other older version) which causes the calculated result of TEXTSPLIT function in your template file incorrect. 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-46382

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.

We cannot fix or provide existing fixes in later versions to older versions anyways, so you have to upgrade to 24.5 to solve some other issues. But for TEXTSPLIT issue, it cannot be solved by upgrading to any existing versions until we provide new fix for it.