Store selected column value from Excel to an Array using column letters in C# Excel Interop

Question

I am working with excel in importing records to the program I am currently creating. I usually get records from excel and store it to array through column index as follows:

Excel.Range xlRange = xlWorksheet.UsedRange;
string[,] arr = new string[rowCount, colCount];

for (int i = 1; i <= rowCount; i++)
{
    for (int j = 1; j <= colCount; j++)
    {
        arr[i, j] = xlRange.Cells[i, j].Value;
    }
}

Now, in the work I am currently doing, column index is unknown. The only given is the column Name such as column "AP", "QR", etc. The user will be the one to define what column will he get the data from.

Is there any way like arr[i, j] = xlRange.Cells["AP", j].Value;?

NOTE:

The array will contain data from two or more columns, not just one.

The columns there are the one seen on the upper part of the excel. Such as cell A1, the column is "A"; AP2, the column is "AP". — ThEpRoGrAmMiNgNoOb, Nov 19 '15 at 04:02

score 1 · Accepted Answer · edited May 23 '17 at 11:51

I found an answer here. Convert the column names to range through the following function:

public static int GetColumnNumber(string name)
{
    int number = 0;
    int pow = 1;
    for (int i = name.Length - 1; i >= 0; i--)
    {
         number += (name[i] - 'A' + 1) * pow;
         pow *= 26;
    }  

    return number;
}

Change the row index to the converted number.

arr[i, j] = xlRange.Cells[GetColumnNumber("PA"), j].Value;

score 1 · Answer 2 · answered Nov 19 '15 at 07:56

I think the Range or get_Range indexer in C# will use either the row/column or the semantic cell description. So, either of these should work:

Excel.Range r = sheet.Range[1, 1];

or

Excel.Range r = sheet.Range["A1"];

Which means you could even simply this and let Excel to the heavy lifting:

public string GetRange(string Column, int Row)
{
    return string.Format("{0}{1}", Column, Row);
}

Store selected column value from Excel to an Array using column letters in C# Excel Interop

2 Answers2