Copy Excel Sheet - For Excel 2007

Introduction

Have you known OOXML? Have you started using ExcelPakage to generate Excel files or other OOXML files on your server? If yes, did you find that there is no CopySheet function in this package? Actually, the section of Copy is empty, there is just a name there.

We must say ExcelPakage is a powerful and useful tool. We can handle OOXML files in a very easy way by using it, and I plan to use it in my new development. However, CopySheet is my main requirement, and it's really a pity that I can't find such a function in ExcelPakage. After taking some effort, I filled it up myself. You can see below how I've done it.

And I'm sorry about the previous version with some mistakes, it's OK now. Thank Felipe and Abhijit. I had a problem with the formatting.

Using the code

Shown below are the two main functions from the whole class:

/// <summary>
/// Copy Excel Worksheet in an Excel file package, Todde, Jul 15, 2007
/// </summary>
/// <param name="fileName">Excel File</param>
/// <param name="sheetName">Name of the sheet that is going to be copied</param>
/// <param name="fileNo">Running No, used after the new sheet name</param>
/// <returns>New sheet name</returns>
protected string CopySheet(string fileName, string sheetName, int fileNo)
{
    string partName = "/xl/workbook.xml";
    string relFile = "/xl/_rels/workbook.xml.rels";
    //Read the Package ==============================================
    Package xlPackage = Package.Open(fileName, FileMode.Open, 
                                     FileAccess.ReadWrite);
    
    // If exception, return string "newSheetName"
    //    
    string newSheetName = "";
    
    try
    {
        //Read workbook.xml======================================= 
        Uri documentUri = new Uri(partName, UriKind.Relative);
        PackagePart documentPart = xlPackage.GetPart(documentUri);
        
        XmlDocument doc = new XmlDocument();
        doc.Load(documentPart.GetStream());
        XmlNamespaceManager nsManager = new XmlNamespaceManager(doc.NameTable);
        nsManager.AddNamespace("d", doc.DocumentElement.NamespaceURI);

        string searchString = string.Format("//d:sheet[@name='{0}']", sheetName);
        XmlNode node = doc.SelectSingleNode(searchString, nsManager);

        if (node == null)
            return null; //The sheet does not exist.
        else
        {
            string relId = node.Attributes["r:id"].Value;
            string sheetId = node.Attributes["sheetId"].Value;
            string name = node.Attributes["name"].Value;

            XmlNode nodeSheets = 
              doc.DocumentElement.SelectSingleNode("d:sheets", nsManager);

            string relId1 = node.Attributes["r:id"].Value + "_" + fileNo.ToString();

            int maxSheetID = 0;
            int tempSheetID = 0;
            foreach (XmlNode note in nodeSheets.ChildNodes)
            {
                tempSheetID = Convert.ToInt32(note.Attributes["sheetId"].Value);
                if (maxSheetID < tempSheetID)
                    maxSheetID = tempSheetID;
            }
            string sheetId1 = Convert.ToString(maxSheetID + 1);
   
            newSheetName = name + "_" + fileNo.ToString();

            //Modify /xl/_rels/workbook.xml.rels
            string sheetFileName;
            Uri xmlUri = new Uri(relFile, UriKind.Relative);
            PackagePart xmlPart = xlPackage.GetPart(xmlUri);
            XmlDocument doc1 = new XmlDocument();
            doc1.Load(xmlPart.GetStream());

            XmlNode nodeSheet1 = SelectOneNode(doc1.DocumentElement.ChildNodes, 
                                               "Id", relId);
            // [worksheets/sheetname.xml]
            sheetFileName = nodeSheet1.Attributes["Target"].Value;
            string sheetFileName1 = 
               sheetFileName.Substring(sheetFileName.LastIndexOf('/') + 1, 
               (sheetFileName.IndexOf('.') - 
               sheetFileName.LastIndexOf('/') - 1)) + 
               "_" + fileNo.ToString() + ".xml";

            string xmlString = "<Relationship Id=\"" + relId1 + 
                               "\" Type=\"http://schemas.openxmlformats." + 
                               "org/officeDocument/2006/relationships/worksheet\" 
                               Target=\"worksheets/" + 
                               sheetFileName1.ToLower() + "\" />";
            XmlNode node1 = doc1.DocumentElement;
            node1.InnerXml += xmlString;

            //Copy sheet xml file (if existing,
            //throw an error and modify[content_types].xml)
            string sheetXmlToPaste = 
              "/xl/worksheets/" + sheetFileName1.ToLower();
            CopyXmlFile(xlPackage, "/xl/" + sheetFileName, sheetXmlToPaste);

            //Modify workbook.xml
            nodeSheets.InnerXml += "<sheet name=\"" + newSheetName + 
              "\" sheetId=\"" + sheetId1 + 
              "\" r:id=\"" + relId1 + "\" />";
            doc.Save(documentPart.GetStream(FileMode.Create, FileAccess.Write));

            //Modify /xl/_rels/workbook.xml.rels
            doc1.Save(xmlPart.GetStream(FileMode.Create, FileAccess.Write));
            xlPackage.Flush();

            xlPackage.Close();
            return newSheetName;
        }
    }
    catch
    {
        xlPackage.Close();
        return newSheetName;
    }
}

//Copy sheet xml file
internal void CopyXmlFile(Package xlPackage, string sheetXmlToCopy, 
                          string sheetXmlToPaste)
{
    Uri sheetUri = new Uri(sheetXmlToCopy, UriKind.Relative);
    PackagePart sheetPart = xlPackage.GetPart(sheetUri);
    XmlDocument doc = new XmlDocument();
    doc.Load(sheetPart.GetStream());
    Uri xmlUri = new Uri(sheetXmlToPaste, UriKind.Relative);
    if (xlPackage.PartExists(xmlUri))
    {
        xlPackage.Close();
        throw new InvalidOperationException("XML part is existing.");
    }
    PackagePart xmlPart = xlPackage.CreatePart(xmlUri, 
      @"application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml");

    using (Stream outputStream = xmlPart.GetStream(FileMode.Create, FileAccess.Write))
    {
        using (StreamWriter writer = new StreamWriter(outputStream))
        {
            writer.Write(doc.InnerXml);
            writer.Close();
        }
    }
    //Modify the main relationship file: [content_types].xml
    string schemaRelationships = 
      @"http://schemas.openxmlformats.org/officeDocument/2006/relationships";
    PackageRelationship rel = xlPackage.CreateRelationship(xmlUri, 
           TargetMode.Internal, schemaRelationships + "/worksheet");
    xlPackage.Flush();
}

Points of Interest

I'm sure there are smarter ways for copying sheets, and I will be very happy if you can email me and share anything you have with me.