ListObject not copied correctly

Using Aspose Cells for Java 19.7.

Sample code:

Workbook source = new Workbook("table_copy_test.xlsx");
Worksheet sourceSheet = source.getWorksheets().get(0);
Cells sourceCells = sourceSheet.getCells();
Workbook target = new Workbook();
Worksheet targetSheet = target.getWorksheets().get(0);

targetSheet.getCells().copyColumns(sourceCells, 0, 0, 2);
target.save("result.xlsx");

Source workbook:
table_copy_test.zip (7.4 KB)

Comparing the source and result workbooks, there are a number of differences:

  • The background color of style “STYLE2” has changed
  • The table name has been reverted to “Table1”
  • The formula has been changed from a calculated column formula to a regular formula

Our use case is very simple: we need to copy a sheet or range of cells that contains one of more Excel tables. We expect the copied result to be fully consistent with the source table definition.

Thanks in advance,
Taras

@TarasTielkes,
We were able to observe the issue but we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.

This issue has been logged as

CELLSJAVA-42961 – Table properties not copied properly while copying data using copyColumns ( )

P.S. You may try the option PasteType.All in copyColumns function which will resolve your formula related issue.

@TarasTielkes,
This is to inform you that we have fixed your issue (logged earlier as “CELLSJAVA-42961”) now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.

Hi @ahsaniqbalsidiqui,

To be more specific about the last point (“The formula has been changed from a calculated column formula to a regular formula”), if you look into the internals of the original .xlsx file, you’ll see (in xl/tables/table1.xml) the following section:

<tableColumn id=“2” name=“ai” dataDxfId=“0” dataCellStyle=“STYLE2”>
<calculatedColumnFormula>1
* 0.8
+ 2
- 1
</calculatedColumnFormula>
</tableColumn>

Note in particular the <calculatedColumnFormula> element.

If we perform the operation without specifying PasteType.All, this table formula gets lost (as you will be able to see in the file generated by Aspose Cells).
If we perform the operation with specifying PasteType.All,the table seems to disappear completely, so this option does not help.

To test this difference from the end-result perspective, try to type something in cell A3.
In the original workbook, Excel will recognize the presence of a table formula in B2, and will extend it down to the new row, in cell B3.

In the workbook saved by Aspose Cells, in contrast, typing something in cell A3 will result in cell B3 staying empty.

So this way, you can verify if the fix is complete in this regard, or not.

Kind regards,
Taras

@TarasTielkes,
Thank you for the feedback. We have recorded this information with the logged ticket for our reference.

@TarasTielkes,
Please try our latest version/fix: Aspose.Cells for Java v19.7.1:
aspose-cells-19.7.1.zip (6.5 MB)

Your issue should be fixed in it.

Let us know your feedback.

Hi @ahsaniqbalsidiqui,

The new version (19.7.1) is indeed an improvement over the previous one: the column formula is now properly added to the table in the destination. Thank you for investigating and improving this!

However, in the copy process, some styling seems to get lost.

If you run the test with 19.7.1, you’ll see that the color of the second table column (column B in sheet terms) is different between the sheet in the original and the sheet in the destination.

If you compare the table1.xml inside the before/after .xlsx files, you’ll see that in the “after” version, the following data is dropped:

  • on the root <table> element, attribute headerRowCellStyle="TH_STYLE" gets dropped
  • on the two <tableColumn> elements, the dataCellStyle attribute gets dropped

Kind regards,
Taras

Hi @ahsaniqbalsidiqui,

One additional observation: in the destination workbook, the style with name “STYLE2” is present after the copy operation. However, the actual color of that style seems to have changed during the process of copying.

Where it is pinkish in the source workbook, it is light green in the destination workbook. It seems that Aspose Cells mutates the actual style parameters during the process of copying.

Kind regards,
Taras

@TarasTielkes,
Thank you for the feedback. I have observed the issues and logged it with the ticket for further investigation. I will write back here as soon as some feedback is ready to share.

@TarasTielkes,

We evaluated your issue further.

  1. The color of the second table column (column B in sheet terms) is different between the sheet in the original and the sheet in the destination.
    Please copy theme before copying the data to sort it out. See following sample code:
    e.g
    Sample code:

    Workbook target = new Workbook();
    target.CopyTheme(source);

  2. For headerRowCellStyle and dataCellStyle being lost. We use headerRowDxfId and dataDxfId to replace them. When copying a part from other workbook, it’s hard to keep the style name because duplicate names will come.

The issues you have found earlier (filed as CELLSJAVA-42961) have been fixed in Aspose.Cells for Java v19.8. This message was posted using BugNotificationTool from Downloads module by ahsaniqbalsidiqui