Aspose.Cells.copy behaviour differs from Excel equivalent

The Aspose.Cells.copyCellRange does not update formulas in the same way as the corresponding "manual" operations in Excel. Moreover, copyCellRange and copyColumn seems to update formulas in an "inconsistent" manner.

  • Aspose.Cells.copyColumn gives the expected result (i.e. same behaviour as Excel).
  • Aspose.Cells.copyRow does not update references that refer to other rows than the current row. Seems like references to other than the current row are treated as absolute.
  • Aspose.Cells.copyCellRange behaves in the same way as Aspose.Cells.copyRow when cells are copied from one row to another, but behaves differently from Aspose.Cells.copyColumn when copying from one column to another. References to other than the current column are treated as absolute.

Is there any way I can get the Aspose.Cells.copy* methods to behave in the same way as the correspondin "manual" operations in Excel (I'm using a recent version og Aspose.Cells for Java)?

Example

A B C D
12 10.5
13 5.25 =B13*C12
14

Result of manual operations in Excel; copy C13 to D13, copy C13 to C14:

A B C D
12 10.5
13 5.25 =B13*C12 =C13*D12
14 =B14*C13

Using Aspose.Cells.copyColumn; copy column C to column D:

A B C D
12 10.5
13 5.25 =B13*C12 =C13*D12
14

Using Aspose.Cells.copyRow; copy row 13 to row 14:

A B C D
12 10.5
13 5.25 =B13*C12
14 =B14*C12

Using Aspose.Cells.copyCellRange; copy C13 to D13, copy C13 to C14:

A B C D
12 10.5
13 5.25 =B13*C12 =B13*D12
14 =B14*C12

Hi,

Thanks for sharing the details.

Normally, Aspose.Cells for Java copy operations works in the same way as MS Excel does. We are checking it. But, we appreciate if you could post your sample template and output files with the related sample codes, it will help us really to figure out the problem (if we find) more accurately.

Thanks for your time and understanding.

Thanks for your quick reply. Please find input and result files attached.

Regards,

Hanne

Test program source code:

import java.io.IOException;

import com.aspose.cells.Cell;
import com.aspose.cells.Cells;
import com.aspose.cells.Workbook;
import com.aspose.cells.Worksheet;

public class CopyTest {

public static void main(String[] args) throws IOException {
	Workbook workbook = new Workbook();
	workbook.open("Input.xls");
	
	Worksheet sheet1 = workbook.getWorksheets().addSheet("ColumnCopy");
	Cells cells1 = sheet1.getCells();
		
	Cell Sheet1_B13 = cells1.getCell("B13");
	Cell Sheet1_C12 = cells1.getCell("C12");
	Cell Sheet1_C13 = cells1.getCell("C13");
	Sheet1_B13.setValue(5.25);
	Sheet1_C12.setValue(10.5);
	Sheet1_C13.setFormula("=B13*C12");
		
	cells1.copyColumn(cells1, Sheet1_C13.getColumnIndex(), Sheet1_C13.getColumnIndex() + 1);
		
	Worksheet sheet2 = workbook.getWorksheets().addSheet("RowCopy");
	Cells cells2 = sheet2.getCells();
		
	Cell Sheet2_B13 = cells2.getCell("B13");
	Cell Sheet2_C12 = cells2.getCell("C12");
	Cell Sheet2_C13 = cells2.getCell("C13");
	Sheet2_B13.setValue(5.25);
	Sheet2_C12.setValue(10.5);
	Sheet2_C13.setFormula("=B13*C12");
		
	cells2.copyRow(cells2, Sheet2_C13.getRowIndex(), Sheet2_C13.getRowIndex() + 1);
		
	Worksheet sheet3 = workbook.getWorksheets().addSheet("CellRangeCopy");
	Cells cells3 = sheet3.getCells();
		
	Cell Sheet3_B13 = cells3.getCell("B13");
	Cell Sheet3_C12 = cells3.getCell("C12");
	Cell Sheet3_C13 = cells3.getCell("C13");
	Sheet3_B13.setValue(5.25);
	Sheet3_C12.setValue(10.5);
	Sheet3_C13.setFormula("=B13*C12");
		
	cells3.copyCellRange(cells3, Sheet3_C13.getRowIndex(), Sheet3_C13.getColumnIndex(), Sheet3_C13.getRowIndex()+1, Sheet3_C13.getColumnIndex(), 1, 1);
	cells3.copyCellRange(cells3, Sheet3_C13.getRowIndex(), Sheet3_C13.getColumnIndex(), Sheet3_C13.getRowIndex(), Sheet3_C13.getColumnIndex()+1, 1, 1);
		
	workbook.save("Result.xls");
}

}

Hi,

Thanks for providing us the template files with sample codes.

I have tested your codes with the template file. I found some issues with Cells.copyRow() and Cells.copyCellRange() methods as you have described. I confirmed the issues manually trying your scenario in MS Excel.

We will figure out the issue(s) soon.

Thank you.

Hi,

Please try this fix, thank you.

Works fine. Thank you!