Formula in Table (ListObject) does not propogate with new rows

Aspose.Cells Product Family
1

I am trying to put a formula in a table (list object) and have it propagate down automatically as Excel does natively. The various Aspose methods for putting a formula in do not seem to work with this.


Compare the sheet generated with the following code:


Workbook book = new Workbook();

Worksheet sheet = book.Worksheets[0];

sheet.Cells[0, 0].PutValue(“Column A”);
sheet.Cells[0, 1].PutValue(“Column B”);

ListObject listObject =
sheet.ListObjects[sheet.ListObjects.Add(0, 0, 1, sheet.Cells.MaxColumn, true)];
listObject.TableStyleType = TableStyleType.TableStyleMedium2;
listObject.DisplayName = “Table”;

sheet.Cells[1, 1].Formula = “=[Column A] + 1”;
book.Save(“C:\test\testbook2.xlsx”);


Against the attached file that is identical, albeit created manually.

When you type values into Column A - the manual file automatically propagates the formula down. But the one generated from Aspose does not. Please assist.

2

Hi,


Thanks for your posting and using Aspose.Cells.

Please try the Resize() method of ListObject to fulfill your needs. Let us know your feedback.

3

Thank you for your response.


I am not trying to programatically extend the list object (which appears to be what Resize() is for). I am trying to make a template for manual data entry. I want the formula in Cell[1,1] to automatically copy down as the user enters more rows. This happens automatically when you build the table by hand. But when setting the formula via Aspose, it does not.

I attempted to use Resize nonetheless, and it doesn’t appear to accomplish what I want.
4
Hi,

Thanks for your clarification of the issue and using Aspose.Cells.

We have tested this issue with the following sample code using the latest version: Aspose.Cells for .NET v9.0.9.0 and found the issue. If we add 2 in cell A3 inside your template file, we see that the cell B3 automatically gets formula but this does not happen when the similar file is created using Aspose.Cells

We have logged this issue in our database for investigation. We will look into it and fix this issue. Once the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as

  • CELLSNET-44808 - Formula in Table aka ListObject does not propagate with new rows

I have also attached the output excel file generated with this code for a reference.

C#
Workbook book = new Workbook();

Worksheet sheet = book.Worksheets[0];

sheet.Cells[0, 0].PutValue("Column A");
sheet.Cells[0, 1].PutValue("Column B");

ListObject listObject =
sheet.ListObjects[sheet.ListObjects.Add(0, 0, 1, sheet.Cells.MaxColumn, true)];
listObject.TableStyleType = TableStyleType.TableStyleMedium2;
listObject.DisplayName = "Table";

sheet.Cells[1, 1].Formula = "=[Column A] + 1";
book.Save("output.xlsx");

5
Hi,

Thanks for using Aspose.Cells.

We are afraid, we do not support this feature for performance because we have to iterate all Tables when input value to any cell.

6

I don’t understand how that can be the case. The table only has 2 rows.


If I configure the second row in Aspose, then when I add a 3rd row in Excel, it doesn’t copy.

If I configure the second row manually, then when I add a 3rd row in Excel, it automatically copies it itself.

This has nothing to do with iteration, it has to do with some sort of property on the cell when that formula is set.

Try this.
1) Generate the Excel file with Aspose using the code we’ve discussed.
2) Enter the formula block for Cell B2
3) Press "Enter"
4) Note that now the block behaves exactly as if I had created the file manually in the first place.

This is not an iteration problem. This is a problem with how the formula is committed to that cell.
7

Hi,


Thanks for your valid points and using Aspose.Cells.

We have logged your comment in our database for product team reconsideration and investigation. Once, there is some news for you, we will update you asap.

8
Hi,

Thanks for using Aspose.Cells.

This is to inform you that we have fixed your issue CELLSNET-44808 now. We will soon provide the fix after performing QA and including other enhancements and fixes.

9
Hi,

Thanks for your using Aspose.Cells.

Please download and try the latest fix: Aspose.Cells for .NET (Latest Version) and let us know your feedback.

Please try the fix with the following code:

C#
Workbook book = new Workbook();

Worksheet sheet = book.Worksheets[0];

sheet.Cells[0, 0].PutValue("Column A");
sheet.Cells[0, 1].PutValue("Column B");

ListObject listObject =
sheet.ListObjects[sheet.ListObjects.Add(0, 0, 1, sheet.Cells.MaxColumn, true)];
listObject.TableStyleType = TableStyleType.TableStyleMedium2;
listObject.DisplayName = "Table";
listObject.ListColumns[1].Formula = "=[Column A] + 1";

10

This fix solves the problem.


However, was some other modification made to this release? I am seeing a host of compilation issues in my actual solution now with code that worked before.

if (worksheet.ListObjects.Any())

Worksheets.Where(ws => ws.Name.Equals(name))

These blurbs both fail to compile now. It’s as if the collections for ListObjects and Worksheets no longer support IEnumerable?

Note: I did not see these issues with version 9.0
11

Hi again,


Thank you for the confirmation on the fix. Regarding the compilation errors, I believe it is because the provided assembly was complied against .NET framework 2.0. Please try the case against the attached assembly which is complied against .NET framework 4.0.
12

Perfect. Thank you.

13

Hi,


Thanks for your feedback.

Good to know that your issue is sorted out by the new fix/version (.NET 4.0 compiled version). Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

Thank you.
14

The issues you have found earlier (filed as CELLSNET-44808) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.