Chart source data series not udated and formulas not copied after column/row inserts

Aspose.Cells Product Family
1

Hello,

I have an Excel template file that contains several individual worksheet charts linked to a data worksheet. My Java application would add real data rows and a few extra columns and then save the file under a new name. I used the insertRows and insertColumns methods but after saving and then re-opening the new file I find two problems:

1 Each worksheet chart source data series has not been updated to reflect the new inserted columns and rows.

2 The inserted rows in the data worksheet didn’t copy the formatting, formulas etc.

Could you look at my example Java program to see if I have missed something?

Regards,
Dan

2

Hi Dan,

Thanks for the template file with sample code.

We can reproduce the issue you have mentioned. We will figure it out soon.

Thank you.

3

Hi Dan,

This two features are not supported currently and we are working on it, hopefully we can release a new fix to support that soon

4

Hi Dan,

Please try this fix. And For formula, Aspose.Cells acts just same with MS Excel, won't copy it when inserting rows/columns. If needed, I think you can copy it manually after insert rows/columns.

5

Aspose Nanjing Team,

Thanks guys, the chart data now reflects the row/column inserts!

Regards,
Dan

6

Aspose Nanjing Team,



I am trying to copy formulas with updated ranges into cells of newly inserted rows and I am not having very good success.



Method #1 - Copy all formulas

Add ‘cells.copyRow( cells, 8, 9 );’ after ‘cells.insertRows( 9, 10 );’


copyRow method generates a java exception:



java.lang.ArrayIndexOutOfBoundsException: 28

at com.aspose.cells.v.a(Unknown Source)

at com.aspose.cells.Cells.a(Unknown Source)

at com.aspose.cells.Cells.copyRow(Unknown Source)

at Test.main(Test.java:21)



Method #2 - Copy a formula in a single column

Add ‘cells.copyCellRange( cells, 8, 20, 9, 20, 11, 2 );’ after ‘cells.insertRows( 9, 10 );’



Using Cells copyCellRange with a row count of 11 (not 10) and a column
count of 2 (not 1) was the only way I could copy the formula in U9 to
U10:U19 but the original formula in U20 gets erased?

Which method would be the fastest since I could be inserting 1000+ rows?

Regards,
Dan

7

Hi Dan,

Please try this fix.


For this two method, copyCellRange should be the faster one for your current case. But for the usage of copyCellRange method, I think you had better change the code:
cells.copyCellRange( cells, 8, 20, 9, 20, 10, 1 );
to:
for(int i=9; i<19; i++)
{
cells.copyCellRange(cells, 8, 20, i, 20, 1, 1);
}

For current fixed version, this two code snatches give same result, but in fact, "cells.copyCellRange( cells, 8, 20, 9, 20, 10, 1 );" should not give the result you wished if it acts same with MS Excel.

In MS Excel, if you select area from row 8 to 18 at column 20 and copy it into the area from row 9 to 19 at column 20, what you can get is only the cell “U10” having new value, following cells from “U11” down to “U19” keeps blank. So, maybe we will modify the logic of copyCellRange method to make it act same with Ms Excel in next version, that will cause only the second code snatch by a For-loop to give the result you want.

8

Aspose Nanjing Team,

Thanks for the Cells.copyRow fix, clearing up my confusion over Cells.copyCellRange usage and for the great product support!

Regards,
-Dan