Writing data to table row does not copy formula to the column of that row

[seifordd]

I have been doing some more testing with a spreadsheet having a table that contains columns with formulas.



If you write to the first data row of an empty table, the formulas are not copied to the columns of that row. (Table was not resized because it already had an empty row for data.)



If you enter data into this empty cell with Excel, the formula is copied to the column



Cell B2 in the attached xlsx file is the one with the issues.



I have tested the latest Aspose.Cells dll (8.3.0)





Sample code.



private void button1_Click(object sender, EventArgs e)

{

string filePath = @".\SampleWithEmptyTable.xlsx";

Workbook workbook = new Workbook(filePath);

Worksheet worksheet = workbook.Worksheets[0];

ListObject lo = worksheet.ListObjects[0];



worksheet.Cells[lo.EndRow, lo.StartColumn].PutValue(1); // Write data to data row of empty table.

workbook.Save(“output.xlsx”);

}

[shakeel.faiz]

Hi Dave,

Thanks for your posting and using Aspose.Cells.

We were able to observe this issue after executing your sample code with your sample Excel file using the latest version: Aspose.Cells
for .NET v8.3.0.3. Writing data to table row does not copy formula to the column of that row.

Could you please let us know how did you create such a table?

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-43217 - Writing data to table row does not copy formula to the column of that row

Please note, we have split your thread. The previous thread has gone very lengthy so it is not a good thing to post any newer issues inside it. Whenever, you find any new issue, please create new threads for it. It will help us log your issues in our database in more manageable way.

Here is a link to your old thread.

( ListObject Resize deletes CalculatedColumnFormula )

[seifordd]

The sample spreadsheet I uploaded is a copy of the xls file I was sent from Aspose in response to this post.

ListObject Resize deletes CalculatedColumnFormula

I have an Excel spreadsheet that has a table defined with formulas defined on some of the columns. If I enter data on a new row in the table using Excel, the formulas are copied to the new row automatically by Excel. I am wanting to add data to the spreadsheet using Aspose.Cells. I can find the Table object in the listObjectCollection. I can see the formulas defined on the columns of this listObject in the debugger. If I call the Resize method on the listObject, the size of the table i…

That sample.xls file had the table defined in it.

[shakeel.faiz]

Hi Dave,

Thanks for your posting and using Aspose.Cells.

We have evaluated this issue. We found, we have to check all tables when putting value to the cell to support this feature.

We are afraid, we cannot support this feature for performance.

[seifordd]

Can you look at adding a method on a Table object that will initialize it and tell it to copy all formula and settings on columns defined on the table.

This is only needed if the table has no data in it. Just the column headers.

That way a program can tell the table to initialize itself when you first start writing data into the table.

Otherwise there is no way of writing data into a table and have the columns setup with the columns default settings as defined in the table.

[shakeel.faiz]

Hi Dave,

Thanks for your suggestion and using Aspose.Cells.

We have logged your comment in our database against this issue. We will look into your requirements and see if it is achievable. Once, there is some news for you, we will let you know asap.

[shakeel.faiz]

Hi,

Thanks for using Aspose.Cells.

We will add ListObject.PutValue(int rowOffset,int columnOffset,object value) method to support this feature. Does it fit your needs?

[seifordd]

The Listobject.PutValue() method sounds good.

I am assuming I would only need to call that method for a table that does not have any data in it. The ListObject.PutValue() would initialize all the column formula, styling, validation etc defined on the table to the table columns.

Once there is at least 1 row of data, calling lo.Resize() will copy the column definitions to the new rows.

[shakeel.faiz]

Hi Dave,

Thanks for your input and using Aspose.Cells.

We have logged your comment in our database against this issue for consideration. We will look into it and provide you such a method to fulfill your requirements if possible. Please spare us some time. Once, there is some news for you, we will let you know asap.

[amjad.sahi]

Hi,

Please try our latest version/fix: Aspose.Cells for .NET v8.3.1.

We have added a new method ListObject.PutCellValue() for your needs.

Thank you.

[seifordd]

I have been using the new PutCellValue that was added in Aspose.Cells (v8.3.1) and have run into a problem.



My spreadsheet has a table with validation and a VLookup formula that use a Table as the source of data.

When trying to use the new PutCellValue method to initialize the first new row in my data table, the VLookup formula is not initialized correctly.

The cell with the VLookup formula shows #Name?



If the table has a row that was added by the Excel UI, the Aspose table Resize method works correctly.

Or if you use Excel to edit the row added by the PutCelValue and save the formula, it works.



I am guessing the problem is with the way you have to format the Formula when the VLookup formula is referencing a Table.



When setting the formula, the formala has to have this format

=VLOOKUP($L5,Table1[],2,0)



but when you read it back it has this format

=VLOOKUP($L5,Table1,2,0)





Here is some sample code that shows the problem.

I have also uploaded the sample xls file used by the code.



static void Main(string[] args)

{

string filePath = @".\Sample.xlsx";

Workbook workbook = new Workbook(filePath);



// Get Table 1. This is an empty table.

// When the new row is initialized with ‘PutCellValue’, the VLookup formula is not initialized correctly

Worksheet worksheet = workbook.Worksheets[0];

ListObject lo = worksheet.ListObjects[1];

lo.PutCellValue((lo.EndRow - lo.StartRow), (lo.StartColumn - lo.StartColumn), 1); // Initialize the Table Formulas

worksheet.Cells[lo.EndRow, lo.StartColumn + 1].PutValue(2); // Write values to data row of empty table.

worksheet.Cells[lo.EndRow, lo.StartColumn + 3].PutValue(“A”);



lo.Resize(lo.StartRow, lo.StartColumn, lo.EndRow + 1, lo.EndColumn, true);

worksheet.Cells[lo.EndRow, lo.StartColumn].PutValue(10); // Write data to second row of table

worksheet.Cells[lo.EndRow, lo.StartColumn + 1].PutValue(20);

worksheet.Cells[lo.EndRow, lo.StartColumn + 3].PutValue(“B”);



// Get Table 24. This table has a row that was added with the Excel UI.

// Call the Resize method to add a new row. This time the VLookup formula is initialized corretly

lo = worksheet.ListObjects[2];

lo.Resize(lo.StartRow, lo.StartColumn, lo.EndRow + 1, lo.EndColumn, true);

worksheet.Cells[lo.EndRow, lo.StartColumn].PutValue(100); // Write data to second row of table

worksheet.Cells[lo.EndRow, lo.StartColumn + 1].PutValue(200);

worksheet.Cells[lo.EndRow, lo.StartColumn + 3].PutValue(“C”);



workbook.Save(“output.xlsx”);

}

[shakeel.faiz]

Hi Dave,

Thanks for your posting and using Aspose.Cells.

We were able to observe this issue after executing your sample code with your provided Excel file using the latest version: Aspose.Cells
for .NET v8.3.1.4. ListObject.PutCellValue() does not initialize the VLookup formula inside the Table.

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-43286 - ListObject.PutCellValue() does not initialize the VLookup formula inside the Table

I have also attached the output Excel file generated by your code for a reference.

[shakeel.faiz]

Hi Dave,

Thanks for using Aspose.Cells.

Please download and try the latest fix: Aspose.Cells for .NET v8.3.1.6 and let us know your feedback.

Please try the new fix with the following code.

C#

Workbook workbook = new Workbook(Constants.sourcePath + “Cellsnet43286.xlsx”);

Worksheet worksheet = workbook.Worksheets[0];

ListObject lo = worksheet.ListObjects[1];

lo.PutCellValue((lo.EndRow - lo.StartRow), (lo.StartColumn - lo.StartColumn), 1); // Initialize the Table Formulas

worksheet.Cells[lo.EndRow, lo.StartColumn + 1].PutValue(2); // Write values to data row of empty table.

worksheet.Cells[lo.EndRow, lo.StartColumn + 3].PutValue(“A”);


// lo.Resize(lo.StartRow, lo.StartColumn, lo.EndRow + 1, lo.EndColumn, true);

lo.PutCellValue((lo.EndRow - lo.StartRow) + 1, (lo.StartColumn - lo.StartColumn), 10); // Write data to second row of table

worksheet.Cells[lo.EndRow, lo.StartColumn + 1].PutValue(20);

worksheet.Cells[lo.EndRow, lo.StartColumn + 3].PutValue(“B”);


//// Get Table 24. This table has a row that was added with the Excel UI.

//// Call the Resize method to add a new row. This time the VLookup formula is initialized corretly

lo = worksheet.ListObjects[2];

lo.Resize(lo.StartRow, lo.StartColumn, lo.EndRow + 1, lo.EndColumn, true);

lo.PutCellValue((lo.EndRow - lo.StartRow) + 1, (lo.StartColumn - lo.StartColumn), 100); // Write data to second row of table

worksheet.Cells[lo.EndRow, lo.StartColumn + 1].PutValue(200);

worksheet.Cells[lo.EndRow, lo.StartColumn + 3].PutValue(“C”);

[aspose.notifier]

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

---

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

[seifordd]

I downloaded the v8.3.1.6 version of the Aspose.Cells dll and ran my original code.

What I noticed was that the using PutCellValue to write to the first row of an empty call now does initialize the formulas as expected.



I then used Resize to add a second row to the Table. Resize now does not work properly.

Resize seems to not use the correct row number when initializing formulas.

The formula added by resize should have been ‘=SUM($B6,$C6)’ but it was ‘=SUM($B7,$C7)’



I was hoping to use the PutCellValue method only if the table did not have any data rows.

The PutCellValue method would then initialize all the formulas for the table columns



Once the table had at least 1 data row, I was wanting to use the Resize method to add new rows. I was expecting Resize to copy

all formulas to the new row based on the formula defined for the columns. (with the correct row numbers based on the new row added)



With this latest version of the DLL, it seems the Resize method does not work as expected if it is used after PutCellValue was called.



I uploaded the output.xlsx file that shows the incorrect formulas. (wrong row # references)

[shakeel.faiz]

Hi Dave,

Thanks for your feedback and using Aspose.Cells.

We were able to observe this issue in your output Excel file. However, in order to replicate this issue at our end, we need your source Excel file and the runnable sample code reproducing this issue with the latest version: Aspose.Cells for .NET 8.3.2. It will help us look into your issue more precisely and fix it.

Thanks for your cooperation.

[seifordd]

Here is the source code I used to reproduce the issue.



I also attached the Sample.xlsx file used by the test code.





static void Main(string[] args)

{

string filePath = @".\Sample.xlsx";

Workbook workbook = new Workbook(filePath);



// Get Table 2. This is an empty table.

// When the new row is initialized with ‘PutCellValue’, the VLookup formula is not initialized correctly

Worksheet worksheet = workbook.Worksheets[0];

ListObject lo = worksheet.ListObjects[1];

lo.PutCellValue((lo.EndRow - lo.StartRow), (lo.StartColumn - lo.StartColumn), 1); // Initialize the Table Formulas

worksheet.Cells[lo.EndRow, lo.StartColumn + 1].PutValue(2); // Write values to data row of empty table.

worksheet.Cells[lo.EndRow, lo.StartColumn + 3].PutValue(“A”);



lo.Resize(lo.StartRow, lo.StartColumn, lo.EndRow + 1, lo.EndColumn, true);

worksheet.Cells[lo.EndRow, lo.StartColumn].PutValue(10); // Write data to second row of table

worksheet.Cells[lo.EndRow, lo.StartColumn + 1].PutValue(20);

worksheet.Cells[lo.EndRow, lo.StartColumn + 3].PutValue(“B”);



// Get Table 24. This table has a row that was added with the Excel UI.

// Call the Resize method to add a new row. This time the VLookup formula is initialized corretly

lo = worksheet.ListObjects[2];

lo.Resize(lo.StartRow, lo.StartColumn, lo.EndRow + 1, lo.EndColumn, true);

worksheet.Cells[lo.EndRow, lo.StartColumn].PutValue(100); // Write data to second row of table

worksheet.Cells[lo.EndRow, lo.StartColumn + 1].PutValue(200);

worksheet.Cells[lo.EndRow, lo.StartColumn + 3].PutValue(“C”);



workbook.Save(“output.xlsx”);

}

[shakeel.faiz]

Hi Dave,

Thanks for your sample code, sample Excel file and using Aspose.Cells.

We were able to observe this issue after executing your sample code with your sample Excel file using the latest version. ListObject.Resize() method sets the wrong formula in newly added rows.

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-43364 - ListObject.Resize() method sets the wrong formula in newly added rows

I have also attached the output Excel file for a reference.

[amjad.sahi]

Hi,

Please try our latest version/fix: Aspose.Cells for .NET v8.3.2.3

We have fixed your issue “CELLSNET-43364” now.

Let us know your feedback.

Thank you.

[seifordd]

I downloaded the new v8.3.2.3 version of the Aspose.Cells dll and tested it with the sample code. The PutCellValue() and lo.Resize() methods now work as expected.

Thank you for fixing the issues and providing an updated dll.

Dave