Cut and paste cells b/w MS Excel worksheets using Aspose.Cells in .NET

Aspose.Cells Product Family
1

Hi All,

I want to cut cell “A1” in worksheet “sheet1” and past selected cell to worksheet “sheet2”.

Is it possible to do that ? Could you give me the sample code to do please ?

Thanks,
Kimny.

2

Hi,

Please see the code below. It copies first row from sheet1 to sheet2.

I have attached both the input and output files. You can modify this code to suit your requirements.

C#


string path = @“F:\Shak-Data-RW\Downloads\source.xlsx”;


Workbook workbook = new Workbook(path);


Worksheet sheet1 = workbook.Worksheets[0];

Worksheet sheet2 = workbook.Worksheets[1];


//Copy the first row from sheet1 to sheet2

sheet2.Cells.CopyRows(sheet1.Cells, 0, 0, 1);


workbook.Save(path + “.out.xlsx”);


3

Hi Kimny,


Another way to do it, please see the following simple sample code that demonstrates your needs.

Sample code:
// Create a Workbook.
Workbook excel = new Workbook();
// Create a Cells object obtaining all the cells of the first
// (default)Worksheet.
Cells cells = excel.Worksheets[0].Cells;
// Create a Cell object and get the A1 cell.
Aspose.Cells.Cell cell = cells[0, 0];
// Put a string value into it.
cell.PutValue(“Testing…”);
//Adding another sheet.
Worksheet sheet2 = excel.Worksheets[excel.Worksheets.Add()];
sheet2.Name = “MySheet2”;
// Get the D1 cell in the second sheet.
Aspose.Cells.Cell cell1 = sheet2.Cells[“D1”];
// Copy the cell.
cell1.Copy(cell);
// Save the excel file.
excel.Save(@“e:\test2\testcopyofcells.xls”);

Also, there are some other ways to do it (you may create ranges in the worksheets and copy one range to other), please see the document/article for your reference:
http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/copy-range-data-with-style.html

Thank you.






4

Hello All,

Actually I want to cut and pasts cells from Sheet1 to Shee2 as the same way I do in Microsoft Excel. Cut and pasts functionality should be like this:
1. Copy selected cell
2. Pasts the selected to destination cell
3. Remove the original selected cell.

Could we do like this ?

Thanks and Best Regards,
Kimny.

5

Hi,


Well, you may use Cells.ClearRange() or Cells.DeleteRange() method accordingly. See the updated code (it has one extra line for your need.)

Sample code:

// Create a Workbook.
Workbook excel = new Workbook();
// Create a Cells object obtaining all the cells of the first
// (default)Worksheet.
Cells cells = excel.Worksheets[0].Cells;
// Create a Cell object and get the A1 cell.
Aspose.Cells.Cell cell = cells[0, 0];
// Put a string value into it.
cell.PutValue(“Testing…”);
//Adding another sheet.
Worksheet sheet2 = excel.Worksheets[excel.Worksheets.Add()];
sheet2.Name = “MySheet2”;
// Get the D1 cell in the second sheet.
Aspose.Cells.Cell cell1 = sheet2.Cells[“D1”];
// Copy the cell.
cell1.Copy(cell);

//Remove the original cell data in the first sheet.
cells.ClearRange(cell.Row, cell.Column, cell.Row, cell.Column);
//Note: you may also use Cells.DeleteRange() according to your needs.

// Save the excel file.
excel.Save(@“e:\test2\testcopyofcells.xls”);


Thank you.
6

Hi Amjad Sahi,

Cells.ClearRange() and Cells.DeleteRange() method will remove the selected cell range without reference to new cell. You can see if you click Ctrl + X in Microsoft Excel, and pasts to another cell, the selected cell will be cut and reference to new cell.
For example, If I have A1,A2,A3,A4 with the value.
A1 = 10
A2 = 20
A3 = 30
A4 = Sum(A1:A3)

I want to cut A1:A3 from sheet1 to sheet2. So I should have data in Sheet2 like this:
A1 = 10

A2 = 20

A3 = 30
Sheet1 should have data:
A4 = Sum(Sheet2!A1:A3)

Can you help me to solve this issue please ?

Thanks & Regards,
Kimny.

7

Hi,


Well, we do provide Cells.MoveRange() method for this purpose, but currently it moves range in the same worksheet. The following code will work fine, but in the same worksheet:

// Create a Workbook.
Workbook excel = new Workbook();
// Create a Cells object obtaining all the cells of the first
// (default)Worksheet.
Cells cells = excel.Worksheets[0].Cells;
// Create a Cell object and get the A1 cell.
Aspose.Cells.Cell cell = cells[0, 0];
// Put values to the cells A1:A3.
cell.PutValue(10);
cell = cells[“A2”];
cell.PutValue(20);
cell = cells[“A3”];
cell.PutValue(30);
cell = cells[“A4”];
//Set formula to A4 cell.
cell.Formula = “=Sum(A1:A3)”;

CellArea ca = new CellArea();
ca.StartRow = 0;
ca.StartColumn = 0;
ca.EndRow = 2;
ca.EndColumn = 0;

cells.MoveRange(ca, 9, 0);

// Save the excel file.
excel.Save(@“e:\test2\testcopyofcells1.xls”);

I have logged an enhancement request with an id: CELLSNET-30060. We will look into it and get back to you soon.

Thank you.


8

Thank you, I will look forward to see the new enhancement.
I really need to move range in another worksheet to make cut and pasts functionality the same as Microsoft Excel one.

9

Hi,

Please try the following code:

C#


// Create a Workbook.

Workbook excel = new Workbook();


// Create a Cells object obtaining all the cells of the first

// (default)Worksheet.

Cells cells = excel.Worksheets[0].Cells;


// Create a Cell object and get the A1 cell.

Aspose.Cells.Cell cell = cells[0, 0];


// Put values to the cells A1:A3.

cell.PutValue(10);

cell = cells[“A2”];

cell.PutValue(20);

cell = cells[“A3”];

cell.PutValue(30);

cell = cells[“A4”];


//Set formula to A4 cell.

cell.Formula = “=Sum(A1:A3)”;


Range range1 = cells.CreateRange(“A1:A4”);


//Adding another sheet.

Worksheet sheet2 = excel.Worksheets[excel.Worksheets.Add()];

sheet2.Name = “MySheet2”;


// Get cells in the second sheet.

Aspose.Cells.Cells cells2 = sheet2.Cells;

Range range2 = cells2.CreateRange(“A1:A4”);


CellArea ca = new CellArea();

ca.StartRow = 0;

ca.StartColumn = 0;

ca.EndRow = 2;

ca.EndColumn = 0;


range2.Copy(range1);


range1.Value = null;


// Save the excel file.

excel.Save(@“D:\FileTemp\dest.xls”);

10

Hi,


Well, as you need to cut the range i.e. A1:A3 cells from Sheet1 to paste it into other sheet. Actually, you have a formula set in the A4 cell (Sheet1) that should be updated accordingly when the range (A1:A3) from Sheet1 is cut from sheet1 to be pasted in the second sheet.
E.g
If we change the above code with the line:
Range range2 = cells2.CreateRange(“A1:A4”);
to:
Range range2 = cells2.CreateRange(“A1:A3”);
and,
Range range1 = cells.CreateRange(“A1:A4”);
to:
Range range1 = cells.CreateRange(“A1:A3”);

In the generated file, the formula in A4 cell in Sheet1 will still refer to:
=Sum(A1:A3)
instead of:
=Sum(MySheet2!A1:A3) as you actually wanted.
so, this issue would still remain.

We will look into the issue (already registered) later on if we can provide some solution for it. In the mean time, I am afraid, you have to manually do it, I mean you have to update the formula in the Sheet1 accordingly.

Thank you.
11

Hi Amjad Sahi,

Thank you for your support, now you understand my problem well.

Now I think I should wait for your solution rather than I have to update formula manually.

Thanks & Regards,
Kimny.