Alex Rivera | Logout

Rebase a 1-based array in c#

Asked 2009-09-30T12:20:29.123
11

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.

Edit
Report

4 Answers

10

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.

answered 2009-09-30T12:22:19.530
0

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.

answered 2009-09-30T12:23:27.720
0

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[,
answered 2013-04-16T17:12:38.327
-1

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

answered 2009-09-30T21:35:19.447

Your Answer