How to copy a row multiple times in the worksheet using Aspose.Cells for Java API

[TarasTielkes]

Hi,

Using Aspose Cells for Java, version 19.12.2.

A common case in our application is to populate ranges/tables with external data, i.e. the results of a query performed on a database.

Typically we use a template workbook, that specifies the styles and formatting for a single record. For each additional record, we perform the following:

• insert an additional row
• copy the contents of the previous row, to apply styles, formatting and calculation formulas operating on the row data for the newly inserted record

We have found out that, if we know the number of records/rows to be inserted beforehand, it makes a huge difference (in terms of performance) to insert the new rows in bulk. Specifically, instead of calling Cells#insertRows(rowIndex, totalRows) for each row, we call the method once with the total amount of additional rows that we want to insert. In the case where there are many formulas present in the workbook that operate on the inserted rows, this batching optimization seems to make a large difference in performance when using Aspose Cells.

However, in addition to inserting new rows, we also want to copy styles, formatting and formulas to each of the inserted rows. Typically, we have a number of columns with formulas that calculate relevant data for each record/row inserted. At the moment, we are using Cells#copyRow(sourceCells, sourceRowIndex, destinationRowIndex) for each additional row populated by the database query.

When we know that we are inserting 100.000 new rows, calling this method 100.000 times seems clumsy. What we want to express to Aspose Cells is “copy this single row N times, starting at destination row X”. It seems that there is an opportunity for a more convenient API here. In addition, there should be some potential for a performance improvement: since Aspose Cells will know that I want to copy a given row N times, it will need to read the source row only once, instead of reading it N times when using the current API.

In essence, this feature request is to introduce a method like the following on the Cells class:

public void copyRow(Cells sourceCells, int sourceRowIndex, int destinationRowIndex, int rowCount);

The method will copy the source row, “pasting” it rowCount times to the rows starting from the specified destination row.
Given the tabular nature of typical Excel workbooks, I think this is quite a common use case. It could be optimized for, both in terms of API usability, as well as performance.

I hope the feature request is clear, and makes sense. If you have any questions, please ask.

Kind regards,
Taras

[ahsaniqbalsidiqui]

@TarasTielkes,
We are clear about your requirement 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-43080 – Copy the source row, "pasting" it rowCount times to the rows starting from the specified destination row

[Amjad_Sahi]

@TarasTielkes,

1. Please note, copyRow(Cells sourceCells, int sourceRowIndex, int destinationRowIndex, int rowCount) only can save the time to search the data in the source rows.

2. Please try the following codes to copy rows quickly for your needs:
   e.g
   Sample code:

int count = 1;
int startRow = 0;
int n = 0;
while(true) {
int left = count;
int destRow = startRow + count;
if (100 - destRow < count) {
left = 100 - destRow;
}
cells.CopyRows(cells, startRow, destRow, left);
count + = count;
n++;
if(startRow + count > 100) {
break;
}

```
}            Console.WriteLine(n);
```  

Let us know your feedback.