/*******************************************************************************
 * 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
}