Rebase a 1-based array in c#

Question

I have an array in c# that is 1-based (generated from a call to get_Value for an Excel Range I get a 2D array for example

object[,] ExcelData = (object[,]) MySheet.UsedRange.get_Value(Excel.XlRangeValueDataType.xlRangeValueDefault);

this appears as an array for example ExcelData[1..20,1..5]

is there any way to tell the compiler to rebase this so that I do not need to add 1 to loop counters the whole time?

List<string> RowHeadings = new List<string>();
string [,] Results = new string[MaxRows, 1]
for (int Row = 0; Row < MaxRows; Row++) {
    if (ExcelData[Row+1, 1] != null)
        RowHeadings.Add(ExcelData[Row+1, 1]);
        ...
        ...
        Results[Row, 0] = ExcelData[Row+1, 1];
        & other stuff in here that requires a 0-based Row
}

It makes things less readable since when creating an array for writing the array will be zero based.

Woodprint

Arrays in c# are always 0 based, do you mean your data is 1-based? – Puberulent

Arrays declared in C# source code are always 0-based, but the CLR supports arrays with arbitrary lower bounds. – Pentagram

Arrays created in c# may well be zero based, however I believe that I am correct in stating that the the array here is 1 based - if you try and access ExcelData[0,0] the IncexOutOfRangeException is thrown. – Woodprint

Maybe this a stupid question, but did you validate that ExcelData has any data in it? – Uninspired

@ LMoser - not a stupid question, relavent but not part of this question. Thanks for 3 excellent and different ways of solving the problem - the issue now is that SO only allows 1 'correct' answer! 1. Copy the array to a zero-based one (@Andrew Hare) 2. Add second index (for int Row = 0, ExcelRow = 1; Row < ... ; Row++, ExcelRow++) (@Jason) 3. wrap the array and offset within the new class (@Darren Thomas) In re-evaluating in the light of these three I have chosen option 1 (and have now written wrappers for all excel range functions) so that everything in my code is zero-based. – Woodprint

Answer 1

Why not just change your index?

List<string> RowHeadings = new List<string>();
for (int Row = 1; Row <= MaxRows; Row++) {
    if (ExcelData[Row, 1] != null)
        RowHeadings.Add(ExcelData[Row, 1]);
}

Edit: Here is an extension method that would create a new, zero-based array from your original one (basically it just creates a new array that is one element smaller and copies to that new array all elements but the first element that you are currently skipping anyhow):

public static T[] ToZeroBasedArray<T>(this T[] array)
{
    int len = array.Length - 1;
    T[] newArray = new T[len];
    Array.Copy(array, 1, newArray, 0, len);
    return newArray;
}

That being said you need to consider if the penalty (however slight) of creating a new array is worth improving the readability of the code. I am not making a judgment (it very well may be worth it) I am just making sure you don't run with this code if it will hurt the performance of your application.

Iyar

I can't believe tree of us posted the same answer at the same time :) – Germaun

sorry - the code I supplied is too simple - Row is used elsewhere in the for loop requiring zero-based counting. – Woodprint

Answer 2

Create a wrapper for the ExcelData array with a this[,] indexer and do rebasing logic there. Something like:

class ExcelDataWrapper
{
    private object[,] _excelData;
    public ExcelDataWrapper(object[,] excelData)
    {
        _excelData = excelData;
    }
    public object this[int x, int y]
    {
        return _excelData[x+1, y+1];
    }
}

Greek

I like this solution because you can make it dynamic. I added a 'start index' parameter to the constructor so this class can be used for 1 and 0 based arrays alike. I also added RowCount & ColumnCount properties instead of having to specify array.GetLength(x) – Lampion

Answer 3

Since you need Row to remain as-is (based on your comments), you could just introduce another loop variable:

List<string> RowHeadings = new List<string>();
string [,] Results = new string[MaxRows, 1]
for (int Row = 0, SrcRow = 1; SrcRow <= MaxRows; Row++, SrcRow++) {
    if (ExcelData[SrcRow, 1] != null)
        RowHeadings.Add(ExcelData[SrcRow, 1]);
        ...
        ...
        Results[Row, 0] = ExcelData[SrcRow, 1];
}

Stilted

Answer 4

Why not use:

for (int Row = 1; Row <= MaxRows; Row++) {

Or is there something I'm missing?

EDIT: as it turns out that something is missing, I would use another counter (starting at 0) for that purpose, and use a 1 based Row index for the array. It's not good practice to use the index for another use than the index in the target array.

Germaun

Answer 5

Is changing the loop counter too hard for you?

for (int Row = 1; Row <= MaxRows; Row++)

If the counter's range is right, you don't have to add 1 to anything inside the loop so you don't lose readability. Keep it simple.

Streetwalker

no need to be too patronising - see changes to the original question (others said the same thing slightly more constructively)... – Woodprint

Answer 6

I agree that working with base-1 arrays from .NET can be a hassle. It is also potentially error-prone, as you have to mentally make a shift each time you use it, as well as correctly remember which situations will be base 1 and which will be base 0.

The most performant approach is to simply make these mental shifts and index appropriately, using base-1 or base-0 as required.

I personally prefer to convert the two dimensional base-1 arrays to two dimensional base-0 arrays. This, unfortunately, requires the performance hit of copying over the array to a new array, as there is no way to re-base an array in place.

Here's an extension method that can do this for the 2D arrays returned by Excel:

public static TResult[,] CloneBase0<TSource, TResult>(
    this TSource[,] sourceArray)
{
    If (sourceArray == null)
    {
        throw new ArgumentNullException(
            "The 'sourceArray' is null, which is invalid.");
    }

    int numRows = sourceArray.GetLength(0);
    int numColumns = sourceArray.GetLength(1);
    TResult[,] resultArray = new TResult[numRows, numColumns];

    int lb1 = sourceArray.GetLowerBound(0); 
    int lb2 = sourceArray.GetLowerBound(1); 

    for (int r = 0; r < numRows; r++)
    {
        for (int c = 0; c < numColumns; c++)
        {
            resultArray[r, c] = sourceArray[lb1 + r, lb2 + c];
        }
    }

    return resultArray;
}

And then you can use it like this:

object[,] array2DBase1 = (object[,]) MySheet.UsedRange.get_Value(Type.Missing);

object[,] array2DBase0 = array2DBase1.CloneBase0();

for (int row = 0; row < array2DBase0.GetLength(0); row++) 
{
    for (int column = 0; column < array2DBase0.GetLength(1); column++) 
    {
        // Your code goes here...
    }
}

For massively sized arrays, you might not want to do this, but I find that, in general, it really cleans up your code (and mind-set) to make this conversion, and then always work in base-0.

Hope this helps...

Mike

Lathery

thanks - an optimisation, having validated the lengths, you might like to use System.Array.Copy(from, to, length) which (presumably?) copies the 2D array more efficiently. – Woodprint

That's a nice idea, except that you can't. The 'Array.Copy' method only works with 1 dimensional arrays, while the array returned by Excel.Range.get_Value is a two dimensional array. So the extension method has to return TResult[,] based on TSource[,] and cannot use 'Array.Copy'. No choice! – Lathery

By the way, I use this approach for all my code when dealing with base-1 arrays returned by Excel. I suppose that I'm slowing down all my code, but not so much that I've noticed, and the ability to always deal in base-0 arrays really helps keep me sane. :) – Lathery

I know this is old, but you can use Array.Copy with 2D arrays if you are changing the number of rows and not columns. 2D arrays are laid out sequentially in memory, so if the columns don't change, the assumption of where the one row ends and the next begins will still line up. – Varix

Answer 7

For 1 based arrays and Excel range operations as well as UDF (SharePoint) functions I use this utility function

public static object[,] ToObjectArray(this Object Range)
    {
        Type type = Range.GetType();
        if (type.IsArray && type.Name == "Object[,]")
        {
            var sourceArray = Range as Object[,];               

            int lb1 = sourceArray.GetLowerBound(0);
            int lb2 = sourceArray.GetLowerBound(1);
            if (lb1 == 0 && lb2 == 0)
            {
                return sourceArray;
            }
            else
            {
                int numRows = sourceArray.GetLength(0);
                int numColumns = sourceArray.GetLength(1);
                var resultArray = new Object[numRows, numColumns];
                for (int r = 0; r < numRows; r++)
                {
                    for (int c = 0; c < numColumns; c++)
                    {
                        resultArray[r, c] = sourceArray[lb1 + r, lb2 + c];
                    }
                }

                return resultArray;
            }

        }
        else if (type.IsCOMObject) 
        {
            // Get the Value2 property from the object.
            Object value = type.InvokeMember("Value2",

                   System.Reflection.BindingFlags.Instance |

                   System.Reflection.BindingFlags.Public |

                   System.Reflection.BindingFlags.GetProperty,

                   null,

                   Range,

                   null);
            if (value == null)
                value = string.Empty;
            if (value is string)
                return new object[,] { { value } };
            else if (value is double)
                return new object[,] { { value } };
            else
            {
                object[,] range = (object[,])value;

                int rows = range.GetLength(0);

                int columns = range.GetLength(1);

                object[,] param = new object[rows, columns];

                Array.Copy(range, param, rows * columns);
                return param;
            }
        }

        else
            throw new ArgumentException("Not A Excel Range Com Object");

    }

Usage

    public object[,] RemoveZeros(object range)
    {
        return this.RemoveZeros(range.ToObjectArray());
    }
    [ComVisible(false)]
    [UdfMethod(IsVolatile = false)]
    public object[,] RemoveZeros(Object[,] range)
    {...}

The first function is com visible and will accept an excel range or a chained call from another function (the chained call will return a 1 based object array), the second call is UDF enabled for Excel Services in SharePoint. All of the logic is in the second function. In this example we are just reformatting a range to replace zero with string.empty.

Angulate

Answer 8

You could use a 3rd party Excel compatible component such as SpreadsheetGear for .NET which has .NET friendly APIs - including 0 based indexing for APIs such as IRange[int rowIndex, int colIndex].

Such components will also be much faster than the Excel API in almost all cases.

Disclaimer: I own SpreadsheetGear LLC

Marchpast

Thanks, though seems pricey for all I need currently (a set of wrappers) - there is no mention of marshalling that I found - would I still need to handle that or does SpreadsheetGear handle it all? – Woodprint

SpreadsheetGear is not just a wrapper for Excel, but a safe .NET assembly written in C#. It does not use COM Interop for anything. It does not rely on Excel. It is an Excel compatible spreadsheet component with it's own calculation, editing, formatting, rendering, etc... engine. In many cases customers tell us that applications speed up dramatically when they move from COM Interop / Excel Automation to SpreadsheetGear. You are also freed from worrying about which version of Excel you users have. – Marchpast