Move cells within worksheet

Aspose.Cells Product Family
1

Hello again.

I’m having some difficulties finding a way to move cells within a worksheet. I was trying to use the Range.Copy(rng) method but, obviously, this method only copies the cells, that is, no formulas are updated as they would be in MS Excel, when I move cells from one place to another.

In other words, I would like to be able to pick some cells of the sheet, move them to another location (within the same sheet) and have the formulas that pointed to those cells automatically updated to the new locations. Is this already possible? If not, is it something you are considering to develop in a near future? It would really be helpful!

Thanks again,
Cruz

2

Hi,

Well, I tested your scenario a bit with 4.4.1.15 fix and it works fine same as MS Excel. Do you use that version / fix (4.4.1.15).

Here is my sample code and the output file is fine with the formulas are updated accordingly.

// Create a Workbook object
Workbook workbook = new Workbook();
// Open the Excel file
workbook.Open("d:\\test\\book1.xls");
// Get the first worksheet in the Excel file
Worksheet worksheet = workbook.Worksheets[0];
// Create a named range of the Cells
Range range1 = worksheet.Cells.CreateRange("A1", "C9");
// Get the source range of cells...i.e., A21..C29. //has some forumla like = Sum(C21:C22) into C24 cell.
Range range2 = worksheet.Cells.CreateRange("A21", "C29");
// Copy the data with formatting of the range to the first range.
range1.Copy(range2);
// (Optional) Clear the contents of the source range cells with formattings.
worksheet.Cells.ClearRange(20,0,28,2);
// Save the modified Excel file in default (Excel 2003) format
workbook.Save("d:\\test\\copiedrangebook.xls",FileFormatType.Default);

If you still find the problem, post your template excel file with the sample code, we will check it soon.

Thank you.

3

Hello again,

Yes, your code does work fine… but that is not the issue… let me give you an example:

Lets say we have the following table (where Total = Products * Price) :

Total Products Price
Item A 30 3 10
Item B 50 10 5

Now lets say that I want to take this table and turn it into this one:

Total Products

Item A 30 3

Item B 50 10

Price
10
5


To achieve this, I have to move the 3 “Price” cells to another place within the sheet but I want the “Total” formula to auto update the referenced cells in order to continue showing the correct values (as they would in Excel). Obviously, this can’t be achieved with the copy tool, because when you copy the cells, you are only copying their values (and the formulas they contain), but because you don’t move them, the cells that reference these “moving” cells, are not updated.

I’ll try to post a sample code here, along with a template file.

Thanks again,
Cruz

4

Now I understand your need. Actually you need a “Cut and Paste” feature. We will check how to make it. Maybe we can provide a Range.Move method.

5

Yeap, that would be the ideal way. If you can provide me with timings for this development, I would very much apreciate.

Thanks again for your great work

6

Actually Range.Move method is a complex feature and it's very time consuming because we have to look through the whole workbook to check all cells if they contain formulas referring to this range. Hopefully we will make it in the next week.

And please use this method very carefully because it will degrade the performance. If your file is very large, we suggest not to use this method.

7

Ok, thanks for the advice and the timing. Actually the book where I want to use this method is small, so it shouldn’t be a problem for me. Thanks again for the advice.

Cruz

8

Hi again,

I was wondering if you have any news for me, regarding the method to move cells?

9

Hi,

Thanks for your inquiry,

We will update you soon.

Thank you.

10

Hi,

Please try this fix.Please use the method Cells.MoveRange(CellArea sourceArea,int destRow,int destColumn) .We only support to move the value ,formula and style of cell in this feature.

See following codes:

Workbook workbook = new Workbook();
Cells cells = workbook.Worksheets[0].Cells;
CellArea ca = new CellArea();
ca.StartColumn = 2;
ca.EndColumn = 3;
ca.StartRow = 0;
ca.EndRow = 2;
cells.MoveRange(ca, 4, 3);

11

Hi, Warren.

It works just fine. I tried both the Range.MoveTo and the Cell.MoveRange methods.

To be perfect I think its just missing the move of merged cells also. Are you thinking of implementing this soon?

Thanks for the help.

Cruz

12

Hi Cruz,

Thanks for your info.

We will support it soon.Thanks for your patience.

13

Hi Cruz,

Please try this fix.

We have supported to move merged cells.

14

Hello Warren,

Once again it’s perfect. It works just fine. Thanks for your quick support.

Keep up the good work.
Cruz