BasicFormulas.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.Globalization;
using NanoXLSX.Utils;
using FormatException = NanoXLSX.Exceptions.FormatException;
 
namespace NanoXLSX
{
    public static class BasicFormulas
    {
        public static Cell Average(Range range)
        { return Average(null, range); }

        public static Cell Average(Worksheet target, Range range)
        { return GetBasicFormula(target, range, "AVERAGE", null); }

        public static Cell Ceil(Address address, int decimals)
        { return Ceil(null, address, decimals); }

        public static Cell Ceil(Worksheet target, Address address, int decimals)
        { return GetBasicFormula(target, new Range(address, address), "ROUNDUP", decimals.ToString(CultureInfo.InvariantCulture)); }

        public static Cell Floor(Address address, int decimals)
        { return Floor(null, address, decimals); }

        public static Cell Floor(Worksheet target, Address address, int decimals)
        { return GetBasicFormula(target, new Range(address, address), "ROUNDDOWN", decimals.ToString(CultureInfo.InvariantCulture)); }

        public static Cell Max(Range range)
        { return Max(null, range); }

        public static Cell Max(Worksheet target, Range range)
        { return GetBasicFormula(target, range, "MAX", null); }

        public static Cell Median(Range range)
        { return Median(null, range); }

        public static Cell Median(Worksheet target, Range range)
        { return GetBasicFormula(target, range, "MEDIAN", null); }

        public static Cell Min(Range range)
        { return Min(null, range); }

        public static Cell Min(Worksheet target, Range range)
        { return GetBasicFormula(target, range, "MIN", null); }

        public static Cell Round(Address address, int decimals)
        { return Round(null, address, decimals); }

        public static Cell Round(Worksheet target, Address address, int decimals)
        { return GetBasicFormula(target, new Range(address, address), "ROUND", decimals.ToString(CultureInfo.InvariantCulture)); }

        public static Cell Sum(Range range)
        { return Sum(null, range); }

        public static Cell Sum(Worksheet target, Range range)
        { return GetBasicFormula(target, range, "SUM", null); }
 

        public static Cell VLookup(object number, Range range, int columnIndex, bool exactMatch)
        { return VLookup(number, null, range, columnIndex, exactMatch); }

        public static Cell VLookup(object number, Worksheet rangeTarget, Range range, int columnIndex, bool exactMatch)
        { return GetVLookup(null, new Address(), number, rangeTarget, range, columnIndex, exactMatch, true); }

        public static Cell VLookup(Address address, Range range, int columnIndex, bool exactMatch)
        { return VLookup(null, address, null, range, columnIndex, exactMatch); }

        public static Cell VLookup(Worksheet queryTarget, Address address, Worksheet rangeTarget, Range range, int columnIndex, bool exactMatch)
        {
            return GetVLookup(queryTarget, address, 0, rangeTarget, range, columnIndex, exactMatch, false);
        }

        private static Cell GetVLookup(Worksheet queryTarget, Address address, object number, Worksheet rangeTarget, Range range, int columnIndex, bool exactMatch, bool numericLookup)
        {
            int rangeWidth = Math.Abs(range.EndAddress.Column - range.StartAddress.Column) + 1;
            if (columnIndex < 1 || columnIndex > rangeWidth)
            {
                throw new FormatException("The column index on range " + range.ToString() + " can only be between 1 and " + rangeWidth);
            }
            CultureInfo culture = CultureInfo.InvariantCulture;
            string arg1;
            string arg2;
            string arg3;
            string arg4;
            if (numericLookup)
            {
                if (number == null)
                {
                    throw new FormatException("The lookup variable can only be a cell address or a numeric value. The passed value was null.");
                }
                Type t = number.GetType();
                if (t == typeof(byte))
                { arg1 = ((byte)number).ToString("G", culture); }
                else if (t == typeof(sbyte))
                { arg1 = ((sbyte)number).ToString("G", culture); }
                else if (t == typeof(decimal))
                { arg1 = ((decimal)number).ToString("G", culture); }
                else if (t == typeof(double))
                { arg1 = ((double)number).ToString("G", culture); }
                else if (t == typeof(float))
                { arg1 = ((float)number).ToString("G", culture); }
                else if (t == typeof(int))
                { arg1 = ((int)number).ToString("G", culture); }
                else if (t == typeof(uint))
                { arg1 = ((uint)number).ToString("G", culture); }
                else if (t == typeof(long))
                { arg1 = ((long)number).ToString("G", culture); }
                else if (t == typeof(ulong))
                { arg1 = ((ulong)number).ToString("G", culture); }
                else if (t == typeof(short))
                { arg1 = ((short)number).ToString("G", culture); }
                else if (t == typeof(ushort))
                { arg1 = ((ushort)number).ToString("G", culture); }
                else
                {
                    throw new FormatException("The lookup variable can only be a cell address or a numeric value. The value '" + number + "' is invalid.");
                }
            }
            else
            {
                if (queryTarget != null)
                { arg1 = queryTarget.SheetName + "!" + address; }
                else { arg1 = address.ToString(); }
            }
            if (rangeTarget != null)
            { arg2 = rangeTarget.SheetName + "!" + range; }
            else { arg2 = range.ToString(); }
            arg3 = ParserUtils.ToString(columnIndex);
            if (exactMatch)
            { arg4 = "TRUE"; }
            else { arg4 = "FALSE"; }
            return new Cell("VLOOKUP(" + arg1 + "," + arg2 + "," + arg3 + "," + arg4 + ")", Cell.CellType.Formula);
        }
 

        private static Cell GetBasicFormula(Worksheet target, Range range, string functionName, string postArg)
        {
            string arg1;
            string arg2;
            string prefix;
            if (postArg == null)
            { arg2 = ""; }
            else { arg2 = "," + postArg; }
            if (target != null)
            { prefix = target.SheetName + "!"; }
            else { prefix = ""; }
            if (range.StartAddress.Equals(range.EndAddress))
            { arg1 = prefix + range.StartAddress; }
            else { arg1 = prefix + range; }
            return new Cell(functionName + "(" + arg1 + arg2 + ")", Cell.CellType.Formula);
        }
    }
 
}