ListObject with calculated column creates invalid Excel file

Aspose.Cells Product Family
1

Hello,

An invalid Excel file is generated using this example from the documentation located at: Propagate Formula in Table or List Object automatically while entering data in new rows|Documentation

When you open the file and try to enter a number in Column A row 1, or row 2 (new row) Excel locks up and crashes.

We ran into a similar issue in our code where we insert rows for an existing table, and then we go back and re-apply the ListColumn’s Formula property to apply the formula to the inserted rows. This used to work, but now produces an Excel file that shows an error on open. After opening the calculated column displays =#N/A. It appears that setting a ListColumn.Formula that references another list column causes the issue, setting the formula to =42 works just fine.

Output File showing error
out_test.zip (6.8 KB)

    [Test]
    public void foo68()
    {
        var filename = @"c:\data\temp\out_test.xlsx";

        // Create workbook object
        Workbook book = new Workbook();

        // Access first worksheet
        Worksheet sheet = book.Worksheets[0];

        // Add column headings in cell A1 and B1
        sheet.Cells[0, 0].PutValue("Column A");
        sheet.Cells[0, 1].PutValue("Column B");

        // Add list object, set its name and style
        ListObject listObject = sheet.ListObjects[sheet.ListObjects.Add(0, 0, 1, sheet.Cells.MaxColumn, true)];
        listObject.TableStyleType = TableStyleType.TableStyleMedium2;
        listObject.DisplayName = "Table";

        // Set the formula of second column so that it propagates to new rows automatically while entering data
        listObject.ListColumns[1].Formula = "=[Column A] + 1";

        // Save the workbook in xlsx format
        book.Save(filename);
    }
2

@weissa

It seems you are encountering an issue with generating an invalid Excel file when using a calculated column in a ListObject. This can happen when the formula in the ListColumn references another ListColumn, which may lead to errors when opening the file in Excel.

To troubleshoot this issue, you can try the following steps:

  1. Check the Formula Syntax: Ensure that the formula you are using is correctly formatted. In your case, the formula =[Column A] + 1 should be valid, but if it references another ListColumn that is not properly defined or populated, it may cause issues.

  2. Test with Simple Formulas: As you mentioned, using a simple formula like =42 works fine. This indicates that the problem lies with the reference to another column. You might want to test with other simple references to see if they work, such as =[Column A] alone, to isolate the issue.

  3. Reapply Formulas After Inserting Rows: If you are inserting rows into an existing table and then reapplying the ListColumn’s Formula property, ensure that the references in the formula are still valid after the rows are added. You may need to adjust the formula dynamically based on the new row positions.

  4. Update Aspose.Cells Library: Ensure that you are using the latest version of the Aspose.Cells library, as bugs and issues are often fixed in newer releases.

  5. Review Documentation and Examples: You can refer to the official documentation and examples provided by Aspose for guidance on how to properly set up ListObjects and formulas. This can help ensure that you are following best practices.

If the problem persists, consider sharing a minimal reproducible example or the specific version of the Aspose.Cells library you are using, as this can help in diagnosing the issue further.

If you need more detailed assistance, please feel free to ask!

3

@weissa,

Thanks for the sample file and details.

After initial testing, I am able to reproduce the issue as you mentioned by using the sample code snippet. I found ListObject with calculated column creates invalid Excel file.

We require thorough evaluation of the issue. 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-58315

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

@weissa,

We are pleased to inform you that your issue (Ticket ID: “CELLSNET-58315”) has been resolved. The fix will be included in the 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.

5

@amjad.sahi,

Thank you for the update. We’ll keep an eye out for the release.

-Andy

6

@weissa,

You are welcome. You can expect the new release in the second week of May 2025.

7

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