none
Error making more than one copy of a worksheet RRS feed

  • Question

  • I have a program which modifies an existing spreadsheet. Part of the program creates copies of certain sheets. To create one copy, the program runs perfectly and the resulting file is also fine, presenting no errors when opened in Excel. However, when creating two copies of the same worksheet, the program still runs just fine but, when opened in Excel, the following error appears regarding unreadable content:

    Repaired Records: Worksheet properties from /xl/workbook.xml part (Workbook)

    Here is the code used to perform the copy:

    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);
        var sheetData = part.Worksheet.ChildElements[5].Clone() as SheetData;
        var merge = part.Worksheet.ChildElements[6].Clone() as MergeCells;
        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>(sheetData, 5);
        //copy.Worksheet.RemoveChild<MergeCells>(copy.Worksheet.ChildElements[6] as MergeCells);
        //copy.Worksheet.InsertAt<MergeCells>(merge, 6);
        //copy.Worksheet.SheetProperties.CodeName.Value = "Phase" + pNum + type;
    
        var sheets = Document.WorkbookPart.Workbook.Sheets;
        var sheet = new Sheet();
        sheet.Id = Document.WorkbookPart.GetIdOfPart(copy);
        sheet.Name = "Phase " + pNum + " " + type;
        sheet.SheetId = (uint)sheets.ChildElements.Count;
        sheets.Append(sheet);
    }

    (Document.XGetWorkSheetPart(int index) is an extension of SpreadsheetDocument which calls Document.WorkbookPart.GetPartById((doc.WorkbookPart.Workbook.Sheets.ChildElements.GetItem(index) as Sheet).Id) as WorksheetPart;)

    This method makes use of the fact that .AddPart<>() performs a deep copy of any Part (and anything it references to) which does not already belong in the document to, with the help of a temporary sheet, create a deep copy of all referenced parts of a WorkSheet.

    As stated above, this works quite well if the function is called only once for a given sheet. If it is called more than once, however, the file when opened in Excel gives an error of unreadable content. That being said, the file itself seems just fine, with no missing data or anything (which would help to figure out what exactly was wrong), just the error saying there was something wrong.

    The lines that are commented out are a "hack" I had to do to deal with problems regarding .AddPart<>(), but I won't go into much detail with them here because I've already posted about this here (but I still haven't gotten a reply, so by all means, please answer that question, too!). That being said, those lines seem to have no relevance to this current problem since the error appears with or without those lines of code.

    Sunday, February 16, 2014 3:12 PM

Answers

  • Hi,

    Do you mean that when you use the CopySheet method twice, the result workbook has error to open normally?

    I use the code below to run the CopySheet method twice to copy an existed sheet to 2 new different sheets and it works well. You could refer to it.

     

    static SpreadsheetDocument doc;
    
    static void Main(string[] args)
    {
        doc = SpreadsheetDocument.Open(@"C:\Users\Documents\test1.xlsx", true); 
        CopySheet(1, 1, "test");
        CopySheet(1, 1, "test1");
        doc.Close();
    }
    
    private static void CopySheet(int sNum, int pNum, string type)
    {
        var tempSheet = SpreadsheetDocument.Create(new MemoryStream(), SpreadsheetDocumentType.Workbook);
        WorkbookPart tempWBP = tempSheet.AddWorkbookPart();
        var part = doc.WorkbookPart.GetPartById((doc.WorkbookPart.Workbook.Sheets.ChildElements.GetItem(sNum) as Sheet).Id) as WorksheetPart;
    
        WorksheetPart tempWSP = tempWBP.AddPart<WorksheetPart>(part);
    
        var copy = doc.WorkbookPart.AddPart<WorksheetPart>(tempWSP);
    
    
        var sheets = doc.WorkbookPart.Workbook.Sheets;
        var sheet = new Sheet();
        sheet.Id = doc.WorkbookPart.GetIdOfPart(copy);
        sheet.Name = "Phase " + pNum + " " + type;
        sheet.SheetId = (uint)sheets.ChildElements.Count + 1;
        sheets.Append(sheet);
    }

    The result:



    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, February 19, 2014 9:30 AM
    Moderator
  • I've solved it. The problem was in fact what I posted above (regarding the SheetID conflict). I just kept getting an error due to a stupid typo while writing the code which caused a conflict in the names. But now it works. So remember kids, check for SheetID conflicts (and don't make typos)!
    • Marked as answer by w4sabi Thursday, February 20, 2014 1:21 AM
    Thursday, February 20, 2014 1:21 AM

All replies

  • Hi,

    According to your description and the code you provide, I think you want to copy and paste certain worksheets within the same workbook.

    I try to run your code to reproduce your issue but failed. I just run the code as below and it will not create a new worksheet in the workbook as a result. Even reviewing the workbook with Open XML productivity tool, I cannot find a new worksheet. Are you sure the code you provided is a whole one?

    In addition, how do you "called more than once" this program? In fact, we could not add a new worksheet named same as an existing one within a workbook. So you should make sure two new sheets have different names.

    By the way, I find there is hard-codes when defining “sheetData” and “merge” object. I don’t suggest you doing that because in different workbook the index of SheetData element maybe different.

    Here is a blog introduce a way to Copy a Worksheet within a Workbook for your reference.

    How to Copy a Worksheet within a Workbook

    static void Main(string[] args)
    {
        CopySheet(0, 1, "tt");
    }
    
    private static void CopySheet(int sNum, int pNum, string type)
    {
        string fileName1 = @"C:\Users\test\Documents\test.xlsx";
        SpreadsheetDocument Document = SpreadsheetDocument.Open(fileName1, true);
    
    
        var tempSheet = SpreadsheetDocument.Create(new MemoryStream(), SpreadsheetDocumentType.Workbook);
        WorkbookPart tempWBP = tempSheet.AddWorkbookPart();
        //var part = Document.XGetWorkSheetPart(sNum);
        var part = Document.WorkbookPart.GetPartById("rId1") as WorksheetPart;
        var sheetData = part.Worksheet.ChildElements[3].Clone() as SheetData;
        var merge = part.Worksheet.ChildElements[4].Clone() as MergeCells;
        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>(sheetData, 5);
        //copy.Worksheet.RemoveChild<MergeCells>(copy.Worksheet.ChildElements[6] as MergeCells);
        //copy.Worksheet.InsertAt<MergeCells>(merge, 6);
        //copy.Worksheet.SheetProperties.CodeName.Value = "Phase" + pNum + type;
    
        var sheets = Document.WorkbookPart.Workbook.Sheets;
        var sheet = new Sheet();
        sheet.Id = Document.WorkbookPart.GetIdOfPart(copy);
        sheet.Name = "Phase " + pNum + " " + type;
        sheet.SheetId = (uint)sheets.ChildElements.Count;
        sheets.Append(sheet);
    
    }

    Please feel free to let me know if I have any misunderstanding.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, February 17, 2014 12:36 PM
    Moderator
  • Thanks for the input, but I'd already seen that link (and even linked to it in my other post which I referenced here :p). That's actually where I got this entire method from because I'd never have thought of this myself.

    And this is not a full code, but it comes close. The full code is (obviously, the class is called from within a larger program, but anything regarding the file is done from here, so I'll leave the rest out):

        public class NPrinter
        {
            SpreadsheetDocument Document;
            /// <summary>Fills in the information on the first page of the results file.</summary>
            /// <param name="fck">The concrete's 28-day nominal compressive strength.</param>
            /// <param name="concrete">The beam's concrete.</param>
            /// <param name="phi1">The frequent live load coefficient.</param>
            /// <param name="phi2">The almost-permanent live load coefficient.</param>
            public void HeaderPage(double fck, NConcreteType concrete, double phi1, double phi2)
            {
                //First page
                var wsP = Document.XGetWorkSheetPart(0);
                SheetData sData = wsP.Worksheet.Descendants<SheetData>().First();
                Cell c = sData.XGetCell("B1");
                c.CellValue = new CellValue(fck.ToString());
                c = sData.XGetCell("B2");
                switch (concrete)
                {
                    case NConcreteType.CPI:
                        c.CellValue = new CellValue("0.25");
                        break;
                    case NConcreteType.CPIII:
                        c.CellValue = new CellValue("0.38");
                        break;
                    case NConcreteType.CPV:
                        c.CellValue = new CellValue("0.20");
                        break;
                }
                c = sData.XGetCell("B3");
                c.CellValue = new CellValue(phi1.ToString());
                c = sData.XGetCell("B4");
                c.CellValue = new CellValue(phi2.ToString());
            }
            /// <summary>Sets up the load and stress pages for the given phase with the correct number of sections. Does not update values.</summary>
            /// <param name="nSections">The number of sections to be used in the results.</param>
            public void SetupPages(int nSections)
            {
                #region Loads Page
                Regex rn = new Regex("[0-9]+");
                Regex rs = new Regex("[A-Z]+");
                SheetData sData = Document.XGet<SheetData>(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);
                }
                #endregion
                #region Stresses Page
                sData = Document.XGet<SheetData>(2);
                var mCells = Document.XGet<MergeCells>(2);
                for (uint i = 1; i < nSections; i++)
                {
                    var rows = new List<Row>(4);
                    for (int j = 0; j < 4; j++)
                    {
                        var r = sData.ChildElements.GetItem(3 + j).Clone() as Row;
                        r.RowIndex.Value += 4 * 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) + 4 * i).ToString();
                            c.CellReference.Value = ms.Value + str;
                        }
                        rows.Add(r);
                    }
                    (rows[0].FirstChild as Cell).CellValue = new CellValue((i + 1).ToString());
                    foreach(Row r in rows)
                        sData.Append(r);
                    var mergedCells = new List<MergeCell>(5);
                    mergedCells.Add(new MergeCell() { Reference = "A" + (4 * (i + 1)) + ":A" + (4 * (i + 1) + 3) });
                    mergedCells.Add(new MergeCell() { Reference = "C" + (4 * (i + 1)) + ":C" + (4 * (i + 1) + 3) });
                    mergedCells.Add(new MergeCell() { Reference = "D" + (4 * (i + 1)) + ":D" + (4 * (i + 1) + 3) });
                    mergedCells.Add(new MergeCell() { Reference = "E" + (4 * (i + 1)) + ":E" + (4 * (i + 1) + 3) });
                    mergedCells.Add(new MergeCell() { Reference = "F" + (4 * (i + 1)) + ":F" + (4 * (i + 1) + 3) });
                    foreach (MergeCell m in mergedCells)
                        mCells.Append(m);
                }
                #endregion
            }
            /// <summary>Copies the sheet at the given index.</summary>
            /// <param name="sNum">The index of the sheet to be copied.</param>
            /// <param name="pNum">The phase to which the sheets belong.</param>
            /// <param name="type">A postfix to the sheet's name. The name will be "Phase N type".</param>
            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);
                var sheetData = part.Worksheet.ChildElements[5].Clone() as SheetData;
                var merge = part.Worksheet.ChildElements[6].Clone() as MergeCells;
                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>(sheetData, 5);
                //copy.Worksheet.RemoveChild<MergeCells>(copy.Worksheet.ChildElements[6] as MergeCells);
                //copy.Worksheet.InsertAt<MergeCells>(merge, 6);
                //copy.Worksheet.SheetProperties.CodeName.Value = "Phase" + pNum + type;
    
                var sheets = Document.WorkbookPart.Workbook.Sheets;
                var sheet = new Sheet();
                sheet.Id = Document.WorkbookPart.GetIdOfPart(copy);
                sheet.Name = "Phase " + pNum + " " + type;
                sheet.SheetId = (uint)sheets.ChildElements.Count;
                sheets.Append(sheet);
            }
            public void Close()
            {
                Document.Close();
            }
            public NPrinter(string filename, int nSections, int nPhases)
            {
                System.IO.File.Copy("Template.xlsx", filename, true);
                Document = SpreadsheetDocument.Open(filename, true);
                SetupPages(nSections);
                for (int i = 1; i < nPhases; i++)
                {
                    CopySheet(1, i + 1, "Loads");
                    CopySheet(2, i + 1, "Stresses");
                }
    
                if (nPhases > 1)
                {
                    //for (int i = 1; i < nPhases; i++)
                    //{
                    //    CopySheet(2*i+1);
                    //    CopySheet(2*(i + 1));
                    //}
                }
            }
        }


    The template file comes with three sheets. The first is the header, the second is "Phase 1 Loads" and the third, "Phase 1 Stresses". The header is modified by HeaderPage(), while the "Loads" and "Stresses" sheets are first modified by SetupPages() by copying some rows a few times and then the entire sheets are supposed to be copied, creating "Phase 2", "Phase 3" ... "Phase N Loads/Stresses" sheets with the same number of rows.

    As stated, that much is now actually working. I have as many copies of the sheets as I need, all the data in the sheets is copied (so long as the "hack" code that is commented out above is uncommented, but that is another issue), the sheet names are correct (doesn't have "Repaired Sheet" or anything of the sort). The only problem is that I open the file and get a "Repair" error, but with no visible problems in the file itself (no missing rows, formatting, sheets, etc), so clearly the repair had to do with some slight internal error which Excel fixed correctly.



    • Edited by w4sabi Monday, February 17, 2014 1:15 PM
    Monday, February 17, 2014 1:09 PM
  • Update (still not a solution): I noticed the source file's sheets were not numbered properly for whatever reason, so their SheetID's were not sequencial (6, 1, 3). Therefore, my program was, when creating two copies of the file with the code sheet.SheetID = sheets.ChildElements.Count + 1, setting the values as 4, 5, 6 and 7, which lead to a conflict with the existing sheets. I've therefore modified that section so that the program always gets a valid ID:

     

    // ...
    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;
    //...

     

    With this my sheetIDs become (6, 1, 3, 2, 4, 5, 7), and therefore there are no conflicts.

    Unfortunately, that being said, this still doesn't work and I still get a "Repair" error. I just posted this in case anyone thought this might be a problem.

    Wednesday, February 19, 2014 2:45 AM
  • Hi,

    Do you mean that when you use the CopySheet method twice, the result workbook has error to open normally?

    I use the code below to run the CopySheet method twice to copy an existed sheet to 2 new different sheets and it works well. You could refer to it.

     

    static SpreadsheetDocument doc;
    
    static void Main(string[] args)
    {
        doc = SpreadsheetDocument.Open(@"C:\Users\Documents\test1.xlsx", true); 
        CopySheet(1, 1, "test");
        CopySheet(1, 1, "test1");
        doc.Close();
    }
    
    private static void CopySheet(int sNum, int pNum, string type)
    {
        var tempSheet = SpreadsheetDocument.Create(new MemoryStream(), SpreadsheetDocumentType.Workbook);
        WorkbookPart tempWBP = tempSheet.AddWorkbookPart();
        var part = doc.WorkbookPart.GetPartById((doc.WorkbookPart.Workbook.Sheets.ChildElements.GetItem(sNum) as Sheet).Id) as WorksheetPart;
    
        WorksheetPart tempWSP = tempWBP.AddPart<WorksheetPart>(part);
    
        var copy = doc.WorkbookPart.AddPart<WorksheetPart>(tempWSP);
    
    
        var sheets = doc.WorkbookPart.Workbook.Sheets;
        var sheet = new Sheet();
        sheet.Id = doc.WorkbookPart.GetIdOfPart(copy);
        sheet.Name = "Phase " + pNum + " " + type;
        sheet.SheetId = (uint)sheets.ChildElements.Count + 1;
        sheets.Append(sheet);
    }

    The result:



    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, February 19, 2014 9:30 AM
    Moderator
  • I've solved it. The problem was in fact what I posted above (regarding the SheetID conflict). I just kept getting an error due to a stupid typo while writing the code which caused a conflict in the names. But now it works. So remember kids, check for SheetID conflicts (and don't make typos)!
    • Marked as answer by w4sabi Thursday, February 20, 2014 1:21 AM
    Thursday, February 20, 2014 1:21 AM