0

Requiring to build a UDF to be used in Excel, using Excel DNA, to present a result specially sorted in a jagged manner, using C#'s IComparer I was quite surprised as I was not getting a result required as expected.

While using the same code in a Console App the outcome is working well enough, for which code used is:

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

namespace Clxn
{
    public static class fComparer
    {
        static void Main(string[] args)
        {
            object[,] MainRange =
            {
                { "c", "E", "B" },
                { "b", "A", "B" },
                { "A", "D", "D" },
                { "F", "D", "C" }
            };

            int Cntr = 0;
            object[] SortingKey = (from object x in MainRange select x).ToArray();
            object[,] Rxlt = new object[SortingKey.Length, MainRange.GetLength(1) + 1];

            for (int xCol = 0; xCol < Rxlt.GetLength(1); xCol++)
            {
                Cntr = 0;
                for (int xRow = 0; xRow < Rxlt.GetLength(0); xRow++)
                {
                    if (xCol == 0) { Rxlt[xRow, 0] = SortingKey.OrderBy(x => x, new fxl_ComparerObjects()).ToArray()[(Rxlt.GetLength(0) * (xCol)) + xRow]; }
                    else
                    {
                        object[] OrderedSlice = SlicedColumn(MainRange, xCol - 1).ToArray().OrderBy(f => f, new fxl_ComparerObjects()).ToArray();
                        if (Rxlt[xRow, 0] == OrderedSlice[Cntr]) { Rxlt[xRow, xCol] = OrderedSlice[Cntr]; Cntr++; }
                    }
                    if (Cntr >= MainRange.GetLength(0)) Cntr = 0;
                }
            }

            for (int xRow = 0; xRow < Rxlt.GetLength(0); xRow++)
            {
                for (int xCol = 0; xCol < Rxlt.GetLength(1); xCol++) { Console.Write(Rxlt[xRow, xCol] + "\t"); }
                Console.WriteLine("");
            }
        }

        internal static IEnumerable<T> SlicedColumn<T>(this T[,] array, int column)
        {
            for (var i = array.GetLowerBound(0); i <= array.GetUpperBound(0); i++) { yield return array[i, column]; }
        }

        internal class fxl_ComparerObjects : IComparer<object>
        {
            public int SorterIndex { get; set; }
            public int Compare(object x, object y)
            {
                double xVal, yVal;
                var xIsVal = double.TryParse(x.ToString(), out xVal);
                var yIsVal = double.TryParse(y.ToString(), out yVal);
                if (xIsVal && yIsVal) return xVal.CompareTo(yVal);
                if (!xIsVal && !yIsVal) return x.ToString().CompareTo(y.ToString());
                if (xIsVal) return -1;
                return 1;
            }
        }
    }
}

which, in case of Console App, as required and expected, leads to generate a sorted Key Column first and then from the double dimension array each and every relevant column's value being placed only if matching, at the very first match, i.e.:

enter image description here

However, was surprised the same code being used for Excel leads out to be a result like:

x

i.e. the values being rather spread out to fill each and every column and row, although the code for the same used, was exactly the same.

Upon debugging, I have also found some of the values, although being same, as per the clips as follows, declared to be not equal, although the very first ones of both the array variables being "A":

x

enter image description here

Upon hovering the cursor on a statement like the following, the result is also found to be not equal, i.e. false being generated:

enter image description here

What could be the reason behind the same?

Thanks in advance for all your contribution.

  • I'm confused. You declare the array as `object` type, yet the values are `string`. But, you use `double` to draw the comparisons in `IComparer`. Is there reasoning behind this? – beautifulcoder Mar 05 '23 at 17:52
  • Dear @beautifulcoder, data to compare and sort could be either in string format or a number, as is the case in Excel, therefore needs to be declared as an object. – Faraz Ahmed Qureshi Mar 05 '23 at 17:54

0 Answers0