Copy cells between Worksheets

Aspose.Cells Product Family
1

Hello,



I’m trying to move or copy a range of cells between worksheets, but I
can’t seem to find a suitable method. I’ve searched the forum, but
can’t seem to find an answer. Is there an easy way to do this?



Thanks in advance,

Cruz

2
Hi Cruz,

Thanks for considering Aspose.

Well, There are no. of Options. You may use Range.Copy(rangeObject) method to copy the range with formattings. And If you want to copy only data from a range of cells you may export a range's data to fill a datatable using Range.ExportDataTableAsString() method and in other worksheet you may get the data using Cells.ImportDataTable() method. And Alternatively, you may also use Cells.ExportDataTableAsString() method to export the data to a data table.

May the following code help you for your need.

// 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];

//Define a style object adding a new style
//to the collection list.
Style stl2 = workbook.Styles[workbook.Styles.Add()];
//Set the custom cell shading color.
stl2.ForegroundColor = Color.Yellow;
//Set the solid background pattern for fillment color.
stl2.Pattern = BackgroundType.Solid;
//Set the font.
stl2.Font.Name = "Trebuchet MS";
//Set the font color.
stl2.Font.Color = Color.Maroon;
//Set the font size.
stl2.Font.Size = 10;
//Set the style flag struct.
StyleFlag flag = new StyleFlag();
//Apply cell shading.
flag.CellShading = true;
//Apply the font.
flag.FontName = true;
//Apply the font color.
flag.FontColor = true;
//Apply the font size.
flag.FontSize = true;


// Create a named range of the Cells
Range range = worksheet.Cells.CreateRange("A1", "C9");
// Set the name of the named range
range.Name = "TestRange1";
range.ApplyStyle(stl2,flag);
// Add a new Worksheet
int index =workbook.Worksheets.Add();
// Access the Worksheet
Worksheet testsheet = workbook.Worksheets[index];
// Create a DataTable
DataTable dt = new DataTable("testtable");
// Export data to the DataTable
dt =range.ExportDataTableAsString();
// Fill the Cells of the new sheet from the DataTable, only data will be obtained.
/*
for (int i = 0;i< dt.Rows.Count; i++)
{
for (int j=0;j<dt.Columns.Count; j++ )
{
testsheet.Cells[i,j].PutValue(dt.Rows[i][j]);

}
}
*/
//OR
// Fill the Cells of the new sheet from the DataTable, only data will be obtained.
testsheet.Cells.ImportDataTable(dt,false,"A1");

// Adjust the rows and columns of the sheet.
testsheet.AutoFitColumns();
testsheet.AutoFitRows();
// Create another Range based on A21..C29 Cells.
Range range2 = worksheet.Cells.CreateRange("A21", "C29");
// Name the range.
range2.Name = "TestRange2";
// Get the total no. of columns in the range.
int cols = range2.ColumnCount;
// Get the total no. of rows in the range.
int rows = range2.RowCount;
// Get the first row index.
int frow = range2.FirstRow;
// Get the first column index
int fcol = range2.FirstColumn;
//MessageBox.Show(cols.ToString());
//MessageBox.Show(range2.RowCount.ToString());
//Copy the range with data and formattings
range2.Copy(range);
// Save the modified Excel file in default format
workbook.Save("d:\\test\\cellsranges_out.xls",FileFormatType.Default);

Thank you.

3

Ah, I get it. Many thanks :wink: