-
Notifications
You must be signed in to change notification settings - Fork 287
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"]);
This method is available from EPPlus 8.0. If the destination range is larger than the source range, and the number of cells in the destination range is evenly divisible by the number of cells in the source range, the values from the source range will be repeated in the destination range. For example, if the source range contains the values 1 and 2, and the destination range has 4 cells, it will be filled with 1, 2, 1, 2.
ws.Cells["A1:A2"].CopyFill(ws.Cells["B1:B4"]);
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);
EPPlus Software AB - https://epplussoftware.com
- What is new in EPPlus 5+
- Breaking Changes in EPPlus 5
- Breaking Changes in EPPlus 6
- Breaking Changes in EPPlus 7
- Breaking Changes in EPPlus 8 (beta)
- Addressing a worksheet
- Dimension/Used range
- Copying ranges/sheets
- Insert/Delete
- Filling ranges
- Sorting ranges
- Taking and skipping columns/rows
- Data validation
- Comments
- Freeze and Split Panes
- Header and Footer
- Autofit columns
- Grouping and Ungrouping Rows and Columns
- Formatting and styling
- Conditional formatting
- Using Themes
- Working with custom named table- or slicer- styles