Copying worksheets

Jun 16, 2008 at 2:49 PM
Hi People,

Does Anyone have a nice implementation of copying a worksheet ? ( Not only text-content) but formula's styles included ?
I obviously don't want to be dependent on a template worksheet with fixed rows and columns.

Regards,

Hugo

Jun 17, 2008 at 4:32 PM
I didn't thoroughly test this, but give it a shot and let me know how it goes. Replace the body of ExcelWorksheets.Copy with the following:

/// <summary>
        /// Copies the named worksheet and creates a new worksheet in the same workbook
        /// </summary>
        /// <param name="Name">The name of the existing worksheet</param>
        /// <param name="NewName">The name of the new worksheet to create</param>
        /// <returns></returns>
        public ExcelWorksheet Copy(string Name, string NewName)
        {
            // we need a new sheetID
            ExcelWorksheet old_sheet = this[Name];
            if (old_sheet == null)
                throw new Exception(string.Format("Copy worksheet error: Could not find worksheet to copy '{0}'", Name));

            // first fine maximum existing sheetID
            // also create a unique name
            int sheetID = 0;

            foreach (XmlNode sheet in _worksheetsNode.ChildNodes)
            {
                XmlAttribute attr = (XmlAttribute)sheet.Attributes.GetNamedItem("sheetId");
                if (attr != null)
                {
                    int curID = int.Parse(attr.Value);
                    if (curID > sheetID)
                        sheetID = curID;
                }
                attr = (XmlAttribute)sheet.Attributes.GetNamedItem("name");
                if (attr != null)
                {
                    if (attr.Value == NewName)
                        throw new Exception("Add worksheet Error: attempting to create worksheet with duplicate name");
                }
            }
            // we now have the max existing values, so add one
            sheetID++;

            // add the new worksheet to the package
            Uri uriWorksheet = new Uri("/xl/worksheets/sheet" + sheetID.ToString() + ".xml", UriKind.Relative);
            PackagePart worksheetPart = _xlPackage.Package.CreatePart(uriWorksheet, @"application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml");

            // create the new, empty worksheet and save it to the package
            StreamWriter streamWorksheet = new StreamWriter(worksheetPart.GetStream(FileMode.Create, FileAccess.Write));
            XmlDocument worksheetXml = old_sheet.WorksheetXml;
            worksheetXml.Save(streamWorksheet);
            streamWorksheet.Close();
            _xlPackage.Package.Flush();

            // create the relationship between the workbook and the new worksheet
            PackageRelationship rel = _xlPackage.Workbook.Part.CreateRelationship(uriWorksheet, TargetMode.Internal, ExcelPackage.schemaRelationships + "/worksheet");
            _xlPackage.Package.Flush();

            // now create the new worksheet tag and set name/SheetId attributes in the workbook.xml
            XmlElement worksheetNode = _xlPackage.Workbook.WorkbookXml.CreateElement("sheet", ExcelPackage.schemaMain);
            // create the new sheet node
            worksheetNode.SetAttribute("name", NewName);
            worksheetNode.SetAttribute("sheetId", sheetID.ToString());
            // set the r:id attribute
            worksheetNode.SetAttribute("id", ExcelPackage.schemaRelationships, rel.Id);
            // insert the sheet tag with all attributes set as above
            _worksheetsNode.AppendChild(worksheetNode);

            ExcelWorksheet worksheet = new ExcelWorksheet(_xlPackage, rel.Id, NewName, uriWorksheet, sheetID, false);
            XmlDocument doc = worksheet.WorksheetXml; // this will load the worksheet XML into the member variable.

            int positionID = _worksheets.Count + 1;
            _worksheets.Add(positionID, worksheet);
            return worksheet;
        }
Sep 17, 2008 at 9:51 PM
Edited Sep 17, 2008 at 9:56 PM
I'm using the following, but it's so simple that I can't imagine the original author wouldn't have implemented it himself if this was 100% correct.  

 


public
ExcelWorksheet Copy(string Name, string NewName)
{
     ExcelWorksheet destination = this.Add(NewName);
     destination.WorksheetXml.InnerXml =
this[Name].WorksheetXml.InnerXml;
     destination.Save();
     return destination;
}

 

 

 

Mar 4, 2012 at 9:17 PM

This one works. How's the workssheet.delete?