Click here to Skip to main content
15,798,200 members
Articles / Programming Languages / C#

Copy Excel Sheet - For Excel 2007

Rate me:
Please Sign up or sign in to vote.
4.14/5 (6 votes)
14 Jan 2008CPOL 42.7K   4   18   8
Allows you to copy Excel sheets when your program generates Excel files.


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, 
    // If exception, return string "newSheetName"
    string newSheetName = "";
        //Read workbook.xml======================================= 
        Uri documentUri = new Uri(partName, UriKind.Relative);
        PackagePart documentPart = xlPackage.GetPart(documentUri);
        XmlDocument doc = new XmlDocument();
        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.
            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();

            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." + 
                               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));

            return newSheetName;
        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();
    Uri xmlUri = new Uri(sheetXmlToPaste, UriKind.Relative);
    if (xlPackage.PartExists(xmlUri))
        throw new InvalidOperationException("XML part is existing.");
    PackagePart xmlPart = xlPackage.CreatePart(xmlUri, 

    using (Stream outputStream = xmlPart.GetStream(FileMode.Create, FileAccess.Write))
        using (StreamWriter writer = new StreamWriter(outputStream))
    //Modify the main relationship file: [content_types].xml
    string schemaRelationships = 
    PackageRelationship rel = xlPackage.CreateRelationship(xmlUri, 
           TargetMode.Internal, schemaRelationships + "/worksheet");

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.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By
Team Leader State Street Hangzhou
China China
I don't think I'm good at programming, but, I love it, I am very enjoyable when some tough issues conquered by myself.

Comments and Discussions

QuestionSelectOneNode method Pin
TFox25-Aug-13 4:16
TFox25-Aug-13 4:16 
Generalnice Pin
yuety17-Jan-08 17:19
yuety17-Jan-08 17:19 
Generalnice Pin
Abhijit Jana14-Jan-08 21:48
professionalAbhijit Jana14-Jan-08 21:48 
GeneralRe: nice Pin
Todde14-Jan-08 22:02
Todde14-Jan-08 22:02 
GeneralRe: nice Pin
Abhijit Jana14-Jan-08 22:59
professionalAbhijit Jana14-Jan-08 22:59 
GeneralRe: nice Pin
Todde14-Jan-08 23:26
Todde14-Jan-08 23:26 
GeneralArticle Code Pin
felipesabino14-Jan-08 7:31
felipesabino14-Jan-08 7:31 
AnswerRe: Article Code Pin
Todde14-Jan-08 15:16
Todde14-Jan-08 15:16 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.