/*******************************************************************************
* 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		        2011-11-02
* Jan Källman          Total rewrite               2010-03-01
* Jan Källman		    License changed GPL-->LGPL  2011-12-27
*******************************************************************************/

using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Globalization;
using System.IO;
using System.Security;
using System.Text;
using System.Text.RegularExpressions;
using System.Xml;
using OfficeOpenXml.ConditionalFormatting;
using OfficeOpenXml.DataValidation;
using OfficeOpenXml.Drawing.Vml;
using OfficeOpenXml.FormulaParsing.LexicalAnalysis;
using OfficeOpenXml.Packaging;
using OfficeOpenXml.Style.XmlAccess;
using OfficeOpenXml.Table;
using OfficeOpenXml.Table.PivotTable;
using OfficeOpenXml.Utils;

namespace OfficeOpenXml;

/// <summary>
/// Worksheet hidden enumeration
/// </summary>
public enum eWorkSheetHidden {
  /// <summary>
  /// The worksheet is visible
  /// </summary>
  Visible,

  /// <summary>
  /// The worksheet is hidden but can be shown by the user via the user interface
  /// </summary>
  Hidden,

  /// <summary>
  /// The worksheet is hidden and cannot be shown by the user via the user interface
  /// </summary>
  VeryHidden,
}

[Flags]
internal enum CellFlags {
  //Merged = 0x1,
  RichText = 0x2,
  SharedFormula = 0x4,
  ArrayFormula = 0x8,
}

/// <summary>
/// Represents an Excel Chartsheet and provides access to its properties and methods
/// </summary>
public class ExcelChartsheet : ExcelWorksheet {
  public ExcelChartsheet(
      XmlNamespaceManager ns,
      ExcelPackage pck,
      ExcelWorkbook workbook,
      string relId,
      Uri uriWorksheet,
      string sheetName,
      int sheetId,
      int positionId,
      eWorkSheetHidden hidden)
      : base(ns, pck, workbook, relId, uriWorksheet, sheetName, sheetId, positionId, hidden) {}
}

/// <summary>
/// Represents an Excel worksheet and provides access to its properties and methods
/// </summary>
public class ExcelWorksheet : XmlHelper, IEqualityComparer<ExcelWorksheet> {
  internal class Formulas {
    public Formulas(ISourceCodeTokenizer tokenizer) {
      _tokenizer = tokenizer;
    }

    public static string RemoveDummyFunction(string formula) {
      const string dummyFunctionConcatenate = "\"&\"";
      const string dummyFunctionPrefix = "IFERROR(__xludf.DUMMYFUNCTION(\"";
      const string dummyFunctionSuffix = "\"),";

      if (string.IsNullOrEmpty(formula)) {
        return formula;
      }

      // Look for Prefix
      if (!formula.StartsWith(dummyFunctionPrefix)) {
        return formula;
      }

      // Look for Suffix
      int index = formula.LastIndexOf(dummyFunctionSuffix);
      if (index < 0) {
        return formula;
      }

      // Trim Suffix
      formula = formula.Substring(0, index);

      // Trim Prefix
      formula = formula.Replace(dummyFunctionPrefix, "");

      // Remove string concatentations from long formulas.
      // Google break the quoted string into 254 character segments which are concatenated.
      if (formula.Length >= 254) {
        formula = formula.Replace(dummyFunctionConcatenate, "");
      }

      // Replace doubled quotes with single quote
      formula = formula.Replace("\"\"", "\"");

      // Return formula
      return formula;
    }

    private readonly ISourceCodeTokenizer _tokenizer;

    internal int Index { get; set; }

    internal string Address { get; set; }

    internal bool IsArray { get; set; }

    public string Formula { get; set; }

    public int StartRow { get; set; }

    public int StartCol { get; set; }

    private IEnumerable<Token> Tokens { get; set; }

    internal string GetFormula(int row, int column, string worksheet) {
      if ((StartRow == row && StartCol == column) || IsArray) {
        return RemoveDummyFunction(Formula);
      }

      if (Tokens == null) {
        Tokens = _tokenizer.Tokenize(RemoveDummyFunction(Formula), worksheet);
      }

      string f = "";
      foreach (var token in Tokens) {
        if (token.TokenType == TokenType.ExcelAddress) {
          var a = new ExcelFormulaAddress(token.Value);
          f += a.GetOffset(row - StartRow, column - StartCol);
        } else {
          f += token.Value;
        }
      }
      return f;
    }
  }

  /// <summary>
  /// Collection containing merged cell addresses
  /// </summary>
  public class MergeCellsCollection : IEnumerable<string> {
    internal MergeCellsCollection() {}

    internal CellStore<int> _cells = new();
    private readonly List<string> _list = new();

    internal List<string> List => _list;

    public string this[int row, int column] {
      get {
        int ix = -1;
        if (_cells.Exists(row, column, ref ix)
            && ix >= 0
            && ix
                < List.Count) //Fixes issue 15075
        {
          return List[ix];
        }
        return null;
      }
    }

    public string this[int index] => _list[index];

    internal void Add(ExcelAddressBase address, bool doValidate) {
      int ix = 0;

      //Validate
      if (doValidate && Validate(address) == false) {
        throw (new ArgumentException("Can't merge and already merged range"));
      }
      lock (this) {
        ix = _list.Count;
        _list.Add(address.Address);
        SetIndex(address, ix);
      }
    }

    private bool Validate(ExcelAddressBase address) {
      int ix = 0;
      if (_cells.Exists(address._fromRow, address._fromCol, ref ix)) {
        if (ix >= 0 && ix < _list.Count && _list[ix] != null && address.Address == _list[ix]) {
          return true;
        }
        return false;
      }

      var cse = new CellsStoreEnumerator<int>(
          _cells,
          address._fromRow,
          address._fromCol,
          address._toRow,
          address._toCol);
      //cells
      while (cse.Next()) {
        return false;
      }
      //Entire column
      cse = new(_cells, 0, address._fromCol, 0, address._toCol);
      while (cse.Next()) {
        return false;
      }
      //Entire row
      cse = new(_cells, address._fromRow, 0, address._toRow, 0);
      while (cse.Next()) {
        return false;
      }
      return true;
    }

    internal void SetIndex(ExcelAddressBase address, int ix) {
      if (address._fromRow == 1
          && address._toRow
              == ExcelPackage.MaxRows) //Entire row
      {
        for (int col = address._fromCol; col <= address._toCol; col++) {
          _cells.SetValue(0, col, ix);
        }
      } else if (address._fromCol == 1
          && address._toCol
              == ExcelPackage.MaxColumns) //Entire row
      {
        for (int row = address._fromRow; row <= address._toRow; row++) {
          _cells.SetValue(row, 0, ix);
        }
      } else {
        for (int col = address._fromCol; col <= address._toCol; col++) {
          for (int row = address._fromRow; row <= address._toRow; row++) {
            _cells.SetValue(row, col, ix);
          }
        }
      }
    }

    public int Count => _list.Count;

    internal void Remove(string item) {
      _list.Remove(item);
    }

    public IEnumerator<string> GetEnumerator() {
      return _list.GetEnumerator();
    }

    IEnumerator IEnumerable.GetEnumerator() {
      return _list.GetEnumerator();
    }

    internal void Clear(ExcelAddressBase destination) {
      var cse = new CellsStoreEnumerator<int>(
          _cells,
          destination._fromRow,
          destination._fromCol,
          destination._toRow,
          destination._toCol);
      var used = new HashSet<int>();
      while (cse.Next()) {
        var v = cse.Value;
        if (!used.Contains(v) && _list[v] != null) {
          var adr = new ExcelAddressBase(_list[v]);
          if (!(destination.Collide(adr) == ExcelAddressBase.eAddressCollition.Inside
                      || destination.Collide(adr) == ExcelAddressBase.eAddressCollition.Equal)) {
            throw (new InvalidOperationException(
                    string.Format(
                        "Can't delete merged cells. A range is partly merged with the deleted range. {0}",
                        adr._address)));
          }
          used.Add(v);
        }
      }

      _cells.Clear(
          destination._fromRow,
          destination._fromCol,
          destination._toRow - destination._fromRow + 1,
          destination._toCol - destination._fromCol + 1);
      foreach (var i in used) {
        _list[i] = null;
      }
    }
  }

  internal CellStore<object> _values;
  internal CellStore<string> _types;
  internal CellStore<int> _styles;
  internal CellStore<object> _formulas;
  internal FlagCellStore _flags;
  internal CellStore<List<Token>> _formulaTokens;

  internal CellStore<Uri> _hyperLinks;
  internal CellStore<ExcelComment> _commentsStore;

  internal Dictionary<int, Formulas> _sharedFormulas = new();
  internal int _minCol = ExcelPackage.MaxColumns;
  internal int _maxCol = 0;

  internal readonly ExcelPackage _package;
  private readonly ExcelWorkbook _workbook;
  private readonly Uri _worksheetUri;
  private string _name;
  private readonly int _sheetID;
  private int _positionID;
  private readonly string _relationshipID;
  private XmlDocument _worksheetXml;
  private ExcelWorksheetView _sheetView;
  private ExcelHeaderFooter _headerFooter;

  internal ExcelWorksheet(
      XmlNamespaceManager ns,
      ExcelPackage excelPackage,
      ExcelWorkbook workbook,
      string relId,
      Uri uriWorksheet,
      string sheetName,
      int sheetId,
      int positionId,
      eWorkSheetHidden hide)
      : base(ns, null) {
    _workbook = workbook;
    SchemaNodeOrder = new[] {
      "sheetPr",
      "tabColor",
      "outlinePr",
      "pageSetUpPr",
      "dimension",
      "sheetViews",
      "sheetFormatPr",
      "cols",
      "sheetData",
      "sheetProtection",
      "protectedRanges",
      "scenarios",
      "autoFilter",
      "sortState",
      "dataConsolidate",
      "customSheetViews",
      "customSheetViews",
      "mergeCells",
      "phoneticPr",
      "conditionalFormatting",
      "dataValidations",
      "hyperlinks",
      "printOptions",
      "pageMargins",
      "pageSetup",
      "headerFooter",
      "linePrint",
      "rowBreaks",
      "colBreaks",
      "customProperties",
      "cellWatches",
      "ignoredErrors",
      "smartTags",
      "drawing",
      "legacyDrawing",
      "legacyDrawingHF",
      "picture",
      "oleObjects",
      "activeXControls",
      "webPublishItems",
      "tableParts",
      "extLst",
    };
    _package = excelPackage;
    _workbook = workbook;
    _relationshipID = relId;
    _worksheetUri = uriWorksheet;
    _name = sheetName;
    _sheetID = sheetId;
    _positionID = positionId;
    Hidden = hide;

    /**** Cellstore ****/
    _values = new();
    _types = new();
    _styles = new();
    _formulas = new();
    _flags = new();
    _commentsStore = new();
    _hyperLinks = new();

    _names = new(Workbook, this);

    CreateXml();
    TopNode = _worksheetXml.DocumentElement;
  }

  /// <summary>
  /// The Uri to the worksheet within the package
  /// </summary>
  internal Uri WorksheetUri => (_worksheetUri);

  /// <summary>
  /// The Zip.ZipPackagePart for the worksheet within the package
  /// </summary>
  internal ZipPackagePart Part => (_package.Package.GetPart(WorksheetUri));

  /// <summary>
  /// The ID for the worksheet's relationship with the workbook in the package
  /// </summary>
  internal string RelationshipID => (_relationshipID);

  /// <summary>
  /// The unique identifier for the worksheet.
  /// </summary>
  internal int SheetID => (_sheetID);

  /// <summary>
  /// The position of the worksheet.
  /// </summary>
  internal int PositionID {
    get => (_positionID);
    set => _positionID = value;
  }

  /// <summary>
  /// The index in the worksheets collection
  /// </summary>
  public int Index => (_positionID);

  /// <summary>
  /// Address for autofilter
  /// <seealso cref="ExcelRangeBase.AutoFilter" />
  /// </summary>
  public ExcelAddressBase AutoFilterAddress {
    get {
      CheckSheetType();
      string address = GetXmlNodeString("d:autoFilter/@ref");
      if (address == "") {
        return null;
      }
      return new(address);
    }
    internal set {
      CheckSheetType();
      SetXmlNodeString("d:autoFilter/@ref", value.Address);
    }
  }

  internal void CheckSheetType() {
    if (this is ExcelChartsheet) {
      throw (new NotSupportedException(
              "This property or method is not supported for a Chartsheet"));
    }
  }

  /// <summary>
  /// Returns a ExcelWorksheetView object that allows you to set the view state properties of the worksheet
  /// </summary>
  public ExcelWorksheetView View {
    get {
      if (_sheetView == null) {
        XmlNode node = TopNode.SelectSingleNode("d:sheetViews/d:sheetView", NameSpaceManager);
        if (node == null) {
          CreateNode("d:sheetViews/d:sheetView"); //this one shouls always exist. but check anyway
          node = TopNode.SelectSingleNode("d:sheetViews/d:sheetView", NameSpaceManager);
        }
        _sheetView = new(NameSpaceManager, node, this);
      }
      return (_sheetView);
    }
  }

  /// <summary>
  /// The worksheet's display name as it appears on the tab
  /// </summary>
  public string Name {
    get => (_name);
    set {
      if (value == _name) {
        return;
      }
      value = _workbook.Worksheets.ValidateFixSheetName(value);
      foreach (var ws in Workbook.Worksheets) {
        if (ws.PositionID != PositionID
            && ws.Name.Equals(value, StringComparison.InvariantCultureIgnoreCase)) {
          throw (new ArgumentException("Worksheet name must be unique"));
        }
      }
      _workbook.SetXmlNodeString(
          string.Format("d:sheets/d:sheet[@sheetId={0}]/@name", _sheetID),
          value);
      ChangeNames(value);

      _name = value;
    }
  }

  private void ChangeNames(string value) {
    //Renames name in this Worksheet;
    foreach (var n in Workbook.Names) {
      if (string.IsNullOrEmpty(n.NameFormula) && n.NameValue == null) {
        n.ChangeWorksheet(_name, value);
      }
    }
    foreach (var ws in Workbook.Worksheets) {
      if (!(ws is ExcelChartsheet)) {
        foreach (var n in ws.Names) {
          if (string.IsNullOrEmpty(n.NameFormula) && n.NameValue == null) {
            n.ChangeWorksheet(_name, value);
          }
        }
      }
    }
  }

  internal ExcelNamedRangeCollection _names;

  /// <summary>
  /// Provides access to named ranges
  /// </summary>
  public ExcelNamedRangeCollection Names {
    get {
      CheckSheetType();
      return _names;
    }
  }

  /// <summary>
  /// Indicates if the worksheet is hidden in the workbook
  /// </summary>
  public eWorkSheetHidden Hidden {
    get {
      string state = _workbook.GetXmlNodeString(
          string.Format("d:sheets/d:sheet[@sheetId={0}]/@state", _sheetID));
      if (state == "hidden") {
        return eWorkSheetHidden.Hidden;
      }
      if (state == "veryHidden") {
        return eWorkSheetHidden.VeryHidden;
      }
      return eWorkSheetHidden.Visible;
    }
    set {
      if (value == eWorkSheetHidden.Visible) {
        _workbook.DeleteNode(string.Format("d:sheets/d:sheet[@sheetId={0}]/@state", _sheetID));
      } else {
        string v;
        v = value.ToString();
        v = v.Substring(0, 1).ToLower(CultureInfo.InvariantCulture) + v.Substring(1);
        _workbook.SetXmlNodeString(
            string.Format("d:sheets/d:sheet[@sheetId={0}]/@state", _sheetID),
            v);
      }
    }
  }

  private double _defaultRowHeight = double.NaN;

  /// <summary>
  /// Get/set the default height of all rows in the worksheet
  /// </summary>
  public double DefaultRowHeight {
    get {
      CheckSheetType();
      if (double.IsNaN(_defaultRowHeight)) {
        _defaultRowHeight = GetXmlNodeDouble("d:sheetFormatPr/@defaultRowHeight");
        if (double.IsNaN(_defaultRowHeight)) {
          _defaultRowHeight = 15; // Excel default height
        }
      }
      return _defaultRowHeight;
    }
    set {
      CheckSheetType();
      _defaultRowHeight = value;
      SetXmlNodeString(
          "d:sheetFormatPr/@defaultRowHeight",
          value.ToString(CultureInfo.InvariantCulture));
      SetXmlNodeBool("d:sheetFormatPr/@customHeight", value != 15);

      if (double.IsNaN(GetXmlNodeDouble("d:sheetFormatPr/@defaultColWidth"))) {
        DefaultColWidth = 9.140625;
      }
    }
  }

  /// <summary>
  /// Get/set the default width of all rows in the worksheet
  /// </summary>
  public double DefaultColWidth {
    get {
      CheckSheetType();
      double ret = GetXmlNodeDouble("d:sheetFormatPr/@defaultColWidth");
      if (double.IsNaN(ret)) {
        ret = 9.140625; // Excel's default width
      }
      return ret;
    }
    set {
      CheckSheetType();
      SetXmlNodeString(
          "d:sheetFormatPr/@defaultColWidth",
          value.ToString(CultureInfo.InvariantCulture));

      if (double.IsNaN(GetXmlNodeDouble("d:sheetFormatPr/@defaultRowHeight"))) {
        DefaultRowHeight = 15;
      }
    }
  }

  /** <outlinePr applyStyles="1" summaryBelow="0" summaryRight="0" /> **/
  private const string _outLineSummaryBelowPath = "d:sheetPr/d:outlinePr/@summaryBelow";

  /// <summary>
  /// Summary rows below details
  /// </summary>
  public bool OutLineSummaryBelow {
    get {
      CheckSheetType();
      return GetXmlNodeBool(_outLineSummaryBelowPath);
    }
    set {
      CheckSheetType();
      SetXmlNodeString(_outLineSummaryBelowPath, value ? "1" : "0");
    }
  }

  private const string _outLineSummaryRightPath = "d:sheetPr/d:outlinePr/@summaryRight";

  /// <summary>
  /// Summary rows to right of details
  /// </summary>
  public bool OutLineSummaryRight {
    get {
      CheckSheetType();
      return GetXmlNodeBool(_outLineSummaryRightPath);
    }
    set {
      CheckSheetType();
      SetXmlNodeString(_outLineSummaryRightPath, value ? "1" : "0");
    }
  }

  private const string _outLineApplyStylePath = "d:sheetPr/d:outlinePr/@applyStyles";

  /// <summary>
  /// Automatic styles
  /// </summary>
  public bool OutLineApplyStyle {
    get {
      CheckSheetType();
      return GetXmlNodeBool(_outLineApplyStylePath);
    }
    set {
      CheckSheetType();
      SetXmlNodeString(_outLineApplyStylePath, value ? "1" : "0");
    }
  }

  private const string _codeModuleNamePath = "d:sheetPr/@codeName";

  internal string CodeModuleName {
    get => GetXmlNodeString(_codeModuleNamePath);
    set => SetXmlNodeString(_codeModuleNamePath, value);
  }

  internal void CodeNameChange(string value) {
    CodeModuleName = value;
  }

  /// <summary>
  /// The XML document holding the worksheet data.
  /// All column, row, cell, pagebreak, merged cell and hyperlink-data are loaded into memory and removed from the document when loading the document.
  /// </summary>
  public XmlDocument WorksheetXml => (_worksheetXml);

  internal ExcelVmlDrawingCommentCollection _vmlDrawings;

  /// <summary>
  /// Vml drawings. underlaying object for comments
  /// </summary>
  internal ExcelVmlDrawingCommentCollection VmlDrawingsComments {
    get {
      if (_vmlDrawings == null) {
        CreateVmlCollection();
      }
      return _vmlDrawings;
    }
  }

  internal ExcelCommentCollection _comments;

  /// <summary>
  /// Collection of comments
  /// </summary>
  public ExcelCommentCollection Comments {
    get {
      CheckSheetType();
      if (_comments == null) {
        CreateVmlCollection();
        _comments = new(_package, this, NameSpaceManager);
      }
      return _comments;
    }
  }

  private void CreateVmlCollection() {
    var vmlNode = _worksheetXml.DocumentElement.SelectSingleNode(
        "d:legacyDrawing/@r:id",
        NameSpaceManager);
    if (vmlNode == null) {
      _vmlDrawings = new(_package, this, null);
    } else {
      if (Part.RelationshipExists(vmlNode.Value)) {
        var rel = Part.GetRelationship(vmlNode.Value);
        var vmlUri = UriHelper.ResolvePartUri(rel.SourceUri, rel.TargetUri);

        _vmlDrawings = new(_package, this, vmlUri);
        _vmlDrawings.RelId = rel.Id;
      }
    }
  }

  private void CreateXml() {
    _worksheetXml = new();
    _worksheetXml.PreserveWhitespace = ExcelPackage._preserveWhitespace;
    ZipPackagePart packPart = _package.Package.GetPart(WorksheetUri);
    string xml = "";

    // First Columns, rows, cells, mergecells, hyperlinks and pagebreakes are loaded from a xmlstream to optimize speed...
    Stream stream = packPart.GetStream();

    XmlTextReader xr = new XmlTextReader(stream);
    xr.ProhibitDtd = true;
    xr.WhitespaceHandling = WhitespaceHandling.None;
    LoadColumns(xr); //columnXml
    long start = stream.Position;
    LoadCells(xr);
    var nextElementLength = GetAttributeLength(xr);
    long end = stream.Position - nextElementLength;
    LoadMergeCells(xr);
    LoadHyperLinks(xr);
    LoadRowPageBreakes(xr);
    LoadColPageBreakes(xr);
    //...then the rest of the Xml is extracted and loaded into the WorksheetXml document.
    stream.Seek(0, SeekOrigin.Begin);
    xml = GetWorkSheetXml(stream, start, end, out var encoding);

    //first char is invalid sometimes??
    if (xml[0] != '<') {
      LoadXmlSafe(_worksheetXml, xml.Substring(1, xml.Length - 1), encoding);
    } else {
      LoadXmlSafe(_worksheetXml, xml, encoding);
    }

    ClearNodes();
  }

  /// <summary>
  /// Get the lenth of the attributes
  /// Conditional formatting attributes can be extremly long som get length of the attributes to finetune position.
  /// </summary>
  /// <param name="xr"></param>
  /// <returns></returns>
  private int GetAttributeLength(XmlTextReader xr) {
    if (xr.NodeType != XmlNodeType.Element) {
      return 0;
    }
    var length = 0;

    for (int i = 0; i < xr.AttributeCount; i++) {
      var a = xr.GetAttribute(i);
      length += string.IsNullOrEmpty(a) ? 0 : a.Length;
    }
    return length;
  }

  private void LoadRowPageBreakes(XmlTextReader xr) {
    if (!ReadUntil(xr, "rowBreaks", "colBreaks")) {
      return;
    }
    while (xr.Read()) {
      if (xr.LocalName == "brk") {
        if (xr.NodeType == XmlNodeType.Element) {
          if (int.TryParse(xr.GetAttribute("id"), out var id)) {
            Row(id).PageBreak = true;
          }
        }
      } else {
        break;
      }
    }
  }

  private void LoadColPageBreakes(XmlTextReader xr) {
    if (!ReadUntil(xr, "colBreaks")) {
      return;
    }
    while (xr.Read()) {
      if (xr.LocalName == "brk") {
        if (xr.NodeType == XmlNodeType.Element) {
          if (int.TryParse(xr.GetAttribute("id"), out var id)) {
            Column(id).PageBreak = true;
          }
        }
      } else {
        break;
      }
    }
  }

  private void ClearNodes() {
    if (_worksheetXml.SelectSingleNode("//d:cols", NameSpaceManager) != null) {
      _worksheetXml.SelectSingleNode("//d:cols", NameSpaceManager).RemoveAll();
    }
    if (_worksheetXml.SelectSingleNode("//d:mergeCells", NameSpaceManager) != null) {
      _worksheetXml.SelectSingleNode("//d:mergeCells", NameSpaceManager).RemoveAll();
    }
    if (_worksheetXml.SelectSingleNode("//d:hyperlinks", NameSpaceManager) != null) {
      _worksheetXml.SelectSingleNode("//d:hyperlinks", NameSpaceManager).RemoveAll();
    }
    if (_worksheetXml.SelectSingleNode("//d:rowBreaks", NameSpaceManager) != null) {
      _worksheetXml.SelectSingleNode("//d:rowBreaks", NameSpaceManager).RemoveAll();
    }
    if (_worksheetXml.SelectSingleNode("//d:colBreaks", NameSpaceManager) != null) {
      _worksheetXml.SelectSingleNode("//d:colBreaks", NameSpaceManager).RemoveAll();
    }
  }

  private const int _blocksize = 8192;

  private string GetWorkSheetXml(Stream stream, long start, long end, out Encoding encoding) {
    StreamReader sr = new StreamReader(stream);
    int length = 0;
    char[] block;
    int pos;
    StringBuilder sb = new StringBuilder();
    Match startmMatch,
        endMatch;
    do {
      int size = stream.Length < _blocksize ? (int)stream.Length : _blocksize;
      block = new char[size];
      pos = sr.ReadBlock(block, 0, size);
      sb.Append(block, 0, pos);
      length += size;
    } while (length < start + 20 && length < end);
    startmMatch = Regex.Match(sb.ToString(), string.Format("(<[^>]*{0}[^>]*>)", "sheetData"));
    if (!startmMatch.Success) //Not found
    {
      encoding = sr.CurrentEncoding;
      return sb.ToString();
    }
    string s = sb.ToString();
    string xml = s.Substring(0, startmMatch.Index);
    if (startmMatch.Value.EndsWith("/>")) {
      xml += s.Substring(startmMatch.Index, s.Length - startmMatch.Index);
    } else {
      if (sr.Peek() != -1) {
        /**** Fixes issue 14788. Fix by Philip Garrett ****/
        long endSeekStart = end;

        while (endSeekStart >= 0) {
          endSeekStart = Math.Max(endSeekStart - _blocksize, 0);
          int size = (int)(end - endSeekStart);
          stream.Seek(endSeekStart, SeekOrigin.Begin);
          block = new char[size];
          sr = new(stream);
          pos = sr.ReadBlock(block, 0, size);
          sb = new();
          sb.Append(block, 0, pos);
          s = sb.ToString();
          endMatch = Regex.Match(s, string.Format("(</[^>]*{0}[^>]*>)", "sheetData"));
          if (endMatch.Success) {
            break;
          }
        }
      }
      endMatch = Regex.Match(s, string.Format("(</[^>]*{0}[^>]*>)", "sheetData"));
      xml +=
          "<sheetData/>"
              + s.Substring(
                  endMatch.Index + endMatch.Length,
                  s.Length - (endMatch.Index + endMatch.Length));
    }
    if (sr.Peek() > -1) {
      xml += sr.ReadToEnd();
    }

    encoding = sr.CurrentEncoding;
    return xml;
  }

  private void GetBlockPos(string xml, string tag, ref int start, ref int end) {
    Match startmMatch,
        endMatch;
    startmMatch = Regex.Match(xml.Substring(start), string.Format("(<[^>]*{0}[^>]*>)", tag)); //"<[a-zA-Z:]*" + tag + "[?]*>");

    if (!startmMatch.Success) //Not found
    {
      start = -1;
      end = -1;
      return;
    }
    var startPos = startmMatch.Index + start;
    if (startmMatch.Value.Substring(startmMatch.Value.Length - 2, 1) == "/") {
      end = startPos + startmMatch.Length;
    } else {
      endMatch = Regex.Match(xml.Substring(start), string.Format("(</[^>]*{0}[^>]*>)", tag));
      if (endMatch.Success) {
        end = endMatch.Index + endMatch.Length + start;
      }
    }
    start = startPos;
  }

  private bool ReadUntil(XmlTextReader xr, params string[] tagName) {
    if (xr.EOF) {
      return false;
    }
    while (!Array.Exists(tagName, tag => xr.LocalName.EndsWith(tag))) {
      xr.Read();
      if (xr.EOF) {
        return false;
      }
    }
    return (xr.LocalName.EndsWith(tagName[0]));
  }

  private void LoadColumns(
      XmlTextReader xr) //(string xml)
  {
    var colList = new List<IRangeId>();
    if (ReadUntil(xr, "cols", "sheetData")) {
      //if (xml != "")
      //{
      //var xr=new XmlTextReader(new StringReader(xml));
      while (xr.Read()) {
        if (xr.NodeType == XmlNodeType.Whitespace) {
          continue;
        }
        if (xr.LocalName != "col") {
          break;
        }
        if (xr.NodeType == XmlNodeType.Element) {
          int min = int.Parse(xr.GetAttribute("min"));

          ExcelColumn col = new ExcelColumn(this, min);

          col.ColumnMax = int.Parse(xr.GetAttribute("max"));
          col.Width =
              xr.GetAttribute("width") == null
                  ? 0
                  : double.Parse(xr.GetAttribute("width"), CultureInfo.InvariantCulture);
          col.BestFit =
              xr.GetAttribute("bestFit") != null && xr.GetAttribute("bestFit") == "1"
                  ? true
                  : false;
          col.Collapsed =
              xr.GetAttribute("collapsed") != null && xr.GetAttribute("collapsed") == "1"
                  ? true
                  : false;
          col.Phonetic =
              xr.GetAttribute("phonetic") != null && xr.GetAttribute("phonetic") == "1"
                  ? true
                  : false;
          col.OutlineLevel = (short)(xr.GetAttribute("outlineLevel") == null
              ? 0
              : int.Parse(xr.GetAttribute("outlineLevel"), CultureInfo.InvariantCulture));
          col.Hidden =
              xr.GetAttribute("hidden") != null && xr.GetAttribute("hidden") == "1" ? true : false;
          _values.SetValue(0, min, col);

          if (!(xr.GetAttribute("style") == null
                      || !int.TryParse(xr.GetAttribute("style"), out var style))) {
            _styles.SetValue(0, min, style);
          }
        }
      }
    }
  }

  /// <summary>
  /// Load Hyperlinks
  /// </summary>
  /// <param name="xr">The reader</param>
  private void LoadHyperLinks(XmlTextReader xr) {
    if (!ReadUntil(xr, "hyperlinks", "rowBreaks", "colBreaks")) {
      return;
    }
    while (xr.Read()) {
      if (xr.LocalName == "hyperlink") {
        ExcelCellBase.GetRowColFromAddress(
            xr.GetAttribute("ref"),
            out var fromRow,
            out var fromCol,
            out int toRow,
            out var toCol);
        ExcelHyperLink hl = null;
        if (xr.GetAttribute("id", ExcelPackage._schemaRelationships) != null) {
          var rId = xr.GetAttribute("id", ExcelPackage._schemaRelationships);
          var uri = Part.GetRelationship(rId).TargetUri;

          // Get Location, if any.  EPPlus Bug 15517
          var location = xr.GetAttribute("location");
          location = (string.IsNullOrEmpty(location)) ? "" : "#" + location;

          if (uri.IsAbsoluteUri) {
            try {
              hl = new(uri.AbsoluteUri + location);
            } catch {
              hl = new(uri.OriginalString + location, UriKind.Absolute);
            }
          } else {
            hl = new(uri.OriginalString + location, UriKind.Relative);
          }

          hl.RId = rId;
          Part.DeleteRelationship(rId); //Delete the relationship, it is recreated when we save the package.
        } else if (xr.GetAttribute("location") != null) {
          hl = new(xr.GetAttribute("location"), xr.GetAttribute("display"));
          hl.RowSpann = toRow - fromRow;
          hl.ColSpann = toCol - fromCol;
        }

        string tt = xr.GetAttribute("tooltip");
        if (!string.IsNullOrEmpty(tt)) {
          hl.ToolTip = tt;
        }
        _hyperLinks.SetValue(fromRow, fromCol, hl);
      } else {
        break;
      }
    }
  }

  /// <summary>
  /// Load cells
  /// </summary>
  /// <param name="xr">The reader</param>
  private void LoadCells(XmlTextReader xr) {
    //var cellList=new List<IRangeID>();
    //var rowList = new List<IRangeID>();
    //var formulaList = new List<IRangeID>();
    ReadUntil(xr, "sheetData", "mergeCells", "hyperlinks", "rowBreaks", "colBreaks");
    ExcelAddressBase address = null;
    string type = "";
    int style = 0;
    int row = 0;
    int col = 0;
    xr.Read();

    while (!xr.EOF) {
      while (xr.NodeType == XmlNodeType.EndElement) {
        xr.Read();
      }
      if (xr.LocalName == "row") {
        var r = xr.GetAttribute("r");
        if (r == null) {
          row++;
        } else {
          row = Convert.ToInt32(r);
        }

        if (DoAddRow(xr)) {
          _values.SetValue(row, 0, AddRow(xr, row));
          if (xr.GetAttribute("s") != null) {
            _styles.SetValue(row, 0, int.Parse(xr.GetAttribute("s"), CultureInfo.InvariantCulture));
          }
        }
        xr.Read();
      } else if (xr.LocalName == "c") {
        //if (cell != null) cellList.Add(cell);
        //cell = new ExcelCell(this, xr.GetAttribute("r"));
        var r = xr.GetAttribute("r");
        if (r == null) {
          //Handle cells with no reference
          col++;
          address = new(row, col, row, col);
        } else {
          address = new(r);
          col = address._fromCol;
        }

        //Datetype
        if (xr.GetAttribute("t") != null) {
          type = xr.GetAttribute("t");
          _types.SetValue(address._fromRow, address._fromCol, type);
        } else {
          type = "";
        }
        //Style
        if (xr.GetAttribute("s") != null) {
          style = int.Parse(xr.GetAttribute("s"));
          _styles.SetValue(address._fromRow, address._fromCol, style);
          _values.SetValue(address._fromRow, address._fromCol, null); //TODO:Better Performance ??
        } else {
          style = 0;
        }
        xr.Read();
      } else if (xr.LocalName == "v") {
        SetValueFromXml(xr, type, style, address._fromRow, address._fromCol);

        xr.Read();
      } else if (xr.LocalName == "f") {
        string t = xr.GetAttribute("t");
        if (t == null) {
          _formulas.SetValue(address._fromRow, address._fromCol, xr.ReadElementContentAsString());
          _values.SetValue(address._fromRow, address._fromCol, null);
          //formulaList.Add(cell);
        } else if (t == "shared") {
          string si = xr.GetAttribute("si");
          if (si != null) {
            var sfIndex = int.Parse(si);
            _formulas.SetValue(address._fromRow, address._fromCol, sfIndex);
            _values.SetValue(address._fromRow, address._fromCol, null);
            string fAddress = xr.GetAttribute("ref");
            string formula = ConvertUtil.ExcelDecodeString(xr.ReadElementContentAsString());
            if (formula != "") {
              _sharedFormulas.Add(
                  sfIndex,
                  new(SourceCodeTokenizer.Default) {
                    Index = sfIndex,
                    Formula = formula,
                    Address = fAddress,
                    StartRow = address._fromRow,
                    StartCol = address._fromCol,
                  });
            }
          } else {
            xr.Read(); //Something is wrong in the sheet, read next
          }
        } else if (t
            == "array") //TODO: Array functions are not support yet. Read the formula for the start cell only.
        {
          string aAddress = xr.GetAttribute("ref");
          ExcelRange addressRange = new ExcelRange(this, aAddress);
          string formula = xr.ReadElementContentAsString();
          bool isIndexMatchFormula =
              Regex.IsMatch(formula, @"INDEX\(", RegexOptions.IgnoreCase)
                  && Regex.IsMatch(formula, @"MATCH\(", RegexOptions.IgnoreCase)
                  && !aAddress.Contains(":");
          if (isIndexMatchFormula) {
            addressRange.IsArrayFormula = false;
            for (int colIndex = addressRange.Start.Column;
                colIndex <= addressRange.End.Column;
                colIndex++) {
              for (int rowIndex = addressRange.Start.Row;
                  rowIndex <= addressRange.End.Row;
                  rowIndex++) {
                var afIndex = GetMaxShareFunctionIndex(true);
                _formulas.SetValue(rowIndex, colIndex, afIndex);
                _values.SetValue(rowIndex, colIndex, null);
                _sharedFormulas.Add(
                    afIndex,
                    new(SourceCodeTokenizer.Default) {
                      Index = afIndex,
                      Formula = formula,
                      Address = aAddress,
                      StartRow = address._fromRow,
                      StartCol = address._fromCol,
                      IsArray = false,
                    });
              }
            }
          } else {
            addressRange.IsArrayFormula = true;
            var afIndex = GetMaxShareFunctionIndex(true);
            for (int colIndex = addressRange.Start.Column;
                colIndex <= addressRange.End.Column;
                colIndex++) {
              for (int rowIndex = addressRange.Start.Row;
                  rowIndex <= addressRange.End.Row;
                  rowIndex++) {
                _formulas.SetValue(rowIndex, colIndex, afIndex);
                _values.SetValue(rowIndex, colIndex, null);
              }
            }
            _sharedFormulas.Add(
                afIndex,
                new(SourceCodeTokenizer.Default) {
                  Index = afIndex,
                  Formula = formula,
                  Address = aAddress,
                  StartRow = address._fromRow,
                  StartCol = address._fromCol,
                  IsArray = true,
                });
          }
        } else // ??? some other type
        {
          xr.Read(); //Something is wrong in the sheet, read next
        }
      } else if (xr.LocalName
          == "is") //Inline string
      {
        xr.Read();
        if (xr.LocalName == "t") {
          _values.SetValue(
              address._fromRow,
              address._fromCol,
              ConvertUtil.ExcelDecodeString(xr.ReadElementContentAsString()));
          //cell._value = xr.ReadInnerXml();
        } else {
          _values.SetValue(address._fromRow, address._fromCol, xr.ReadOuterXml());
          _types.SetValue(address._fromRow, address._fromCol, "rt");
          _flags.SetFlagValue(address._fromRow, address._fromCol, true, CellFlags.RichText);
          //cell.IsRichText = true;
        }
      } else {
        break;
      }
    }
    //if (cell != null) cellList.Add(cell);

    //_cells = new RangeCollection(cellList);
    //_rows = new RangeCollection(rowList);
    //_formulaCells = new RangeCollection(formulaList);
  }

  private bool DoAddRow(XmlTextReader xr) {
    var c = xr.GetAttribute("r") == null ? 0 : 1;
    if (xr.GetAttribute("spans") != null) {
      c++;
    }
    return xr.AttributeCount > c;
  }

  /// <summary>
  /// Load merged cells
  /// </summary>
  /// <param name="xr"></param>
  private void LoadMergeCells(XmlTextReader xr) {
    if (ReadUntil(xr, "mergeCells", "hyperlinks", "rowBreaks", "colBreaks") && !xr.EOF) {
      while (xr.Read()) {
        if (xr.LocalName != "mergeCell") {
          break;
        }
        if (xr.NodeType == XmlNodeType.Element) {
          string address = xr.GetAttribute("ref");
          //int fromRow, fromCol, toRow, toCol;
          //ExcelCellBase.GetRowColFromAddress(address, out fromRow, out fromCol, out toRow, out toCol);
          //for (int row = fromRow; row <= toRow; row++)
          //{
          //    for (int col = fromCol; col <= toCol; col++)
          //    {
          //        _flags.SetFlagValue(row, col, true,CellFlags.Merged);
          //    }
          //}
          //_mergedCells.List.Add(address);
          _mergedCells.Add(new ExcelAddress(address), false);
        }
      }
    }
  }

  /// <summary>
  /// Update merged cells
  /// </summary>
  /// <param name="sw">The writer</param>
  private void UpdateMergedCells(StreamWriter sw) {
    sw.Write("<mergeCells>");
    foreach (string address in _mergedCells) {
      sw.Write("<mergeCell ref=\"{0}\" />", address);
    }
    sw.Write("</mergeCells>");
  }

  /// <summary>
  /// Reads a row from the XML reader
  /// </summary>
  /// <param name="xr">The reader</param>
  /// <param name="row">The row number</param>
  /// <returns></returns>
  private RowInternal AddRow(XmlTextReader xr, int row) {
    return new() {
      Collapsed =
          (xr.GetAttribute("collapsed") != null && xr.GetAttribute("collapsed") == "1"
              ? true
              : false),
      OutlineLevel =
          (xr.GetAttribute("outlineLevel") == null
              ? (short)0
              : short.Parse(xr.GetAttribute("outlineLevel"), CultureInfo.InvariantCulture)),
      Height =
          (xr.GetAttribute("ht") == null
              ? -1
              : double.Parse(xr.GetAttribute("ht"), CultureInfo.InvariantCulture)),
      Hidden =
          (xr.GetAttribute("hidden") != null && xr.GetAttribute("hidden") == "1" ? true : false),
      Phonetic = xr.GetAttribute("ph") != null && xr.GetAttribute("ph") == "1" ? true : false,
      CustomHeight =
          xr.GetAttribute("customHeight") == null ? false : xr.GetAttribute("customHeight") == "1",
    };
  }

  private static readonly DateTime _excelEpoch = new(1899, 12, 30);

  public static DateTime IncorrectDurationFromOaDate(double value) {
    // This behavior is wrong. Real OADate values have a discontinuity on 30 December 1899.
    // For real OADate values, the negative sign applies only to the integer portion of
    // the float, *not* to the decimal portion. For example, -0.5 and 0.5 both refer to the
    // same date, and -1.5 is actually 1899-12-29 12:00 (1 day before 1899-12-30 00:00
    // plus 0.5 days), *not* 1899-12-28 12:00 (1.5 days before 1899-12-30 00:00).
    //
    // Unfortunately, AppSheet's duration-handling code gets this very wrong, and treats the
    // duration as the offset from 1899-12-30 00:00. This is correct for positive durations,
    // but it's wrong for negative durations. This code tries to fix the bug that exists in
    // AppSheet's duration-handling code here, and it succeeds in some cases and fails in
    // others.
    //
    // This code also breaks date/time handling for dates before 1899-12-30 00:00 in some
    // cases. Specifically, dates end up being offset by one day.
    //
    // Regardless, changing this behavior is risky, so this code simply replicates the
    // existing behavior for
    if (value >= 0) {
      return DateTime.FromOADate(value);
    }
    // This looks like a very complicated way to call TimeSpan.FromDays(value), but
    // TimeSpan.FromDays actually only guarantees millisecond precision, and critically
    // rounding is different on .NET Core, resulting in values like (e.g.) 3:15:00 being
    // incorrectly rounded.
    var offset = DateTime.FromOADate(-value) - _excelEpoch;
    return _excelEpoch - offset;
  }

  private void SetValueFromXml(XmlTextReader xr, string type, int styleId, int row, int col) {
    //XmlNode vnode = colNode.SelectSingleNode("d:v", NameSpaceManager);
    //if (vnode == null) return null;
    if (type == "s") {
      int ix = xr.ReadElementContentAsInt();

      // Temporary debugging code to locate intermittent 'Index was out of range' exception.
      if (ix < 0) {
        throw new(
            string.Format(
                "ReadElementContentAsInt returned value '{0}' which is less than zero.",
                ix));
      }
      if (ix >= _workbook._sharedStringsList.Count) {
        throw new(
            string.Format(
                "ReadElementContentAsInt returned index value '{0}' which is greater than _sharedStringsList count of {1}.",
                ix,
                _workbook._sharedStringsList.Count));
      }

      _values.SetValue(row, col, _workbook._sharedStringsList[ix].Text);
      if (_workbook._sharedStringsList[ix].isRichText) {
        _flags.SetFlagValue(row, col, true, CellFlags.RichText);
      }
    } else if (type == "str") {
      _values.SetValue(row, col, ConvertUtil.ExcelDecodeString(xr.ReadElementContentAsString()));
    } else if (type == "b") {
      _values.SetValue(row, col, (xr.ReadElementContentAsString() != "0"));
    } else if (type == "e") {
      _values.SetValue(row, col, GetErrorType(xr.ReadElementContentAsString()));
    } else {
      string v = xr.ReadElementContentAsString();
      var nf = Workbook.Styles.CellXfs[styleId].NumberFormatId;
      if ((nf >= 20 && nf <= 21)
          || (nf >= 45
                  && nf
                      <= 47)) // Duration
      {
        if (double.TryParse(v, NumberStyles.Any, CultureInfo.InvariantCulture, out var res)) {
          if (Workbook.Date1904) {
            res += ExcelWorkbook._date1904Offset;
          }
          if (res >= -657435.0 && res < 2958465.9999999) {
            // Get the Duration value expressed as a DateTime.
            _values.SetValue(row, col, IncorrectDurationFromOaDate(res));
          } else {
            // Cope with Google Sheets export of cells having a formula.
            // Rather than exporting the native value, they export the formatted value.
            _values.SetValue(row, col, v);
          }
        } else {
          // Cope with Google Sheets export of cells having a formula.
          // Rather than exporting the native value, they export the formatted value.
          _values.SetValue(row, col, v);
        }
      } else if ((nf >= 14 && nf <= 19)
          || (nf
                  == 22)) // DateTime
      {
        if (double.TryParse(v, NumberStyles.Any, CultureInfo.InvariantCulture, out var res)) {
          if (Workbook.Date1904) {
            res += ExcelWorkbook._date1904Offset;
          }
          if (res >= -657435.0 && res < 2958465.9999999) {
            _values.SetValue(row, col, DateTime.FromOADate(res));
          } else {
            // Cope with Google Sheets export of cells having a formula.
            // Rather than exporting the native value, they export the formatted value.
            _values.SetValue(row, col, v);
          }
        } else {
          // Cope with Google Sheets export of cells having a formula.
          // Rather than exporting the native value, they export the formatted value.
          _values.SetValue(row, col, v);
        }
      } else {
        if (double.TryParse(v, NumberStyles.Any, CultureInfo.InvariantCulture, out var d)) {
          _values.SetValue(row, col, d);
        } else {
          // Cope with Google Sheets export of cells having a formula.
          // Rather than exporting the native value, they export the formatted value.
          _values.SetValue(row, col, v);

          //_values.SetValue(row, col, double.NaN);
        }
      }
    }
  }

  private object GetErrorType(string v) {
    return ExcelErrorValue.Parse(v.ToUpper(CultureInfo.InvariantCulture));
    //switch(v.ToUpper())
    //{
    //    case "#DIV/0!":
    //        return new ExcelErrorValue.cre(eErrorType.Div0);
    //    case "#REF!":
    //        return new ExcelErrorValue(eErrorType.Ref);
    //    case "#N/A":
    //        return new ExcelErrorValue(eErrorType.NA);
    //    case "#NAME?":
    //        return new ExcelErrorValue(eErrorType.Name);
    //    case "#NULL!":
    //        return new ExcelErrorValue(eErrorType.Null);
    //    case "#NUM!":
    //        return new ExcelErrorValue(eErrorType.Num);
    //    default:
    //        return new ExcelErrorValue(eErrorType.Value);
    //}
  }

  //private string GetSharedString(int stringID)
  //{
  //    string retValue = null;
  //    XmlNodeList stringNodes = xlPackage.Workbook.SharedStringsXml.SelectNodes(string.Format("//d:si", stringID), NameSpaceManager);
  //    XmlNode stringNode = stringNodes[stringID];
  //    if (stringNode != null)
  //        retValue = stringNode.InnerText;
  //    return (retValue);
  //}

  /// <summary>
  /// A reference to the header and footer class which allows you to
  /// set the header and footer for all odd, even and first pages of the worksheet
  /// </summary>
  /// <remarks>
  /// To format the text you can use the following format
  /// <list type="table">
  /// <listheader><term>Prefix</term><description>Description</description></listheader>
  /// <item><term>&amp;U</term><description>Underlined</description></item>
  /// <item><term>&amp;E</term><description>Double Underline</description></item>
  /// <item><term>&amp;K:xxxxxx</term><description>Color. ex &amp;K:FF0000 for red</description></item>
  /// <item><term>&amp;"Font,Regular Bold Italic"</term><description>Changes the font. Regular or Bold or Italic or Bold Italic can be used. ex &amp;"Arial,Bold Italic"</description></item>
  /// <item><term>&amp;nn</term><description>Change font size. nn is an integer. ex &amp;24</description></item>
  /// <item><term>&amp;G</term><description>Placeholder for images. Images can not be added by the library, but its possible to use in a template.</description></item>
  /// </list>
  /// </remarks>
  public ExcelHeaderFooter HeaderFooter {
    get {
      if (_headerFooter == null) {
        XmlNode headerFooterNode = TopNode.SelectSingleNode("d:headerFooter", NameSpaceManager);
        if (headerFooterNode == null) {
          headerFooterNode = CreateNode("d:headerFooter");
        }
        _headerFooter = new(NameSpaceManager, headerFooterNode, this);
      }
      return (_headerFooter);
    }
  }

  ///// <summary>
  ///// Provides access to an individual cell within the worksheet.
  ///// </summary>
  ///// <param name="row">The row number in the worksheet</param>
  ///// <param name="col">The column number in the worksheet</param>
  ///// <returns></returns>
  //internal ExcelCell Cell(int row, int col)
  //{
  //    return new ExcelCell(_values, row, col);
  //}
  /// <summary>
  /// Provides access to a range of cells
  /// </summary>
  public ExcelRange Cells {
    get {
      CheckSheetType();
      return new(this, 1, 1, ExcelPackage.MaxRows, ExcelPackage.MaxColumns);
    }
  }

  /// <summary>
  /// Provides access to the selected range of cells
  /// </summary>
  public ExcelRange SelectedRange {
    get {
      CheckSheetType();
      return new(this, View.SelectedRange);
    }
  }

  private readonly MergeCellsCollection _mergedCells = new();

  /// <summary>
  /// Addresses to merged ranges
  /// </summary>
  public MergeCellsCollection MergedCells {
    get {
      CheckSheetType();
      return _mergedCells;
    }
  }

  /// <summary>
  /// Provides access to an individual row within the worksheet so you can set its properties.
  /// </summary>
  /// <param name="row">The row number in the worksheet</param>
  /// <returns></returns>
  public ExcelRow Row(int row) {
    //ExcelRow r;
    //ulong id = ExcelRow.GetRowID(_sheetID, row);
    //TODO: Fixa.
    //var v = _values.GetValue(row, 0);
    //if (v!=null)
    //{
    //    var ri=(RowInternal)v;
    //    r = new ExcelRow(this, row)
    //}
    //else
    //{
    //r = new ExcelRow(this, row);
    //_values.SetValue(row, 0, r);
    //_rows.Add(r);
    //}
    CheckSheetType();
    if (row < 1 || row > ExcelPackage.MaxRows) {
      throw (new ArgumentException("Row number out of bounds"));
    }
    return new(this, row);
    //return r;
  }

  /// <summary>
  /// Provides access to an individual column within the worksheet so you can set its properties.
  /// </summary>
  /// <param name="col">The column number in the worksheet</param>
  /// <returns></returns>
  public ExcelColumn Column(int col) {
    CheckSheetType();
    if (col < 1 || col > ExcelPackage.MaxColumns) {
      throw (new ArgumentException("Column number out of bounds"));
    }
    var column = _values.GetValue(0, col) as ExcelColumn;
    if (column != null) {
      if (column.ColumnMin != column.ColumnMax) {
        int maxCol = column.ColumnMax;
        column.ColumnMax = col;
        ExcelColumn copy = CopyColumn(column, col + 1, maxCol);
      }
    } else {
      int r = 0,
          c = col;
      if (_values.PrevCell(ref r, ref c)) {
        column = _values.GetValue(0, c) as ExcelColumn;
        int maxCol = column.ColumnMax;
        if (maxCol >= col) {
          column.ColumnMax = col - 1;
          if (maxCol > col) {
            ExcelColumn newC = CopyColumn(column, col + 1, maxCol);
          }
          return CopyColumn(column, col, col);
        }
      }
      //foreach (ExcelColumn checkColumn in _columns)
      //{
      //    if (col > checkColumn.ColumnMin && col <= checkColumn.ColumnMax)
      //    {
      //        int maxCol = checkColumn.ColumnMax;
      //        checkColumn.ColumnMax = col - 1;
      //        if (maxCol > col)
      //        {
      //            ExcelColumn newC = CopyColumn(checkColumn, col + 1, maxCol);
      //        }
      //        return CopyColumn(checkColumn, col,col);
      //    }
      //}
      column = new(this, col);
      _values.SetValue(0, col, column);
      //_columns.Add(column);
    }
    return column;
  }

  /// <summary>
  /// Returns the name of the worksheet
  /// </summary>
  /// <returns>The name of the worksheet</returns>
  public override string ToString() {
    return Name;
  }

  internal ExcelColumn CopyColumn(ExcelColumn c, int col, int maxCol) {
    ExcelColumn newC = new ExcelColumn(this, col);
    newC.ColumnMax = maxCol < ExcelPackage.MaxColumns ? maxCol : ExcelPackage.MaxColumns;
    if (c.StyleName != "") {
      newC.StyleName = c.StyleName;
    } else {
      newC.StyleID = c.StyleID;
    }

    newC.OutlineLevel = c.OutlineLevel;
    newC.Phonetic = c.Phonetic;
    newC.BestFit = c.BestFit;
    //_columns.Add(newC);
    _values.SetValue(0, col, newC);
    newC._width = c._width;
    newC._hidden = c._hidden;
    return newC;
  }

  /// <summary>
  /// Make the current worksheet active.
  /// </summary>
  public void Select() {
    View.TabSelected = true;
    //Select(Address, true);
  }

  /// <summary>
  /// Selects a range in the worksheet. The active cell is the topmost cell.
  /// Make the current worksheet active.
  /// </summary>
  /// <param name="address">An address range</param>
  public void Select(string address) {
    Select(address, true);
  }

  /// <summary>
  /// Selects a range in the worksheet. The actice cell is the topmost cell.
  /// </summary>
  /// <param name="address">A range of cells</param>
  /// <param name="selectSheet">Make the sheet active</param>
  public void Select(string address, bool selectSheet) {
    CheckSheetType();
    int toCol,
        toRow;
    //Get rows and columns and validate as well
    ExcelCellBase.GetRowColFromAddress(
        address,
        out var fromRow,
        out var fromCol,
        out toRow,
        out toCol);

    if (selectSheet) {
      View.TabSelected = true;
    }
    View.SelectedRange = address;
    View.ActiveCell = ExcelCellBase.GetAddress(fromRow, fromCol);
  }

  /// <summary>
  /// Selects a range in the worksheet. The active cell is the topmost cell of the first address.
  /// Make the current worksheet active.
  /// </summary>
  /// <param name="address">An address range</param>
  public void Select(ExcelAddress address) {
    CheckSheetType();
    Select(address, true);
  }

  /// <summary>
  /// Selects a range in the worksheet. The active cell is the topmost cell of the first address.
  /// </summary>
  /// <param name="address">A range of cells</param>
  /// <param name="selectSheet">Make the sheet active</param>
  public void Select(ExcelAddress address, bool selectSheet) {
    CheckSheetType();
    if (selectSheet) {
      View.TabSelected = true;
    }
    string selAddress =
        ExcelCellBase.GetAddress(address.Start.Row, address.Start.Column)
            + ":"
            + ExcelCellBase.GetAddress(address.End.Row, address.End.Column);
    if (address.Addresses != null) {
      foreach (var a in address.Addresses) {
        selAddress +=
            " "
                + ExcelCellBase.GetAddress(a.Start.Row, a.Start.Column)
                + ":"
                + ExcelCellBase.GetAddress(a.End.Row, a.End.Column);
      }
    }
    View.SelectedRange = selAddress;
    View.ActiveCell = ExcelCellBase.GetAddress(address.Start.Row, address.Start.Column);
  }

  /// <summary>
  /// Inserts a new row into the spreadsheet.  Existing rows below the position are
  /// shifted down.  All formula are updated to take account of the new row.
  /// </summary>
  /// <param name="rowFrom">The position of the new row</param>
  /// <param name="rows">Number of rows to insert</param>
  public void InsertRow(int rowFrom, int rows) {
    InsertRow(rowFrom, rows, 0);
  }

  /// <summary>
  /// Inserts a new row into the spreadsheet.  Existing rows below the position are
  /// shifted down.  All formula are updated to take account of the new row.
  /// </summary>
  /// <param name="rowFrom">The position of the new row</param>
  /// <param name="rows">Number of rows to insert.</param>
  /// <param name="copyStylesFromRow">Copy Styles from this row. Applied to all inserted rows</param>
  public void InsertRow(int rowFrom, int rows, int copyStylesFromRow) {
    CheckSheetType();
    var d = Dimension;

    if (rowFrom < 1) {
      throw (new ArgumentOutOfRangeException("rowFrom can't be lesser that 1"));
    }

    //Check that cells aren't shifted outside the boundries
    if (d != null && d.End.Row > rowFrom && d.End.Row + rows > ExcelPackage.MaxRows) {
      throw (new ArgumentOutOfRangeException(
              "Can't insert. Rows will be shifted outside the boundries of the worksheet."));
    }

    lock (this) {
      _values.Insert(rowFrom, 0, rows, 0);
      _formulas.Insert(rowFrom, 0, rows, 0);
      _styles.Insert(rowFrom, 0, rows, 0);
      _types.Insert(rowFrom, 0, rows, 0);
      _commentsStore.Insert(rowFrom, 0, rows, 0);
      _hyperLinks.Insert(rowFrom, 0, rows, 0);
      _flags.Insert(rowFrom, 0, rows, 0);

      foreach (var f in _sharedFormulas.Values) {
        if (f.StartRow >= rowFrom) {
          f.StartRow += rows;
        }
        var a = new ExcelAddressBase(f.Address);
        if (a._fromRow >= rowFrom) {
          a._fromRow += rows;
          a._toRow += rows;
        } else if (a._toRow >= rowFrom) {
          a._toRow += rows;
        }
        f.Address = ExcelCellBase.GetAddress(a._fromRow, a._fromCol, a._toRow, a._toCol);
        f.Formula = ExcelCellBase.UpdateFormulaReferences(f.Formula, rows, 0, rowFrom, 0);
      }
      var cse = new CellsStoreEnumerator<object>(_formulas);
      while (cse.Next()) {
        if (cse.Value is string) {
          cse.Value = ExcelCellBase.UpdateFormulaReferences(
              cse.Value.ToString(),
              rows,
              0,
              rowFrom,
              0);
        }
      }

      FixMergedCellsRow(rowFrom, rows, false);
      if (copyStylesFromRow > 0) {
        var cseS = new CellsStoreEnumerator<int>(
            _styles,
            copyStylesFromRow,
            0,
            copyStylesFromRow,
            ExcelPackage.MaxColumns); //Fixes issue 15068 , 15090
        while (cseS.Next()) {
          for (var r = 0; r < rows; r++) {
            _styles.SetValue(rowFrom + r, cseS.Column, cseS.Value);
          }
        }
      }
      foreach (var tbl in Tables) {
        tbl.Address = tbl.Address.AddRow(rowFrom, rows);
      }
    }
  }

  /// <summary>
  /// Inserts a new column into the spreadsheet.  Existing columns below the position are
  /// shifted down.  All formula are updated to take account of the new column.
  /// </summary>
  /// <param name="columnFrom">The position of the new column</param>
  /// <param name="columns">Number of columns to insert</param>
  public void InsertColumn(int columnFrom, int columns) {
    InsertColumn(columnFrom, columns, 0);
  }

  ///<summary>
  /// Inserts a new column into the spreadsheet.  Existing column to the left are
  /// shifted.  All formula are updated to take account of the new column.
  /// </summary>
  /// <param name="columnFrom">The position of the new column</param>
  /// <param name="columns">Number of columns to insert.</param>
  /// <param name="copyStylesFromColumn">Copy Styles from this column. Applied to all inserted columns</param>
  public void InsertColumn(int columnFrom, int columns, int copyStylesFromColumn) {
    CheckSheetType();
    var d = Dimension;

    if (columnFrom < 1) {
      throw (new ArgumentOutOfRangeException("columnFrom can't be lesser that 1"));
    }
    //Check that cells aren't shifted outside the boundries
    if (d != null
        && d.End.Column > columnFrom
        && d.End.Column + columns > ExcelPackage.MaxColumns) {
      throw (new ArgumentOutOfRangeException(
              "Can't insert. Columns will be shifted outside the boundries of the worksheet."));
    }

    lock (this) {
      _values.Insert(0, columnFrom, 0, columns);
      _formulas.Insert(0, columnFrom, 0, columns);
      _styles.Insert(0, columnFrom, 0, columns);
      _types.Insert(0, columnFrom, 0, columns);
      _commentsStore.Insert(0, columnFrom, 0, columns);
      _hyperLinks.Insert(0, columnFrom, 0, columns);
      _flags.Insert(0, columnFrom, 0, columns);

      foreach (var f in _sharedFormulas.Values) {
        if (f.StartCol >= columnFrom) {
          f.StartCol += columns;
        }
        var a = new ExcelAddressBase(f.Address);
        if (a._fromCol >= columnFrom) {
          a._fromCol += columns;
          a._toCol += columns;
        } else if (a._toCol >= columnFrom) {
          a._toCol += columns;
        }
        f.Address = ExcelCellBase.GetAddress(a._fromRow, a._fromCol, a._toRow, a._toCol);
        f.Formula = ExcelCellBase.UpdateFormulaReferences(f.Formula, 0, columns, 0, columnFrom);
      }

      var cse = new CellsStoreEnumerator<object>(_formulas);
      while (cse.Next()) {
        if (cse.Value is string) {
          cse.Value = ExcelCellBase.UpdateFormulaReferences(
              cse.Value.ToString(),
              0,
              columns,
              0,
              columnFrom);
        }
      }

      FixMergedCellsColumn(columnFrom, columns, false);

      var csec = new CellsStoreEnumerator<object>(_values, 0, 1, 0, ExcelPackage.MaxColumns);
      var lst = new List<ExcelColumn>();
      foreach (var col in csec) {
        if (col is ExcelColumn column) {
          lst.Add(column);
        }
      }

      for (int i = lst.Count - 1; i >= 0; i--) {
        var c = lst[i];
        if (c._columnMin >= columnFrom) {
          if (c._columnMin + columns <= ExcelPackage.MaxColumns) {
            c._columnMin += columns;
          } else {
            c._columnMin = ExcelPackage.MaxColumns;
          }

          if (c._columnMax + columns <= ExcelPackage.MaxColumns) {
            c._columnMax += columns;
          } else {
            c._columnMax = ExcelPackage.MaxColumns;
          }
        } else if (c._columnMax >= columnFrom) {
          var cc = c._columnMax - columnFrom;
          c._columnMax = columnFrom - 1;
          CopyColumn(c, columnFrom + columns, columnFrom + columns + cc);
        }
      }

      if (copyStylesFromColumn > 0) {
        for (var c = 0; c < columns; c++) {
          var col = Column(columnFrom + c);
          col.StyleID = Column(copyStylesFromColumn).StyleID;
        }
      }
      //Adjust tables
      foreach (var tbl in Tables) {
        if (columnFrom > tbl.Address.Start.Column && columnFrom <= tbl.Address.End.Column) {
          InsertTableColumns(columnFrom, columns, tbl);
        }

        tbl.Address = tbl.Address.AddColumn(columnFrom, columns);
      }
    }
  }

  private static void InsertTableColumns(int columnFrom, int columns, ExcelTable tbl) {
    var node = tbl.Columns[0].TopNode.ParentNode;
    var ix = columnFrom - tbl.Address.Start.Column - 1;
    var insPos = node.ChildNodes[ix];
    ix += 2;
    for (int i = 0; i < columns; i++) {
      var name = tbl.Columns.GetUniqueName(
          string.Format("Column{0}", (ix++).ToString(CultureInfo.InvariantCulture)));
      XmlElement tableColumn = (XmlElement)
        tbl.TableXml.CreateNode(XmlNodeType.Element, "tableColumn", ExcelPackage._schemaMain);
      tableColumn.SetAttribute(
          "id",
          (tbl.Columns.Count + i + 1).ToString(CultureInfo.InvariantCulture));
      tableColumn.SetAttribute("name", name);
      insPos = node.InsertAfter(tableColumn, insPos);
    } //Create tbl Column
    tbl._cols = new(tbl);
  }

  /// <summary>
  /// Adds a value to the row of merged cells to fix for inserts or deletes
  /// </summary>
  /// <param name="row"></param>
  /// <param name="rows"></param>
  /// <param name="delete"></param>
  private void FixMergedCellsRow(int row, int rows, bool delete) {
    if (delete) {
      _mergedCells._cells.Delete(row, 0, rows, 0);
    } else {
      _mergedCells._cells.Insert(row, 0, rows, 0);
    }

    List<int> removeIndex = new List<int>();
    for (int i = 0; i < _mergedCells.Count; i++) {
      if (!string.IsNullOrEmpty(_mergedCells[i])) {
        ExcelAddressBase addr = new(_mergedCells[i]),
            newAddr;
        if (delete) {
          newAddr = addr.DeleteRow(row, rows);
          if (newAddr == null) {
            removeIndex.Add(i);
            continue;
          }
        } else {
          newAddr = addr.AddRow(row, rows);
          if (newAddr.Address != addr.Address) {
            //    _mergedCells._cells.Insert(row, 0, rows, 0);
            _mergedCells.SetIndex(newAddr, i);
          }
        }

        if (newAddr.Address != addr.Address) {
          _mergedCells.List[i] = newAddr._address;
        }
      }
    }
    for (int i = removeIndex.Count - 1; i >= 0; i--) {
      _mergedCells.List.RemoveAt(removeIndex[i]);
    }
  }

  /// <summary>
  /// Adds a value to the row of merged cells to fix for inserts or deletes
  /// </summary>
  /// <param name="column"></param>
  /// <param name="columns"></param>
  /// <param name="delete"></param>
  private void FixMergedCellsColumn(int column, int columns, bool delete) {
    if (delete) {
      _mergedCells._cells.Delete(0, column, 0, columns);
    } else {
      _mergedCells._cells.Insert(0, column, 0, columns);
    }
    List<int> removeIndex = new List<int>();
    for (int i = 0; i < _mergedCells.Count; i++) {
      if (!string.IsNullOrEmpty(_mergedCells[i])) {
        ExcelAddressBase addr = new(_mergedCells[i]),
            newAddr;
        if (delete) {
          newAddr = addr.DeleteColumn(column, columns);
          if (newAddr == null) {
            removeIndex.Add(i);
            continue;
          }
        } else {
          newAddr = addr.AddColumn(column, columns);
          if (newAddr.Address != addr.Address) {
            _mergedCells.SetIndex(newAddr, i);
          }
        }

        if (newAddr.Address != addr.Address) {
          _mergedCells.List[i] = newAddr._address;
        }
      }
    }
    for (int i = removeIndex.Count - 1; i >= 0; i--) {
      _mergedCells.List.RemoveAt(removeIndex[i]);
    }
  }

  /// <summary>
  /// Delete the specified row from the worksheet.
  /// </summary>
  /// <param name="row">A row to be deleted</param>
  public void DeleteRow(int row) {
    DeleteRow(row, 1);
  }

  /// <summary>
  /// Delete the specified row from the worksheet.
  /// </summary>
  /// <param name="rowFrom">The start row</param>
  /// <param name="rows">Number of rows to delete</param>
  public void DeleteRow(int rowFrom, int rows) {
    CheckSheetType();
    if (rowFrom < 1 || rowFrom + rows > ExcelPackage.MaxRows) {
      throw (new ArgumentException(
              "Row out of range. Spans from 1 to "
                  + ExcelPackage.MaxRows.ToString(CultureInfo.InvariantCulture)));
    }
    lock (this) {
      _values.Delete(rowFrom, 0, rows, ExcelPackage.MaxColumns);
      _types.Delete(rowFrom, 0, rows, ExcelPackage.MaxColumns);
      _formulas.Delete(rowFrom, 0, rows, ExcelPackage.MaxColumns);
      _styles.Delete(rowFrom, 0, rows, ExcelPackage.MaxColumns);
      _flags.Delete(rowFrom, 0, rows, ExcelPackage.MaxColumns);
      _commentsStore.Delete(rowFrom, 0, rows, ExcelPackage.MaxColumns);
      _hyperLinks.Delete(rowFrom, 0, rows, ExcelPackage.MaxColumns);

      AdjustFormulasRow(rowFrom, rows);
      FixMergedCellsRow(rowFrom, rows, true);

      foreach (var tbl in Tables) {
        tbl.Address = tbl.Address.DeleteRow(rowFrom, rows);
      }
    }
  }

  /// <summary>
  /// Delete the specified column from the worksheet.
  /// </summary>
  /// <param name="column">The column to be deleted</param>
  public void DeleteColumn(int column) {
    DeleteColumn(column, 1);
  }

  /// <summary>
  /// Delete the specified column from the worksheet.
  /// </summary>
  /// <param name="columnFrom">The start column</param>
  /// <param name="columns">Number of columns to delete</param>
  public void DeleteColumn(int columnFrom, int columns) {
    if (columnFrom < 1 || columnFrom + columns > ExcelPackage.MaxColumns) {
      throw (new ArgumentException(
              "Column out of range. Spans from 1 to "
                  + ExcelPackage.MaxColumns.ToString(CultureInfo.InvariantCulture)));
    }
    lock (this) {
      var col = _values.GetValue(0, columnFrom) as ExcelColumn;
      if (col == null) {
        var r = 0;
        var c = columnFrom;
        if (_values.PrevCell(ref r, ref c)) {
          col = _values.GetValue(0, c) as ExcelColumn;
          if (col._columnMax >= columnFrom) {
            col.ColumnMax = columnFrom - 1;
          }
        }
      }

      _values.Delete(0, columnFrom, ExcelPackage.MaxRows, columns);
      _types.Delete(0, columnFrom, ExcelPackage.MaxRows, columns);
      _formulas.Delete(0, columnFrom, ExcelPackage.MaxRows, columns);
      _styles.Delete(0, columnFrom, ExcelPackage.MaxRows, columns);
      _flags.Delete(0, columnFrom, ExcelPackage.MaxRows, columns);
      _commentsStore.Delete(0, columnFrom, ExcelPackage.MaxRows, columns);
      _hyperLinks.Delete(0, columnFrom, ExcelPackage.MaxRows, columns);

      AdjustFormulasColumn(columnFrom, columns);
      FixMergedCellsColumn(columnFrom, columns, true);

      var csec = new CellsStoreEnumerator<object>(
          _values,
          0,
          columnFrom,
          0,
          ExcelPackage.MaxColumns);
      foreach (var column in csec) {
        if (column is ExcelColumn excelColumn) {
          if (excelColumn._columnMin >= columnFrom) {
            excelColumn._columnMin -= columns;
            excelColumn._columnMax -= columns;
          }
        }
      }

      foreach (var tbl in Tables) {
        if (columnFrom >= tbl.Address.Start.Column && columnFrom <= tbl.Address.End.Column) {
          var node = tbl.Columns[0].TopNode.ParentNode;
          var ix = columnFrom - tbl.Address.Start.Column;
          for (int i = 0; i < columns; i++) {
            if (node.ChildNodes.Count > ix) {
              node.RemoveChild(node.ChildNodes[ix]);
            }
          }
          tbl._cols = new(tbl);
        }

        tbl.Address = tbl.Address.DeleteColumn(columnFrom, columns);
      }
    }
  }

  internal void AdjustFormulasRow(int rowFrom, int rows) {
    var delSf = new List<int>();
    foreach (var sf in _sharedFormulas.Values) {
      var a = new ExcelAddress(sf.Address).DeleteRow(rowFrom, rows);
      if (a == null) {
        delSf.Add(sf.Index);
      } else {
        sf.Address = a.Address;
        if (sf.StartRow > rowFrom) {
          var r = Math.Min(sf.StartRow - rowFrom, rows);
          sf.Formula = ExcelCellBase.UpdateFormulaReferences(sf.Formula, -r, 0, rowFrom, 0);
          sf.StartRow -= r;
        }
      }
    }
    foreach (var ix in delSf) {
      _sharedFormulas.Remove(ix);
    }
    delSf = null;
    var cse = new CellsStoreEnumerator<object>(
        _formulas,
        1,
        1,
        ExcelPackage.MaxRows,
        ExcelPackage.MaxColumns);
    while (cse.Next()) {
      if (cse.Value is string) {
        cse.Value = ExcelCellBase.UpdateFormulaReferences(
            cse.Value.ToString(),
            -rows,
            0,
            rowFrom,
            0);
      }
    }
  }

  internal void AdjustFormulasColumn(int columnFrom, int columns) {
    var delSf = new List<int>();
    foreach (var sf in _sharedFormulas.Values) {
      var a = new ExcelAddress(sf.Address).DeleteColumn(columnFrom, columns);
      if (a == null) {
        delSf.Add(sf.Index);
      } else {
        sf.Address = a.Address;
        if (sf.StartCol > columnFrom) {
          var c = Math.Min(sf.StartCol - columnFrom, columns);
          sf.Formula = ExcelCellBase.UpdateFormulaReferences(sf.Formula, 0, -c, 0, 1);
          sf.StartCol -= c;
        }
      }
    }
    foreach (var ix in delSf) {
      _sharedFormulas.Remove(ix);
    }
    delSf = null;
    var cse = new CellsStoreEnumerator<object>(
        _formulas,
        1,
        1,
        ExcelPackage.MaxRows,
        ExcelPackage.MaxColumns);
    while (cse.Next()) {
      if (cse.Value is string) {
        cse.Value = ExcelCellBase.UpdateFormulaReferences(
            cse.Value.ToString(),
            0,
            -columns,
            0,
            columnFrom);
      }
    }
  }

  /// <summary>
  /// Deletes the specified row from the worksheet.
  /// </summary>
  /// <param name="rowFrom">The number of the start row to be deleted</param>
  /// <param name="rows">Number of rows to delete</param>
  /// <param name="shiftOtherRowsUp">Not used. Rows are always shifted</param>
  public void DeleteRow(int rowFrom, int rows, bool shiftOtherRowsUp) {
    DeleteRow(rowFrom, rows);
  }

  /// <summary>
  /// Get the cell value from thw worksheet
  /// </summary>
  /// <param name="row">The row number</param>
  /// <param name="column">The row number</param>
  /// <returns>The value</returns>
  public object GetValue(int row, int column) {
    CheckSheetType();
    var v = _values.GetValue(row, column);
    if (v != null) {
      if (_flags.GetFlagValue(row, column, CellFlags.RichText)) {
        return Cells[row, column].RichText.Text;
      }
      return v;
    }
    return null;
  }

  /// <summary>
  /// Get a strongly typed cell value from the worksheet
  /// </summary>
  /// <typeparam name="T">The type</typeparam>
  /// <param name="row">The row number</param>
  /// <param name="column">The row number</param>
  /// <returns>The value. If the value can't be converted to the specified type, the default value will be returned</returns>
  public T GetValue<T>(int row, int column) {
    CheckSheetType();
    //ulong cellID=ExcelCellBase.GetCellID(SheetID, Row, Column);
    var v = _values.GetValue(row, column);
    if (v == null) {
      return default(T);
    }

    //var cell=((ExcelCell)_cells[cellID]);
    if (_flags.GetFlagValue(row, column, CellFlags.RichText)) {
      return (T)(object)Cells[row, column].RichText.Text;
    }
    return GetTypedValue<T>(v);
  }

  //Thanks to Michael Tran for parts of this method
  internal T GetTypedValue<T>(object v) {
    if (v == null) {
      return default(T);
    }
    Type fromType = v.GetType();
    Type toType = typeof(T);
    if (fromType == toType) {
      return (T)v;
    }
    var cnv = TypeDescriptor.GetConverter(fromType);
    if (toType
        == typeof(DateTime)) //Handle dates
    {
      if (fromType == typeof(TimeSpan)) {
        return ((T)(object)(new DateTime(((TimeSpan)v).Ticks)));
      }
      if (fromType == typeof(string)) {
        if (DateTime.TryParse(v.ToString(), out var dt)) {
          return (T)(object)(dt);
        }
        return default(T);
      }
      if (cnv.CanConvertTo(typeof(double))) {
        return (T)(object)(DateTime.FromOADate((double)cnv.ConvertTo(v, typeof(double))));
      }
      return default(T);
    }
    if (toType
        == typeof(TimeSpan)) //Handle timespan
    {
      if (fromType == typeof(DateTime)) {
        return ((T)(object)(new TimeSpan(((DateTime)v).Ticks)));
      }
      if (fromType == typeof(string)) {
        if (TimeSpan.TryParse(v.ToString(), out var ts)) {
          return (T)(object)(ts);
        }
        return default(T);
      }
      if (cnv.CanConvertTo(typeof(double))) {
        return (T)
          (object)(new TimeSpan(
              DateTime.FromOADate((double)cnv.ConvertTo(v, typeof(double))).Ticks));
      }
      try {
        // Issue 14682 -- "GetValue<decimal>() won't convert strings"
        // As suggested, after all special cases, all .NET to do it's
        // preferred conversion rather than simply returning the default
        return (T)Convert.ChangeType(v, typeof(T));
      } catch (Exception) {
        // This was the previous behaviour -- no conversion is available.
        return default(T);
      }
    }
    if (cnv.CanConvertTo(toType)) {
      return (T)cnv.ConvertTo(v, typeof(T));
    }
    if (toType.IsGenericType && toType.GetGenericTypeDefinition().Equals(typeof(Nullable<>))) {
      toType = Nullable.GetUnderlyingType(toType);
      if (cnv.CanConvertTo(toType)) {
        return (T)cnv.ConvertTo(v, typeof(T));
      }
    }

    if (fromType == typeof(double) && toType == typeof(decimal)) {
      return (T)(object)Convert.ToDecimal(v);
    }
    if (fromType == typeof(decimal) && toType == typeof(double)) {
      return (T)(object)Convert.ToDouble(v);
    }
    return default(T);
  }

  /// <summary>
  /// Set the value of a cell
  /// </summary>
  /// <param name="row">The row number</param>
  /// <param name="column">The column number</param>
  /// <param name="value">The value</param>
  public void SetValue(int row, int column, object value) {
    CheckSheetType();
    if (row < 1 || column < 1 || row > ExcelPackage.MaxRows && column > ExcelPackage.MaxColumns) {
      throw new ArgumentOutOfRangeException("Row or Column out of range");
    }
    _values.SetValue(row, column, value);
  }

  /// <summary>
  /// Set the value of a cell
  /// </summary>
  /// <param name="address">The Excel address</param>
  /// <param name="value">The value</param>
  public void SetValue(string address, object value) {
    CheckSheetType();
    ExcelCellBase.GetRowCol(address, out var row, out var col, true);
    if (row < 1 || col < 1 || row > ExcelPackage.MaxRows && col > ExcelPackage.MaxColumns) {
      throw new ArgumentOutOfRangeException("Address is invalid or out of range");
    }
    _values.SetValue(row, col, value);
  }

  /// <summary>
  /// Get MergeCell Index No
  /// </summary>
  /// <param name="row"></param>
  /// <param name="column"></param>
  /// <returns></returns>
  public int GetMergeCellId(int row, int column) {
    for (int i = 0; i < _mergedCells.Count; i++) {
      if (!string.IsNullOrEmpty(_mergedCells[i])) {
        ExcelRange range = Cells[_mergedCells[i]];

        if (range.Start.Row <= row && row <= range.End.Row) {
          if (range.Start.Column <= column && column <= range.End.Column) {
            return i + 1;
          }
        }
      }
    }
    return 0;
  }

  internal void Save() {
    DeletePrinterSettings();

    if (_worksheetXml != null) {
      if (!(this is ExcelChartsheet)) {
        // save the header & footer (if defined)
        if (_headerFooter != null) {
          HeaderFooter.Save();
        }

        var d = Dimension;
        if (d == null) {
          DeleteAllNode("d:dimension/@ref");
        } else {
          SetXmlNodeString("d:dimension/@ref", d.Address);
        }

        SaveComments();
        SaveTables();
        SavePivotTables();
      }
    }
  }

  internal void SaveHandler(StreamWriter streamWriter) {
    //Create the nodes if they do not exist.
    if (this is ExcelChartsheet) {
      streamWriter.Write(_worksheetXml.OuterXml);
    } else {
      CreateNode("d:cols");
      CreateNode("d:sheetData");
      CreateNode("d:mergeCells");
      CreateNode("d:hyperlinks");
      CreateNode("d:rowBreaks");
      CreateNode("d:colBreaks");

      //StreamWriter sw=new StreamWriter(Part.GetStream(FileMode.Create, FileAccess.Write));
      var xml = _worksheetXml.OuterXml;
      int colStart = 0,
          colEnd = 0;
      GetBlockPos(xml, "cols", ref colStart, ref colEnd);

      streamWriter.Write(xml.Substring(0, colStart));
      UpdateColumnData(streamWriter);

      int cellStart = colEnd,
          cellEnd = colEnd;
      GetBlockPos(xml, "sheetData", ref cellStart, ref cellEnd);

      streamWriter.Write(xml.Substring(colEnd, cellStart - colEnd));
      UpdateRowCellData(streamWriter);

      int mergeStart = cellEnd,
          mergeEnd = cellEnd;

      GetBlockPos(xml, "mergeCells", ref mergeStart, ref mergeEnd);
      streamWriter.Write(xml.Substring(cellEnd, mergeStart - cellEnd));

      CleanupMergedCells(_mergedCells);
      if (_mergedCells.Count > 0) {
        UpdateMergedCells(streamWriter);
      }

      int hyperStart = mergeEnd,
          hyperEnd = mergeEnd;
      GetBlockPos(xml, "hyperlinks", ref hyperStart, ref hyperEnd);
      streamWriter.Write(xml.Substring(mergeEnd, hyperStart - mergeEnd));
      UpdateHyperLinks(streamWriter);

      int rowBreakStart = hyperEnd,
          rowBreakEnd = hyperEnd;
      GetBlockPos(xml, "rowBreaks", ref rowBreakStart, ref rowBreakEnd);
      streamWriter.Write(xml.Substring(hyperEnd, rowBreakStart - hyperEnd));
      UpdateRowBreaks(streamWriter);

      int colBreakStart = rowBreakEnd,
          colBreakEnd = rowBreakEnd;
      GetBlockPos(xml, "colBreaks", ref colBreakStart, ref colBreakEnd);
      streamWriter.Write(xml.Substring(rowBreakEnd, colBreakStart - rowBreakEnd));
      UpdateColBreaks(streamWriter);
      streamWriter.Write(xml.Substring(colBreakEnd, xml.Length - colBreakEnd));
    }
  }

  /// <summary>
  /// Delete the printersettings relationship and part.
  /// </summary>
  private void DeletePrinterSettings() {
    //Delete the relationship from the pageSetup tag
    XmlAttribute attr = (XmlAttribute)
      WorksheetXml.SelectSingleNode("//d:pageSetup/@r:id", NameSpaceManager);
    if (attr != null) {
      string relId = attr.Value;
      //First delete the attribute from the XML
      attr.OwnerElement.Attributes.Remove(attr);
      if (Part.RelationshipExists(relId)) {
        var rel = Part.GetRelationship(relId);
        Uri printerSettingsUri = UriHelper.ResolvePartUri(rel.SourceUri, rel.TargetUri);
        Part.DeleteRelationship(rel.Id);

        //Delete the part from the package
        if (_package.Package.PartExists(printerSettingsUri)) {
          _package.Package.DeletePart(printerSettingsUri);
        }
      }
    }
  }

  private void SaveComments() {
    if (_comments != null) {
      if (_comments.Count == 0) {
        if (_comments.Uri != null) {
          Part.DeleteRelationship(_comments.RelId);
          _package.Package.DeletePart(_comments.Uri);
        }
        RemoveLegacyDrawingRel(VmlDrawingsComments.RelId);
      } else {
        if (_comments.Uri == null) {
          _comments.Uri = new(string.Format("/xl/comments{0}.xml", SheetID), UriKind.Relative);
        }
        if (_comments.Part == null) {
          _comments.Part = _package.Package.CreatePart(
              _comments.Uri,
              "application/vnd.openxmlformats-officedocument.spreadsheetml.comments+xml",
              _package.Compression);
          var rel = Part.CreateRelationship(
              UriHelper.GetRelativeUri(WorksheetUri, _comments.Uri),
              TargetMode.Internal,
              ExcelPackage._schemaRelationships + "/comments");
        }
        _comments.CommentXml.Save(_comments.Part.GetStream(FileMode.Create));
      }
    }

    if (_vmlDrawings != null) {
      if (_vmlDrawings.Count == 0) {
        if (_vmlDrawings.Uri != null) {
          Part.DeleteRelationship(_vmlDrawings.RelId);
          _package.Package.DeletePart(_vmlDrawings.Uri);
        }
      } else {
        if (_vmlDrawings.Uri == null) {
          _vmlDrawings.Uri = GetNewUri(_package.Package, "/xl/drawings/vmlDrawing{0}.vml");
        }
        if (_vmlDrawings.Part == null) {
          _vmlDrawings.Part = _package.Package.CreatePart(
              _vmlDrawings.Uri,
              "application/vnd.openxmlformats-officedocument.vmlDrawing",
              _package.Compression);
          var rel = Part.CreateRelationship(
              UriHelper.GetRelativeUri(WorksheetUri, _vmlDrawings.Uri),
              TargetMode.Internal,
              ExcelPackage._schemaRelationships + "/vmlDrawing");
          SetXmlNodeString("d:legacyDrawing/@r:id", rel.Id);
          _vmlDrawings.RelId = rel.Id;
        }
        _vmlDrawings.VmlDrawingXml.Save(_vmlDrawings.Part.GetStream());
      }
    }
  }

  /// <summary>
  /// Save all table data
  /// </summary>
  private void SaveTables() {
    foreach (var tbl in Tables) {
      if (tbl.ShowHeader || tbl.ShowTotal) {
        int colNum = tbl.Address._fromCol;
        var colVal = new HashSet<string>();
        foreach (var col in tbl.Columns) {
          string n = col.Name.ToLower(CultureInfo.InvariantCulture);
          if (tbl.ShowHeader) {
            n = tbl.WorkSheet.GetValue<string>(
                tbl.Address._fromRow,
                tbl.Address._fromCol + col.Position);
            if (string.IsNullOrEmpty(n)) {
              n = col.Name.ToLower(CultureInfo.InvariantCulture);
            } else {
              col.Name = n;
            }
          } else {
            n = col.Name.ToLower(CultureInfo.InvariantCulture);
          }

          if (colVal.Contains(n)) {
            throw (new InvalidDataException(
                    string.Format(
                        "Table {0} Column {1} does not have a unique name.",
                        tbl.Name,
                        col.Name)));
          }
          colVal.Add(n);
          col.Name = ConvertUtil.ExcelEncodeString(col.Name);
          if (tbl.ShowHeader) {
            _values.SetValue(tbl.Address._fromRow, colNum, col.Name);
          }
          if (tbl.ShowTotal) {
            SetTableTotalFunction(tbl, col, colNum);
          }
          if (!string.IsNullOrEmpty(col.CalculatedColumnFormula)) {
            int fromRow = tbl.ShowHeader ? tbl.Address._fromRow + 1 : tbl.Address._fromRow;
            int toRow = tbl.ShowTotal ? tbl.Address._toRow - 1 : tbl.Address._toRow;
            for (int row = fromRow; row <= toRow; row++) {
              //Cell(row, colNum).Formula = col.CalculatedColumnFormula;
              SetFormula(row, colNum, col.CalculatedColumnFormula);
            }
          }
          colNum++;
        }
      }
      if (tbl.Part == null) {
        tbl.TableUri = GetNewUri(_package.Package, "/xl/tables/table{0}.xml", tbl.Id);
        tbl.Part = _package.Package.CreatePart(
            tbl.TableUri,
            "application/vnd.openxmlformats-officedocument.spreadsheetml.table+xml",
            Workbook._package.Compression);
        var stream = tbl.Part.GetStream(FileMode.Create);
        tbl.TableXml.Save(stream);
        var rel = Part.CreateRelationship(
            UriHelper.GetRelativeUri(WorksheetUri, tbl.TableUri),
            TargetMode.Internal,
            ExcelPackage._schemaRelationships + "/table");
        tbl.RelationshipID = rel.Id;

        CreateNode("d:tableParts");
        XmlNode tbls = TopNode.SelectSingleNode("d:tableParts", NameSpaceManager);

        var tblNode = tbls.OwnerDocument.CreateElement("tablePart", ExcelPackage._schemaMain);
        tbls.AppendChild(tblNode);
        tblNode.SetAttribute("id", ExcelPackage._schemaRelationships, rel.Id);
      } else {
        var stream = tbl.Part.GetStream(FileMode.Create);
        tbl.TableXml.Save(stream);
      }
    }
  }

  internal void SetTableTotalFunction(ExcelTable tbl, ExcelTableColumn col, int colNum = -1) {
    if (tbl.ShowTotal == false) {
      return;
    }
    if (colNum == -1) {
      for (int i = 0; i < tbl.Columns.Count; i++) {
        if (tbl.Columns[i].Name == col.Name) {
          colNum = tbl.Address._fromCol + i;
        }
      }
    }
    if (col.TotalsRowFunction == RowFunctions.Custom) {
      SetFormula(tbl.Address._toRow, colNum, col.TotalsRowFormula);
    } else if (col.TotalsRowFunction != RowFunctions.None) {
      switch (col.TotalsRowFunction) {
        case RowFunctions.Average:
          SetFormula(tbl.Address._toRow, colNum, GetTotalFunction(col, "101"));
          break;
        case RowFunctions.Count:
          SetFormula(tbl.Address._toRow, colNum, GetTotalFunction(col, "102"));
          break;
        case RowFunctions.CountNums:
          SetFormula(tbl.Address._toRow, colNum, GetTotalFunction(col, "103"));
          break;
        case RowFunctions.Max:
          SetFormula(tbl.Address._toRow, colNum, GetTotalFunction(col, "104"));
          break;
        case RowFunctions.Min:
          SetFormula(tbl.Address._toRow, colNum, GetTotalFunction(col, "105"));
          break;
        case RowFunctions.StdDev:
          SetFormula(tbl.Address._toRow, colNum, GetTotalFunction(col, "107"));
          break;
        case RowFunctions.Var:
          SetFormula(tbl.Address._toRow, colNum, GetTotalFunction(col, "110"));
          break;
        case RowFunctions.Sum:
          SetFormula(tbl.Address._toRow, colNum, GetTotalFunction(col, "109"));
          break;
        default:
          throw (new("Unknown RowFunction enum"));
      }
    } else {
      _values.SetValue(tbl.Address._toRow, colNum, col.TotalsRowLabel);
    }
  }

  internal void SetFormula(int row, int col, object value) {
    _formulas.SetValue(row, col, value);
    if (!_values.Exists(row, col)) {
      _values.SetValue(row, col, null);
    }
  }

  internal void SetStyle(int row, int col, int value) {
    _styles.SetValue(row, col, value);
    if (!_values.Exists(row, col)) {
      _values.SetValue(row, col, null);
    }
  }

  private void SavePivotTables() {
    foreach (var pt in PivotTables) {
      if (pt.DataFields.Count > 1) {
        XmlElement parentNode;
        if (pt.DataOnRows) {
          parentNode =
              pt.PivotTableXml.SelectSingleNode("//d:rowFields", pt.NameSpaceManager) as XmlElement;
          if (parentNode == null) {
            pt.CreateNode("d:rowFields");
            parentNode =
                pt.PivotTableXml.SelectSingleNode("//d:rowFields", pt.NameSpaceManager)
                    as XmlElement;
          }
        } else {
          parentNode =
              pt.PivotTableXml.SelectSingleNode("//d:colFields", pt.NameSpaceManager) as XmlElement;
          if (parentNode == null) {
            pt.CreateNode("d:colFields");
            parentNode =
                pt.PivotTableXml.SelectSingleNode("//d:colFields", pt.NameSpaceManager)
                    as XmlElement;
          }
        }

        if (parentNode.SelectSingleNode("d:field[@ x= \"-2\"]", pt.NameSpaceManager) == null) {
          XmlElement fieldNode = pt.PivotTableXml.CreateElement("field", ExcelPackage._schemaMain);
          fieldNode.SetAttribute("x", "-2");
          parentNode.AppendChild(fieldNode);
        }
      }
      var ws = Workbook.Worksheets[pt.CacheDefinition.SourceRange.WorkSheet];
      var t = ws.Tables.GetFromRange(pt.CacheDefinition.SourceRange);
      var fields = pt.CacheDefinition.CacheDefinitionXml.SelectNodes(
          "d:pivotCacheDefinition/d:cacheFields/d:cacheField",
          NameSpaceManager);
      int ix = 0;
      if (fields != null) {
        var flds = new HashSet<string>();
        foreach (XmlElement node in fields) {
          if (ix >= pt.CacheDefinition.SourceRange.Columns) {
            break;
          }
          var fldName = node.GetAttribute("name"); //Fixes issue 15295 dup name error
          if (string.IsNullOrEmpty(fldName)) {
            fldName =
                (t == null
                    ? pt.CacheDefinition.SourceRange.Offset(0, ix++, 1, 1).Value.ToString()
                    : t.Columns[ix++].Name);
          }
          if (flds.Contains(fldName)) {
            fldName = GetNewName(flds, fldName);
          }
          flds.Add(fldName);
          node.SetAttribute("name", fldName);
        }
        foreach (var df in pt.DataFields) {
          if (string.IsNullOrEmpty(df.Name)) {
            string name;
            if (df.Function == DataFieldFunctions.None) {
              name = df.Field.Name; //Name must be set or Excel will crash on rename.
            } else {
              name = df.Function + " of " + df.Field.Name; //Name must be set or Excel will crash on rename.
            }
            //Make sure name is unique
            var newName = name;
            var i = 2;
            while (pt.DataFields.ExistsDfName(newName, df)) {
              newName = name + (i++).ToString(CultureInfo.InvariantCulture);
            }
            df.Name = newName;
          }
        }
      }
      pt.PivotTableXml.Save(pt.Part.GetStream(FileMode.Create));
      pt.CacheDefinition.CacheDefinitionXml.Save(
          pt.CacheDefinition.Part.GetStream(FileMode.Create));
    }
  }

  private string GetNewName(HashSet<string> flds, string fldName) {
    int ix = 2;
    while (flds.Contains(fldName + ix.ToString(CultureInfo.InvariantCulture))) {
      ix++;
    }
    return fldName + ix.ToString(CultureInfo.InvariantCulture);
  }

  private static string GetTotalFunction(ExcelTableColumn col, string functionNum) {
    return string.Format("SUBTOTAL({0},{1}[{2}])", functionNum, col._tbl.Name, col.Name);
  }

  private void CleanupMergedCells(MergeCellsCollection mergedCells) {
    int i = 0;
    while (i < mergedCells.List.Count) {
      if (mergedCells[i] == null) {
        mergedCells.List.RemoveAt(i);
      } else {
        i++;
      }
    }
  }

  private void UpdateColBreaks(StreamWriter sw) {
    StringBuilder breaks = new StringBuilder();
    int count = 0;
    var cse = new CellsStoreEnumerator<object>(_values, 0, 0, 0, ExcelPackage.MaxColumns);
    while (cse.Next()) {
      var col = cse.Value as ExcelColumn;
      if (col != null && col.PageBreak) {
        breaks.AppendFormat("<brk id=\"{0}\" max=\"16383\" man=\"1\" />", cse.Column);
        count++;
      }
    }
    if (count > 0) {
      sw.Write("<colBreaks count=\"{0}\" manualBreakCount=\"{0}\">{1}</colBreaks>", count, breaks);
    }
  }

  private void UpdateRowBreaks(StreamWriter sw) {
    StringBuilder breaks = new StringBuilder();
    int count = 0;
    var cse = new CellsStoreEnumerator<object>(_values, 0, 0, ExcelPackage.MaxRows, 0);
    //foreach(ExcelRow row in _rows)
    while (cse.Next()) {
      var row = cse.Value as RowInternal;
      if (row != null && row.PageBreak) {
        breaks.AppendFormat("<brk id=\"{0}\" max=\"1048575\" man=\"1\" />", cse.Row);
        count++;
      }
    }
    if (count > 0) {
      sw.Write("<rowBreaks count=\"{0}\" manualBreakCount=\"{0}\">{1}</rowBreaks>", count, breaks);
    }
  }

  /// <summary>
  /// Inserts the cols collection into the XML document
  /// </summary>
  private void UpdateColumnData(StreamWriter sw) {
    var cse = new CellsStoreEnumerator<object>(_values, 0, 1, 0, ExcelPackage.MaxColumns);
    bool first = true;
    while (cse.Next()) {
      if (first) {
        sw.Write("<cols>");
        first = false;
      }
      var col = cse.Value as ExcelColumn;
      ExcelStyleCollection<ExcelXfs> cellXfs = _workbook.Styles.CellXfs;

      sw.Write("<col min=\"{0}\" max=\"{1}\"", col.ColumnMin, col.ColumnMax);
      if (col.Hidden) {
        //sbXml.Append(" width=\"0\" hidden=\"1\" customWidth=\"1\"");
        sw.Write(" hidden=\"1\"");
      } else if (col.BestFit) {
        sw.Write(" bestFit=\"1\"");
      }
      sw.Write(
          string.Format(
              CultureInfo.InvariantCulture,
              " width=\"{0}\" customWidth=\"1\"",
              col.Width));
      if (col.OutlineLevel > 0) {
        sw.Write(" outlineLevel=\"{0}\" ", col.OutlineLevel);
        if (col.Collapsed) {
          if (col.Hidden) {
            sw.Write(" collapsed=\"1\"");
          } else {
            sw.Write(" collapsed=\"1\" hidden=\"1\""); //Always hidden
          }
        }
      }
      if (col.Phonetic) {
        sw.Write(" phonetic=\"1\"");
      }

      var styleId = col.StyleID >= 0 ? cellXfs[col.StyleID].newID : col.StyleID;
      if (styleId > 0) {
        sw.Write(" style=\"{0}\"", styleId);
      }
      sw.Write(" />");
    }
    if (!first) {
      sw.Write("</cols>");
    }
  }

  /// <summary>
  /// Insert row and cells into the XML document
  /// </summary>
  private void UpdateRowCellData(StreamWriter sw) {
    ExcelStyleCollection<ExcelXfs> cellXfs = _workbook.Styles.CellXfs;

    int row = -1;

    StringBuilder sbXml = new StringBuilder();
    var ss = _workbook._sharedStrings;
    var styles = _workbook.Styles;
    var cache = new StringBuilder();
    cache.Append("<sheetData>");

    //Set a value for cells with style and no value set.
    var cseStyle = new CellsStoreEnumerator<int>(
        _styles,
        0,
        0,
        ExcelPackage.MaxRows,
        ExcelPackage.MaxColumns);
    foreach (var s in cseStyle) {
      if (!_values.Exists(cseStyle.Row, cseStyle.Column)) {
        _values.SetValue(cseStyle.Row, cseStyle.Column, null);
      }
    }

    var cse = new CellsStoreEnumerator<object>(
        _values,
        1,
        0,
        ExcelPackage.MaxRows,
        ExcelPackage.MaxColumns);
    //foreach (IRangeID r in _cells)
    while (cse.Next()) {
      if (cse.Column > 0) {
        int styleId = cellXfs[styles.GetStyleId(this, cse.Row, cse.Column)].newID;
        //Add the row element if it's a new row
        if (cse.Row != row) {
          WriteRow(cache, cellXfs, row, cse.Row);
          row = cse.Row;
        }
        object v = cse.Value;
        object formula = _formulas.GetValue(cse.Row, cse.Column);
        if (formula is int sfId) {
          var f = _sharedFormulas[sfId];
          if (f.Address.IndexOf(':') > 0) {
            if (f.StartCol == cse.Column && f.StartRow == cse.Row) {
              if (f.IsArray) {
                cache.AppendFormat(
                    "<c r=\"{0}\" s=\"{1}\"{5}><f ref=\"{2}\" t=\"array\">{3}</f>{4}</c>",
                    cse.CellAddress,
                    styleId < 0 ? 0 : styleId,
                    f.Address,
                    SecurityElement.Escape(f.Formula),
                    GetFormulaValue(v),
                    GetCellType(v, true));
              } else {
                cache.AppendFormat(
                    "<c r=\"{0}\" s=\"{1}\"{6}><f ref=\"{2}\" t=\"shared\"  si=\"{3}\">{4}</f>{5}</c>",
                    cse.CellAddress,
                    styleId < 0 ? 0 : styleId,
                    f.Address,
                    sfId,
                    SecurityElement.Escape(f.Formula),
                    GetFormulaValue(v),
                    GetCellType(v, true));
              }
            } else if (f.IsArray) {
              cache.AppendFormat(
                  "<c r=\"{0}\" s=\"{1}\" />",
                  cse.CellAddress,
                  styleId < 0 ? 0 : styleId);
            } else {
              cache.AppendFormat(
                  "<c r=\"{0}\" s=\"{1}\"{4}><f t=\"shared\" si=\"{2}\" />{3}</c>",
                  cse.CellAddress,
                  styleId < 0 ? 0 : styleId,
                  sfId,
                  GetFormulaValue(v),
                  GetCellType(v, true));
            }
          } else {
            // We can also have a single cell array formula
            if (f.IsArray) {
              cache.AppendFormat(
                  "<c r=\"{0}\" s=\"{1}\"{5}><f ref=\"{2}\" t=\"array\">{3}</f>{4}</c>",
                  cse.CellAddress,
                  styleId < 0 ? 0 : styleId,
                  string.Format("{0}:{1}", f.Address, f.Address),
                  SecurityElement.Escape(f.Formula),
                  GetFormulaValue(v),
                  GetCellType(v, true));
            } else {
              cache.AppendFormat("<c r=\"{0}\" s=\"{1}\">", f.Address, styleId < 0 ? 0 : styleId);
              cache.AppendFormat(
                  "<f>{0}</f>{1}</c>",
                  SecurityElement.Escape(f.Formula),
                  GetFormulaValue(v));
            }
          }
        } else if (formula != null && formula.ToString() != "") {
          cache.AppendFormat(
              "<c r=\"{0}\" s=\"{1}\"{2}>",
              cse.CellAddress,
              styleId < 0 ? 0 : styleId,
              GetCellType(v, true));
          cache.AppendFormat(
              "<f>{0}</f>{1}</c>",
              SecurityElement.Escape(formula.ToString()),
              GetFormulaValue(v));
        } else {
          if (v == null && styleId > 0) {
            cache.AppendFormat(
                "<c r=\"{0}\" s=\"{1}\" />",
                cse.CellAddress,
                styleId < 0 ? 0 : styleId);
          } else if (v != null) {
            if ((v.GetType().IsPrimitive
                        || v is double
                        || v is decimal
                        || v is DateTime
                        || v is TimeSpan)) {
              //string sv = GetValueForXml(v);
              cache.AppendFormat(
                  "<c r=\"{0}\" s=\"{1}\" {2}>",
                  cse.CellAddress,
                  styleId < 0 ? 0 : styleId,
                  GetCellType(v));
              cache.AppendFormat("{0}</c>", GetFormulaValue(v));
            } else {
              int ix;
              if (!ss.ContainsKey(v.ToString())) {
                ix = ss.Count;
                ss.Add(
                    v.ToString(),
                    new() {
                      isRichText = _flags.GetFlagValue(cse.Row, cse.Column, CellFlags.RichText),
                      pos = ix,
                    });
              } else {
                ix = ss[v.ToString()].pos;
              }
              cache.AppendFormat(
                  "<c r=\"{0}\" s=\"{1}\" t=\"s\">",
                  cse.CellAddress,
                  styleId < 0 ? 0 : styleId);
              cache.AppendFormat("<v>{0}</v></c>", ix);
            }
          }
        }
      } else //ExcelRow
      {
        WriteRow(cache, cellXfs, row, cse.Row);
        row = cse.Row;
      }
      if (cache.Length > 0x600000) {
        sw.Write(cache.ToString());
        cache = new();
      }
    }

    if (row != -1) {
      cache.Append("</row>");
    }
    cache.Append("</sheetData>");
    sw.Write(cache.ToString());
    sw.Flush();
  }

  private object GetFormulaValue(object v) {
    if (v != null && v.ToString() != "") {
      return "<v>" + SecurityElement.Escape(GetValueForXml(v)) + "</v>"; //Fixes issue 15071
    }
    return "";
  }

  private string GetCellType(object v, bool allowStr = false) {
    if (v is bool) {
      return " t=\"b\"";
    }
    if ((v is double d && double.IsInfinity(d)) || v is ExcelErrorValue) {
      return " t=\"e\"";
    }
    if (allowStr
        && v != null
        && !(v.GetType().IsPrimitive
                || v is double
                || v is decimal
                || v is DateTime
                || v is TimeSpan)) {
      return " t=\"str\"";
    }
    return "";
  }

  private string GetValueForXml(object v) {
    string s;
    try {
      if (v is DateTime time) {
        double sdv = time.ToOADate();

        if (Workbook.Date1904) {
          sdv -= ExcelWorkbook._date1904Offset;
        }

        s = sdv.ToString(CultureInfo.InvariantCulture);
      } else if (v is TimeSpan span) {
        s = new DateTime(span.Ticks).ToOADate().ToString(CultureInfo.InvariantCulture);
        ;
      } else if (v.GetType().IsPrimitive || v is double || v is decimal) {
        if (v is double d && double.IsNaN(d)) {
          s = "";
        } else if (v is double d1 && double.IsInfinity(d1)) {
          s = "#NUM!";
        } else {
          s = Convert
              .ToDouble(v, CultureInfo.InvariantCulture)
              .ToString("R15", CultureInfo.InvariantCulture);
        }
      } else {
        s = v.ToString();
      }
    } catch {
      s = "0";
    }
    return s;
  }

  private void WriteRow(
      StringBuilder cache,
      ExcelStyleCollection<ExcelXfs> cellXfs,
      int prevRow,
      int row) {
    if (prevRow != -1) {
      cache.Append("</row>");
    }
    //ulong rowID = ExcelRow.GetRowID(SheetID, row);
    cache.AppendFormat("<row r=\"{0}\" ", row);
    RowInternal currRow = _values.GetValue(row, 0) as RowInternal;
    if (currRow != null) {
      if (currRow.Hidden) {
        cache.Append("ht=\"0\" hidden=\"1\" ");
      } else if (currRow.Height != DefaultRowHeight && currRow.Height >= 0) {
        cache.AppendFormat(CultureInfo.InvariantCulture, "ht=\"{0}\" ", currRow.Height);
        if (currRow.CustomHeight) {
          cache.Append("customHeight=\"1\" ");
        }
      }

      if (currRow.OutlineLevel > 0) {
        cache.AppendFormat("outlineLevel =\"{0}\" ", currRow.OutlineLevel);
        if (currRow.Collapsed) {
          if (currRow.Hidden) {
            cache.Append(" collapsed=\"1\" ");
          } else {
            cache.Append(" collapsed=\"1\" hidden=\"1\" "); //Always hidden
          }
        }
      }
      if (currRow.Phonetic) {
        cache.Append("ph=\"1\" ");
      }
    }
    var s = _styles.GetValue(row, 0);
    if (s > 0) {
      cache.AppendFormat("s=\"{0}\" customFormat=\"1\"", cellXfs[s].newID);
    }
    cache.Append(">");
  }

  /// <summary>
  /// Update xml with hyperlinks
  /// </summary>
  /// <param name="sw">The stream</param>
  private void UpdateHyperLinks(StreamWriter sw) {
    Dictionary<string, string> hyps = new Dictionary<string, string>();
    var cse = new CellsStoreEnumerator<Uri>(_hyperLinks);
    bool first = true;
    //foreach (ulong cell in _hyperLinks)
    while (cse.Next()) {
      if (first) {
        sw.Write("<hyperlinks>");
        first = false;
      }
      //int row, col;
      var uri = _hyperLinks.GetValue(cse.Row, cse.Column);
      //ExcelCell cell = _cells[cellId] as ExcelCell;
      if (uri is ExcelHyperLink link && !string.IsNullOrEmpty(link.ReferenceAddress)) {
        sw.Write(
            "<hyperlink ref=\"{0}\" location=\"{1}\" {2}{3}/>",
            Cells[cse.Row, cse.Column, cse.Row + link.RowSpann, cse.Column + link.ColSpann].Address,
            ExcelCellBase.GetFullAddress(
                SecurityElement.Escape(Name),
                SecurityElement.Escape(link.ReferenceAddress)),
            string.IsNullOrEmpty(link.Display)
                ? ""
                : "display=\"" + SecurityElement.Escape(link.Display) + "\" ",
            string.IsNullOrEmpty(link.ToolTip)
                ? ""
                : "tooltip=\"" + SecurityElement.Escape(link.ToolTip) + "\" ");
      } else if (uri != null) {
        string id;
        Uri hyp;
        if (uri is ExcelHyperLink hyperLink) {
          hyp = hyperLink.OriginalUri;
        } else {
          hyp = uri;
        }
        if (hyps.ContainsKey(hyp.OriginalString)) {
          id = hyps[hyp.OriginalString];
        } else {
          var relationship = Part.CreateRelationship(
              hyp,
              TargetMode.External,
              ExcelPackage._schemaHyperlink);
          if (uri is ExcelHyperLink hl) {
            sw.Write(
                "<hyperlink ref=\"{0}\" {2}{3}r:id=\"{1}\" />",
                ExcelCellBase.GetAddress(cse.Row, cse.Column),
                relationship.Id,
                string.IsNullOrEmpty(hl.Display)
                    ? ""
                    : "display=\"" + SecurityElement.Escape(hl.Display) + "\" ",
                string.IsNullOrEmpty(hl.ToolTip)
                    ? ""
                    : "tooltip=\"" + SecurityElement.Escape(hl.ToolTip) + "\" ");
          } else {
            sw.Write(
                "<hyperlink ref=\"{0}\" r:id=\"{1}\" />",
                ExcelCellBase.GetAddress(cse.Row, cse.Column),
                relationship.Id);
          }
          id = relationship.Id;
        }
        //cell.HyperLinkRId = id;
      }
    }
    if (!first) {
      sw.Write("</hyperlinks>");
    }
  }

  /// <summary>
  /// Create the hyperlinks node in the XML
  /// </summary>
  /// <returns></returns>
  private XmlNode CreateHyperLinkCollection() {
    XmlElement hl = _worksheetXml.CreateElement("hyperlinks", ExcelPackage._schemaMain);
    XmlNode prevNode = _worksheetXml.SelectSingleNode(
        "//d:conditionalFormatting",
        NameSpaceManager);
    if (prevNode == null) {
      prevNode = _worksheetXml.SelectSingleNode("//d:mergeCells", NameSpaceManager);
      if (prevNode == null) {
        prevNode = _worksheetXml.SelectSingleNode("//d:sheetData", NameSpaceManager);
      }
    }
    return _worksheetXml.DocumentElement.InsertAfter(hl, prevNode);
  }

  /// <summary>
  /// Dimension address for the worksheet.
  /// Top left cell to Bottom right.
  /// If the worksheet has no cells, null is returned
  /// </summary>
  public ExcelAddressBase Dimension {
    get {
      CheckSheetType();
      if (_values.GetDimension(out var fromRow, out var fromCol, out var toRow, out var toCol)) {
        var addr = new ExcelAddressBase(fromRow, fromCol, toRow, toCol);
        addr._ws = Name;
        return addr;
      }
      return null;
    }
  }

  private ExcelSheetProtection _protection;

  /// <summary>
  /// Access to sheet protection properties
  /// </summary>
  public ExcelSheetProtection Protection {
    get {
      if (_protection == null) {
        _protection = new(NameSpaceManager, TopNode, this);
      }
      return _protection;
    }
  }

  private ExcelProtectedRangeCollection _protectedRanges;

  public ExcelProtectedRangeCollection ProtectedRanges {
    get {
      if (_protectedRanges == null) {
        _protectedRanges = new(NameSpaceManager, TopNode, this);
      }
      return _protectedRanges;
    }
  }

  private ExcelTableCollection _tables;

  /// <summary>
  /// Tables defined in the worksheet.
  /// </summary>
  public ExcelTableCollection Tables {
    get {
      CheckSheetType();
      if (Workbook._nextTableID == int.MinValue) {
        Workbook.ReadAllTables();
      }
      if (_tables == null) {
        _tables = new(this);
      }
      return _tables;
    }
  }

  private ExcelPivotTableCollection _pivotTables;

  /// <summary>
  /// Pivottables defined in the worksheet.
  /// </summary>
  public ExcelPivotTableCollection PivotTables {
    get {
      CheckSheetType();
      if (_pivotTables == null) {
        if (Workbook._nextPivotTableID == int.MinValue) {
          Workbook.ReadAllTables();
        }
        _pivotTables = new(this);
      }
      return _pivotTables;
    }
  }

  private ExcelConditionalFormattingCollection _conditionalFormatting;

  /// <summary>
  /// ConditionalFormatting defined in the worksheet. Use the Add methods to create ConditionalFormatting and add them to the worksheet. Then
  /// set the properties on the instance returned.
  /// </summary>
  /// <seealso cref="ExcelConditionalFormattingCollection"/>
  public ExcelConditionalFormattingCollection ConditionalFormatting {
    get {
      CheckSheetType();
      if (_conditionalFormatting == null) {
        _conditionalFormatting = new(this);
      }
      return _conditionalFormatting;
    }
  }

  private ExcelDataValidationCollection _dataValidation;

  /// <summary>
  /// DataValidation defined in the worksheet. Use the Add methods to create DataValidations and add them to the worksheet. Then
  /// set the properties on the instance returned.
  /// </summary>
  /// <seealso cref="ExcelDataValidationCollection"/>
  public ExcelDataValidationCollection DataValidations {
    get {
      CheckSheetType();
      if (_dataValidation == null) {
        _dataValidation = new(this);
      }
      return _dataValidation;
    }
  }

  /// <summary>
  /// Returns the style ID given a style name.
  /// The style ID will be created if not found, but only if the style name exists!
  /// </summary>
  /// <param name="styleName"></param>
  /// <returns></returns>
  internal int GetStyleId(string styleName) {
    ExcelNamedStyleXml namedStyle = null;
    Workbook.Styles.NamedStyles.FindById(styleName, ref namedStyle);
    if (namedStyle.XfId == int.MinValue) {
      namedStyle.XfId = Workbook.Styles.CellXfs.FindIndexById(namedStyle.Style.Id);
    }
    return namedStyle.XfId;
  }

  /// <summary>
  /// The workbook object
  /// </summary>
  public ExcelWorkbook Workbook => _workbook;

  /// <summary>
  /// Get the next ID from a shared formula or an Array formula
  /// Sharedforumlas will have an id from 0-x. Array formula ids start from 0x4000001-.
  /// </summary>
  /// <param name="isArray">If the formula is an array formula</param>
  /// <returns></returns>
  internal int GetMaxShareFunctionIndex(bool isArray) {
    int i = _sharedFormulas.Count + 1;
    if (isArray) {
      i |= 0x40000000;
    }

    while (_sharedFormulas.ContainsKey(i)) {
      i++;
    }
    return i;
  }

  internal void SetHfLegacyDrawingRel(string relId) {
    SetXmlNodeString("d:legacyDrawingHF/@r:id", relId);
  }

  internal void RemoveLegacyDrawingRel(string relId) {
    var n = WorksheetXml.DocumentElement.SelectSingleNode(
        string.Format("d:legacyDrawing[@r:id=\"{0}\"]", relId),
        NameSpaceManager);
    if (n != null) {
      n.ParentNode.RemoveChild(n);
    }
  }

  internal void UpdateCellsWithDate1904Setting() {
    var cse = new CellsStoreEnumerator<object>(_values);
    var offset = Workbook.Date1904 ? -ExcelWorkbook._date1904Offset : ExcelWorkbook._date1904Offset;
    while (cse.MoveNext()) {
      if (cse.Value is DateTime time) {
        try {
          double sdv = time.ToOADate();
          sdv += offset;

          cse.Value = DateTime.FromOADate(sdv);
        } catch {}
      }
    }
  }

  public string GetFormula(int row, int col) {
    var v = _formulas.GetValue(row, col);
    if (v is int i) {
      return _sharedFormulas[i].GetFormula(row, col, Name);
    }
    if (v != null) {
      return v.ToString();
    }
    return "";
  }

  public string GetFormulaR1C1(int row, int col) {
    var v = _formulas.GetValue(row, col);
    if (v is int i) {
      var sf = _sharedFormulas[i];
      return ExcelCellBase.TranslateToR1C1(
          Formulas.RemoveDummyFunction(sf.Formula),
          sf.StartRow,
          sf.StartCol);
    }
    if (v != null) {
      return ExcelCellBase.TranslateToR1C1(Formulas.RemoveDummyFunction(v.ToString()), row, col);
    }
    return "";
  }

  public string GetFormulaR1C1_V1(int row, int col) {
    var v = _formulas.GetValue(row, col);
    if (v is int i) {
      var sf = _sharedFormulas[i];
      return ExcelCellBase.TranslateToR1C1_V1(
          Formulas.RemoveDummyFunction(sf.Formula),
          sf.StartRow,
          sf.StartCol);
    }
    if (v != null) {
      return ExcelCellBase.TranslateToR1C1_V1(Formulas.RemoveDummyFunction(v.ToString()), row, col);
    }
    return "";
  }

  public bool IsArrayFormula(int row, int col) =>
    _flags.GetFlagValue(row, col, CellFlags.ArrayFormula);

  public string GetArrayFormulaAddress(int row, int col) {
    var v = _formulas.GetValue(row, col);
    if ((v is int i) && (_sharedFormulas[i].IsArray)) {
      return _sharedFormulas[i].Address;
    }
    return "";
  }

  public int GetStyleId(int row, int col) {
    int styleId = 0;
    if (!_styles.Exists(row, col, ref styleId) && !_styles.Exists(row, 0, ref styleId)) {
      styleId = _styles.GetValue(0, col);
    }
    return styleId;
  }

  /// <summary>
  /// Get the ExcelColumn for column (span ColumnMin and ColumnMax)
  /// </summary>
  /// <param name="column"></param>
  /// <returns></returns>
  internal ExcelColumn GetColumn(int column) {
    var c = _values.GetValue(0, column) as ExcelColumn;
    if (c == null) {
      int row = 0,
          col = column;
      if (_values.PrevCell(ref row, ref col)) {
        c = _values.GetValue(0, col) as ExcelColumn;
        if (c != null && c.ColumnMax >= column) {
          return c;
        }
        return null;
      }
    }
    return c;
  }

  public bool Equals(ExcelWorksheet x, ExcelWorksheet y) {
    return x.Name == y.Name
        && x.SheetID == y.SheetID
        && x.WorksheetXml.OuterXml == y.WorksheetXml.OuterXml;
  }

  public int GetHashCode(ExcelWorksheet obj) {
    return obj.WorksheetXml.OuterXml.GetHashCode();
  }
}