-
Notifications
You must be signed in to change notification settings - Fork 275
Copy Ranges or Entire Worksheets
Mats Alm edited this page Dec 4, 2023
·
12 revisions
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);
EPPlus Software AB - https://epplussoftware.com
- Getting Started
- The Sample Project
- EPPlus and Docker
- EPPlus and Blazor
- EPPlus and Linux
- Security
- Configuration
- Releases/versioning
- What is new in EPPlus 5+
- Breaking Changes in EPPlus 5
- Breaking Changes in EPPlus 6
- Breaking Changes in EPPlus 7
- 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