AppsheetEpplus/FormulaParsing/EpplusExcelDataProvider.cs

using System.Collections;
using System.Collections.Generic;
using System.Linq;

namespace AppsheetEpplus;

public class EpplusExcelDataProvider : ExcelDataProvider {
  public class RangeInfo : IRangeInfo {
    internal ExcelWorksheet _ws;
    private readonly CellsStoreEnumerator<object> _values;
    private readonly int _fromRow;
    private readonly int _toRow;
    private readonly int _fromCol;
    private readonly int _toCol;
    private int _cellCount;
    private readonly ExcelAddressBase _address;
    private readonly ICellInfo _cell;

    public RangeInfo(ExcelWorksheet ws, int fromRow, int fromCol, int toRow, int toCol) {
      _ws = ws;
      _fromRow = fromRow;
      _fromCol = fromCol;
      _toRow = toRow;
      _toCol = toCol;
      _address = new(_fromRow, _fromCol, _toRow, _toCol);
      _address._ws = ws.Name;
      _values = new(ws._values, _fromRow, _fromCol, _toRow, _toCol);
      _cell = new CellInfo(_ws, _values);
    }

    public int GetNCells() {
      return ((_toRow - _fromRow) + 1) * ((_toCol - _fromCol) + 1);
    }

    public bool IsEmpty {
      get {
        if (_cellCount > 0) {
          return false;
        }
        if (_values.Next()) {
          _values.Reset();
          return false;
        }
        return true;
      }
    }

    public bool IsMulti {
      get {
        if (_cellCount == 0) {
          if (_values.Next() && _values.Next()) {
            _values.Reset();
            return true;
          }
          _values.Reset();
          return false;
        }
        if (_cellCount > 1) {
          return true;
        }
        return false;
      }
    }

    public ICellInfo Current => _cell;

    public ExcelWorksheet Worksheet => _ws;

    public void Dispose() {}

    object IEnumerator.Current => this;

    public bool MoveNext() {
      _cellCount++;
      return _values.MoveNext();
    }

    public void Reset() {
      _values.Init();
    }

    public bool NextCell() {
      _cellCount++;
      return _values.MoveNext();
    }

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

    IEnumerator IEnumerable.GetEnumerator() {
      return this;
    }

    public ExcelAddressBase Address => _address;

    public object GetValue(int row, int col) {
      return _ws.GetValue(row, col);
    }

    public object GetOffset(int rowOffset, int colOffset) {
      if (_values.Row < _fromRow || _values.Column < _fromCol) {
        return _ws.GetValue(_fromRow + rowOffset, _fromCol + colOffset);
      }
      return _ws.GetValue(_values.Row + rowOffset, _values.Column + colOffset);
    }
  }

  public class CellInfo : ICellInfo {
    private readonly ExcelWorksheet _ws;
    private readonly CellsStoreEnumerator<object> _values;

    internal CellInfo(ExcelWorksheet ws, CellsStoreEnumerator<object> values) {
      _ws = ws;
      _values = values;
    }

    public string Address => _values.CellAddress;

    public int Row => _values.Row;

    public int Column => _values.Column;

    public string Formula => _ws.GetFormula(_values.Row, _values.Column);

    public object Value => _values.Value;

    public double ValueDouble => ConvertUtil.GetValueDouble(_values.Value, true);

    public double ValueDoubleLogical => ConvertUtil.GetValueDouble(_values.Value);

    public bool IsHiddenRow {
      get {
        var row = _ws._values.GetValue(_values.Row, 0) as RowInternal;
        if (row != null) {
          return row.Hidden || row.Height == 0;
        }
        return false;
      }
    }

    public bool IsExcelError => ExcelErrorValue.Values.IsErrorValue(_values.Value);

    public IList<Token> Tokens => _ws._formulaTokens.GetValue(_values.Row, _values.Column);
  }

  public class NameInfo : INameInfo {
    public ulong Id { get; set; }

    public string Worksheet { get; set; }

    public string Name { get; set; }

    public string Formula { get; set; }

    public IList<Token> Tokens { get; internal set; }

    public object Value { get; set; }
  }

  private readonly ExcelWorkbook _workbook;
  private ExcelWorksheet _currentWorksheet;
  private Dictionary<ulong, INameInfo> _names = new();

  public EpplusExcelDataProvider(ExcelWorkbook workbook) {
    _workbook = workbook;
  }

  public override ExcelNamedRangeCollection GetWorksheetNames(string worksheet) {
    var ws = _workbook.Worksheets[worksheet];
    if (ws != null) {
      return ws.Names;
    }
    return null;
  }

  public override ExcelNamedRangeCollection GetWorkbookNameValues() {
    return _workbook.Names;
  }

  public override IRangeInfo GetRange(
      string worksheet,
      int fromRow,
      int fromCol,
      int toRow,
      int toCol) {
    SetCurrentWorksheet(worksheet);
    var wsName = string.IsNullOrEmpty(worksheet) ? _currentWorksheet.Name : worksheet;
    var ws = _workbook.Worksheets[wsName];
    return new RangeInfo(ws, fromRow, fromCol, toRow, toCol);
  }

  public override IRangeInfo GetRange(string worksheet, int row, int column, string address) {
    var addr = new ExcelAddress(worksheet, address);
    if (addr.Table != null) {
      addr.SetRcFromTable(_workbook, new(row, column, row, column));
    }
    //SetCurrentWorksheet(addr.WorkSheet);
    var wsName = string.IsNullOrEmpty(addr.WorkSheet) ? _currentWorksheet.Name : addr.WorkSheet;
    var ws = _workbook.Worksheets[wsName];
    //return new CellsStoreEnumerator<object>(ws._values, addr._fromRow, addr._fromCol, addr._toRow, addr._toCol);
    return new RangeInfo(ws, addr._fromRow, addr._fromCol, addr._toRow, addr._toCol);
  }

  public override INameInfo GetName(string worksheet, string name) {
    ExcelNamedRange nameItem;
    ulong id;
    ExcelWorksheet ws;
    if (string.IsNullOrEmpty(worksheet)) {
      if (_workbook.Names.ContainsKey(name)) {
        nameItem = _workbook.Names[name];
      } else {
        return null;
      }
      ws = null;
    } else {
      ws = _workbook.Worksheets[worksheet];
      if (ws != null && ws.Names.ContainsKey(name)) {
        nameItem = ws.Names[name];
      } else if (_workbook.Names.ContainsKey(name)) {
        nameItem = _workbook.Names[name];
      } else {
        return null;
      }
    }
    id = ExcelCellBase.GetCellId(nameItem.LocalSheetId, nameItem.Index, 0);

    if (_names.ContainsKey(id)) {
      return _names[id];
    }
    var ni = new NameInfo {
      Id = id,
      Name = name,
      Worksheet = nameItem.Worksheet == null ? nameItem._ws : nameItem.Worksheet.Name,
      Formula = nameItem.Formula,
    };
    if (nameItem._fromRow > 0) {
      ni.Value = new RangeInfo(
          nameItem.Worksheet ?? ws,
          nameItem._fromRow,
          nameItem._fromCol,
          nameItem._toRow,
          nameItem._toCol);
    } else {
      ni.Value = nameItem.Value;
    }
    _names.Add(id, ni);
    return ni;
  }

  public override IEnumerable<object> GetRangeValues(string address) {
    SetCurrentWorksheet(ExcelAddressInfo.Parse(address));
    var addr = new ExcelAddress(address);
    var wsName = string.IsNullOrEmpty(addr.WorkSheet) ? _currentWorksheet.Name : addr.WorkSheet;
    var ws = _workbook.Worksheets[wsName];
    return (new CellsStoreEnumerator<object>(
            ws._values,
            addr._fromRow,
            addr._fromCol,
            addr._toRow,
            addr._toCol));
  }

  public object GetValue(int row, int column) {
    return _currentWorksheet._values.GetValue(row, column);
  }

  public bool IsMerged(int row, int column) {
    //return _currentWorksheet._flags.GetFlagValue(row, column, CellFlags.Merged);
    return _currentWorksheet.MergedCells[row, column] != null;
  }

  public bool IsHidden(int row, int column) {
    return _currentWorksheet.Column(column).Hidden
        || _currentWorksheet.Column(column).Width == 0
        || _currentWorksheet.Row(row).Hidden
        || _currentWorksheet.Row(column).Height == 0;
  }

  public override object GetCellValue(string sheetName, int row, int col) {
    SetCurrentWorksheet(sheetName);
    return _currentWorksheet._values.GetValue(row, col);
  }

  public override ExcelCellAddress GetDimensionEnd(string worksheet) {
    ExcelCellAddress address = null;
    try {
      address = _workbook.Worksheets[worksheet].Dimension.End;
    } catch {}

    return address;
  }

  private void SetCurrentWorksheet(ExcelAddressInfo addressInfo) {
    if (addressInfo.WorksheetIsSpecified) {
      _currentWorksheet = _workbook.Worksheets[addressInfo.Worksheet];
    } else if (_currentWorksheet == null) {
      _currentWorksheet = _workbook.Worksheets.First();
    }
  }

  private void SetCurrentWorksheet(string worksheetName) {
    if (!string.IsNullOrEmpty(worksheetName)) {
      _currentWorksheet = _workbook.Worksheets[worksheetName];
    } else {
      _currentWorksheet = _workbook.Worksheets.First();
    }
  }

  public override int ExcelMaxColumns => ExcelPackage.MaxColumns;

  public override int ExcelMaxRows => ExcelPackage.MaxRows;

  public override string GetRangeFormula(string worksheetName, int row, int column) {
    SetCurrentWorksheet(worksheetName);
    return _currentWorksheet.GetFormula(row, column);
  }

  public override object GetRangeValue(string worksheetName, int row, int column) {
    SetCurrentWorksheet(worksheetName);
    return _currentWorksheet.GetValue(row, column);
  }

  public override string GetFormat(object value, string format) {
    var styles = _workbook.Styles;
    ExcelNumberFormatXml.ExcelFormatTranslator ft = null;
    foreach (var f in styles.NumberFormats) {
      if (f.Format == format) {
        ft = f.FormatTranslator;
        break;
      }
    }
    if (ft == null) {
      ft = new(format, -1);
    }
    return ExcelRangeBase.FormatValue(value, ft, format, ft.NetFormat);
  }

  public override List<Token> GetRangeFormulaTokens(string worksheetName, int row, int column) {
    return _workbook.Worksheets[worksheetName]._formulaTokens.GetValue(row, column);
  }

  public override bool IsRowHidden(string worksheetName, int row) {
    var b =
        _workbook.Worksheets[worksheetName].Row(row).Height == 0
            || _workbook.Worksheets[worksheetName].Row(row).Hidden;

    return b;
  }

  public override void Reset() {
    _names = new(); //Reset name cache.
  }
}