This project has moved. For the latest updates, please go here.

Issue while copying worksheets

Jun 24, 2014 at 3:08 PM
Hi,

I am trying to copy worksheets from one workbook to another. But AddWorksheet() & copyTo() methods throws "An item with the same key has already been added" exception while copying.

can anyone figure it out, what is the issue???

Here is the code:
var workbook = new XLWorkbook();
WriteStaticSheets(workbook);

public void WriteStaticSheets(XLWorkbook wb)
{
            var workbookTemplate = new XLWorkbook("D:\\test.xlsx");
            foreach(var ws in workbookTemplate.Worksheets)
            {
                wb.AddWorksheet(ws); // throws "An item with the same key has already been added" exception
                 ws.CopyTo(wb,ws.Name);  //even this throws the same exception
            }
}
Coordinator
Jun 24, 2014 at 3:14 PM
There's already a sheet with the same name in the workbook.
Jun 25, 2014 at 6:31 AM
MDeLeon.. Thanks for the reply..

That workbook doesnt have any worksheets in it.. I am getting the same error even I change it to following code.
public void WriteStaticSheets(XLWorkbook wb)
        {
            var workbookTemplate = new XLWorkbook("D:\\BIP Forecast Template - Guide.xlsx");
           foreach(var ws in workbookTemplate.Worksheets)
            {
                if (!wb.Worksheets.Where(x => x.Name == ws.Name).Any())
                {
                    wb.AddWorksheet(ws);
                }
            }
        }
Jun 25, 2014 at 1:39 PM
Your code does the following.
1) find a worksheet in the workbook
2) add that worksheet into the same workbook without changing the name

The result is that it finds that a worksheet already exists in the workbook with that name.
You need to give it a new name when you make the copy.
You would need to do "ws.CopyTo(wb,"NewName");
or "ws.CopyTo("NewName");

Check out the documentation https://closedxml.codeplex.com/wikipage?title=Copying%20Worksheets
Jun 26, 2014 at 7:25 AM
roberttanenbaum,

If you notice, my code doesn't add the worksheet into the same workbook, instead it adds into the workbook wb which is sent as a parameter.

I even tried with ws.CopyTo(wb,"NewName"), but still getting the same exception.
Jun 26, 2014 at 8:16 AM
I got the same exception but there are not exist the same name sheet.
Interesting and there are two sheet in workbook A,named 'Sheet999' and 'Travel',one of this sheet 'Sheet999' can be copy to another workbook correctly ,but the other one has throw the exception.(An item with the same key has already been added)

And i give the example code here :
( http://www.liuzhiyong2012.cn/dl/ClosedXML_0626.rar )
Jun 26, 2014 at 9:02 PM
I apologize. Indeed the worksheet does not exist.
I ran the sample code by cooper360 and the exception appears to take place in ClosedXml.Excel.XLRowsCollection.Add().
It also throws the exception when trying to copy to a brand new empty workbook.
It's interesting that it fails on the XLRowsCollection.Add() and not in the XLWorksheetsCollection.Add().

Here is the stack trace:
at System.ThrowHelper.ThrowArgumentException(ExceptionResource resource)
at System.Collections.Generic.Dictionary2.Insert(TKey key, TValue value, Boolean add)
at System.Collections.Generic.Dictionary
2.Add(TKey key, TValue value)
at ClosedXML.Excel.XLRowsCollection.Add(Int32 key, XLRow value)
at ClosedXML.Excel.XLWorksheet.<>c__DisplayClass3d.<CopyTo>b__39(KeyValuePair2 kp)
at ClosedXML.Excel.EnumerableExtensions.ForEach[T](IEnumerable
1 source, Action`1 action)
at ClosedXML.Excel.XLWorksheet.CopyTo(XLWorkbook workbook, String newSheetName, Int32 position)
at ClosedXML.Excel.XLWorksheet.CopyTo(XLWorkbook workbook, String newSheetName)
at ClosedXML_Test.Form2.button1_Click(Object sender, EventArgs e) in C:\Users\rtanenbaum\Documents\Downloads\ClosedXML_TestCopyTo\Form2.cs:line 33
Jun 27, 2014 at 7:14 AM
MDeLeon wrote:
There's already a sheet with the same name in the workbook.
Please try the simple code and it's interesting.
Coordinator
Jul 1, 2014 at 7:24 PM
Edited Jul 1, 2014 at 7:25 PM
Okay good people, I've got some good news and some bad news.

The good news is that I just committed a quick fix to the ws copying issue. I don't have your file manjunathg88 so test it.

cooper,

The rows and columns are copied because they contain information like height and width.

In your case you would also want to copy the workbook's named ranges. You can do it with the following code (after copying the worksheets)
foreach (var r in wbSource.NamedRanges.Where(nr => nr.Name != "CellHasFormula"))
{
    wbTarget.NamedRanges.Add(r.Name, r.Ranges);
}
The reason why I'm excluding the CellHasFormula range is because it refers to "NOT(GET.CELL(48,INDIRECT("rc",FALSE)))" and I have no clue how to deal with this kind of named range (the bad news).

So pick up the lates code and let me know if you guys have questions.

/Manuel
Jul 2, 2014 at 2:01 AM
Edited Jul 2, 2014 at 8:32 AM
Hi MDeLeon, tanks for copying worksheet issue has been fixed .
And I used the code that you provide to copy the workbook's named ranges, it's OK. :-D

And do u find the copy worksheet's style is different between the source worksheet in my last example code?
Looks like auto created more boders or background colors in the worksheet named "Travel".


I have a screen snapshots here and source code in the last example.
http://www.liuzhiyong2012.cn/dl/differ.jpg