Copy Excel Sheet - for Excel 2007

• Download CopyExcelSheet-Todde - 16.92 KB

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, 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 we also plan to use it in our new development. However, CopySheet is our main requirement and it�s real a pity that we can�t find such a function in ExcelPakage. After taking some efforts, we fill it up ourself. Following you can see how we�ve done it.

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

Using the code

The following you can find 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);
                    sheetFileName = nodeSheet1.Attributes["Target"].Value; //     [worksheets/sheetname.xml]
                    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 must be any smarter ways for copying sheets and I will be very happy when you can email me and share anything you want with me.


History