Copying worksheet data (only) to another workbook

Aspose.Cells Product Family
1

hi,

I have a worksheet which contains different formulas and I want to copy it to another workkbook but without the formulas (data only).

the copy function of the worksheet copies also the formulas.

what is the correct way to do so?

2

Hi,

First you should copy all the data from one worksheet into another worksheet using the following code that uses range and copy range methods. The code basically copies first few cells from one worksheet and add another sheet, you can modify it according to your needs

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”);


Once you are done, then you should convert all formulas into values using the following code.

C#

workbook.CalculateFormula(true);

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

for(int j = 0; j < cells.Count; j ++)
{

Cell cell = cells[ j ];


if(cell.IsFormula)
{

cell.PutValue(cell.Value);

}

}