Free Support Forum - aspose.com

Problems with Range.Copy; please explain Cells.CopyColumn

Really what I want to do is copy an entire column. Why does the Cells.CopyColumn take 3 parameters? The first parameter is what is confusing (sourceCells) because it seems to me to copy a column I should just need to specify a source index and destination index. Seems to work when I call:

_cells.CopyColumn(_cells, 5, 6)

but is very unintuitive. Additionally, why is the 2nd parameter of the CreateRange method a byte? Why not just an int to be consistent?

The range copy below doesn't seem to work...

Range r1 = _worksheet.Cells.CreateRange(0, _worksheet.Cells[0, 5].Column, 171, 1);
Range r2 = _worksheet.Cells.CreateRange(0, _worksheet.Cells[0, 6].Column, 171, 1);
r2.Copy(r1);

I get:

System.ArgumentOutOfRangeException: Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: startIndex

at System.BitConverter.ToUInt16(Byte[] value, Int32 startIndex)

at Aspose.Excel.Record.?.?(? ?, Int32 ?, Byte ?)

at Aspose.Excel.Cell.Copy(Cell cell)

at Aspose.Excel.Range.Copy(Range range)

Thanks!

1. About the CopyColumn method, the first parameter specify the source cells. If you copy a column with a same worksheet, this parameter seems to be no use. However, if you want to copy a column from another worksheet, it's a must.

2. About the CreateRange method, column index is between 0 and 255, so I make it as byte type. I think your suggestion is better. I will change it to int type in the next release to make it consistent. Thank you.

3. About the Range.Copy problem, which version of Aspose.Excel are you using? Is it v3.6? Could you please post your template file and sample code here? I will check it ASAP.

It turns out that the problem with the Range.Copy is that you can't copy a Range onto itself. In my code I was using the first column as a "template" to add additional columns each time through a loop. I was copying the first column to the current column each time through the loop. Thus, the Range.Copy was failing for the first copy. This has different ramifications for the Range.Copy and Worksheet.CopyColumn. The WorkSheet.CopyColumn doesn't fail the first time through (copying onto itself), but subsequent copies of the column loose conditional formatting.

To fix this, I don't do the copy for the first column by checking if (cols ==0) //[see code below].

I've been evaluating Aspose.Excel [3.5.3.0].

Also, your answers above make sense. I didn't consider for #1 copying a column from a different worksheet. I guess it would have been a little more intuitive if I were calling CopyColumn from a worksheet or passing in a worksheet as the first parameter.

When you say you will "change it..." hopefully it will still support the old way (byte) so that old code written against it won't break.

Just my 2 cents...

Thanks!

//_app is a valid Excel instance...
//_ws is a valid worksheet in _app...
for (int cols=0; cols<_app.Worksheets.Count; cols++)
{
//...

//without the if, this fails
if (cols>0)
{
Range r1 = _ws.Cells.CreateRange(0, Convert.ToByte(5), 171, 1);
Range r2 = _ws.Cells.CreateRange(0, Convert.ToByte(cols+5), 171, 1);
r2.Copy(r1);
}

//works, but looses conditional formating for columns 2...n
_ws.Cells.CopyColumn(summary.Cells, 5, cols+5);

//winner...
if (cols>0)
_ws.Cells.CopyColumn(summary.Cells, 5, cols+5);

//...
}

Thanks for your information. I will change the Range.Copy method and CopyColumn method to avoid the problem to copy itself.

For CreateRange method, my modification won't break any of your code.