Excel table / Aspose.Cells ListObject => how to add rows

Aspose.Cells Product Family
1

Hi,

I’ve got this MS Excel template containing a Table. I understand it is modelized as a ListObject in Aspose.Cells. Please check attached screenshot.

In MS Excel, when I type a value in the cell B6, the table will extend automatically and the formulas in it will be copied down. I would like to reproduce this behaviour with Aspose.Cells.

I am able to resize the ListObject but the formula in the last column doesn’t get copied in the newly created row(s). How to achieve that?

Regards

2

Hi,

Thanks for your posting and using Aspose.Cells.

We have fixed a similar issue in a recent versions. So please try the latest version: Aspose.Cells for Java 8.3.1 and see if it resolves your issue.

In case your problem still persists, then please provide us your source Excel file containing your table with the formulas. We will look into it and help you asap.

3 
Hi,

Using v 8.3.1 I am doing this:

import com.aspose.cells.*;

/**
 * Date: 12/15/14
 * Time: 4:18 PM
 */
public class AsposeTableTest {

    public static void main(String[] args) throws Exception {
        Workbook wb = new Workbook("c:/test-aspose/table.xlsx");

        Cells cells = wb.getWorksheets().get(0).getCells();
        cells.get("B6").setValue(10);
        cells.get("C6").setValue(12);

        ListObject table = wb.getWorksheets().get(0).getListObjects().get(0);
        table.resize(table.getStartRow(), table.getStartColumn(), table.getEndRow() + 1, table.getEndColumn(), true);
        table.applyStyleToRange();

        wb.save("c:/test-aspose/table." + System.currentTimeMillis() + ".xlsx");
    }

}

The table gets extended, but cell D6 is not getting set with the correct formula. It is kept empty. Is it how it is supposed to be ?
4

Hi,

Thanks for your sample code, posting and using Aspose.Cells.

Please also provide us table.xlsx file used inside the code so that we could investigate this issue at our end.

5

Here is the file.

6

Hi,

Thanks for your file and using Aspose.Cells.

We were able to observe this issue after executing your code with your source Excel file using the latest version: Aspose.Cells for Java 8.3.1. Extending Table does not copy formula to new rows. However this issue does not occur in .NET version.

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

  • CELLSJAVA-41140 - Extending Table does not copy formula to new rows

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

7

Hi,

Thanks for using Aspose.Cells.

Please use ListObject.putCellValue() method to replace Resize method .

Please see the following code:

Java


Workbook wb = new Workbook(“table.xlsx”);


Cells cells = wb.getWorksheets().get(0).getCells();


ListObject table = wb.getWorksheets().get(0).getListObjects().get(0);


table.putCellValue(4, 0, 10);

table.putCellValue(4, 1, 12);


// table.resize(table.getStartRow(), table.getStartColumn(), table.getEndRow() + 1, table.getEndColumn(), true);

table.applyStyleToRange();


wb.save(“table.” + “.xlsx”);


And we will fix the bug of resize method.
8

It is working better, except cell C6 does not get set with the value 12. Can you check on your side ?

9

Hi,

Thanks for your feedback and using Aspose.Cells.

We were able to observe this issue after executing the above code using the latest version: Aspose.Cells
for Java v8.3.1.1. Cell C6 does not get set with ListObject.putCellValue() method.

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

  • CELLSJAVA-41141 - Cell C6 does not get set with ListObject.putCellValue() method

I have attached the output Excel file for a reference.

10

Hi,

Thanks for using Aspose.Cells for Java.

We have fixed these issues.

  • CELLSJAVA-41140 - Extending Table does not copy formula to new rows
  • CELLSJAVA-41141 - Cell C6 does not get set with ListObject.putCellValue() method

Please download and try this fix: Aspose.Cells for Java v8.3.1.2 and let us know your feedback.

11

Thank you, this test is now working.

Best wishes for the new year.

12

Hi,


Thanks for your feedback

Good to know that it figures out your issue(s) now. Feel free to write back in case you have further comments or questions, we will be happy to assist you soon.

Thank you.
13

The issues you have found earlier (filed as CELLSJAVA-41141;CELLSJAVA-41140) have been fixed in this update.

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