AppsheetEpplus/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.Text.RegularExpressions;
 using System.Xml;

 namespace AppsheetEpplus;

 /// <summary>
 /// The collection of worksheets for the workbook
 /// </summary>
 public class ExcelWorksheets : XmlHelper, IEnumerable<ExcelWorksheet> {
   private readonly ExcelPackage _pck;
   private readonly ExcelWorkbook _workbook;
   private Dictionary<int, ExcelWorksheet> _worksheets = new();
   private readonly XmlNamespaceManager _namespaceManager;

   internal ExcelWorksheets(
       ExcelPackage pck,
       ExcelWorkbook workbook,
       XmlNamespaceManager nsm,
       XmlNode topNode)
       : base(nsm, topNode) {
     _pck = pck;
     _workbook = workbook;
     _namespaceManager = nsm;
     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 = _workbook.Part.GetRelationship(relId);
         Uri uriWorksheet = UriHelper.ResolvePartUri(
             ExcelWorkbook.WorkbookUri,
             sheetRelation.TargetUri);

         //add the worksheet
         if (sheetRelation.RelationshipType.EndsWith("chartsheet")) {
           _worksheets.Add(
               positionId,
               new ExcelChartsheet(
                   _namespaceManager,
                   pck,
                   _workbook,
                   uriWorksheet,
                   name,
                   sheetId,
                   positionId,
                   hidden));
         } else {
           _worksheets.Add(
               positionId,
               new(
                   _namespaceManager,
                   pck,
                   _workbook,
                   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;
     }
   }

   /// <summary>
   /// Returns the number of worksheets in the workbook
   /// </summary>
   public int Count => (_worksheets.Count);

   private const string _errDupWorksheet =
       "A worksheet with this name already exists in the workbook";
   internal const string _worksheetContentType =
       "application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml";

   /// <summary>
   /// Foreach support
   /// </summary>
   /// <returns>An enumerator</returns>
   public IEnumerator<ExcelWorksheet> GetEnumerator() {
     return (_worksheets.Values.GetEnumerator());
   }

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

   /// <summary>
   /// Adds a new blank worksheet.
   /// </summary>
   /// <param name="name">The name of the workbook</param>
   public ExcelWorksheet Add(string name) {
     name = ValidateFixSheetName(name);
     if (GetByName(name) != null) {
       throw (new InvalidOperationException(_errDupWorksheet + " : " + name));
     }
     GetSheetUri(ref name, out var sheetId, out var uriWorksheet, false);

     // Create the new worksheet
     var rel = _pck.CreateXmlDocument(
         uriWorksheet,
         _worksheetContentType,
         ExcelPackage._schemaRelationships + "/worksheet",
         CreateNewWorksheet(false));

     // Add worksheet to the workbook
     XmlElement worksheetNode = _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);

     int positionId = _worksheets.Count + 1;

     ExcelWorksheet worksheet = new(
         _namespaceManager,
         _pck,
         _workbook,
         uriWorksheet,
         name,
         sheetId,
         positionId,
         eWorkSheetHidden.Visible);

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

   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("/xl/chartsheets/chartsheet" + sheetId + ".xml", UriKind.Relative);
     } else {
       uriWorksheet = new("/xl/worksheets/sheet" + sheetId + ".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 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;
   }

   /// <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];
       }
       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] => GetByName(name);

   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);
   }
 } // 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.Text.RegularExpressions;
	using System.Xml;

	namespace AppsheetEpplus;

	/// <summary>
	/// The collection of worksheets for the workbook
	/// </summary>
	public class ExcelWorksheets : XmlHelper, IEnumerable<ExcelWorksheet> {
	private readonly ExcelPackage _pck;
	private readonly ExcelWorkbook _workbook;
	private Dictionary<int, ExcelWorksheet> _worksheets = new();
	private readonly XmlNamespaceManager _namespaceManager;

	internal ExcelWorksheets(
	ExcelPackage pck,
	ExcelWorkbook workbook,
	XmlNamespaceManager nsm,
	XmlNode topNode)
	: base(nsm, topNode) {
	_pck = pck;
	_workbook = workbook;
	_namespaceManager = nsm;
	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 = _workbook.Part.GetRelationship(relId);
	Uri uriWorksheet = UriHelper.ResolvePartUri(
	ExcelWorkbook.WorkbookUri,
	sheetRelation.TargetUri);

	//add the worksheet
	if (sheetRelation.RelationshipType.EndsWith("chartsheet")) {
	_worksheets.Add(
	positionId,
	new ExcelChartsheet(
	_namespaceManager,
	pck,
	_workbook,
	uriWorksheet,
	name,
	sheetId,
	positionId,
	hidden));
	} else {
	_worksheets.Add(
	positionId,
	new(
	_namespaceManager,
	pck,
	_workbook,
	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;
	}
	}

	/// <summary>
	/// Returns the number of worksheets in the workbook
	/// </summary>
	public int Count => (_worksheets.Count);

	private const string _errDupWorksheet =
	"A worksheet with this name already exists in the workbook";
	internal const string _worksheetContentType =
	"application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml";

	/// <summary>
	/// Foreach support
	/// </summary>
	/// <returns>An enumerator</returns>
	public IEnumerator<ExcelWorksheet> GetEnumerator() {
	return (_worksheets.Values.GetEnumerator());
	}

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

	/// <summary>
	/// Adds a new blank worksheet.
	/// </summary>
	/// <param name="name">The name of the workbook</param>
	public ExcelWorksheet Add(string name) {
	name = ValidateFixSheetName(name);
	if (GetByName(name) != null) {
	throw (new InvalidOperationException(_errDupWorksheet + " : " + name));
	}
	GetSheetUri(ref name, out var sheetId, out var uriWorksheet, false);

	// Create the new worksheet
	var rel = _pck.CreateXmlDocument(
	uriWorksheet,
	_worksheetContentType,
	ExcelPackage._schemaRelationships + "/worksheet",
	CreateNewWorksheet(false));

	// Add worksheet to the workbook
	XmlElement worksheetNode = _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);

	int positionId = _worksheets.Count + 1;

	ExcelWorksheet worksheet = new(
	_namespaceManager,
	_pck,
	_workbook,
	uriWorksheet,
	name,
	sheetId,
	positionId,
	eWorkSheetHidden.Visible);

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

	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("/xl/chartsheets/chartsheet" + sheetId + ".xml", UriKind.Relative);
	} else {
	uriWorksheet = new("/xl/worksheets/sheet" + sheetId + ".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 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;
	}

	/// <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];
	}
	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] => GetByName(name);

	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);
	}
	} // end class Worksheets