EPPlus/ExcelWorksheets.cs

/*******************************************************************************
 * 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;
using OfficeOpenXml.Utils;

namespace OfficeOpenXml;

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