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 © 2025
 * 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 System.Text.RegularExpressions;
using NanoXLSX.Exceptions;
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,
            Default
        }

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

        public enum AddressScope
        {
            SingleAddress,
            Range,
            Invalid
        }
 
        #endregion
 
        #region privateFileds
        private Style cellStyle;
        private int columnNumber;
        private int rowNumber;
        private object value;
        #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; set; }
 

        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();
            }
 
        }
 
        #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.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;
        }

        public void ResolveCellType()
        {
            if (this.value == null)
            {
                DataType = CellType.Empty;
                this.value = null;
                return;
            }
            if (DataType == CellType.Formula)
            { return; }
            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 { 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
            };
            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);
                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");
            }
            address = ParserUtils.ToUpper(address);
            Regex pattern = new Regex("(^(\\$?)([A-Z]{1,3})(\\$?)([0-9]{1,7})$)");
            Match matcher = pattern.Match(address);
            if (matcher.Groups.Count != 6)
            {
                throw new FormatException("The format of the cell address (" + address + ") is malformed");
            }
            int digits = int.Parse(matcher.Groups[5].Value, CultureInfo.InvariantCulture);
            column = ResolveColumn(matcher.Groups[3].Value);
            row = digits - 1;
            ValidateRowNumber(row);
            if (!String.IsNullOrEmpty(matcher.Groups[2].Value) && !String.IsNullOrEmpty(matcher.Groups[4].Value))
            {
                addressType = AddressType.FixedRowAndColumn;
            }
            else if (!String.IsNullOrEmpty(matcher.Groups[2].Value) && String.IsNullOrEmpty(matcher.Groups[4].Value))
            {
                addressType = AddressType.FixedColumn;
            }
            else if (String.IsNullOrEmpty(matcher.Groups[2].Value) && !String.IsNullOrEmpty(matcher.Groups[4].Value))
            {
                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).");
            }
        }
        #endregion
 
    }
}