source: branches/DataPreprocessing/HeuristicLab.ExtLibs/HeuristicLab.EPPlus/3.1.3/EPPlus-3.1.3/ExcelWorksheets.cs @ 10187

/*******************************************************************************
 * You may amend and distribute as you like, but don't remove this header!
 *
 * EPPlus provides server-side generation of Excel 2007/2010 spreadsheets.
 * See http://www.codeplex.com/EPPlus for details.
 *
 * Copyright (C) 2011  Jan Källman
 *
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or (at your option) any later version.

 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  
 * See the GNU Lesser General Public License for more details.
 *
 * The GNU Lesser General Public License can be viewed at http://www.opensource.org/licenses/lgpl-license.php
 * If you unfamiliar with this license or have questions about it, here is an http://www.gnu.org/licenses/gpl-faq.html
 *
 * All code and executables are provided "as is" with no warranty either express or implied. 
 * The author accepts no liability for any damage or loss of business that this product may cause.
 *
 * Code change notes:
 * 
 * Author             Change            Date
 * ******************************************************************************
 * Jan Källman        Initial Release          2009-10-01
 * Jan Källman        License changed GPL-->LGPL 2011-12-27
 *******************************************************************************/
using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Xml;
using System.IO;
using System.IO.Packaging;
using OfficeOpenXml.Style;
using OfficeOpenXml.Drawing;
using OfficeOpenXml.Drawing.Chart;
using OfficeOpenXml.Style.XmlAccess;
using OfficeOpenXml.Drawing.Vml;
using OfficeOpenXml.VBA;
namespace OfficeOpenXml
{
  /// <summary>
  /// The collection of worksheets for the workbook
  /// </summary>
  public class ExcelWorksheets : XmlHelper, IEnumerable<ExcelWorksheet>
  {
    #region Private Properties
        private ExcelPackage _pck;
        private Dictionary<int, ExcelWorksheet> _worksheets;
    private XmlNamespaceManager _namespaceManager;
    #endregion
    #region ExcelWorksheets Constructor
    internal ExcelWorksheets(ExcelPackage pck, XmlNamespaceManager nsm, XmlNode topNode) :
            base(nsm, topNode)
    {
      _pck = pck;
            _namespaceManager = nsm;
      _worksheets = new Dictionary<int, ExcelWorksheet>();
      int positionID = 1;

            foreach (XmlNode sheetNode in topNode.ChildNodes)
      {
        string name = sheetNode.Attributes["name"].Value;
        //Get the relationship id
        string relId = sheetNode.Attributes["r:id"].Value;
        int sheetID = Convert.ToInt32(sheetNode.Attributes["sheetId"].Value);
                
                //Hidden property
                eWorkSheetHidden hidden = eWorkSheetHidden.Visible;
        XmlNode attr = sheetNode.Attributes["state"];
        if (attr != null)
          hidden = TranslateHidden(attr.Value);

        PackageRelationship sheetRelation = pck.Workbook.Part.GetRelationship(relId);
        Uri uriWorksheet = PackUriHelper.ResolvePartUri(pck.Workbook.WorkbookUri, sheetRelation.TargetUri);
        
        //add the worksheet
                _worksheets.Add(positionID, new ExcelWorksheet(_namespaceManager, _pck, relId, uriWorksheet, name, sheetID, positionID, hidden));
        positionID++;
      }
    }

        private eWorkSheetHidden TranslateHidden(string value)
        {
            switch (value)
            {
                case "hidden":
                    return eWorkSheetHidden.Hidden;
                case "veryHidden":
                    return eWorkSheetHidden.VeryHidden;
                default:
                    return eWorkSheetHidden.Visible;
            }
        }
    #endregion

    #region ExcelWorksheets Public Properties
    /// <summary>
    /// Returns the number of worksheets in the workbook
    /// </summary>
    public int Count
    {
      get { return (_worksheets.Count); }
    }
    #endregion
        private const string ERR_DUP_WORKSHEET = "A worksheet with this name already exists in the workbook";
        internal const string WORKSHEET_CONTENTTYPE = @"application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml";
    #region ExcelWorksheets Public Methods
    /// <summary>
        /// Foreach support
    /// </summary>
    /// <returns>An enumerator</returns>
    public IEnumerator<ExcelWorksheet> GetEnumerator()
    {
      return (_worksheets.Values.GetEnumerator());
        }
        #region IEnumerable Members

        IEnumerator IEnumerable.GetEnumerator()
        {
            return (_worksheets.Values.GetEnumerator());
        }

        #endregion


    #region Add Worksheet
    /// <summary>
    /// Adds a new blank worksheet.
    /// </summary>
    /// <param name="Name">The name of the workbook</param>
    public ExcelWorksheet Add(string Name)
    {
            int sheetID;
            Uri uriWorksheet;
            if (GetByName(Name) != null)
            {
                throw (new InvalidOperationException(ERR_DUP_WORKSHEET));
            }
            GetSheetURI(ref Name, out sheetID, out uriWorksheet);
            PackagePart worksheetPart = _pck.Package.CreatePart(uriWorksheet, WORKSHEET_CONTENTTYPE, _pck.Compression);

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

            string rel = CreateWorkbookRel(Name, sheetID, uriWorksheet);

            int positionID = _worksheets.Count + 1;
            ExcelWorksheet worksheet = new ExcelWorksheet(_namespaceManager, _pck, rel, uriWorksheet, Name, sheetID, positionID, eWorkSheetHidden.Visible);

      _worksheets.Add(positionID, worksheet);
            if (_pck.Workbook.VbaProject != null)
            {
                _pck.Workbook.VbaProject.Modules.Add(new VBA.ExcelVBAModule(worksheet.CodeNameChange) { Name = Name, Code = "", Attributes = _pck.Workbook.VbaProject.GetDocumentAttributes(Name, "0{00020820-0000-0000-C000-000000000046}"), Type = eModuleType.Document, HelpContext = 0 });
                worksheet.CodeModuleName = Name;

            }
      return worksheet;
    }
        /// <summary>
        /// Adds a copy of a worksheet
        /// </summary>
        /// <param name="Name">The name of the workbook</param>
        /// <param name="Copy">The worksheet to be copied</param>
        public ExcelWorksheet Add(string Name, ExcelWorksheet Copy)
        {
            int sheetID;
            Uri uriWorksheet;

            if (GetByName(Name) != null)
            {
                throw (new InvalidOperationException(ERR_DUP_WORKSHEET));
            }

            GetSheetURI(ref Name, out sheetID, out uriWorksheet);

            //Create a copy of the worksheet XML
            PackagePart worksheetPart = _pck.Package.CreatePart(uriWorksheet, WORKSHEET_CONTENTTYPE, _pck.Compression);
            StreamWriter streamWorksheet = new StreamWriter(worksheetPart.GetStream(FileMode.Create, FileAccess.Write));
            XmlDocument worksheetXml = new XmlDocument();
            worksheetXml.LoadXml(Copy.WorksheetXml.OuterXml);
            worksheetXml.Save(streamWorksheet);
            streamWorksheet.Close();
            _pck.Package.Flush();


            //Create a relation to the workbook
            string relID = CreateWorkbookRel(Name, sheetID, uriWorksheet);
            ExcelWorksheet added = new ExcelWorksheet(_namespaceManager, _pck, relID, uriWorksheet, Name, sheetID, _worksheets.Count + 1, eWorkSheetHidden.Visible);

            //Copy comments
            if (Copy.Comments.Count > 0)
            {
                CopyComment(Copy, added);
            }
            else if (Copy.VmlDrawingsComments.Count > 0)    //Vml drawings are copied as part of the comments. 
            {
                CopyVmlDrawing(Copy, added);
            }

            //Copy HeaderFooter
            CopyHeaderFooterPictures(Copy, added);

            //Copy all relationships 
            //CopyRelationShips(Copy, added);
            if (Copy.Drawings.Count > 0)
            {
                CopyDrawing(Copy, added);
            }
      if (Copy.Tables.Count > 0)
            {
                CopyTable(Copy, added);
            }
            if (Copy.PivotTables.Count > 0)
            {
                CopyPivotTable(Copy, added);
            }
            if (Copy.Names.Count > 0)
            {
                CopySheetNames(Copy, added);
            }

            //Copy all cells
            CloneCells(Copy, added);

            _worksheets.Add(_worksheets.Count + 1, added);

            //Remove any relation to printersettings.
            XmlNode pageSetup = added.WorksheetXml.SelectSingleNode("//d:pageSetup", _namespaceManager);
            if (pageSetup != null)
            {
                XmlAttribute attr = (XmlAttribute)pageSetup.Attributes.GetNamedItem("id", ExcelPackage.schemaRelationships);
                if (attr != null)
                {
                    relID = attr.Value;
                    // first delete the attribute from the XML
                    pageSetup.Attributes.Remove(attr);
                }
            }

            return added;
        }

        private void CopySheetNames(ExcelWorksheet Copy, ExcelWorksheet added)
        {
            foreach (var name in Copy.Names)
            {
                ExcelNamedRange newName;
                if (!name.IsName)
                {
                    if (name.WorkSheet == Copy.Name)
                    {
                        newName = added.Names.Add(name.Name, added.Cells[name.FirstAddress]);
                    }
                    else
                    {
                        newName = added.Names.Add(name.Name, added.Workbook.Worksheets[name.WorkSheet].Cells[name.FirstAddress]);
                    }
                }
                else if (!string.IsNullOrEmpty(name.NameFormula))
                {
                    newName=added.Names.AddFormula(name.Name, name.Formula);
                }
                else
                {
                    newName=added.Names.AddValue(name.Name, name.Value);
                }
               newName.NameComment = name.NameComment;
            }
        }

        private void CopyTable(ExcelWorksheet Copy, ExcelWorksheet added)
        {
            string prevName = "";
            //First copy the table XML
            foreach (var tbl in Copy.Tables)
            {
                string xml=tbl.TableXml.OuterXml;
                int Id = _pck.Workbook._nextTableID++;
                string name;
                if (prevName == "")
                {
                    name = Copy.Tables.GetNewTableName();
                }
                else
                {
                    int ix = int.Parse(prevName.Substring(5)) + 1;
                    name = string.Format("Table{0}", ix);
                    while (_pck.Workbook.ExistsPivotTableName(name))
                    {
                        name = string.Format("Table{0}", ++ix);
                    }
                }
                prevName = name;
                XmlDocument xmlDoc = new XmlDocument();
                xmlDoc.LoadXml(xml);
                xmlDoc.SelectSingleNode("//d:table/@id", tbl.NameSpaceManager).Value = Id.ToString();
                xmlDoc.SelectSingleNode("//d:table/@name", tbl.NameSpaceManager).Value = name;
                xmlDoc.SelectSingleNode("//d:table/@displayName", tbl.NameSpaceManager).Value = name;
                xml = xmlDoc.OuterXml;

                var uriTbl = new Uri(string.Format("/xl/tables/table{0}.xml", Id), UriKind.Relative);
                var part = _pck.Package.CreatePart(uriTbl, "application/vnd.openxmlformats-officedocument.spreadsheetml.table+xml", _pck.Compression);
                StreamWriter streamTbl = new StreamWriter(part.GetStream(FileMode.Create, FileAccess.Write));
                streamTbl.Write(xml);
                streamTbl.Close();

                //create the relationship and add the ID to the worksheet xml.
                var rel = added.Part.CreateRelationship(PackUriHelper.GetRelativeUri(added.WorksheetUri,uriTbl), TargetMode.Internal, ExcelPackage.schemaRelationships + "/table");

                if (tbl.RelationshipID == null)
                {
                    var topNode = added.WorksheetXml.SelectSingleNode("//d:tableParts", tbl.NameSpaceManager);
                    if (topNode == null)
                    {
                        added.CreateNode("d:tableParts");
                        topNode = added.WorksheetXml.SelectSingleNode("//d:tableParts", tbl.NameSpaceManager);
                    }
                    XmlElement elem = added.WorksheetXml.CreateElement("tablePart", ExcelPackage.schemaMain);
                    topNode.AppendChild(elem);
                    elem.SetAttribute("id",ExcelPackage.schemaRelationships, rel.Id);
                }
                else
                {
                    XmlAttribute relAtt;
                    relAtt = added.WorksheetXml.SelectSingleNode(string.Format("//d:tableParts/d:tablePart/@r:id[.='{0}']", tbl.RelationshipID), tbl.NameSpaceManager) as XmlAttribute;
                    relAtt.Value = rel.Id;
                }
            }
        }
        private void CopyPivotTable(ExcelWorksheet Copy, ExcelWorksheet added)
        {
            string prevName = "";
            foreach (var tbl in Copy.PivotTables)
            {
                string xml = tbl.PivotTableXml.OuterXml;
                int Id = _pck.Workbook._nextPivotTableID++;

                string name;
                if (prevName == "")
                {
                    name = Copy.PivotTables.GetNewTableName();
                }
                else
                {
                    int ix=int.Parse(prevName.Substring(10))+1;
                    name = string.Format("PivotTable{0}", ix);
                    while (_pck.Workbook.ExistsPivotTableName(name))
                    {
                        name = string.Format("PivotTable{0}", ++ix);
                    }
                }
                prevName=name;
                XmlDocument xmlDoc = new XmlDocument();
                Copy.Save();    //Save the worksheet first
                xmlDoc.LoadXml(xml);
                //xmlDoc.SelectSingleNode("//d:table/@id", tbl.NameSpaceManager).Value = Id.ToString();
                xmlDoc.SelectSingleNode("//d:pivotTableDefinition/@name", tbl.NameSpaceManager).Value = name;
                xml = xmlDoc.OuterXml;

                var uriTbl = new Uri(string.Format("/xl/pivotTables/pivotTable{0}.xml", Id), UriKind.Relative);
                var part = _pck.Package.CreatePart(uriTbl, ExcelPackage.schemaPivotTable , _pck.Compression);
                StreamWriter streamTbl = new StreamWriter(part.GetStream(FileMode.Create, FileAccess.Write));
                streamTbl.Write(xml);
                streamTbl.Close();

                //create the relationship and add the ID to the worksheet xml.
                added.Part.CreateRelationship(PackUriHelper.ResolvePartUri(added.WorksheetUri, uriTbl), TargetMode.Internal, ExcelPackage.schemaRelationships + "/pivotTable");
                part.CreateRelationship(PackUriHelper.ResolvePartUri(tbl.Relationship.SourceUri, tbl.CacheDefinition.Relationship.TargetUri), tbl.CacheDefinition.Relationship.TargetMode, tbl.CacheDefinition.Relationship.RelationshipType);
            }
        }
        private void CopyHeaderFooterPictures(ExcelWorksheet Copy, ExcelWorksheet added)
        {
            if (Copy._headerFooter == null) return;
            //Copy the texts
            CopyText(Copy.HeaderFooter._oddHeader, added.HeaderFooter.OddHeader);
            CopyText(Copy.HeaderFooter._oddFooter, added.HeaderFooter.OddFooter);
            CopyText(Copy.HeaderFooter._evenHeader, added.HeaderFooter.EvenHeader);
            CopyText(Copy.HeaderFooter._evenFooter, added.HeaderFooter.EvenFooter);
            CopyText(Copy.HeaderFooter._firstHeader, added.HeaderFooter.FirstHeader);
            CopyText(Copy.HeaderFooter._firstFooter, added.HeaderFooter.FirstFooter);
            
            //Copy any images;
            if (Copy.HeaderFooter.Pictures.Count > 0)
            {
                Uri source = Copy.HeaderFooter.Pictures.Uri;
                Uri dest = XmlHelper.GetNewUri(_pck.Package, @"/xl/drawings/vmlDrawing{0}.vml");
                
                var part = _pck.Package.CreatePart(dest, "application/vnd.openxmlformats-officedocument.vmlDrawing", _pck.Compression);
                foreach (ExcelVmlDrawingPicture pic in Copy.HeaderFooter.Pictures)
                {
                    var item = added.HeaderFooter.Pictures.Add(pic.Id, pic.ImageUri, pic.Title, pic.Width, pic.Height);
                    foreach (XmlAttribute att in pic.TopNode.Attributes)
                    {
                        (item.TopNode as XmlElement).SetAttribute(att.Name, att.Value);
                    }
                    item.TopNode.InnerXml = pic.TopNode.InnerXml;
                }
            }
        }

        private void CopyText(ExcelHeaderFooterText from, ExcelHeaderFooterText to)
        {
            if (from == null) return;
            to.LeftAlignedText=from.LeftAlignedText;
            to.CenteredText = from.CenteredText;
            to.RightAlignedText = from.RightAlignedText;
        }
        private void CloneCells(ExcelWorksheet Copy, ExcelWorksheet added)
        {
            bool sameWorkbook=(Copy.Workbook == _pck.Workbook);

            bool doAdjust = _pck.DoAdjustDrawings;
            _pck.DoAdjustDrawings = false;
            added.MergedCells.List.AddRange(Copy.MergedCells.List);
            //Formulas
            foreach (IRangeID f in Copy._formulaCells)
            {
                added._formulaCells.Add(f);
            }
            //Shared Formulas
            foreach (int key in Copy._sharedFormulas.Keys)
            {
                added._sharedFormulas.Add(key, Copy._sharedFormulas[key]);
            }
            
            Dictionary<int, int> styleCashe = new Dictionary<int, int>();
            //Cells
            foreach (ExcelCell cell in Copy._cells)
            {                
                if (sameWorkbook)   //Same workbook == same styles
                {
                    added._cells.Add(cell.Clone(added));
                }
                else
                {
                    ExcelCell addedCell=cell.Clone(added);
                    if (styleCashe.ContainsKey(cell.StyleID))
                    {
                        addedCell.StyleID = styleCashe[cell.StyleID];
                    }
                    else
                    {
                        addedCell.StyleID = added.Workbook.Styles.CloneStyle(Copy.Workbook.Styles,  cell.StyleID);
                        if (cell.StyleName != "") //Named styles
                        {
                            if (!Copy.Workbook.Styles.NamedStyles.ExistsKey(cell.StyleName))
                            {
                               var ns=Copy.Workbook.Styles.CreateNamedStyle(cell.StyleName);
                               ns.StyleXfId  = addedCell.StyleID;
                            }
                            
                        }
                        styleCashe.Add(cell.StyleID, addedCell.StyleID);
                    }
                    added._cells.Add(addedCell);
                }
            }
            //Rows
            foreach (ExcelRow row in Copy._rows)
            {
                row.Clone(added);
                if (!sameWorkbook)   //Same workbook == same styles
                {
                    ExcelRow addedRow = added.Row(row.Row) as ExcelRow;
                    if (styleCashe.ContainsKey(row.StyleID))
                    {
                        addedRow.StyleID = styleCashe[row.StyleID];
                    }
                    else
                    {
                        addedRow.StyleID = added.Workbook.Styles.CloneStyle(Copy.Workbook.Styles, addedRow.StyleID);
                        if (row.StyleName != "") //Named styles
                        {
                            if (!Copy.Workbook.Styles.NamedStyles.ExistsKey(row.StyleName))
                            {
                                var ns = Copy.Workbook.Styles.CreateNamedStyle(row.StyleName);
                                ns.StyleXfId = addedRow.StyleID;
                            }

                        }
                        styleCashe.Add(row.StyleID, addedRow.StyleID);
                    }
                }                
            }
            //Columns
            foreach (ExcelColumn col in Copy._columns)
            {
                col.Clone(added);
                if (!sameWorkbook)   //Same workbook == same styles
                {
                    ExcelColumn addedCol = added.Column(col.ColumnMin) as ExcelColumn;
                    if (styleCashe.ContainsKey(col.StyleID))
                    {
                        addedCol.StyleID = styleCashe[col.StyleID];
                    }
                    else
                    {
                        addedCol.StyleID = added.Workbook.Styles.CloneStyle(Copy.Workbook.Styles, addedCol.StyleID);
                        if (col.StyleName != "") //Named styles
                        {
                            if (!Copy.Workbook.Styles.NamedStyles.ExistsKey(col.StyleName))
                            {
                                var ns = Copy.Workbook.Styles.CreateNamedStyle(col.StyleName);
                                ns.StyleXfId = addedCol.StyleID;
                            }

                        }
                        styleCashe.Add(col.StyleID, addedCol.StyleID);
                    }
                }
            }
            added._package.DoAdjustDrawings = doAdjust;
        }
        private void CopyComment(ExcelWorksheet Copy, ExcelWorksheet workSheet)
        {
            //First copy the drawing XML
            string xml = Copy.Comments.CommentXml.InnerXml;
            var uriComment = new Uri(string.Format("/xl/comments{0}.xml", workSheet.SheetID), UriKind.Relative);
            if (_pck.Package.PartExists(uriComment))
            {
                uriComment = XmlHelper.GetNewUri(_pck.Package, "/xl/drawings/vmldrawing{0}.vml");
            }

            var part = _pck.Package.CreatePart(uriComment, "application/vnd.openxmlformats-officedocument.spreadsheetml.comments+xml", _pck.Compression);

            StreamWriter streamDrawing = new StreamWriter(part.GetStream(FileMode.Create, FileAccess.Write));
            streamDrawing.Write(xml);
            streamDrawing.Close();

            //Add the relationship ID to the worksheet xml.
            PackageRelationship commentRelation = workSheet.Part.CreateRelationship(PackUriHelper.GetRelativeUri(workSheet.WorksheetUri,uriComment), TargetMode.Internal, ExcelPackage.schemaRelationships + "/comments");

            xml = Copy.VmlDrawingsComments.VmlDrawingXml.InnerXml;

            var uriVml = new Uri(string.Format("/xl/drawings/vmldrawing{0}.vml", workSheet.SheetID), UriKind.Relative);
            if (_pck.Package.PartExists(uriVml))
            {
                uriVml = XmlHelper.GetNewUri(_pck.Package, "/xl/drawings/vmldrawing{0}.vml");
            }

            var vmlPart = _pck.Package.CreatePart(uriVml, "application/vnd.openxmlformats-officedocument.vmlDrawing", _pck.Compression);
            StreamWriter streamVml = new StreamWriter(vmlPart.GetStream(FileMode.Create, FileAccess.Write));
            streamVml.Write(xml);
            streamVml.Close();

            PackageRelationship newVmlRel = workSheet.Part.CreateRelationship(PackUriHelper.GetRelativeUri(workSheet.WorksheetUri,uriVml), TargetMode.Internal, ExcelPackage.schemaRelationships + "/vmlDrawing");

            //Add the relationship ID to the worksheet xml.
            XmlElement e = workSheet.WorksheetXml.SelectSingleNode("//d:legacyDrawing", _namespaceManager) as XmlElement;
            if (e == null)
            {
                workSheet.CreateNode("d:legacyDrawing");
                e = workSheet.WorksheetXml.SelectSingleNode("//d:legacyDrawing", _namespaceManager) as XmlElement;
            }

            e.SetAttribute("id", ExcelPackage.schemaRelationships, newVmlRel.Id);
        }
        private void CopyDrawing(ExcelWorksheet Copy, ExcelWorksheet workSheet/*, PackageRelationship r*/)
        {
            
            //Check if the worksheet has drawings
            //if(_xlPackage.Package.PartExists(r.TargetUri))
            //{
                //First copy the drawing XML                
                string xml = Copy.Drawings.DrawingXml.OuterXml;            
                var uriDraw=new Uri(string.Format("/xl/drawings/drawing{0}.xml", workSheet.SheetID),  UriKind.Relative);
                var part= _pck.Package.CreatePart(uriDraw,"application/vnd.openxmlformats-officedocument.drawing+xml", _pck.Compression);
                StreamWriter streamDrawing = new StreamWriter(part.GetStream(FileMode.Create, FileAccess.Write));
                streamDrawing.Write(xml);
                streamDrawing.Close();

                XmlDocument drawXml = new XmlDocument();
                drawXml.LoadXml(xml);
                //Add the relationship ID to the worksheet xml.
                PackageRelationship drawRelation = workSheet.Part.CreateRelationship(PackUriHelper.GetRelativeUri(workSheet.WorksheetUri,uriDraw), TargetMode.Internal, ExcelPackage.schemaRelationships + "/drawing");
                XmlElement e = workSheet.WorksheetXml.SelectSingleNode("//d:drawing", _namespaceManager) as XmlElement;
                e.SetAttribute("id",ExcelPackage.schemaRelationships, drawRelation.Id);

                foreach (ExcelDrawing draw in Copy.Drawings)
                {
                    if (draw is ExcelChart)
                    {
                        ExcelChart chart = draw as ExcelChart;
                        xml = chart.ChartXml.InnerXml;

                        var UriChart = XmlHelper.GetNewUri(_pck.Package, "/xl/charts/chart{0}.xml");
                        var chartPart = _pck.Package.CreatePart(UriChart, "application/vnd.openxmlformats-officedocument.drawingml.chart+xml", _pck.Compression);
                        StreamWriter streamChart = new StreamWriter(chartPart.GetStream(FileMode.Create, FileAccess.Write));
                        streamChart.Write(xml);
                        streamChart.Close();
                        //Now create the new relationship to the copied chart xml
                        var prevRelID=draw.TopNode.SelectSingleNode("xdr:graphicFrame/a:graphic/a:graphicData/c:chart/@r:id", Copy.Drawings.NameSpaceManager).Value;
                        var rel = part.CreateRelationship(PackUriHelper.GetRelativeUri(uriDraw,UriChart), TargetMode.Internal, ExcelPackage.schemaRelationships + "/chart");
                        XmlAttribute relAtt = drawXml.SelectSingleNode(string.Format("//c:chart/@r:id[.='{0}']", prevRelID), Copy.Drawings.NameSpaceManager) as XmlAttribute;
                        relAtt.Value=rel.Id;
                    }
                    else if (draw is ExcelPicture)
                    {
                        ExcelPicture pic = draw as ExcelPicture;
                        var uri = pic.UriPic;
                        if(!workSheet.Workbook._package.Package.PartExists(uri))
                        {
                            var picPart = workSheet.Workbook._package.Package.CreatePart(uri, pic.ContentType, CompressionOption.NotCompressed);
                            pic.Image.Save(picPart.GetStream(FileMode.Create, FileAccess.Write), pic.ImageFormat);
                        }

                        var prevRelID = draw.TopNode.SelectSingleNode("xdr:pic/xdr:blipFill/a:blip/@r:embed", Copy.Drawings.NameSpaceManager).Value;
                        var rel = part.CreateRelationship(PackUriHelper.GetRelativeUri(workSheet.WorksheetUri, uri), TargetMode.Internal, ExcelPackage.schemaRelationships + "/image");
                        XmlAttribute relAtt = drawXml.SelectSingleNode(string.Format("//xdr:pic/xdr:blipFill/a:blip/@r:embed[.='{0}']", prevRelID), Copy.Drawings.NameSpaceManager) as XmlAttribute;
                        relAtt.Value = rel.Id;
                    }
                }
                //rewrite the drawing xml with the new relID's
                streamDrawing = new StreamWriter(part.GetStream(FileMode.Create, FileAccess.Write));
                streamDrawing.Write(drawXml.OuterXml);
                streamDrawing.Close();
            //}
        }

    private void CopyVmlDrawing(ExcelWorksheet origSheet, ExcelWorksheet newSheet)
    {
      var xml = origSheet.VmlDrawingsComments.VmlDrawingXml.OuterXml;
      var vmlUri = new Uri(string.Format("/xl/drawings/vmlDrawing{0}.vml", newSheet.SheetID), UriKind.Relative);
      var part = _pck.Package.CreatePart(vmlUri, "application/vnd.openxmlformats-officedocument.vmlDrawing", _pck.Compression);
      using (var streamDrawing = new StreamWriter(part.GetStream(FileMode.Create, FileAccess.Write)))
      {
        streamDrawing.Write(xml);
      }

      //Add the relationship ID to the worksheet xml.
      PackageRelationship vmlRelation = newSheet.Part.CreateRelationship(PackUriHelper.GetRelativeUri(newSheet.WorksheetUri,vmlUri), TargetMode.Internal, ExcelPackage.schemaRelationships + "/vmlDrawing");
      var e = newSheet.WorksheetXml.SelectSingleNode("//d:legacyDrawing", _namespaceManager) as XmlElement;
      if (e == null)
      {
        e = newSheet.WorksheetXml.CreateNode(XmlNodeType.Entity, "//d:legacyDrawing", _namespaceManager.LookupNamespace("d")) as XmlElement;
      }
      if (e != null)
      {
        e.SetAttribute("id", ExcelPackage.schemaRelationships, vmlRelation.Id);
      }
    }

    string CreateWorkbookRel(string Name, int sheetID, Uri uriWorksheet)
        {
            //Create the relationship between the workbook and the new worksheet
            PackageRelationship rel = _pck.Workbook.Part.CreateRelationship(PackUriHelper.GetRelativeUri(_pck.Workbook.WorkbookUri, uriWorksheet), TargetMode.Internal, ExcelPackage.schemaRelationships + "/worksheet");
            _pck.Package.Flush();

            //Create the new sheet node
            XmlElement worksheetNode = _pck.Workbook.WorkbookXml.CreateElement("sheet", ExcelPackage.schemaMain);
            worksheetNode.SetAttribute("name", Name);
            worksheetNode.SetAttribute("sheetId", sheetID.ToString());
            worksheetNode.SetAttribute("id", ExcelPackage.schemaRelationships, rel.Id);

            TopNode.AppendChild(worksheetNode);
            return rel.Id;
        }
        private void GetSheetURI(ref string Name, out int sheetID, out Uri uriWorksheet)
        {
            Name = ValidateFixSheetName(Name);

            //First find maximum existing sheetID
            sheetID = 0;
            foreach(var ws in this)
            {                
                if (ws.SheetID > sheetID)
                {
                    sheetID = ws.SheetID;
                }
            }
            // we now have the max existing values, so add one
            sheetID++;

            // add the new worksheet to the package
            uriWorksheet = new Uri("/xl/worksheets/sheet" + sheetID.ToString() + ".xml", UriKind.Relative);
        }

        internal string ValidateFixSheetName(string Name)
        {
            //remove invalid characters
            if (ValidateName(Name))
            {
                if (Name.IndexOf(':') > -1) Name = Name.Replace(":", " ");
                if (Name.IndexOf('/') > -1) Name = Name.Replace("/", " ");
                if (Name.IndexOf('\\') > -1) Name = Name.Replace("\\", " ");
                if (Name.IndexOf('?') > -1) Name = Name.Replace("?", " ");
                if (Name.IndexOf('[') > -1) Name = Name.Replace("[", " ");
                if (Name.IndexOf(']') > -1) Name = Name.Replace("]", " ");
            }

            if (Name.Trim() == "")
            {
                throw new ArgumentException("The worksheet can not have an empty name");
            }
            if (Name.Length > 31) Name = Name.Substring(0, 31);   //A sheet can have max 31 char's
            return Name;
        }
        /// <summary>
        /// Validate the sheetname
        /// </summary>
        /// <param name="Name">The Name</param>
        /// <returns>True if valid</returns>
        private bool ValidateName(string Name)
        {
            return System.Text.RegularExpressions.Regex.IsMatch(Name, @":|\?|/|\\|\[|\]");
        }

    /// <summary>
    /// Creates the XML document representing a new empty worksheet
    /// </summary>
    /// <returns></returns>
    internal XmlDocument CreateNewWorksheet()
    {
      XmlDocument xmlDoc = new XmlDocument();
            XmlElement elemWs = xmlDoc.CreateElement("worksheet", ExcelPackage.schemaMain);
            elemWs.SetAttribute("xmlns:r", ExcelPackage.schemaRelationships);
            xmlDoc.AppendChild(elemWs);

            XmlElement elemSheetViews = xmlDoc.CreateElement("sheetViews", ExcelPackage.schemaMain);
            elemWs.AppendChild(elemSheetViews);

            XmlElement elemSheetView = xmlDoc.CreateElement("sheetView", ExcelPackage.schemaMain);
            elemSheetView.SetAttribute("workbookViewId", "0");
            elemSheetViews.AppendChild(elemSheetView);

            XmlElement elemSheetFormatPr = xmlDoc.CreateElement("sheetFormatPr", ExcelPackage.schemaMain);
            elemSheetFormatPr.SetAttribute("defaultRowHeight", "15");
            elemWs.AppendChild(elemSheetFormatPr);

            XmlElement elemSheetData = xmlDoc.CreateElement("sheetData", ExcelPackage.schemaMain);
            elemWs.AppendChild(elemSheetData);
            return xmlDoc;
    }
    #endregion

    #region Delete Worksheet
    /// <summary>
    /// Deletes a worksheet from the collection
    /// </summary>
    /// <param name="Index">The position of the worksheet in the workbook</param>
    public void Delete(int Index)
    {
      ExcelWorksheet worksheet = _worksheets[Index];
            if (worksheet.Drawings.Count > 0)
            {
                worksheet.Drawings.Clear();
            }
            //Delete the worksheet part and relation from the package 
      _pck.Package.DeletePart(worksheet.WorksheetUri);
      _pck.Workbook.Part.DeleteRelationship(worksheet.RelationshipID);

            //Delete worksheet from the workbook XML
      XmlNode sheetsNode = _pck.Workbook.WorkbookXml.SelectSingleNode("//d:workbook/d:sheets", _namespaceManager);
      if (sheetsNode != null)
      {
        XmlNode sheetNode = sheetsNode.SelectSingleNode(string.Format("./d:sheet[@sheetId={0}]", worksheet.SheetID), _namespaceManager);
        if (sheetNode != null)
        {
          sheetsNode.RemoveChild(sheetNode);
        }
      }
      _worksheets.Remove(Index);
            if (_pck.Workbook.VbaProject != null)
            {
                _pck.Workbook.VbaProject.Modules.Remove(worksheet.CodeModule);
            }
      ReindexWorksheetDictionary();
    }

    /// <summary>
    /// Deletes a worksheet from the collection
    /// </summary>
    /// <param name="name">The name of the worksheet in the workbook</param>
    public void Delete(string name)
    {
      var sheet = this[name];
      if (sheet == null)
      {
        throw new ArgumentException(string.Format("Could not find worksheet to delete '{0}'", name));
      }
      Delete(sheet.PositionID);
    }
    /// <summary>
        /// Delete a worksheet from the collection
        /// </summary>
        /// <param name="Worksheet">The worksheet to delete</param>
        public void Delete(ExcelWorksheet Worksheet)
    {
            if (Worksheet.PositionID <= _worksheets.Count && Worksheet == _worksheets[Worksheet.PositionID])
            {
                Delete(Worksheet.PositionID);
            }
            else
            {
                throw (new ArgumentException("Worksheet is not in the collection."));
            }
        }
        #endregion

    private void ReindexWorksheetDictionary()
    {
      var index = 1;
      var worksheets = new Dictionary<int, ExcelWorksheet>();
      foreach (var entry in _worksheets)
      {
        entry.Value.PositionID = index;
        worksheets.Add(index++, entry.Value);
      }
      _worksheets = worksheets;
    }

    /// <summary>
    /// Returns the worksheet at the specified position.  
    /// </summary>
    /// <param name="PositionID">The position of the worksheet. 1-base</param>
    /// <returns></returns>
    public ExcelWorksheet this[int PositionID]
    {
      get
      {
                return (_worksheets[PositionID]);
      }
    }

    /// <summary>
    /// Returns the worksheet matching the specified name
    /// </summary>
    /// <param name="Name">The name of the worksheet</param>
    /// <returns></returns>
    public ExcelWorksheet this[string Name]
    {
      get
      {
                return GetByName(Name);
      }
    }
    /// <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>The new copy added to the end of the worksheets collection</returns>
    public ExcelWorksheet Copy(string Name, string NewName)
    {
            ExcelWorksheet Copy = this[Name];
            if (Copy == null)
                throw new ArgumentException(string.Format("Copy worksheet error: Could not find worksheet to copy '{0}'", Name));

            ExcelWorksheet added = Add(NewName, Copy);
            return added;
        }
    #endregion

        internal ExcelWorksheet GetBySheetID(int localSheetID)
        {
            foreach (ExcelWorksheet ws in this)
            {
                if (ws.SheetID == localSheetID)
                {
                    return ws;
                }
            }
            return null;
        }
        private ExcelWorksheet GetByName(string Name)
        {
            if (string.IsNullOrEmpty(Name)) return null;
            ExcelWorksheet xlWorksheet = null;
            foreach (ExcelWorksheet worksheet in _worksheets.Values)
            {
                if (worksheet.Name.ToLower() == Name.ToLower())
                    xlWorksheet = worksheet;
            }
            return (xlWorksheet);
        }
    #region MoveBefore and MoveAfter Methods
    /// <summary>
    /// Moves the source worksheet to the position before the target worksheet
    /// </summary>
    /// <param name="sourceName">The name of the source worksheet</param>
    /// <param name="targetName">The name of the target worksheet</param>
    public void MoveBefore(string sourceName, string targetName)
    {
      Move(sourceName, targetName, false);
    }

    /// <summary>
    /// Moves the source worksheet to the position before the target worksheet
    /// </summary>
    /// <param name="sourcePositionId">The id of the source worksheet</param>
    /// <param name="targetPositionId">The id of the target worksheet</param>
    public void MoveBefore(int sourcePositionId, int targetPositionId)
    {
      Move(sourcePositionId, targetPositionId, false);
    }

    /// <summary>
    /// Moves the source worksheet to the position after the target worksheet
    /// </summary>
    /// <param name="sourceName">The name of the source worksheet</param>
    /// <param name="targetName">The name of the target worksheet</param>
    public void MoveAfter(string sourceName, string targetName)
    {
      Move(sourceName, targetName, true);
    }

    /// <summary>
    /// Moves the source worksheet to the position after the target worksheet
    /// </summary>
    /// <param name="sourcePositionId">The id of the source worksheet</param>
    /// <param name="targetPositionId">The id of the target worksheet</param>
    public void MoveAfter(int sourcePositionId, int targetPositionId)
    {
      Move(sourcePositionId, targetPositionId, true);
    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="sourceName"></param>
    public void MoveToStart(string sourceName)
    {
      var sourceSheet = this[sourceName];
      if (sourceSheet == null)
      {
        throw new Exception(string.Format("Move worksheet error: Could not find worksheet to move '{0}'", sourceName));
      }
      Move(sourceSheet.PositionID, 1, false);
    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="sourcePositionId"></param>
    public void MoveToStart(int sourcePositionId)
    {
      Move(sourcePositionId, 1, false);
    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="sourceName"></param>
    public void MoveToEnd(string sourceName)
    {
      var sourceSheet = this[sourceName];
      if (sourceSheet == null)
      {
        throw new Exception(string.Format("Move worksheet error: Could not find worksheet to move '{0}'", sourceName));
      }
      Move(sourceSheet.PositionID, _worksheets.Count, true);
    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="sourcePositionId"></param>
    public void MoveToEnd(int sourcePositionId)
    {
      Move(sourcePositionId, _worksheets.Count, true);
    }

    private void Move(string sourceName, string targetName, bool placeAfter)
    {
      var sourceSheet = this[sourceName];
      if (sourceSheet == null)
      {
        throw new Exception(string.Format("Move worksheet error: Could not find worksheet to move '{0}'", sourceName));
      }
      var targetSheet = this[targetName];
      if (targetSheet == null)
      {
        throw new Exception(string.Format("Move worksheet error: Could not find worksheet to move '{0}'", targetName));
      }
      Move(sourceSheet.PositionID, targetSheet.PositionID, placeAfter);
    }

    private void Move(int sourcePositionId, int targetPositionId, bool placeAfter)
    {
      var sourceSheet = this[sourcePositionId];
      if (sourceSheet == null)
      {
        throw new Exception(string.Format("Move worksheet error: Could not find worksheet at position '{0}'", sourcePositionId));
      }
      var targetSheet = this[targetPositionId];
      if (targetSheet == null)
      {
        throw new Exception(string.Format("Move worksheet error: Could not find worksheet at position '{0}'", targetPositionId));
      }
      if (_worksheets.Count < 2)
      {
        return;   //--- no reason to attempt to re-arrange a single item with itself
      }

      var index = 1;
      var newOrder = new Dictionary<int, ExcelWorksheet>();
      foreach (var entry in _worksheets)
      {
        if (entry.Key == targetPositionId)
        {
          if (!placeAfter)
          {
            sourceSheet.PositionID = index;
            newOrder.Add(index++, sourceSheet);
          }

          entry.Value.PositionID = index;
          newOrder.Add(index++, entry.Value);

          if (placeAfter)
          {
            sourceSheet.PositionID = index;
            newOrder.Add(index++, sourceSheet);
          }
        }
        else if (entry.Key == sourcePositionId)
        {
          //--- do nothing
        }
        else
        {
          entry.Value.PositionID = index;
          newOrder.Add(index++, entry.Value);
        }
      }
      _worksheets = newOrder;

      MoveSheetXmlNode(sourceSheet, targetSheet, placeAfter);
    }

    private void MoveSheetXmlNode(ExcelWorksheet sourceSheet, ExcelWorksheet targetSheet, bool placeAfter)
    {
      var sourceNode = TopNode.SelectSingleNode(string.Format("d:sheet[@sheetId = '{0}']", sourceSheet.SheetID), _namespaceManager);
            var targetNode = TopNode.SelectSingleNode(string.Format("d:sheet[@sheetId = '{0}']", targetSheet.SheetID), _namespaceManager);
      if (sourceNode == null || targetNode == null)
      {
        throw new Exception("Source SheetId and Target SheetId must be valid");
      }
      if (placeAfter)
      {
                TopNode.InsertAfter(sourceNode, targetNode);
      }
      else
      {
                TopNode.InsertBefore(sourceNode, targetNode);
      }
    }

    #endregion
  } // end class Worksheets
}