Cell.cs

/*
 * NanoXLSX is a small .NET library to generate and read XLSX (Microsoft Excel 2007 or newer) files in an easy and native way
 * Copyright Raphael Stoeckli © 2026
 * This library is licensed under the MIT License.
 * You find a copy of the license in project folder or on: http://opensource.org/licenses/MIT
 */
 
using System;
using System.Collections.Generic;
using System.Globalization;
using System.Text;
using NanoXLSX.Enums;
using NanoXLSX.Exceptions;
using NanoXLSX.Internal;
using NanoXLSX.Styles;
using NanoXLSX.Utils;
using FormatException = NanoXLSX.Exceptions.FormatException;
 
namespace NanoXLSX
{
    public class Cell : IComparable<Cell>
    {
        #region constants
        private const int ASCII_OFFSET = 64;
        #endregion
 
        #region enums
        public enum CellType
        {
#pragma warning disable CA1720 // Suppress warning: Identifier contains type name
            String,
#pragma warning restore CA1720
            Number,
            Date,
            Time,
            Bool,
            Formula,
            Empty,
            Error,
            Default
        }

        public enum AddressType
        {
            Default,
            FixedRow,
            FixedColumn,
            FixedRowAndColumn
        }

        public enum AddressScope
        {
            Any,
            SingleAddress,
            Range,
            Invalid
        }
 
        #endregion
 
        #region privateFileds
        private Style cellStyle;
        private int columnNumber;
        private CellType dataType;
        private FormulaData formula;
        private int rowNumber;
        private object value;
        private FeatureSet worksheetFeatures;
        #endregion
 
        #region properties

        public string CellAddress
        {
            get { return ResolveCellAddress(ColumnNumber, RowNumber, CellAddressType); }
            set
            {
                AddressType addressType;
                ResolveCellCoordinate(value, out columnNumber, out rowNumber, out addressType);
                CellAddressType = addressType;
            }
        }

        public Address CellAddress2
        {
            get { return new Address(ColumnNumber, RowNumber, CellAddressType); }
            set
            {
                ColumnNumber = value.Column;
                RowNumber = value.Row;
                CellAddressType = value.Type;
            }
        }

        public Style CellStyle
        {
            get { return cellStyle; }
        }

        public int ColumnNumber
        {
            get { return columnNumber; }
            set
            {
                ValidateColumnNumber(value);
                columnNumber = value;
            }
        }

        public CellType DataType
        {
            get { return dataType; }
            set
            {
                if (dataType == value)
                {
                    return;
                }
                if (value == CellType.Formula)
                {
                    dataType = value;
                    if (formula == null)
                    {
                        Formula = new FormulaData(GetValueAsFormulaExpression());
                    }
                    else
                    {
                        AttachFormulaFeatures();
                        SynchronitzeValueFromFormula();
                    }
                    return;
                }
                if (dataType == CellType.Formula)
                {
                    ClearFormula();
                }
                dataType = value;
            }
        }

        public int RowNumber
        {
            get { return rowNumber; }
            set
            {
                ValidateRowNumber(value);
                rowNumber = value;
            }
        }

        public AddressType CellAddressType { get; set; }

        public object Value
        {
            get => this.value;
            set
            {
                this.value = value;
                ResolveCellType();
                if (dataType != CellType.Formula || formula == null)
                {
                    return;
                }
                if (formula.MasterCellAddress == null)
                {
                    string expression = GetValueAsFormulaExpression();
                    if (!string.Equals(formula.Expression, expression, StringComparison.Ordinal) && formula.DefinedNameReference != null)
                    {
                        formula.DefinedNameReference = null; // Remove additional references
                    }
                    formula.Expression = expression;
                }
            }
 
        }

        public FormulaData Formula
        {
            get { return formula; }
            internal set
            {
                if (ReferenceEquals(formula, value))
                {
                    return;
                }
                DetachFormulaFeatures();
                formula = value;
                SynchronitzeValueFromFormula();
                AttachFormulaFeatures();
            }
        }
 
        #endregion
 
        #region constructors
        public Cell()
        {
            DataType = CellType.Default;
        }

        public Cell(object value, CellType type)
        {
            if (type == CellType.Empty)
            {
                this.value = null;
            }
            else
            {
                this.value = value;
            }
            DataType = type;
            if (type == CellType.Default)
            {
                ResolveCellType();
            }
        }

        public Cell(object value, CellType type, string address)
        {
            if (type == CellType.Empty)
            {
                this.value = null;
            }
            else
            {
                this.value = value;
            }
            DataType = type;
            CellAddress = address;
            if (type == CellType.Default)
            {
                ResolveCellType();
            }
        }

        public Cell(object value, CellType type, Address address)
        {
            if (type == CellType.Empty)
            {
                this.value = null;
            }
            else
            {
                this.value = value;
            }
            DataType = type;
            columnNumber = address.Column;
            rowNumber = address.Row;
            CellAddressType = address.Type;
            if (type == CellType.Default)
            {
                ResolveCellType();
            }
        }

        public Cell(object value, CellType type, int column, int row) : this(value, type)
        {
            ColumnNumber = column;
            RowNumber = row;
            CellAddressType = AddressType.Default;
            if (type == CellType.Default)
            {
                ResolveCellType();
            }
        }
        #endregion
 
        #region methods
        public int CompareTo(Cell other)
        {
            if (other == null)
            {
                return -1;
            }
            if (RowNumber == other.RowNumber)
            {
                return ColumnNumber.CompareTo(other.ColumnNumber);
            }
 
            return RowNumber.CompareTo(other.RowNumber);
        }

        public override bool Equals(object obj)
        {
            if (obj == null || obj.GetType() != typeof(Cell))
            {
                return false;
            }
            Cell other = (Cell)obj;
            if (!this.CellAddress2.Equals(other.CellAddress2))
            {
                return false;
            }
            if (this.cellStyle != null && other.CellStyle != null && !this.CellStyle.Equals(other.CellStyle))
            {
                return false;
            }
            if (this.Formula != null && other.Formula != null && !this.Formula.Equals(other.Formula))
            {
                return false;
            }
            if (this.DataType != other.DataType)
            {
                return false;
            }
            if (this.Value != null && other.Value != null && !this.Value.Equals(other.Value))
            {
                return false;
            }
            return true;
        }

        public void RemoveStyle()
        {
            cellStyle = null;
        }
 

        internal Range? SetReference(DefinedName definedName, object cachedValue = null)
        {
            if (definedName == null)
            {
                throw new WorksheetException("The defined name to set as cell reference must not be null.");
            }
            FormulaData formula = this.formula ?? new FormulaData();
            Range? referenceRange = null;
            formula.DefinedNameReference = definedName;
            formula.Expression = definedName.Name;
            if (definedName.Type == DefinedName.NameType.Range)
            {
                formula.Type = FormulaData.FormulaType.Array;
                referenceRange = TransposeDefinedNameArrayRange(definedName.TextValue);
            }
            if (definedName.Type == DefinedName.NameType.Constant)
            {
                formula.CachedValue = definedName.TextValue;
                formula.CachedValueType = FormulaData.ResolveCachedValueType(definedName.Value);
            }
            else
            {
                if (cachedValue == null || (cachedValue is string stringValue && stringValue.Length == 0))
                {
                    formula.CachedValueType = CellType.Number;
                }
                else
                {
                    formula.CachedValueType = FormulaData.ResolveCachedValueType(cachedValue);
                }
                formula.CachedValue = ParserUtils.ToCachedValueString(cachedValue); // Force value as plain OOXML string
            }
            this.DataType = CellType.Formula; // Force type
            this.Formula = formula;
            this.value = definedName.Name;
            return referenceRange;
        }

        public void ResolveCellType()
        {
            if (this.value == null)
            {
                DataType = CellType.Empty;
                this.value = null;
                return;
            }
            if (DataType == CellType.Formula)
            { return; } // Do not overwrite type
            Type t = this.value.GetType();
            if (t == typeof(bool))
            { DataType = CellType.Bool; }
            else if (t == typeof(byte) || t == typeof(sbyte))
            { DataType = CellType.Number; }
            else if (t == typeof(decimal))
            { DataType = CellType.Number; }
            else if (t == typeof(double))
            { DataType = CellType.Number; }
            else if (t == typeof(float))
            { DataType = CellType.Number; }
            else if (t == typeof(int) || t == typeof(uint))
            { DataType = CellType.Number; }
            else if (t == typeof(long) || t == typeof(ulong))
            { DataType = CellType.Number; }
            else if (t == typeof(short) || t == typeof(ushort))
            { DataType = CellType.Number; }
            else if (t == typeof(DateTime)) // Not native but standard
            {
                DataType = CellType.Date;
                SetStyle(BasicStyles.DateFormat);
            }
 
            else if (t == typeof(TimeSpan)) // Not native but standard
            {
                DataType = CellType.Time;
                SetStyle(BasicStyles.TimeFormat);
            }
            else if (t == typeof(Errors.FormulaError))
            { DataType = CellType.Error; }
            else { DataType = CellType.String; } // Default (char, string, object)
        }

        public void SetCellLockedState(bool isLocked, bool isHidden)
        {
            Style lockStyle;
            if (cellStyle == null)
            {
                lockStyle = new Style();
            }
            else
            {
                lockStyle = cellStyle.CopyStyle();
            }
            lockStyle.CurrentCellXf.Locked = isLocked;
            lockStyle.CurrentCellXf.Hidden = isHidden;
            SetStyle(lockStyle);
        }

        public Style SetStyle(Style style, bool unmanaged = false)
        {
            if (style == null)
            {
                throw new StyleException("No style to assign was defined");
            }
            cellStyle = unmanaged ? style : StyleRepository.Instance.AddStyle(style);
            return cellStyle;
        }

        internal Cell Copy()
        {
            Cell copy = new Cell
            {
                value = this.value,
                dataType = this.dataType,
                CellAddress = this.CellAddress,
                CellAddressType = this.CellAddressType,
                formula = this.formula?.Copy()
            };
            if (this.cellStyle != null)
            {
                copy.SetStyle(this.cellStyle, true);
            }
            return copy;
        }
        public override int GetHashCode()
        {
            unchecked
            {
                int hash = 17;
                hash = hash * 31 + columnNumber.GetHashCode();
                hash = hash * 31 + rowNumber.GetHashCode();
                hash = hash * 31 + CellAddressType.GetHashCode();
                hash = hash * 31 + DataType.GetHashCode();
                hash = hash * 31 + (cellStyle?.GetHashCode() ?? 0);
                hash = hash * 31 + (value?.GetHashCode() ?? 0);
                hash = hash * 31 + (Formula?.GetHashCode() ?? 0);
                return hash;
            }
        }

        public static bool operator ==(Cell left, Cell right)
        {
            if (ReferenceEquals(left, null))
            {
                return ReferenceEquals(right, null);
            }
 
            return left.Equals(right);
        }

        public static bool operator !=(Cell left, Cell right)
        {
            return !(left == right);
        }

        public static bool operator <(Cell left, Cell right)
        {
            return ReferenceEquals(left, null) ? !ReferenceEquals(right, null) : left.CompareTo(right) < 0;
        }

        public static bool operator <=(Cell left, Cell right)
        {
            return ReferenceEquals(left, null) || left.CompareTo(right) <= 0;
        }

        public static bool operator >(Cell left, Cell right)
        {
            return !ReferenceEquals(left, null) && left.CompareTo(right) > 0;
        }

        public static bool operator >=(Cell left, Cell right)
        {
            return ReferenceEquals(left, null) ? ReferenceEquals(right, null) : left.CompareTo(right) >= 0;
        }
 
        #endregion
 
        #region staticMethods
        public static IEnumerable<Cell> ConvertArray<T>(IEnumerable<T> list)
        {
            List<Cell> output = new List<Cell>();
            if (list == null)
            {
                return output;
            }
            Cell c;
            object o;
            Type t;
            foreach (T item in list)
            {
                if (item == null) // DO NOT LISTEN to code suggestions! This is wrong for bool: if (object.Equals(item, default(T)))
                {
                    c = new Cell(null, CellType.Empty);
                    output.Add(c);
                    continue;
                }
                o = item; // intermediate object is necessary to cast the types below
                t = item.GetType();
                if (t == typeof(Cell))
                { c = item as Cell; }
                else if (t == typeof(bool))
                { c = new Cell((bool)o, CellType.Bool); }
                else if (t == typeof(byte))
                { c = new Cell((byte)o, CellType.Number); }
                else if (t == typeof(sbyte))
                { c = new Cell((sbyte)o, CellType.Number); }
                else if (t == typeof(decimal))
                { c = new Cell((decimal)o, CellType.Number); }
                else if (t == typeof(double))
                { c = new Cell((double)o, CellType.Number); }
                else if (t == typeof(float))
                { c = new Cell((float)o, CellType.Number); }
                else if (t == typeof(int))
                { c = new Cell((int)o, CellType.Number); }
                else if (t == typeof(uint))
                { c = new Cell((uint)o, CellType.Number); }
                else if (t == typeof(long))
                { c = new Cell((long)o, CellType.Number); }
                else if (t == typeof(ulong))
                { c = new Cell((ulong)o, CellType.Number); }
                else if (t == typeof(short))
                { c = new Cell((short)o, CellType.Number); }
                else if (t == typeof(ushort))
                { c = new Cell((ushort)o, CellType.Number); }
                else if (t == typeof(DateTime))
                {
                    c = new Cell((DateTime)o, CellType.Date);
                    c.SetStyle(BasicStyles.DateFormat);
                }
                else if (t == typeof(TimeSpan))
                {
                    c = new Cell((TimeSpan)o, CellType.Time);
                    c.SetStyle(BasicStyles.TimeFormat);
                }
                else if (t == typeof(string))
                { c = new Cell((string)o, CellType.String); }
                else // Default = unspecified object
                {
                    c = new Cell(o.ToString(), CellType.Default);
                }
                output.Add(c);
            }
            return output;
        }

        public static IEnumerable<Address> GetCellRange(string range)
        {
            Range range2 = ResolveCellRange(range);
            return GetCellRange(range2.StartAddress, range2.EndAddress);
        }

        public static IEnumerable<Address> GetCellRange(string startAddress, string endAddress)
        {
            Address start = ResolveCellCoordinate(startAddress);
            Address end = ResolveCellCoordinate(endAddress);
            return GetCellRange(start, end);
        }

        public static IEnumerable<Address> GetCellRange(int startColumn, int startRow, int endColumn, int endRow)
        {
            Address start = new Address(startColumn, startRow);
            Address end = new Address(endColumn, endRow);
            return GetCellRange(start, end);
        }

        public static IEnumerable<Address> GetCellRange(Address startAddress, Address endAddress)
        {
            int startColumn;
            int endColumn;
            int startRow;
            int endRow;
            if (startAddress.Column < endAddress.Column)
            {
                startColumn = startAddress.Column;
                endColumn = endAddress.Column;
            }
            else
            {
                startColumn = endAddress.Column;
                endColumn = startAddress.Column;
            }
            if (startAddress.Row < endAddress.Row)
            {
                startRow = startAddress.Row;
                endRow = endAddress.Row;
            }
            else
            {
                startRow = endAddress.Row;
                endRow = startAddress.Row;
            }
            List<Address> output = new List<Address>();
            for (int column = startColumn; column <= endColumn; column++)
            {
                for (int row = startRow; row <= endRow; row++)
                {
                    output.Add(new Address(column, row));
                }
            }
            return output;
        }

        public static string ResolveCellAddress(int column, int row, AddressType type = AddressType.Default)
        {
            ValidateColumnNumber(column);
            ValidateRowNumber(row);
            switch (type)
            {
                case AddressType.FixedRowAndColumn:
                    return "$" + ResolveColumnAddress(column) + "$" + (row + 1);
                case AddressType.FixedColumn:
                    return "$" + ResolveColumnAddress(column) + (row + 1);
                case AddressType.FixedRow:
                    return ResolveColumnAddress(column) + "$" + (row + 1);
                default:
                    return ResolveColumnAddress(column) + (row + 1);
            }
        }

        public static Address ResolveCellCoordinate(string address)
        {
            int row;
            int column;
            AddressType type;
            ResolveCellCoordinate(address, out column, out row, out type);
            return new Address(column, row, type);
        }

        public static void ResolveCellCoordinate(string address, out int column, out int row)
        {
            ResolveCellCoordinate(address, out column, out row, out _);
        }

        public static void ResolveCellCoordinate(string address, out int column, out int row, out AddressType addressType)
        {
            if (string.IsNullOrEmpty(address))
            {
                throw new FormatException("The cell address is null or empty and could not be resolved");
            }
 
            int i = 0;
            int len = address.Length;
            bool fixedCol = false;
            bool fixedRow = false;
 
            // Optional $ for column
            if (i < len && address[i] == '$') { fixedCol = true; i++; }
 
            // Column letters
            int colStart = i;
            while (i < len && ((address[i] >= 'A' && address[i] <= 'Z') || (address[i] >= 'a' && address[i] <= 'z')))
            {
                i++;
            }
            if (i == colStart)
            {
                throw new FormatException("The format of the cell address (" + address + ") is malformed");
            }
 
            string colPart = address.Substring(colStart, i - colStart);
 
            // Optional $ for row
            if (i < len && address[i] == '$') { fixedRow = true; i++; }
 
            // Row digits
            int rowStart = i;
            while (i < len && address[i] >= '0' && address[i] <= '9')
            {
                i++;
            }
 
            if (i == rowStart || i != len)
            {
                throw new FormatException("The format of the cell address (" + address + ") is malformed");
            }
 
            row = int.Parse(address.Substring(rowStart, i - rowStart), NumberStyles.Integer, CultureInfo.InvariantCulture) - 1;
            column = ResolveColumn(colPart);
            ValidateRowNumber(row);
 
            if (fixedCol && fixedRow) { addressType = AddressType.FixedRowAndColumn; }
            else if (fixedCol) { addressType = AddressType.FixedColumn; }
            else if (fixedRow) { addressType = AddressType.FixedRow; }
            else { addressType = AddressType.Default; }
        }

        public static Range ResolveCellRange(string range)
        {
            if (string.IsNullOrEmpty(range))
            {
                throw new FormatException("The cell range is null or empty and could not be resolved");
            }
            if (!range.Contains(":"))
            {
                return new Range(ResolveCellCoordinate(range), ResolveCellCoordinate(range));
            }
            string[] split = range.Split(':');
            if (split.Length != 2)
            {
                throw new FormatException("The cell range (" + range + ") is malformed and could not be resolved");
            }
            return new Range(ResolveCellCoordinate(split[0]), ResolveCellCoordinate(split[1]));
        }

        public static int ResolveColumn(string columnAddress)
        {
            if (String.IsNullOrEmpty(columnAddress))
            {
                throw new RangeException("The passed address was null or empty");
            }
            columnAddress = ParserUtils.ToUpper(columnAddress);
            int chr;
            int result = 0;
            int multiplier = 1;
            for (int i = columnAddress.Length - 1; i >= 0; i--)
            {
                chr = columnAddress[i];
                chr -= ASCII_OFFSET;
                result += (chr * multiplier);
                multiplier *= 26;
            }
            ValidateColumnNumber(result - 1);
            return result - 1;
        }

        public static string ResolveColumnAddress(int columnNumber)
        {
            ValidateColumnNumber(columnNumber);
            // A - XFD
            StringBuilder sb = new StringBuilder();
            columnNumber++;
            while (columnNumber > 0)
            {
                columnNumber--;
                sb.Insert(0, (char)('A' + (columnNumber % 26)));
                columnNumber /= 26;
            }
            return sb.ToString();
        }

        public static AddressScope GetAddressScope(string addressExpression)
        {
            try
            {
                ResolveCellCoordinate(addressExpression);
                return AddressScope.SingleAddress;
            }
            catch
            {
                try
                {
                    ResolveCellRange(addressExpression);
                    return AddressScope.Range;
                }
                catch
                {
                    return AddressScope.Invalid;
                }
            }
 
        }

        public static void ValidateColumnNumber(int column)
        {
            if (column > Worksheet.MaxColumnNumber || column < Worksheet.MinColumnNumber)
            {
                throw new RangeException("The column number (" + column + ") is out of range. Range is from " +
                    Worksheet.MinColumnNumber + " to " + Worksheet.MaxColumnNumber + " (" + (Worksheet.MaxColumnNumber + 1) + " columns).");
            }
        }

        public static void ValidateRowNumber(int row)
        {
            if (row > Worksheet.MaxRowNumber || row < Worksheet.MinRowNumber)
            {
                throw new RangeException("The row number (" + row + ") is out of range. Range is from " +
                    Worksheet.MinRowNumber + " to " + Worksheet.MaxRowNumber + " (" + (Worksheet.MaxRowNumber + 1) + " rows).");
            }
        }

        internal void BindFeatures(FeatureSet features)
        {
            if (ReferenceEquals(worksheetFeatures, features))
            {
                return;
            }
            UnbindFeatures();
            worksheetFeatures = features;
            AttachFormulaFeatures();
        }

        internal void UnbindFeatures()
        {
            DetachFormulaFeatures();
            worksheetFeatures = null;
        }

        private void SynchronitzeValueFromFormula()
        {
            if (formula.MasterCellAddress == null)
            {
                this.value = formula.Expression;  // Sync back from formula to cell
            }
        }

        private void AttachFormulaFeatures()
        {
            if (worksheetFeatures != null && dataType == CellType.Formula && formula != null)
            {
                formula.Features.Add(worksheetFeatures);
            }
        }

        private void ClearFormula()
        {
            DetachFormulaFeatures();
            formula = null;
        }

        private void DetachFormulaFeatures()
        {
            if (worksheetFeatures != null && dataType == CellType.Formula && formula != null)
            {
                formula.Features.Remove(worksheetFeatures);
            }
        }

        private string GetValueAsFormulaExpression()
        {
            return value == null ? null : value.ToString();
        }

        private Range TransposeDefinedNameArrayRange(string referenceExpression)
        {
            Range resolvedRange = new Range(referenceExpression);
            int rowCount = resolvedRange.EndAddress.Row - resolvedRange.StartAddress.Row;
            int columnCount = resolvedRange.EndAddress.Column - resolvedRange.StartAddress.Column;
            return new Range(this.ColumnNumber, this.rowNumber, this.ColumnNumber + columnCount, this.rowNumber + rowCount);
        }
 
        #endregion
 
    }
}