Copy Ranges or Entire Worksheets
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.
The code comes from sample 1.4 - C# / Visual Basic
//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"]);From EPPlus 5.8 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:
Sometimes it's useful to copy a range and remove the formulas:
var ws = p.Workbook.Worksheets.Add("CopyValues");
//Add some numbers and formulas and calculate the worksheet
ws.Cells["A1:A10"].FillNumber(1);
ws.Cells["B1:B9"].Formula = "A1+A2";
ws.Cells["B10"].Formula = "Sum(B1:B9)";
ws.Calculate();
//Now, copy the values starting at cell D1 without the formulas.
ws.Cells["A1:B10"].Copy(ws.Cells["D1"], ExcelRangeCopyOptionFlags.ExcludeFormulas);You can also use the CopyStyles method to copy and fill styles to a destination range.
//Copy the styles from the sales report.
//If the destination range is larger that the source range styles are filled down and right using the last column/row of the source range.
sourceWs.Cells["A1:G5"].CopyStyles(ws.Cells["A1:G50"]);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);