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.IO;
using System.Text.RegularExpressions;
using System.Xml;
using OfficeOpenXml.Packaging;
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;
  private readonly XmlNamespaceManager _namespaceManager;

  internal ExcelWorksheets(
      ExcelPackage pck,
      ExcelWorkbook workbook,
      XmlNamespaceManager nsm,
      XmlNode topNode)
      : base(nsm, topNode) {
    _pck = pck;
    _workbook = workbook;
    _namespaceManager = nsm;
    _worksheets = new();
    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,
                  relId,
                  uriWorksheet,
                  name,
                  sheetId,
                  positionId,
                  hidden));
        } else {
          _worksheets.Add(
              positionId,
              new(
                  _namespaceManager,
                  pck,
                  _workbook,
                  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;
    }
  }

  /// <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) {
    int sheetId;
    Uri uriWorksheet;
    lock (_worksheets) {
      name = ValidateFixSheetName(name);
      if (GetByName(name) != null) {
        throw (new InvalidOperationException(_errDupWorksheet + " : " + name));
      }
      GetSheetUri(ref name, out sheetId, out uriWorksheet, false);
      ZipPackagePart worksheetPart = _pck.Package.CreatePart(
          uriWorksheet,
          _worksheetContentType,
          _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);

      string rel = CreateWorkbookRel(name, sheetId, uriWorksheet, false);

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

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

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

  private string CreateWorkbookRel(string name, int sheetId, Uri uriWorksheet, bool isChart) {
    //Create the relationship between the workbook and the new worksheet
    var rel = _workbook.Part.CreateRelationship(
        UriHelper.GetRelativeUri(ExcelWorkbook.WorkbookUri, uriWorksheet),
        TargetMode.Internal,
        ExcelPackage._schemaRelationships + "/" + (isChart ? "chartsheet" : "worksheet"));

    //Create the new sheet node
    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);
    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("/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);
  }

  /// <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(
          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(
          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(
          string.Format(
              "Move worksheet error: Could not find worksheet to move '{0}'",
              sourceName));
    }
    var targetSheet = this[targetName];
    if (targetSheet == null) {
      throw new(
          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(
            string.Format(
                "Move worksheet error: Could not find worksheet at position '{0}'",
                sourcePositionId));
      }
      var targetSheet = this[targetPositionId];
      if (targetSheet == null) {
        throw new(
            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("Source SheetId and Target SheetId must be valid");
      }
      if (placeAfter) {
        TopNode.InsertAfter(sourceNode, targetNode);
      } else {
        TopNode.InsertBefore(sourceNode, targetNode);
      }
    }
  }
} // end class Worksheets