Copy Ranges or Entire Worksheets

Copy a range of cells

To copy a range of cells you use the ExcelRangeBase.Copy method. The sample below copies a range from the Sales Report generated in Sample 8 into a new workbook.

//Add a new worksheet
var ws = p.Workbook.Worksheets.Add("CopyValues");

//Use the first 10 rows of the sales report in sample 8 as the source.
var sourceRange = sourceWs.Cells["A1:G10"]; 

//Copy the source range to the destination range. 
//Only one cell is needed for the destination as the size of source range determines the copied size.
sourceRange.Copy(ws.Cells["C1"]);
```C#

You can also exclude different cell properties in the copy operation:

//Copy the same source range to C15 and exclude the hyperlinks.
//We also remove the Hyperlink style from the range containing the hyperlinks, so the blue underline is removed.
sourceRange.Copy(ws.Cells["C15"], ExcelRangeCopyOptionFlags.ExcludeHyperLinks);
ws.Cells["D19:D24"].StyleName = "Normal";

//Copy the values only, excluding merged cells, styles and hyperlinks.
sourceRange.Copy(ws.Cells["C30"], ExcelRangeCopyOptionFlags.ExcludeMergedCells, ExcelRangeCopyOptionFlags.ExcludeStyles , ExcelRangeCopyOptionFlags.ExcludeHyperLinks);

//Copy styles and merged cells, excluding values and hyperlinks.
sourceRange.Copy(ws.Cells["C45"], ExcelRangeCopyOptionFlags.ExcludeValues, ExcelRangeCopyOptionFlags.ExcludeHyperLinks);

Here is what the end result will look like:

Images\Copy\CopyRange.PNG

Copy an entire worksheet.

Sometimes it's usefull to use an existing worksheet as a template when adding a new one. This can easily be done by supplying the template worksheet when adding the new worksheet:

//To copy the entire worksheet just add the source worksheet as parameter 2 when adding the new worksheet.
p.Workbook.Worksheets.Add("CopySalesReport", sourceWs);