Free Support Forum - aspose.com

Copy cells between Worksheets

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

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.

Ah, I get it. Many thanks :wink: