Free Support Forum - aspose.com

Problems with Cells.insertRangeShiftToRight for Aspose 2.2.0.0

Hi,

The cell references gets wrong when calling Cells.insertRangeShiftToRight.

Attached you will find a template file and the result file.
Test program is found below.

When you open the result file you get a #REF error and the references has been corrupted.

Output from test program:
----------------------------------
Aspose version : 2.2.0.0

Before insertRangeShiftToRight
A13 : =A3/100A$9 B13 : =B3/100B$9
A14 : =A4/100A$9 B14 : =B4/100B$9

After insertRangeShiftToRight
A13 : =A3/100A$65521 B13 : =B3/100B$65533 C13 : =C3/100C$65521
A14 : =A4/100
A$65521 B14 : =B4/100B$65521 C14 : =B4/100B$9
=======================

Test program:
--------------
import java.io.FileInputStream;
import java.io.IOException;

import com.aspose.cells.AsposeLicenseException;
import com.aspose.cells.Cells;
import com.aspose.cells.CellsHelper;
import com.aspose.cells.License;
import com.aspose.cells.Workbook;

public class RangeExpand {

private static void printCellFormulas(Cells cells, int fromRow, int fromCol, int toRow, int toCol){
for (int row = fromRow;row<=toRow;row++){
for (int col = fromCol;col<=toCol;col++){
System.out.print(""+new Character((char)(65+col))+(row+1)+" : “+cells.getCell(row, col).getFormula()+”\t");
}
System.out.println();
}
System.out.println();
}
private static void expandRow(Cells cells, int row){

cells.insertRangeShiftToRight(row, 1, row, 1);

cells.copyCellRange(cells, row, 0, row, 1, 1, 1);

}
public static void main(String[] args) throws IOException, AsposeLicenseException {

License lic = new License();
lic.setLicense(new FileInputStream(“lib/Aspose.Cells.lic”));

System.out.println(“Aspose version : " + CellsHelper.getReleaseVersion()+”\n");

Workbook workbook = new Workbook();

workbook.open(“Excel/RangeExpandTemplate.xls”);

Cells cells = workbook.getWorksheets().getSheet(“Production Calculation”).getCells();

System.out.println(“Before insertRangeShiftToRight”);
printCellFormulas(cells, 12, 0, 13, 1);

for (int row=12;row<=13;row++){
expandRow(cells, row);
}
System.out.println(“After insertRangeShiftToRight”);
printCellFormulas(cells, 12, 0, 13, 2);

workbook.save(“Excel/RangeExpandResult.xls”);
}

}

Hi,



Please try the attached latest fix v2.2.0.1, I have tested and it works
fine accordingly.



Thank you.

When using 2.2.0.1 I get:

=================================================
Aspose version : 2.2.0.1

Before insertRangeShiftToRight
A13 : =A3/100A$9 B13 : =B3/100B$9
A14 : =A4/100A$9 B14 : =B4/100B$9

After insertRangeShiftToRight
A13 : =A3/100A$9 B13 : =B3/100B$9 C13 : =C3/100C$9
A14 : =A4/100
A$9 B14 : =B4/100B$9 C14 : =B4/100B$9

=================================================

However, looking at C13 and C14 I find some differences.
C13 is refering to C and C14 is refering to B.
When I do it manually in Excel then i get C13 refering to B.

When opening the produced spreadsheet I still get =#REF! for C13

Egil

Hi Egil,

I have found the issue as you have mentioned. We will figure it out soon.
We have logged your issue into our issue tracking system with an issue id: CELLSJAVA-16421. We will update you once it is sorted out.

Thank you.

This is still an issue for Aspose 2.2.1

The attached result file has errors when opening it.

The outputr from my test program is:

=======================================

>>> Aspose version : 2.2.1.0

---------------------------------------

Excel template: Excel/RangeExpandTemplate.xls

---------------------------------------------

Before insertRangeShiftToRight

A13 : =A3/100*A$9 B13 : =B3/100*B$9

A14 : =A4/100*A$9 B14 : =B4/100*B$9

After insertRangeShiftToRight

A13 : =A3/100*A$9 B13 : =B3/100*B$9 C13 : =C3/100*C$9

A14 : =A4/100*A$9 B14 : =B4/100*B$9 C14 : =B4/100*B$9

Hi,

Well, this issue is still under process and not resolved yet.

Once we resolve the issue, we will let you know about it.

Thank you.

Hi,

This issue has been fixed. Please download the latest version: Aspose.Cells for Java 7.0.1