how to copy a worksheet

Jan 17, 2011 at 11:57 AM

How do I copy a worksheet?
Using the interop a assembly I can do 
    excelWorkSheet.Copy(After: excelWorkSheet)


how do I do it using closedxml?

One option is with Range but it lose the stye format

var agetnWorksheet = workbook.Worksheets.Add("new");//copy the template to a new TAB            
var firstTableCell = worksheet.FirstCellUsed();            
var lastTableCell = worksheet.LastCellUsed();          
 var rngData = worksheet.Range(firstTableCell.Address, lastTableCell.Address);
agetnWorksheet.Cell(1, 1).Value = rngData;


any idea?


Jan 17, 2011 at 6:39 PM
I'll add a way to copy worksheets, probably something like ws.CopyTo(String newWorksheetName)
Jan 17, 2011 at 10:38 PM

that would be great!


Jan 18, 2011 at 4:46 AM

I added the following methods to the worksheet interface (they'll be on the next release):

IXLWorksheet CopyTo(String newSheetName);
IXLWorksheet CopyTo(String newSheetName, Int32 position);

And you'll be able to copy worksheets from one workbook to another too:

IXLWorksheet CopyTo(XLWorkbook workbook, String newSheetName);
IXLWorksheet CopyTo(XLWorkbook workbook, String newSheetName, Int32 position);

I'd like you to create two tickets in the Issue Tracker:

1) The code you provided works for me. Please create a ticket and attach the file with the worksheet you're copying so I can see what styles are being lost. (rows/columns heights/widths will not be copied because the code is copying a range, this is normal)

2) Before releasing I want to tackle that file of yours that doesn't save right (fixing/enhancing what's already done is priority #1 for me). Please attach it to a ticket in the Issue Tracker.


Thanks for the feedback,


P.S. The next release will also have the method range.RangeUsed() which will reduce the boilerplate code to accomplish this common task. In your case the 3 lines of code (getting the first and last cell and then using the addresses) will be reduced to:

var rngData = worksheet.RangeUsed();

Better yet, you'll be able to reduce all 5 lines to a single one:

workbook.Worksheets.Add("new").FirstCell().Value = worksheet.RangeUsed();

To be fair, right now you can do it in one line too, it just doesn't look as good =)

workbook.Worksheets.Add("new").FirstCell().Value = worksheet.Range(worksheet.FirstCellUsed(), worksheet.LastCellUsed());


Jan 19, 2011 at 3:58 AM

v0.42 has these changes.

Thanks for the feedback. I really appreciate it.