在复制OpenXML的工作表之前已修改 [英] Copying a worksheet in OpenXML that has previously been modified

问题描述

我按照使用常规的这里通过使用一个工作簿中复制工作表到同一工作簿 .AddPart<>（）和临时工作簿作为中间人，但都拿出了一些问题。在此之前复制工作表，我已经修改了。但是，工作表的复制版本保留原始片，而不是修改之一。下面的代码：

 文档= SpreadsheetDocument.Open（文件名，真实）; 
 SetupPages（0，nSections）; 
 CopySheet（1）; 
  

SetupPages（）不仅仅是获得了一个工作表哪个有一个表只有一个行，然后复制该行 nSections - 1 倍，所以该表与结束 nSections 行。这个作品

CopySheet（）具有下面的代码：

 私人无效CopySheet（INT SNUM）
 {
 VAR tempSheet = SpreadsheetDocument.Create（新的MemoryStream（），SpreadsheetDocumentType.Workbook）; 
 WorkbookPart tempWBP = tempSheet.AddWorkbookPart（）; 
 VAR部分= Document.XGetWorkSheetPart（SNUM）; 
 WorksheetPart tempWSP = tempWBP.AddPart< WorksheetPart>（部分）; 
 VAR副本= Document.WorkbookPart.AddPart< WorksheetPart>（tempWSP）; 
 
无功表= Document.WorkbookPart.Workbook.GetFirstChild<表及GT;（）; 
无功表=新表（）; 
 sheet.Id = Document.WorkbookPart.GetIdOfPart（复印件）; 
 sheet.Name =AAA; 
 sheet.SheetId =（UINT）sheets.ChildElements.Count + 1; 
 sheets.Append（片）; 
} 
  

这方法获取所需的workSheetPart和用途 tempSheet。 AddPart<>（）执行所需部分的深拷贝，然后再使用它来深拷贝它放回原来的文档。这样，所有包含所需workSheetPart也被复制中引用的对象。

这工作，尽可能多的，因为我现在有工作表的副本我原来的文档中。然而，所有的修改由 SetupPages（）调用 CopySheet（）不会出现在复制工作表前作出。所以我现在有与原来的表（在本例中）11行和只有1行复制的表

我试图放置 Document.WorkBookPart.WorkBook.Save（）之间的 SetupPages（）和 CopySheet（） ，看看保存更改将使其可被复制为好，但无济于事。

是否有任何OpenXML的把戏，我不知道的？

编辑：是我刚才注意到调试器 part.WorkSheet.ChildElements.Count = 15 （后 SetupPages ）。然而， tempWBP.WorkSheet.ChildElements.Count = 5 （在之前的 SetupPages ）。无论出于何种原因，我的程序添加新行没有被深拷贝。现在，这个我只是不明白。
它应该是与一些不正当的结合对我而言什么的行，这里的 SetupPages（）的定义：

 公共无效SetupPages（INT p，诠释nSections）
 {
正则表达式RN =新的正则表达式（[0-9] + ）; 
正则表达式RS =新的正则表达式（[A-Z] +）; 
 SheetData SDATA = Document.XGet&所述; SheetData>（2 * P + 1）; 
为（UINT I = 1; I< nSections;我++）
 {
变种R = sData.ChildElements.GetItem（4）.Clone（）作为行; 
 r.RowIndex.Value + =我; 
的foreach（在r.ChildElements OpenXmlElement _c）
 {
变种C = _c为细胞; 
匹配万= rn.Match（c.CellReference.Value）; 
匹配毫秒= rs.Match（c.CellReference.Value）; 
字符串str =（int.Parse（mn.Value）+ I）的ToString（）; 
 c.CellReference.Value = ms.Value + STR; 
} 
（r.FirstChild如细胞）.CellValue =新CellValue（第（i + 1）的ToString（））; 
 sData.Append（R）; 
} 
} 
  





编辑2：我已经能够充分使复印件的工作，但它是非常unelegant并不能解决什么显然是在代码中的错误，而仅仅是一种应急措施，以假装没有问题。我基本上得到的克隆原 SheetData （包含在 SetupPages 进行修改），并将其设置为复制的表的SheetData 。我只是张贴这在这里提供信息，但是如果有人仍然可以请指出什么是错在我没有看到的代码，我会很感激。这里的 CopySheet（），如果有人有兴趣。

 <$ C的破解版$ C>私人无效CopySheet（INT SNUM）
 {
 VAR tempSheet = SpreadsheetDocument.Create（新的MemoryStream（），SpreadsheetDocumentType.Workbook）; 
 WorkbookPart tempWBP = tempSheet.AddWorkbookPart（）; 
 VAR部分= Document.XGetWorkSheetPart（SNUM）; 
变种B = part.Worksheet.ChildElements [5] .Clone（）作为SheetData; 
 WorksheetPart tempWSP = tempWBP.AddPart< WorksheetPart>（部分）; 
 
 VAR副本= Document.WorkbookPart.AddPart< WorksheetPart>（tempWSP）; 
 copy.Worksheet.RemoveChild&所述; SheetData>（copy.Worksheet.ChildElements [5]如SheetData）; 
 copy.Worksheet.InsertAt&所述; SheetData>（二，5）; 
 
无功表= Document.WorkbookPart.Workbook.GetFirstChild<表及GT;（）; 
无功表=新表（）; 
 sheet.Id = Document.WorkbookPart.GetIdOfPart（复印件）; 
 sheet.Name =AAA; 
 sheet.SheetId =（UINT）sheets.ChildElements.Count + 1; 
 sheets.Append（片）; 
} 
  

解决方案

我已经解决了这个作为最好的，我可以（从我已经从其他论坛聚集我问过，因为最好的他们就可以）通过关闭和文件创建副本之前开业。这使得更改永久，然后复制时，这些变化被复制了。由此，黑客上述变得不必要。因此，该代码的最终版本成为（有变化，以避免 SheetID 和 Sheet.Name 冲突）：

 私人无效CopySheet（INT SNUM，诠释PNUM，字符串类型）
 {
 VAR tempSheet = SpreadsheetDocument.Create （新的MemoryStream（），SpreadsheetDocumentType.Workbook）; 
 WorkbookPart tempWBP = tempSheet.AddWorkbookPart（）; 
 VAR部分= Document.XGetWorkSheetPart（SNUM）; 
 WorksheetPart tempWSP = tempWBP.AddPart< WorksheetPart>（部分）; 
 VAR副本= Document.WorkbookPart.AddPart< WorksheetPart>（tempWSP）; 
无功表= Document.WorkbookPart.Workbook.GetFirstChild<表及GT;（）; 
无功表=新表（）; 
 sheet.Id = Document.WorkbookPart.GetIdOfPart（复印件）; 
 sheet.Name =相位+ PNUM ++类型; 
 UINT ID = 1; 
布尔有效= FALSE; 
，而
 {
 UINT TEMP = ID（有效！）; 
的foreach（OpenXmlElement E在sheets.ChildElements）
 {
变种S = E上表; 
如果（ID == s.SheetId.Value）
 {
 ID ++; 
中断; 
} 
} 
如果（临时== ID）
有效= TRUE; 
} 
 sheet.SheetId = ID; 
 sheets.Append（片）; 
} 
  

I have followed the routine used here to copy a worksheet in a workbook into the same workbook by using .AddPart<>() 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:

Document = SpreadsheetDocument.Open(filename, true);
SetupPages(0, nSections);
CopySheet(1);

SetupPages() simply gets a worksheet which has a table with only one row and then copies that row nSections - 1 times, so that the table ends up with nSections rows. This works.

CopySheet() has the following 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<WorksheetPart>(part);
    var copy = Document.WorkbookPart.AddPart<WorksheetPart>(tempWSP);

    var sheets = Document.WorkbookPart.Workbook.GetFirstChild<Sheets>();
    var sheet = new Sheet();
    sheet.Id = Document.WorkbookPart.GetIdOfPart(copy);
    sheet.Name = "AAA";
    sheet.SheetId = (uint)sheets.ChildElements.Count + 1;
    sheets.Append(sheet);
}

This method gets the desired workSheetPart and uses tempSheet.AddPart<>() 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.

This works, in as much as I now have a copy of the workSheet in my original document. However, all the modifications made by SetupPages() prior to calling CopySheet() 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.

I have tried placing a Document.WorkBookPart.WorkBook.Save() between SetupPages() and CopySheet(), to see if "saving the changes" would make them available to be copied as well, but to no avail.

Is there any OpenXML trick I am unaware of?

EDIT: Using the debugger I have just noticed that part.WorkSheet.ChildElements.Count = 15 (the expected number of rows after SetupPages). However, tempWBP.WorkSheet.ChildElements.Count = 5 (the number of rows in the original sheet prior to the modifications of SetupPages). 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 SetupPages():

public void SetupPages(int p, int nSections)
{
    Regex rn = new Regex("[0-9]+");
    Regex rs = new Regex("[A-Z]+");
    SheetData sData = Document.XGet<SheetData>(2 * p + 1);
    for (uint i = 1; i < 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);
    }
}

EDIT 2: 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 SheetData (which contains the modifications done in SetupPages) and set it as the SheetData of the copied Sheet. 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 CopySheet(), if anyone is interested.

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<WorksheetPart>(part);

    var copy = Document.WorkbookPart.AddPart<WorksheetPart>(tempWSP);
    copy.Worksheet.RemoveChild<SheetData>(copy.Worksheet.ChildElements[5] as SheetData);
    copy.Worksheet.InsertAt<SheetData>(b, 5);

    var sheets = Document.WorkbookPart.Workbook.GetFirstChild<Sheets>();
    var sheet = new Sheet();
    sheet.Id = Document.WorkbookPart.GetIdOfPart(copy);
    sheet.Name = "AAA";
    sheet.SheetId = (uint)sheets.ChildElements.Count + 1;
    sheets.Append(sheet);
}

解决方案

I've solved this as best I can (and from what I've gathered from other forums I've asked, as best "they" can) by closing and opening the file prior to creating the copies. This makes the changes permanent and then, when copied, the changes are copied, too. With this, the "hack" described above becomes unnecessary. The final version of the code therefore became (with a change to avoid SheetID and Sheet.Name conflicts):

private void CopySheet(int sNum, int pNum, string type)
{
    var tempSheet = SpreadsheetDocument.Create(new MemoryStream(), SpreadsheetDocumentType.Workbook);
    WorkbookPart tempWBP = tempSheet.AddWorkbookPart();
    var part = Document.XGetWorkSheetPart(sNum);
    WorksheetPart tempWSP = tempWBP.AddPart<WorksheetPart>(part);
    var copy = Document.WorkbookPart.AddPart<WorksheetPart>(tempWSP);
    var sheets = Document.WorkbookPart.Workbook.GetFirstChild<Sheets>();
    var sheet = new Sheet();
    sheet.Id = Document.WorkbookPart.GetIdOfPart(copy);
    sheet.Name = "Phase " + pNum + " " + type;
    uint id = 1;
    bool valid = false;
    while (!valid)
    {
        uint temp = id;
        foreach (OpenXmlElement e in sheets.ChildElements)
        {
            var s = e as Sheet;
            if (id == s.SheetId.Value)
            {
                id++;
                break;
            }
        }
        if (temp == id)
            valid = true;
    }
    sheet.SheetId = id;
    sheets.Append(sheet);
}

这篇关于在复制OpenXML的工作表之前已修改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！