CopyTo Problem

Mar 8, 2011 at 1:41 AM

I would expect that when updating firstTableCell that only the cell in the first worksheet is updated.  The cloned sheet's cell is also updated though.

var workbook = new XLWorkbook("Test.xlsx");

            var ws = workbook.Worksheet(1);

            // Define a range with the data
            var firstTableCell = ws.FirstCellUsed();

            // Copy the table to another worksheet
            var wsCopy = ws.CopyTo("Contacts Copy");
            firstTableCell.Value = "test";


Mar 8, 2011 at 2:55 AM

To Fix the Problem I modified code in the CopyTo function.  It looks like the other collections need deep copied values as well.


//this.Internals.CellsCollection.ForEach(kp => ws.Internals.CellsCollection.Add(kp.Key, ws.Cell(kp.Value.Address) as XLCell));
this.Internals.CellsCollection.ForEach(kp => (ws.Cell(kp.Value.Address) as XLCell).CopyValues(kp.Value));



Mar 8, 2011 at 3:02 AM

You're right. Good catch.

I'll make the changes (including the other collections) so they'll be on the next release.

Thank you very much for the feedback, I really appreciate it.

Mar 8, 2011 at 3:03 AM

Looks like this fix doesn't work with formatting.

Mar 8, 2011 at 3:16 AM

I just noticed your review:

"If a cell A references another cell B then cellA.Value will return the value of cellB" seems to have hosed my code. I copied a range from Sheet1 to Sheet2 via worksheet.Cell(cellInSheet2.Address).Value = rangeInSheet1; I then updated cellInSheet2 and the value for the same cell in Sheet1 also changed. Is there a workaround to this problem?

Could you please post a piece of code that reproduces this behavior? I tried the following and it works fine:

            var wb = new XLWorkbook();

            var sheet1 = wb.Worksheets.Add("Sheet1");
            var sheet2 = wb.Worksheets.Add("Sheet2");

            sheet1.Cell(1, 1).Value = "Value on Sheet1";
            var range1 = sheet1.Range("A1");

            sheet2.Cell(1, 1).Value = range1;
            sheet2.Cell(1, 1).Value = "We're on Sheet2 now";


Mar 8, 2011 at 3:17 AM

Looks like this fix doesn't work with formatting.

I'll take care of that...

Mar 8, 2011 at 3:31 AM
Used this line instead.
this.Internals.CellsCollection.ForEach(kp => ws.Internals.CellsCollection.Add(kp.Key, kp.Value.CopyTo(ws)));
 and added a CopyTo function to XLCell.  Let me know if it looks like I missed something.

        public XLCell CopyTo(XLWorksheet worksheet)
            XLCell cell = new XLCell(this.Address,, worksheet);
            cell.Value = this.Value;
            cell.SettingHyperlink = this.SettingHyperlink;
            cell.ShareString = this.ShareString;
            if (null != hyperlink)
                cell.Hyperlink = this.Hyperlink;

            return cell;

Mar 8, 2011 at 3:34 AM
Edited Mar 8, 2011 at 3:35 AM

Could you please post a piece of code that reproduces this behavior? I tried the following and it works fine:

I was referring to this issue in this thread.  So the code would be the code I posted at 8:41.  I hadn't gotten to the heart of the problem when I posted that.  Sorry for any confusion.

Mar 8, 2011 at 5:30 PM

I submitted a patch that should fix most of this.  I didn't fix deep copying of the ranges however.  Figured I'd leave that up to you.

Mar 14, 2011 at 3:46 AM

I got the patch and I tried to implement it in this release (v0.47) but I found a few roadblocks so I decided to make a release today instead of holding it off a few more days while I get this one done.

Thanks for your help,

Mar 23, 2011 at 1:15 PM

Is there an issue number that I can follow for this?

Mar 23, 2011 at 3:51 PM

I created an issue ticket for this (6370).

Thank you for your patience, I haven't had enough free time available in the last few weeks.

Mar 24, 2011 at 12:42 AM

It's fine, my patch is good enough for my uses, I just wanted to know what issue to watch so I can know when I can use the trunk again.  I'm using this in a reporting engine at work and really appreciate your hard work on this.  The engine I had written couldn't touch this one by any means.  This has helped me accomplish exactly what I wanted out of my reporting engine with a minimal amount of effort.  Thanks for all of your hard work.