Click here to Skip to main content
Click here to Skip to main content

Copy Excel Sheet - For Excel 2007

, 14 Jan 2008
Rate this:
Please Sign up or sign in to vote.
Allows you to copy Excel sheets when your program generates Excel files.

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.

License

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

About the Author

Todde
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 PinmemberTFox25-Aug-13 3:16 
Generalnice Pinmemberyuety17-Jan-08 16:19 
Generalnice PinmemberAbhijit Jana14-Jan-08 20:48 
GeneralRe: nice PinmemberTodde14-Jan-08 21:02 
GeneralRe: nice PinmemberAbhijit Jana14-Jan-08 21:59 
GeneralRe: nice PinmemberTodde14-Jan-08 22:26 
GeneralArticle Code Pinmemberfelipesabino14-Jan-08 6:31 
AnswerRe: Article Code PinmemberTodde14-Jan-08 14:16 

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

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

| Advertise | Privacy | Mobile
Web03 | 2.8.140709.1 | Last Updated 15 Jan 2008
Article Copyright 2008 by Todde
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid