EPPlus/ExcelWorksheets.cs - epplus - Git at Google

 /*******************************************************************************
  * 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.Xml;
 using System.IO;
 using System.Linq;
 using OfficeOpenXml.Utils;
 namespace OfficeOpenXml
 {
 	/// <summary>
 	/// The collection of worksheets for the workbook
 	/// </summary>
 	public class ExcelWorksheets : XmlHelper, IEnumerable<ExcelWorksheet>, IDisposable
 	{
 		#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)
 			{
                 if (sheetNode.NodeType == XmlNodeType.Element)
                 {
                     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);

                     var sheetRelation = pck.Workbook.Part.GetRelationship(relId);
                     Uri uriWorksheet = UriHelper.ResolvePartUri(pck.Workbook.WorkbookUri, sheetRelation.TargetUri);

                     //add the worksheet
                     if (sheetRelation.RelationshipType.EndsWith("chartsheet"))
                     {
                         _worksheets.Add(positionID, new ExcelChartsheet(_namespaceManager, _pck, relId, uriWorksheet, name, sheetID, positionID, hidden));
                     }
                     else
                     {
                         _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";
         internal const string CHARTSHEET_CONTENTTYPE = @"application/vnd.openxmlformats-officedocument.spreadsheetml.chartsheet+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;
             lock (_worksheets)
             {
                 Name = ValidateFixSheetName(Name);
                 if (GetByName(Name) != null)
                 {
                     throw (new InvalidOperationException(ERR_DUP_WORKSHEET + " : " + Name));
                 }
                 GetSheetURI(ref Name, out sheetID, out uriWorksheet, false);
                 Packaging.ZipPackagePart 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(false);
                 worksheetXml.Save(streamWorksheet);
                 _pck.Package.Flush();

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

                 int positionID = _worksheets.Count + 1;
                 ExcelWorksheet worksheet;

                 {
                     worksheet = new ExcelWorksheet(_namespaceManager, _pck, rel, uriWorksheet, Name, sheetID, positionID, eWorkSheetHidden.Visible);
                 }

                 _worksheets.Add(positionID, worksheet);
                 return worksheet;
             }
         }

 		string CreateWorkbookRel(string Name, int sheetID, Uri uriWorksheet, bool isChart)
         {
             //Create the relationship between the workbook and the new worksheet
             var rel = _pck.Workbook.Part.CreateRelationship(UriHelper.GetRelativeUri(_pck.Workbook.WorkbookUri, uriWorksheet), Packaging.TargetMode.Internal, ExcelPackage.schemaRelationships + "/" + (isChart ? "chartsheet" : "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, bool isChart)
         {
             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
             if (isChart)
             {
                 uriWorksheet = new Uri("/xl/chartsheets/chartsheet" + sheetID.ToString() + ".xml", UriKind.Relative);
             }
             else
             {
                 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(bool isChart)
 		{
 			XmlDocument xmlDoc = new XmlDocument();
             XmlElement elemWs = xmlDoc.CreateElement(isChart ? "chartsheet" : "worksheet", ExcelPackage.schemaMain);
             elemWs.SetAttribute("xmlns:r", ExcelPackage.schemaRelationships);
             xmlDoc.AppendChild(elemWs);


             if (isChart)
             {
                 XmlElement elemSheetPr = xmlDoc.CreateElement("sheetPr", ExcelPackage.schemaMain);
                 elemWs.AppendChild(elemSheetPr);

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

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

                 elemSheetViews.AppendChild(elemSheetView);
             }
             else
             {
                 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

 		/// <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
 			{
 			    if (_worksheets.ContainsKey(PositionID))
 			    {
 			        return _worksheets[PositionID];
 			    }
 			    else
 			    {
 			        throw (new IndexOutOfRangeException("Worksheet position out of range."));
 			    }
 			}
 		}

 		/// <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);
 			}
 		}
 		#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.Equals(Name, StringComparison.InvariantCultureIgnoreCase))
                     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)
 		{
             // Bugfix: if source and target are the same worksheet the following code will create a duplicate
             //         which will cause a corrupt workbook. /swmal 2014-05-10
 		    if (sourcePositionId == targetPositionId) return;

             lock (_worksheets)
             {
                 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 (sourcePositionId == targetPositionId && _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)
 		{
             lock (TopNode.OwnerDocument)
             {
                 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
         public void Dispose()
         {
              foreach (var sheet in this._worksheets.Values)
              {
                  ((IDisposable)sheet).Dispose();
              }
              _worksheets = null;
              _pck = null;
         }
     } // end class Worksheets
 }
	/*******************************************************************************
	* 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.Xml;
	using System.IO;
	using System.Linq;
	using OfficeOpenXml.Utils;
	namespace OfficeOpenXml
	{
	/// <summary>
	/// The collection of worksheets for the workbook
	/// </summary>
	public class ExcelWorksheets : XmlHelper, IEnumerable<ExcelWorksheet>, IDisposable
	{
	#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)
	{
	if (sheetNode.NodeType == XmlNodeType.Element)
	{
	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);

	var sheetRelation = pck.Workbook.Part.GetRelationship(relId);
	Uri uriWorksheet = UriHelper.ResolvePartUri(pck.Workbook.WorkbookUri, sheetRelation.TargetUri);

	//add the worksheet
	if (sheetRelation.RelationshipType.EndsWith("chartsheet"))
	{
	_worksheets.Add(positionID, new ExcelChartsheet(_namespaceManager, _pck, relId, uriWorksheet, name, sheetID, positionID, hidden));
	}
	else
	{
	_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";
	internal const string CHARTSHEET_CONTENTTYPE = @"application/vnd.openxmlformats-officedocument.spreadsheetml.chartsheet+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;
	lock (_worksheets)
	{
	Name = ValidateFixSheetName(Name);
	if (GetByName(Name) != null)
	{
	throw (new InvalidOperationException(ERR_DUP_WORKSHEET + " : " + Name));
	}
	GetSheetURI(ref Name, out sheetID, out uriWorksheet, false);
	Packaging.ZipPackagePart 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(false);
	worksheetXml.Save(streamWorksheet);
	_pck.Package.Flush();

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

	int positionID = _worksheets.Count + 1;
	ExcelWorksheet worksheet;

	{
	worksheet = new ExcelWorksheet(_namespaceManager, _pck, rel, uriWorksheet, Name, sheetID, positionID, eWorkSheetHidden.Visible);
	}

	_worksheets.Add(positionID, worksheet);
	return worksheet;
	}
	}

	string CreateWorkbookRel(string Name, int sheetID, Uri uriWorksheet, bool isChart)
	{
	//Create the relationship between the workbook and the new worksheet
	var rel = _pck.Workbook.Part.CreateRelationship(UriHelper.GetRelativeUri(_pck.Workbook.WorkbookUri, uriWorksheet), Packaging.TargetMode.Internal, ExcelPackage.schemaRelationships + "/" + (isChart ? "chartsheet" : "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, bool isChart)
	{
	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
	if (isChart)
	{
	uriWorksheet = new Uri("/xl/chartsheets/chartsheet" + sheetID.ToString() + ".xml", UriKind.Relative);
	}
	else
	{
	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(bool isChart)
	{
	XmlDocument xmlDoc = new XmlDocument();
	XmlElement elemWs = xmlDoc.CreateElement(isChart ? "chartsheet" : "worksheet", ExcelPackage.schemaMain);
	elemWs.SetAttribute("xmlns:r", ExcelPackage.schemaRelationships);
	xmlDoc.AppendChild(elemWs);


	if (isChart)
	{
	XmlElement elemSheetPr = xmlDoc.CreateElement("sheetPr", ExcelPackage.schemaMain);
	elemWs.AppendChild(elemSheetPr);

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

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

	elemSheetViews.AppendChild(elemSheetView);
	}
	else
	{
	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

	/// <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
	{
	if (_worksheets.ContainsKey(PositionID))
	{
	return _worksheets[PositionID];
	}
	else
	{
	throw (new IndexOutOfRangeException("Worksheet position out of range."));
	}
	}
	}

	/// <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);
	}
	}
	#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.Equals(Name, StringComparison.InvariantCultureIgnoreCase))
	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)
	{
	// Bugfix: if source and target are the same worksheet the following code will create a duplicate
	// which will cause a corrupt workbook. /swmal 2014-05-10
	if (sourcePositionId == targetPositionId) return;

	lock (_worksheets)
	{
	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 (sourcePositionId == targetPositionId && _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)
	{
	lock (TopNode.OwnerDocument)
	{
	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
	public void Dispose()
	{
	foreach (var sheet in this._worksheets.Values)
	{
	((IDisposable)sheet).Dispose();
	}
	_worksheets = null;
	_pck = null;
	}
	} // end class Worksheets
	}