title: Copying a worksheet in OpenXML that has previously been modified
body: <p>I have followed the routine used <a href="http://blogs.msdn.com/b/brian_jones/archive/2009/02/19/how-to-copy-a-worksheet-within-a-workbook.aspx?Redirected=true" rel="nofollow">here</a> to copy a worksheet in a workbook into the same workbook by using <code>.AddPart&lt;&gt;()</code> and a temporary workbook to serve as a middleman, but have come up with some issues. Prior to copying the worksheet, I have made modifications to it. However, the copied version of the worksheet retains the original sheet, not the modified one. Here's the code:</p>
  
  <pre><code>Document = SpreadsheetDocument.Open(filename, true);
  SetupPages(0, nSections);
  CopySheet(1);
  </code></pre>
  
  <p><code>SetupPages()</code> simply gets a worksheet which has a table with only one row and then copies that row <code>nSections - 1</code> times, so that the table ends up with <code>nSections</code> rows. This works.</p>
  
  <p><code>CopySheet()</code> has the following code:</p>
  
  <pre><code>private void CopySheet(int sNum)
  {
      var tempSheet = SpreadsheetDocument.Create(new MemoryStream(), SpreadsheetDocumentType.Workbook);
      WorkbookPart tempWBP = tempSheet.AddWorkbookPart();
      var part = Document.XGetWorkSheetPart(sNum);
      WorksheetPart tempWSP = tempWBP.AddPart&lt;WorksheetPart&gt;(part);
      var copy = Document.WorkbookPart.AddPart&lt;WorksheetPart&gt;(tempWSP);
  
      var sheets = Document.WorkbookPart.Workbook.GetFirstChild&lt;Sheets&gt;();
      var sheet = new Sheet();
      sheet.Id = Document.WorkbookPart.GetIdOfPart(copy);
      sheet.Name = "AAA";
      sheet.SheetId = (uint)sheets.ChildElements.Count + 1;
      sheets.Append(sheet);
  }
  </code></pre>
  
  <p>This method gets the desired workSheetPart and uses <code>tempSheet.AddPart&lt;&gt;()</code> to perform a deep copy of the desired part and then uses it again to deep copy it back into the original document. This way all the referenced objects contained within the desired workSheetPart are also copied.</p>
  
  <p>This works, in as much as I now have a copy of the workSheet in my original document. However, all the modifications made by <code>SetupPages()</code> prior to calling <code>CopySheet()</code> do not appear in the copied workSheet. So I now have the original Sheet with (in this example) 11 rows and the copied Sheet with only 1 row.</p>
  
  <p>I have tried placing a <code>Document.WorkBookPart.WorkBook.Save()</code> between <code>SetupPages()</code> and <code>CopySheet()</code>, to see if "saving the changes" would make them available to be copied as well, but to no avail.</p>
  
  <p>Is there any OpenXML trick I am unaware of?</p>
  
  <p><strong>EDIT:</strong> Using the debugger I have just noticed that <code>part.WorkSheet.ChildElements.Count = 15</code> (the expected number of rows after <code>SetupPages</code>). However, <code>tempWBP.WorkSheet.ChildElements.Count = 5</code> (the number of rows in the original sheet prior to the modifications of <code>SetupPages</code>). For whatever reason, the new Rows added by my program are not being deep-copied. Now this I just don't understand.
  Should it have something to do with some improper binding of the rows on my part or something, here's the definition of <code>SetupPages()</code>:</p>
  
  <pre><code>public void SetupPages(int p, int nSections)
  {
      Regex rn = new Regex("[0-9]+");
      Regex rs = new Regex("[A-Z]+");
      SheetData sData = Document.XGet&lt;SheetData&gt;(2 * p + 1);
      for (uint i = 1; i &lt; nSections; i++)
      {
          var r = sData.ChildElements.GetItem(4).Clone() as Row;
          r.RowIndex.Value += i;
          foreach (OpenXmlElement _c in r.ChildElements)
          {
              var c = _c as Cell;
              Match mn = rn.Match(c.CellReference.Value);
              Match ms = rs.Match(c.CellReference.Value);
              string str = (int.Parse(mn.Value) + i).ToString();
              c.CellReference.Value = ms.Value + str;
          }
          (r.FirstChild as Cell).CellValue = new CellValue((i + 1).ToString());
          sData.Append(r);
      }
  }
  </code></pre>
  
  <p><strong>EDIT 2:</strong> I've been able to make the copy work fully, but it's very unelegant and doesn't fix what is clearly an error in the code, but merely an emergency measure to pretend that there isn't a problem. I basically get a clone of the original <code>SheetData</code> (which contains the modifications done in <code>SetupPages</code>) and set it as the <code>SheetData</code> of the copied <code>Sheet</code>. I'm just posting this here for information purposes, but if someone could still please point out what is wrong in the code that I'm not seeing, I'll be quite thankful. Here's the "hacked" version of <code>CopySheet()</code>, if anyone is interested.</p>
  
  <pre><code>private void CopySheet(int sNum)
  {
      var tempSheet = SpreadsheetDocument.Create(new MemoryStream(), SpreadsheetDocumentType.Workbook);
      WorkbookPart tempWBP = tempSheet.AddWorkbookPart();
      var part = Document.XGetWorkSheetPart(sNum);
      var b = part.Worksheet.ChildElements[5].Clone() as SheetData;
      WorksheetPart tempWSP = tempWBP.AddPart&lt;WorksheetPart&gt;(part);
  
      var copy = Document.WorkbookPart.AddPart&lt;WorksheetPart&gt;(tempWSP);
      copy.Worksheet.RemoveChild&lt;SheetData&gt;(copy.Worksheet.ChildElements[5] as SheetData);
      copy.Worksheet.InsertAt&lt;SheetData&gt;(b, 5);
  
      var sheets = Document.WorkbookPart.Workbook.GetFirstChild&lt;Sheets&gt;();
      var sheet = new Sheet();
      sheet.Id = Document.WorkbookPart.GetIdOfPart(copy);
      sheet.Name = "AAA";
      sheet.SheetId = (uint)sheets.ChildElements.Count + 1;
      sheets.Append(sheet);
  }
  </code></pre>