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.

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.

Answer 2

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.

Answer 3

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.

Answer 4

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];
    }
}

Answer 5

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];
}

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

Answer 7

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