/*******************************************************************************
 * 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		        2010-01-28
 * Jan Källman		    License changed GPL-->LGPL  2011-12-27
 * Eyal Seagull		    Conditional Formatting      2012-04-03
 *******************************************************************************/

using System;
using System.Collections;
using System.Collections.Generic;
using System.Globalization;
using System.Security;
using System.Text;
using System.Xml;

namespace AppsheetEpplus;

/// <summary>
/// A range of cells
/// </summary>
public class ExcelRangeBase
    : ExcelAddress,
      IExcelCell,
      IDisposable,
      IEnumerable<ExcelRangeBase>,
      IEnumerator<ExcelRangeBase> {
  /// <summary>
  /// Reference to the worksheet
  /// </summary>
  protected ExcelWorksheet _worksheet;
  internal ExcelWorkbook _workbook;

  private delegate void ChangePropHandler(SetValueHandler method, object value);

  private delegate void SetValueHandler(object value, int row, int col);

  private ChangePropHandler _changePropMethod;
  private int _styleID;

  private class CopiedCell {
    internal int Row { get; set; }

    internal int Column { get; set; }

    internal object Value { get; set; }

    internal string Type { get; set; }

    internal object Formula { get; set; }

    internal int? StyleID { get; set; }

    internal Uri HyperLink { get; set; }

    internal ExcelComment Comment { get; set; }

    internal Byte Flag { get; set; }
  }

  //private class CopiedFlag
  //{
  //    internal int Row { get; set; }
  //    internal int Column { get; set; }
  //    internal Byte Flag { get; set; }
  //}


  internal ExcelRangeBase(ExcelWorksheet worksheet) {
    _worksheet = worksheet;
    _ws = _worksheet.Name;
    _workbook = _worksheet.Workbook;
    AddressChange += ExcelRangeBase_AddressChange;
    SetDelegate();
  }

  private void ExcelRangeBase_AddressChange(object sender, EventArgs e) {
    if (Table != null) {
      SetRcFromTable(_workbook, null);
    }
    SetDelegate();
  }

  internal ExcelRangeBase(ExcelWorksheet worksheet, string address)
      : base(worksheet == null ? "" : worksheet.Name, address) {
    _worksheet = worksheet;
    _workbook = worksheet.Workbook;
    SetRcFromTable(_workbook, null);
    if (string.IsNullOrEmpty(_ws)) {
      _ws = _worksheet == null ? "" : _worksheet.Name;
    }
    AddressChange += ExcelRangeBase_AddressChange;
    SetDelegate();
  }

  internal ExcelRangeBase(ExcelWorkbook wb, ExcelWorksheet xlWorksheet, string address, bool isName)
      : base(xlWorksheet == null ? "" : xlWorksheet.Name, address, isName) {
    SetRcFromTable(wb, null);
    _worksheet = xlWorksheet;
    _workbook = wb;
    if (string.IsNullOrEmpty(_ws)) {
      _ws = xlWorksheet?.Name;
    }
    AddressChange += ExcelRangeBase_AddressChange;
    SetDelegate();
  }

  ~ExcelRangeBase() {
    //this.AddressChange -= new EventHandler(ExcelRangeBase_AddressChange);
  }

  private void SetDelegate() {
    if (_fromRow == -1) {
      _changePropMethod = SetUnknown;
    }
    //Single cell
    else if (_fromRow == _toRow && _fromCol == _toCol && Addresses == null) {
      _changePropMethod = SetSingle;
    }
    //Range (ex A1:A2)
    else if (Addresses == null) {
      _changePropMethod = SetRange;
    }
    //Multi Range (ex A1:A2,C1:C2)
    else {
      _changePropMethod = SetMultiRange;
    }
  }

  /// <summary>
  /// We dont know the address yet. Set the delegate first time a property is set.
  /// </summary>
  /// <param name="valueMethod"></param>
  /// <param name="value"></param>
  private void SetUnknown(SetValueHandler valueMethod, object value) {
    //Address is not set use, selected range
    if (_fromRow == -1) {
      SetToSelectedRange();
    }
    SetDelegate();
    _changePropMethod(valueMethod, value);
  }

  /// <summary>
  /// Set a single cell
  /// </summary>
  /// <param name="valueMethod"></param>
  /// <param name="value"></param>
  private void SetSingle(SetValueHandler valueMethod, object value) {
    valueMethod(value, _fromRow, _fromCol);
  }

  /// <summary>
  /// Set a range
  /// </summary>
  /// <param name="valueMethod"></param>
  /// <param name="value"></param>
  private void SetRange(SetValueHandler valueMethod, object value) {
    SetValueAddress(this, valueMethod, value);
  }

  /// <summary>
  /// Set a multirange (A1:A2,C1:C2)
  /// </summary>
  /// <param name="valueMethod"></param>
  /// <param name="value"></param>
  private void SetMultiRange(SetValueHandler valueMethod, object value) {
    SetValueAddress(this, valueMethod, value);
    foreach (var address in Addresses) {
      SetValueAddress(address, valueMethod, value);
    }
  }

  /// <summary>
  /// Set the property for an address
  /// </summary>
  /// <param name="address"></param>
  /// <param name="valueMethod"></param>
  /// <param name="value"></param>
  private void SetValueAddress(ExcelAddress address, SetValueHandler valueMethod, object value) {
    IsRangeValid("");
    if (_fromRow == 1
        && _fromCol == 1
        && _toRow == ExcelPackage.MaxRows
        && _toCol
            == ExcelPackage.MaxColumns) //Full sheet (ex ws.Cells.Value=0). Set value for A1 only to avoid hanging
    {
      throw (new ArgumentException(
              "Can't reference all cells. Please use the indexer to set the range"));
    }
    for (int col = address.Start.Column; col <= address.End.Column; col++) {
      for (int row = address.Start.Row; row <= address.End.Row; row++) {
        valueMethod(value, row, col);
      }
    }
  }

  private void Set_StyleID(object value, int row, int col) {
    _worksheet._styles.SetValue(row, col, (int)value);
  }

  private void Set_Value(object value, int row, int col) {
    //ExcelCell c = _worksheet.Cell(row, col);
    var sfi = _worksheet._formulas.GetValue(row, col);
    if (sfi is int) {
      SplitFormulas(_worksheet.Cells[row, col]);
    }
    if (sfi != null) {
      _worksheet._formulas.SetValue(row, col, string.Empty);
    }
    _worksheet._values.SetValue(row, col, value);
  }

  private void Set_Formula(object value, int row, int col) {
    //ExcelCell c = _worksheet.Cell(row, col);
    var f = _worksheet._formulas.GetValue(row, col);
    if (f is int i && i >= 0) {
      SplitFormulas(_worksheet.Cells[row, col]);
    }

    string formula = (value == null ? string.Empty : value.ToString());
    if (formula == string.Empty) {
      _worksheet._formulas.SetValue(row, col, string.Empty);
    } else {
      if (formula[0] == '=') {
        value = formula.Substring(1, formula.Length - 1); // remove any starting equalsign.
      }
      _worksheet._formulas.SetValue(row, col, formula);
      _worksheet._values.SetValue(row, col, null);
    }
  }

  /// <summary>
  /// Handles shared formulas
  /// </summary>
  /// <param name="value">The  formula</param>
  /// <param name="address">The address of the formula</param>
  /// <param name="isArray">If the forumla is an array formula.</param>
  private void Set_SharedFormula(string value, ExcelAddress address, bool isArray) {
    if (_fromRow == 1
        && _fromCol == 1
        && _toRow == ExcelPackage.MaxRows
        && _toCol
            == ExcelPackage.MaxColumns) //Full sheet (ex ws.Cells.Value=0). Set value for A1 only to avoid hanging
    {
      throw (new InvalidOperationException("Can't set a formula for the entire worksheet"));
    }
    if (address.Start.Row == address.End.Row
        && address.Start.Column == address.End.Column
        && !isArray) //is it really a shared formula? Arrayformulas can be one cell only
    {
      //Nope, single cell. Set the formula
      Set_Formula(value, address.Start.Row, address.Start.Column);
      return;
    }
    //RemoveFormuls(address);
    CheckAndSplitSharedFormula(address);
    ExcelWorksheet.Formulas f = new ExcelWorksheet.Formulas(SourceCodeTokenizer.Default);
    f.Formula = value;
    f.Index = _worksheet.GetMaxShareFunctionIndex(isArray);
    f.Address = address.FirstAddress;
    f.StartCol = address.Start.Column;
    f.StartRow = address.Start.Row;
    f.IsArray = isArray;

    _worksheet._sharedFormulas.Add(f.Index, f);
    //_worksheet.Cell(address.Start.Row, address.Start.Column).SharedFormulaID = f.Index;
    //_worksheet.Cell(address.Start.Row, address.Start.Column).Formula = value;

    for (int col = address.Start.Column; col <= address.End.Column; col++) {
      for (int row = address.Start.Row; row <= address.End.Row; row++) {
        //_worksheet.Cell(row, col).SharedFormulaID = f.Index;
        _worksheet._formulas.SetValue(row, col, f.Index);
        _worksheet._values.SetValue(row, col, null);
      }
    }
  }

  private void Set_HyperLink(object value, int row, int col) {
    //_worksheet.Cell(row, col).Hyperlink = value as Uri;
    if (value is Uri uri) {
      _worksheet._hyperLinks.SetValue(row, col, uri);

      if (uri is ExcelHyperLink link) {
        _worksheet._values.SetValue(row, col, link.Display);
      } else {
        _worksheet._values.SetValue(row, col, uri.OriginalString);
      }
    } else {
      _worksheet._hyperLinks.SetValue(row, col, null);
      _worksheet._values.SetValue(row, col, null);
    }
  }

  private void Set_IsArrayFormula(object value, int row, int col) {
    _worksheet._flags.SetFlagValue(row, col, (bool)value, CellFlags.ArrayFormula);
  }

  private void Set_IsRichText(object value, int row, int col) {
    //_worksheet.Cell(row, col).IsRichText = (bool)value;
    _worksheet._flags.SetFlagValue(row, col, (bool)value, CellFlags.RichText);
  }

  private void Exists_Comment(object value, int row, int col) {
    ulong cellId = GetCellId(_worksheet.SheetID, row, col);
    if (_worksheet.Comments._comments.ContainsKey(cellId)) {
      throw (new InvalidOperationException(
              string.Format(
                  "Cell {0} already contain a comment.",
                  new ExcelCellAddress(row, col).Address)));
    }
  }

  private void SetToSelectedRange() {
    if (_worksheet.View.SelectedRange == "") {
      Address = "A1";
    } else {
      Address = _worksheet.View.SelectedRange;
    }
  }

  private void IsRangeValid(string type) {
    if (_fromRow <= 0) {
      if (_address == "") {
        SetToSelectedRange();
      } else {
        if (type == "") {
          throw (new InvalidOperationException(
                  string.Format("Range is not valid for this operation: {0}", _address)));
        }
        throw (new InvalidOperationException(
                string.Format("Range is not valid for {0} : {1}", type, _address)));
      }
    }
  }

  /// <summary>
  /// The styleobject for the range.
  /// </summary>
  public ExcelStyle Style {
    get {
      IsRangeValid("styling");
      int s = 0;
      if (!_worksheet._styles.Exists(
          _fromRow,
          _fromCol,
          ref s)) //Cell exists
      {
        if (!_worksheet._styles.Exists(
            _fromRow,
            0,
            ref s)) //No, check Row style
        {
          var c = Worksheet.GetColumn(_fromCol);
          if (c == null) {
            s = 0;
          } else {
            s = c.StyleID;
          }
        }
      }
      return _worksheet.Workbook.Styles.GetStyleObject(s, _worksheet.PositionID, Address);
    }
  }

  /// <summary>
  /// The named style
  /// </summary>
  public string StyleName {
    get {
      IsRangeValid("styling");
      int xfId;
      if (_fromRow == 1 && _toRow == ExcelPackage.MaxRows) {
        xfId = GetColumnStyle(_fromCol);
      } else if (_fromCol == 1 && _toCol == ExcelPackage.MaxColumns) {
        xfId = 0;
        if (!_worksheet._styles.Exists(_fromRow, 0, ref xfId)) {
          xfId = GetColumnStyle(_fromCol);
        }
      } else {
        xfId = 0;
        if (!_worksheet._styles.Exists(_fromRow, _fromCol, ref xfId)) {
          if (!_worksheet._styles.Exists(_fromRow, 0, ref xfId)) {
            xfId = GetColumnStyle(_fromCol);
          }
        }
      }
      int nsId;
      if (xfId <= 0) {
        nsId = Style.Styles.CellXfs[0].XfId;
      } else {
        nsId = Style.Styles.CellXfs[xfId].XfId;
      }
      foreach (var ns in Style.Styles.NamedStyles) {
        if (ns.StyleXfId == nsId) {
          return ns.Name;
        }
      }

      return "";
    }
    set {
      _styleID = _worksheet.Workbook.Styles.GetStyleIdFromName(value);
      int col = _fromCol;
      if (_fromRow == 1
          && _toRow
              == ExcelPackage.MaxRows) //Full column
      {
        ExcelColumn column;
        //Get the startcolumn
        //ulong colID = ExcelColumn.GetColumnID(_worksheet.SheetID, column);
        var c = _worksheet.GetValue(0, _fromCol);
        if (c == null) {
          column = _worksheet.Column(_fromCol);
          //if (_worksheet._values.PrevCell(ref row, ref col))
          //{
          //    var prevCol = (ExcelColumn)_worksheet._values.GetValue(row, col);
          //    column = prevCol.Clone(_worksheet, column);
          //    prevCol.ColumnMax = column - 1;
          //}
        } else {
          column = (ExcelColumn)c;
        }

        column.StyleName = value;
        column.StyleID = _styleID;

        //var index = _worksheet._columns.IndexOf(colID);
        var cols = new CellsStoreEnumerator<object>(_worksheet._values, 0, _fromCol + 1, 0, _toCol);
        if (cols.Next()) {
          col = _fromCol;
          while (column.ColumnMin <= _toCol) {
            if (column.ColumnMax > _toCol) {
              var newCol = _worksheet.CopyColumn(column, _toCol + 1, column.ColumnMax);
              column.ColumnMax = _toCol;
            }

            column._styleName = value;
            column.StyleID = _styleID;

            if (cols.Value == null) {
              break;
            }
            var nextCol = (ExcelColumn)cols.Value;
            if (column.ColumnMax < nextCol.ColumnMax - 1) {
              column.ColumnMax = nextCol.ColumnMax - 1;
            }
            column = nextCol;
            cols.Next();
          }
        }
        if (column.ColumnMax < _toCol) {
          column.ColumnMax = _toCol;
        }
        //if (column.ColumnMin == column)
        //{
        //    column.ColumnMax = _toCol;
        //}
        //else if (column._columnMax < _toCol)
        //{
        //    var newCol = _worksheet.Column(column._columnMax + 1) as ExcelColumn;
        //    newCol._columnMax = _toCol;

        //    newCol._styleID = _styleID;
        //    newCol._styleName = value;
        //}
        if (_fromCol == 1
            && _toCol
                == ExcelPackage.MaxColumns) //FullRow
        {
          var rows = new CellsStoreEnumerator<object>(
              _worksheet._values,
              1,
              0,
              ExcelPackage.MaxRows,
              0);
          rows.Next();
          while (rows.Value != null) {
            _worksheet._styles.SetValue(rows.Row, 0, _styleID);
            if (!rows.Next()) {
              break;
            }
          }
        }
      } else if (_fromCol == 1
          && _toCol
              == ExcelPackage.MaxColumns) //FullRow
      {
        for (int r = _fromRow; r <= _toRow; r++) {
          _worksheet.Row(r)._styleName = value;
          _worksheet.Row(r).StyleID = _styleID;
        }
      }

      if (!((_fromRow == 1 && _toRow == ExcelPackage.MaxRows)
                  || (_fromCol == 1
                          && _toCol
                              == ExcelPackage.MaxColumns))) //Cell specific
      {
        for (int c = _fromCol; c <= _toCol; c++) {
          for (int r = _fromRow; r <= _toRow; r++) {
            _worksheet._styles.SetValue(r, c, _styleID);
          }
        }
      } else //Only set name on created cells. (uncreated cells is set on full row or full column).
      {
        var cells = new CellsStoreEnumerator<object>(
            _worksheet._values,
            _fromRow,
            _fromCol,
            _toRow,
            _toCol);
        while (cells.Next()) {
          _worksheet._styles.SetValue(cells.Row, cells.Column, _styleID);
        }
      }
      //_changePropMethod(Set_StyleName, value);
    }
  }

  private int GetColumnStyle(int col) {
    object c = null;
    if (_worksheet._values.Exists(0, col, ref c)) {
      return (c as ExcelColumn).StyleID;
    }
    int row = 0;
    if (_worksheet._values.PrevCell(ref row, ref col)) {
      var column = _worksheet._values.GetValue(row, col) as ExcelColumn;
      if (column.ColumnMax >= col) {
        return _worksheet._styles.GetValue(row, col);
      }
    }
    return 0;
  }

  /// <summary>
  /// The style ID.
  /// It is not recomended to use this one. Use Named styles as an alternative.
  /// If you do, make sure that you use the Style.UpdateXml() method to update any new styles added to the workbook.
  /// </summary>
  public int StyleID {
    get {
      int s = 0;
      if (!_worksheet._styles.Exists(_fromRow, _fromCol, ref s)) {
        if (!_worksheet._styles.Exists(_fromRow, 0, ref s)) {
          s = _worksheet._styles.GetValue(0, _fromCol);
        }
      }
      return s;
    }
    set => _changePropMethod(Set_StyleID, value);
  }

  /// <summary>
  /// Set the range to a specific value
  /// </summary>
  public object Value {
    get {
      if (IsName) {
        if (_worksheet == null) {
          return _workbook._names[_address].NameValue;
        }
        return _worksheet.Names[_address].NameValue;
      }
      if (_fromRow == _toRow && _fromCol == _toCol) {
        return _worksheet.GetValue(_fromRow, _fromCol);
      }
      return GetValueArray();
    }
    set {
      if (IsName) {
        if (_worksheet == null) {
          _workbook._names[_address].NameValue = value;
        } else {
          _worksheet.Names[_address].NameValue = value;
        }
      } else {
        _changePropMethod(Set_Value, value);
      }
    }
  }

  private object GetValueArray() {
    ExcelAddressBase addr;
    if (_fromRow == 1
        && _fromCol == 1
        && _toRow == ExcelPackage.MaxRows
        && _toCol == ExcelPackage.MaxColumns) {
      addr = _worksheet.Dimension;
      if (addr == null) {
        return null;
      }
    } else {
      addr = this;
    }
    object[,] v = new object[addr._toRow - addr._fromRow + 1, addr._toCol - addr._fromCol + 1];

    for (int col = addr._fromCol; col <= addr._toCol; col++) {
      for (int row = addr._fromRow; row <= addr._toRow; row++) {
        if (_worksheet._values.Exists(row, col)) {
          if (_worksheet._flags.GetFlagValue(row, col, CellFlags.RichText)) {
            v[row - addr._fromRow, col - addr._fromCol] = GetRichText(row, col).Text;
          } else {
            v[row - addr._fromRow, col - addr._fromCol] = _worksheet._values.GetValue(row, col);
          }
        }
      }
    }
    return v;
  }

  /// <summary>
  /// Returns the formatted value.
  /// </summary>
  public string Text => GetFormattedText(false);

  private string GetFormattedText(bool forWidthCalc) {
    object v = Value;
    if (v == null) {
      return "";
    }
    var styles = Worksheet.Workbook.Styles;
    var nfId = styles.CellXfs[StyleID].NumberFormatId;
    ExcelNumberFormatXml.ExcelFormatTranslator nf = null;
    for (int i = 0; i < styles.NumberFormats.Count; i++) {
      if (nfId == styles.NumberFormats[i].NumFmtId) {
        nf = styles.NumberFormats[i].FormatTranslator;
        break;
      }
    }

    string format,
        textFormat;
    if (forWidthCalc) {
      format = nf.NetFormatForWidth;
      textFormat = nf.NetTextFormatForWidth;
    } else {
      format = nf.NetFormat;
      textFormat = nf.NetTextFormat;
    }

    return FormatValue(v, nf, format, textFormat);
  }

  internal static string FormatValue(
      object v,
      ExcelNumberFormatXml.ExcelFormatTranslator nf,
      string format,
      string textFormat) {
    if (v is decimal || v.GetType().IsPrimitive) {
      double d;
      try {
        d = Convert.ToDouble(v);
      } catch {
        return "";
      }

      if (nf.DataType == ExcelNumberFormatXml.eFormatType.Number) {
        if (string.IsNullOrEmpty(nf.FractionFormat)) {
          return d.ToString(format, nf.Culture);
        }
        return nf.FormatFraction(d);
      }
      if (nf.DataType == ExcelNumberFormatXml.eFormatType.DateTime) {
        var date = DateTime.FromOADate(d);
        return date.ToString(format, nf.Culture);
      }
    } else if (v is DateTime time) {
      if (nf.DataType == ExcelNumberFormatXml.eFormatType.DateTime) {
        return time.ToString(format, nf.Culture);
      }
      double d = time.ToOADate();
      if (string.IsNullOrEmpty(nf.FractionFormat)) {
        return d.ToString(format, nf.Culture);
      }
      return nf.FormatFraction(d);
    } else if (v is TimeSpan span) {
      if (nf.DataType == ExcelNumberFormatXml.eFormatType.DateTime) {
        return new DateTime(span.Ticks).ToString(format, nf.Culture);
      }
      double d = (new DateTime(span.Ticks)).ToOADate();
      if (string.IsNullOrEmpty(nf.FractionFormat)) {
        return d.ToString(format, nf.Culture);
      }
      return nf.FormatFraction(d);
    } else {
      if (textFormat == "") {
        return v.ToString();
      }
      return string.Format(textFormat, v);
    }
    return v.ToString();
  }

  /// <summary>
  /// Gets or sets a formula for a range.
  /// </summary>
  public string Formula {
    get {
      if (IsName) {
        if (_worksheet == null) {
          return _workbook._names[_address].NameFormula;
        }
        return _worksheet.Names[_address].NameFormula;
      }
      return _worksheet.GetFormula(_fromRow, _fromCol);
    }
    set {
      if (IsName) {
        if (_worksheet == null) {
          _workbook._names[_address].NameFormula = value;
        } else {
          _worksheet.Names[_address].NameFormula = value;
        }
      } else {
        if (value == null || value.Trim() == "") {
          //Set the cells to null
          Value = null;
        } else if (_fromRow == _toRow && _fromCol == _toCol) {
          Set_Formula(value, _fromRow, _fromCol);
        } else {
          Set_SharedFormula(value, this, false);
          if (Addresses != null) {
            foreach (var address in Addresses) {
              Set_SharedFormula(value, address, false);
            }
          }
        }
      }
    }
  }

  /// <summary>
  /// Gets or Set a formula in R1C1 format.
  /// </summary>
  public string FormulaR1C1 {
    get {
      IsRangeValid("FormulaR1C1");
      return _worksheet.GetFormulaR1C1(_fromRow, _fromCol);
    }
    set {
      IsRangeValid("FormulaR1C1");
      if (value.Length > 0 && value[0] == '=') {
        value = value.Substring(1, value.Length - 1); // remove any starting equalsign.
      }

      if (value == null || value.Trim() == "") {
        //Set the cells to null
        _worksheet.Cells[TranslateFromR1C1(value, _fromRow, _fromCol)].Value = null;
      } else if (Addresses == null) {
        Set_SharedFormula(TranslateFromR1C1(value, _fromRow, _fromCol), this, false);
      } else {
        Set_SharedFormula(
            TranslateFromR1C1(value, _fromRow, _fromCol),
            new(WorkSheet, FirstAddress),
            false);
        foreach (var address in Addresses) {
          Set_SharedFormula(
              TranslateFromR1C1(value, address.Start.Row, address.Start.Column),
              address,
              false);
        }
      }
    }
  }

  /// <summary>
  /// Gets or Set a formula in R1C1 format.
  ///
  public string FormulaR1C1_V1 {
    get {
      IsRangeValid("FormulaR1C1");
      return _worksheet.GetFormulaR1C1_V1(_fromRow, _fromCol);
    }
  }

  public string ArrayFormulaAddress {
    get {
      IsRangeValid("FormulaR1C1");
      return _worksheet.GetArrayFormulaAddress(_fromRow, _fromCol);
    }
  }

  /// <summary>
  /// Set the hyperlink property for a range of cells
  /// </summary>
  public Uri Hyperlink {
    get {
      IsRangeValid("formulaR1C1");
      return _worksheet._hyperLinks.GetValue(_fromRow, _fromCol);
    }
    set => _changePropMethod(Set_HyperLink, value);
  }

  /// <summary>
  /// If the cells in the range are merged.
  /// </summary>
  public bool Merge {
    get {
      IsRangeValid("merging");
      for (int col = _fromCol; col <= _toCol; col++) {
        for (int row = _fromRow; row <= _toRow; row++) {
          if (_worksheet.MergedCells[row, col] == null) {
            return false;
          }
          //if (!_worksheet._flags.GetFlagValue(row, col, CellFlags.Merged))
          //{
          //    return false;
          //}
        }
      }
      return true;
    }
    set {
      IsRangeValid("merging");
      //SetMerge(value, FirstAddress);
      if (value) {
        _worksheet.MergedCells.Add(new(FirstAddress), true);
        if (Addresses != null) {
          foreach (var address in Addresses) {
            _worksheet.MergedCells.Add(address, true);
            //SetMerge(value, address._address);
          }
        }
      } else {
        _worksheet.MergedCells.Clear(this);
        if (Addresses != null) {
          foreach (var address in Addresses) {
            _worksheet.MergedCells.Clear(address);
            ;
          }
        }
      }
    }
  }

  //private void SetMerge(bool value, string address)
  //{
  //    if (!value)
  //    {
  //        if (_worksheet.MergedCells.List.Contains(address))
  //        {
  //            SetCellMerge(false, address);
  //            _worksheet.MergedCells.List.Remove(address);
  //        }
  //        else if (!CheckMergeDiff(false, address))
  //        {
  //            throw (new Exception("Range is not fully merged.Specify the exact range"));
  //        }
  //    }
  //    else
  //    {
  //        if (CheckMergeDiff(false, address))
  //        {
  //            SetCellMerge(true, address);
  //            _worksheet.MergedCells.List.Add(address);
  //        }
  //        else
  //        {
  //            if (!_worksheet.MergedCells.List.Contains(address))
  //            {
  //                throw (new Exception("Cells are already merged"));
  //            }
  //        }
  //    }
  //}
  /// <summary>
  /// Set an autofilter for the range
  /// </summary>
  public bool AutoFilter {
    get {
      IsRangeValid("autofilter");
      ExcelAddressBase address = _worksheet.AutoFilterAddress;
      if (address == null) {
        return false;
      }
      if (_fromRow >= address.Start.Row
          && _toRow <= address.End.Row
          && _fromCol >= address.Start.Column
          && _toCol <= address.End.Column) {
        return true;
      }
      return false;
    }
    set {
      IsRangeValid("autofilter");
      _worksheet.AutoFilterAddress = this;
      if (_worksheet.Names.ContainsKey("_xlnm._FilterDatabase")) {
        _worksheet.Names.Remove("_xlnm._FilterDatabase");
      }
      var result = _worksheet.Names.Add("_xlnm._FilterDatabase", this);
      result.IsNameHidden = true;
    }
  }

  /// <summary>
  /// If the value is in richtext format.
  /// </summary>
  public bool IsRichText {
    get {
      IsRangeValid("richtext");
      return _worksheet._flags.GetFlagValue(_fromRow, _fromCol, CellFlags.RichText);
    }
    set => _changePropMethod(Set_IsRichText, value);
  }

  /// <summary>
  /// Is the range a part of an Arrayformula
  /// </summary>
  public bool IsArrayFormula {
    get {
      IsRangeValid("arrayformulas");
      return _worksheet._flags.GetFlagValue(_fromRow, _fromCol, CellFlags.ArrayFormula);
    }
    set => _changePropMethod(Set_IsArrayFormula, value);
  }

  private ExcelRichTextCollection _rtc;

  /// <summary>
  /// Cell value is richtext formatted.
  /// Richtext-property only apply to the left-top cell of the range.
  /// </summary>
  public ExcelRichTextCollection RichText {
    get {
      IsRangeValid("richtext");
      if (_rtc == null) {
        _rtc = GetRichText(_fromRow, _fromCol);
      }
      return _rtc;
    }
  }

  private ExcelRichTextCollection GetRichText(int row, int col) {
    XmlDocument xml = new XmlDocument();
    var v = _worksheet._values.GetValue(row, col);
    var isRt = _worksheet._flags.GetFlagValue(row, col, CellFlags.RichText);
    if (v != null) {
      if (isRt) {
        XmlHelper.LoadXmlSafe(
            xml,
            "<d:si xmlns:d=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\" >"
                + v
                + "</d:si>",
            Encoding.UTF8);
      } else {
        xml.LoadXml(
            "<d:si xmlns:d=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\" ><d:r><d:t>"
                + SecurityElement.Escape(v.ToString())
                + "</d:t></d:r></d:si>");
      }
    } else {
      xml.LoadXml("<d:si xmlns:d=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\" />");
    }
    var rtc = new ExcelRichTextCollection(
        _worksheet.NameSpaceManager,
        xml.SelectSingleNode("d:si", _worksheet.NameSpaceManager),
        this);
    return rtc;
  }

  /// <summary>
  /// returns the comment object of the first cell in the range
  /// </summary>
  public ExcelComment Comment {
    get {
      IsRangeValid("comments");
      ulong cellId = GetCellId(_worksheet.SheetID, _fromRow, _fromCol);
      if (_worksheet.Comments._comments.ContainsKey(cellId)) {
        return _worksheet._comments._comments[cellId] as ExcelComment;
      }
      return null;
    }
  }

  /// <summary>
  /// WorkSheet object
  /// </summary>
  public ExcelWorksheet Worksheet => _worksheet;

  /// <summary>
  /// Address including sheetname
  /// </summary>
  public string FullAddress {
    get {
      string fullAddress = GetFullAddress(_worksheet.Name, _address);
      if (Addresses != null) {
        foreach (var a in Addresses) {
          fullAddress += "," + GetFullAddress(_worksheet.Name, a.Address);
          ;
        }
      }
      return fullAddress;
    }
  }

  /// <summary>
  /// Address including sheetname
  /// </summary>
  public string FullAddressAbsolute {
    get {
      string wbwsRef = string.IsNullOrEmpty(_wb) ? _ws : "[" + _wb.Replace("'", "''") + "]" + _ws;
      string fullAddress = GetFullAddress(
          wbwsRef,
          GetAddress(_fromRow, _fromCol, _toRow, _toCol, true));
      if (Addresses != null) {
        foreach (var a in Addresses) {
          fullAddress +=
              ","
                  + GetFullAddress(
                      wbwsRef,
                      GetAddress(a.Start.Row, a.Start.Column, a.End.Row, a.End.Column, true));
          ;
        }
      }
      return fullAddress;
    }
  }

  /// <summary>
  /// Address including sheetname
  /// </summary>
  internal string FullAddressAbsoluteNoFullRowCol {
    get {
      string wbwsRef = string.IsNullOrEmpty(_wb) ? _ws : "[" + _wb.Replace("'", "''") + "]" + _ws;
      string fullAddress = GetFullAddress(
          wbwsRef,
          GetAddress(_fromRow, _fromCol, _toRow, _toCol, true),
          false);
      if (Addresses != null) {
        foreach (var a in Addresses) {
          fullAddress +=
              ","
                  + GetFullAddress(
                      wbwsRef,
                      GetAddress(a.Start.Row, a.Start.Column, a.End.Row, a.End.Column, true),
                      false);
          ;
        }
      }
      return fullAddress;
    }
  }

  /// <summary>
  /// Set the value without altering the richtext property
  /// </summary>
  /// <param name="value">the value</param>
  internal void SetValueRichText(object value) {
    if (_fromRow == 1
        && _fromCol == 1
        && _toRow == ExcelPackage.MaxRows
        && _toCol
            == ExcelPackage.MaxColumns) //Full sheet (ex ws.Cells.Value=0). Set value for A1 only to avoid hanging
    {
      //_worksheet.Cell(1, 1).SetValueRichText(value);
      SetValue(value, 1, 1);
    } else {
      //for (int col = _fromCol; col <= _toCol; col++)
      //{
      //    for (int row = _fromRow; row <= _toRow; row++)
      //    {
      //_worksheet.Cell(row, col).SetValueRichText(value);
      SetValue(value, _fromRow, _fromCol);
      //}
      //}
    }
  }

  private void SetValue(object value, int row, int col) {
    _worksheet.SetValue(row, col, value);
    // if (value is string) _worksheet._types.SetValue(row, col, "S"); else _worksheet._types.SetValue(row, col, "");
    _worksheet._formulas.SetValue(row, col, "");
  }

  internal void SetSharedFormulaId(int id) {
    for (int col = _fromCol; col <= _toCol; col++) {
      for (int row = _fromRow; row <= _toRow; row++) {
        _worksheet._formulas.SetValue(row, col, id);
      }
    }
  }

  private void CheckAndSplitSharedFormula(ExcelAddressBase address) {
    for (int col = address._fromCol; col <= address._toCol; col++) {
      for (int row = address._fromRow; row <= address._toRow; row++) {
        var f = _worksheet._formulas.GetValue(row, col);
        if (f is int i && i >= 0) {
          SplitFormulas(address);
          return;
        }
      }
    }
  }

  private void SplitFormulas(ExcelAddressBase address) {
    List<int> formulas = [];
    for (int col = address._fromCol; col <= address._toCol; col++) {
      for (int row = address._fromRow; row <= address._toRow; row++) {
        var f = _worksheet._formulas.GetValue(row, col);
        if (f is int id) {
          if (id >= 0 && !formulas.Contains(id)) {
            if (_worksheet._sharedFormulas[id].IsArray
                && Collide(_worksheet.Cells[_worksheet._sharedFormulas[id].Address])
                    == eAddressCollition.Partly) // If the formula is an array formula and its on the inside the overwriting range throw an exception
            {
              throw (new InvalidOperationException("Can not overwrite a part of an array-formula"));
            }
            formulas.Add(id);
          }
        }
      }
    }

    foreach (int ix in formulas) {
      SplitFormula(address, ix);
    }

    ////Clear any formula references inside the refered range
    //_worksheet._formulas.Clear(address._fromRow, address._toRow, address._toRow - address._fromRow + 1, address._toCol - address.column + 1);
  }

  private void SplitFormula(ExcelAddressBase address, int ix) {
    var f = _worksheet._sharedFormulas[ix];
    var fRange = _worksheet.Cells[f.Address];
    var collide = address.Collide(fRange);

    //The formula is inside the currenct range, remove it
    if (collide == eAddressCollition.Equal || collide == eAddressCollition.Inside) {
      _worksheet._sharedFormulas.Remove(ix);
      return;
      //fRange.SetSharedFormulaID(int.MinValue);
    }
    var firstCellCollide = address.Collide(
        new(fRange._fromRow, fRange._fromCol, fRange._fromRow, fRange._fromCol));
    if (collide == eAddressCollition.Partly
        && (firstCellCollide == eAddressCollition.Inside
                || firstCellCollide
                    == eAddressCollition.Equal)) //Do we need to split? Only if the functions first row is inside the new range.
    {
      //The formula partly collides with the current range
      bool fIsSet = false;
      string formulaR1C1 = fRange.FormulaR1C1;
      //Top Range
      if (fRange._fromRow < _fromRow) {
        f.Address = GetAddress(fRange._fromRow, fRange._fromCol, _fromRow - 1, fRange._toCol);
        fIsSet = true;
      }
      //Left Range
      if (fRange._fromCol < address._fromCol) {
        if (fIsSet) {
          f = new(SourceCodeTokenizer.Default);
          f.Index = _worksheet.GetMaxShareFunctionIndex(false);
          f.StartCol = fRange._fromCol;
          f.IsArray = false;
          _worksheet._sharedFormulas.Add(f.Index, f);
        } else {
          fIsSet = true;
        }
        if (fRange._fromRow < address._fromRow) {
          f.StartRow = address._fromRow;
        } else {
          f.StartRow = fRange._fromRow;
        }
        if (fRange._toRow < address._toRow) {
          f.Address = GetAddress(f.StartRow, f.StartCol, fRange._toRow, address._fromCol - 1);
        } else {
          f.Address = GetAddress(f.StartRow, f.StartCol, address._toRow, address._fromCol - 1);
        }
        f.Formula = TranslateFromR1C1(formulaR1C1, f.StartRow, f.StartCol);
        _worksheet.Cells[f.Address].SetSharedFormulaId(f.Index);
      }
      //Right Range
      if (fRange._toCol > address._toCol) {
        if (fIsSet) {
          f = new(SourceCodeTokenizer.Default);
          f.Index = _worksheet.GetMaxShareFunctionIndex(false);
          f.IsArray = false;
          _worksheet._sharedFormulas.Add(f.Index, f);
        } else {
          fIsSet = true;
        }
        f.StartCol = address._toCol + 1;
        if (address._fromRow < fRange._fromRow) {
          f.StartRow = fRange._fromRow;
        } else {
          f.StartRow = address._fromRow;
        }

        if (fRange._toRow < address._toRow) {
          f.Address = GetAddress(f.StartRow, f.StartCol, fRange._toRow, fRange._toCol);
        } else {
          f.Address = GetAddress(f.StartRow, f.StartCol, address._toRow, fRange._toCol);
        }
        f.Formula = TranslateFromR1C1(formulaR1C1, f.StartRow, f.StartCol);
        _worksheet.Cells[f.Address].SetSharedFormulaId(f.Index);
      }
      //Bottom Range
      if (fRange._toRow > address._toRow) {
        if (fIsSet) {
          f = new(SourceCodeTokenizer.Default);
          f.Index = _worksheet.GetMaxShareFunctionIndex(false);
          f.IsArray = false;
          _worksheet._sharedFormulas.Add(f.Index, f);
        }

        f.StartCol = fRange._fromCol;
        f.StartRow = _toRow + 1;

        f.Formula = TranslateFromR1C1(formulaR1C1, f.StartRow, f.StartCol);

        f.Address = GetAddress(f.StartRow, f.StartCol, fRange._toRow, fRange._toCol);
        _worksheet.Cells[f.Address].SetSharedFormulaId(f.Index);
      }
    }
  }

  private object ConvertData(ExcelTextFormat format, string v, int col, bool isText) {
    if (isText && (format.DataTypes == null || format.DataTypes.Length < col)) {
      return v;
    }

    double d;
    DateTime dt;
    if (format.DataTypes == null
        || format.DataTypes.Length <= col
        || format.DataTypes[col] == eDataTypes.Unknown) {
      string v2 = v.EndsWith("%") ? v.Substring(0, v.Length - 1) : v;
      if (double.TryParse(v2, NumberStyles.Any, format.Culture, out d)) {
        if (v2 == v) {
          return d;
        }
        return d / 100;
      }
      if (DateTime.TryParse(v, format.Culture, DateTimeStyles.None, out dt)) {
        return dt;
      }
      return v;
    }
    switch (format.DataTypes[col]) {
      case eDataTypes.Number:
        if (double.TryParse(v, NumberStyles.Any, format.Culture, out d)) {
          return d;
        }
        return v;
      case eDataTypes.DateTime:
        if (DateTime.TryParse(v, format.Culture, DateTimeStyles.None, out dt)) {
          return dt;
        }
        return v;
      case eDataTypes.Percent:
        string v2 = v.EndsWith("%") ? v.Substring(0, v.Length - 1) : v;
        if (double.TryParse(v2, NumberStyles.Any, format.Culture, out d)) {
          return d / 100;
        }
        return v;

      default:
        return v;
    }
  }

  /// <summary>
  /// Conditional Formatting for this range.
  /// </summary>
  public IRangeConditionalFormatting ConditionalFormatting =>
    new RangeConditionalFormatting(_worksheet, new(Address));

  /// <summary>
  /// Data validation for this range.
  /// </summary>
  public IRangeDataValidation DataValidation => new RangeDataValidation(_worksheet, Address);

  /// <summary>
  /// Get the strongly typed value of the cell.
  /// </summary>
  /// <typeparam name="T">The type</typeparam>
  /// <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>() {
    return _worksheet.GetTypedValue<T>(Value);
  }

  /// <summary>
  /// Get a range with an offset from the top left cell.
  /// The new range has the same dimensions as the current range
  /// </summary>
  /// <param name="rowOffset">Row Offset</param>
  /// <param name="columnOffset">Column Offset</param>
  /// <returns></returns>
  public ExcelRangeBase Offset(int rowOffset, int columnOffset) {
    if (_fromRow + rowOffset < 1
        || _fromCol + columnOffset < 1
        || _fromRow + rowOffset > ExcelPackage.MaxRows
        || _fromCol + columnOffset > ExcelPackage.MaxColumns) {
      throw (new ArgumentOutOfRangeException("Offset value out of range"));
    }
    string address = GetAddress(
        _fromRow + rowOffset,
        _fromCol + columnOffset,
        _toRow + rowOffset,
        _toCol + columnOffset);
    return new(_worksheet, address);
  }

  /// <summary>
  /// Get a range with an offset from the top left cell.
  /// </summary>
  /// <param name="rowOffset">Row Offset</param>
  /// <param name="columnOffset">Column Offset</param>
  /// <param name="numberOfRows">Number of rows. Minimum 1</param>
  /// <param name="numberOfColumns">Number of colums. Minimum 1</param>
  /// <returns></returns>
  public ExcelRangeBase Offset(
      int rowOffset,
      int columnOffset,
      int numberOfRows,
      int numberOfColumns) {
    if (numberOfRows < 1 || numberOfColumns < 1) {
      throw (new("Number of rows/columns must be greater than 0"));
    }
    numberOfRows--;
    numberOfColumns--;
    if (_fromRow + rowOffset < 1
        || _fromCol + columnOffset < 1
        || _fromRow + rowOffset > ExcelPackage.MaxRows
        || _fromCol + columnOffset > ExcelPackage.MaxColumns
        || _fromRow + rowOffset + numberOfRows < 1
        || _fromCol + columnOffset + numberOfColumns < 1
        || _fromRow + rowOffset + numberOfRows > ExcelPackage.MaxRows
        || _fromCol + columnOffset + numberOfColumns > ExcelPackage.MaxColumns) {
      throw (new ArgumentOutOfRangeException("Offset value out of range"));
    }
    string address = GetAddress(
        _fromRow + rowOffset,
        _fromCol + columnOffset,
        _fromRow + rowOffset + numberOfRows,
        _fromCol + columnOffset + numberOfColumns);
    return new(_worksheet, address);
  }

  /// <summary>
  /// Clear all cells
  /// </summary>
  public void Clear() {
    Delete(this, false);
  }

  /// <summary>
  /// Creates an array-formula.
  /// </summary>
  /// <param name="arrayFormula">The formula</param>
  public void CreateArrayFormula(string arrayFormula) {
    if (Addresses != null) {
      throw (new("An Arrayformula can not have more than one address"));
    }
    Set_SharedFormula(arrayFormula, this, true);
  }

  //private void Clear(ExcelAddressBase Range)
  //{
  //    Clear(Range, true);
  //}
  internal void Delete(ExcelAddressBase range, bool shift) {
    //DeleteCheckMergedCells(Range);
    _worksheet.MergedCells.Clear(range);
    //First find the start cell
    int fromRow,
        fromCol;
    var d = Worksheet.Dimension;
    if (d != null
        && range._fromRow <= d._fromRow
        && range._toRow
            >= d._toRow) //EntireRow?
    {
      fromRow = 0;
    } else {
      fromRow = range._fromRow;
    }
    if (d != null
        && range._fromCol <= d._fromCol
        && range._toCol
            >= d._toCol) //EntireRow?
    {
      fromCol = 0;
    } else {
      fromCol = range._fromCol;
    }

    var rows = range._toRow - fromRow + 1;
    var cols = range._toCol - fromCol + 1;

    _worksheet._values.Delete(fromRow, fromCol, rows, cols, shift);
    _worksheet._types.Delete(fromRow, fromCol, rows, cols, shift);
    _worksheet._styles.Delete(fromRow, fromCol, rows, cols, shift);
    _worksheet._formulas.Delete(fromRow, fromCol, rows, cols, shift);
    _worksheet._hyperLinks.Delete(fromRow, fromCol, rows, cols, shift);
    _worksheet._flags.Delete(fromRow, fromCol, rows, cols, shift);
    _worksheet._commentsStore.Delete(fromRow, fromCol, rows, cols, shift);

    //if(shift)
    //{
    //    _worksheet.AdjustFormulasRow(fromRow, rows);
    //}

    //Clear multi addresses as well
    if (Addresses != null) {
      foreach (var sub in Addresses) {
        Delete(sub, shift);
      }
    }
  }

  public void Dispose() {}

  //int _index;
  //ulong _toCellId;
  //int _enumAddressIx;
  private CellsStoreEnumerator<object> cellEnum;

  public IEnumerator<ExcelRangeBase> GetEnumerator() {
    Reset();
    return this;
  }

  IEnumerator IEnumerable.GetEnumerator() {
    Reset();
    return this;
  }

  /// <summary>
  /// The current range when enumerating
  /// </summary>
  public ExcelRangeBase Current => new(_worksheet, GetAddress(cellEnum.Row, cellEnum.Column));

  /// <summary>
  /// The current range when enumerating
  /// </summary>
  object IEnumerator.Current =>
    new ExcelRangeBase(_worksheet, GetAddress(cellEnum.Row, cellEnum.Column));

  private int _enumAddressIx = -1;

  public bool MoveNext() {
    if (cellEnum.Next()) {
      return true;
    }
    if (_addresses != null) {
      _enumAddressIx++;
      if (_enumAddressIx < _addresses.Count) {
        cellEnum = new(
            _worksheet._values,
            _addresses[_enumAddressIx]._fromRow,
            _addresses[_enumAddressIx]._fromCol,
            _addresses[_enumAddressIx]._toRow,
            _addresses[_enumAddressIx]._toCol);
        return MoveNext();
      }
      return false;
    }
    return false;
  }

  public void Reset() {
    _enumAddressIx = -1;
    cellEnum = new(_worksheet._values, _fromRow, _fromCol, _toRow, _toCol);
  }

  //private void GetNextIndexEnum(int fromRow, int fromCol, int toRow, int toCol)
  //{
  //    if (_index >= _worksheet._cells.Count) return;
  //    ExcelCell cell = _worksheet._cells[_index] as ExcelCell;
  //    while (cell.Column > toCol || cell.Column < fromCol)
  //    {
  //        if (cell.Column < fromCol)
  //        {
  //            _index = _worksheet._cells.IndexOf(ExcelAddress.GetCellID(_worksheet.SheetID, cell.Row, fromCol));
  //        }
  //        else
  //        {
  //            _index = _worksheet._cells.IndexOf(ExcelAddress.GetCellID(_worksheet.SheetID, cell.Row + 1, fromCol));
  //        }

  //        if (_index < 0)
  //        {
  //            _index = ~_index;
  //        }
  //        if (_index >= _worksheet._cells.Count || _worksheet._cells[_index].RangeID > _toCellId)
  //        {
  //            break;
  //        }
  //        cell = _worksheet._cells[_index] as ExcelCell;
  //    }
  //}

  //private void GetStartIndexEnum(int fromRow, int fromCol, int toRow, int toCol)
  //{
  //    _index = _worksheet._cells.IndexOf(ExcelCellBase.GetCellID(_worksheet.SheetID, fromRow, fromCol));
  //    _toCellId = ExcelCellBase.GetCellID(_worksheet.SheetID, toRow, toCol);
  //    if (_index < 0)
  //    {
  //        _index = ~_index;
  //    }
  //    _index--;
  //}
}