CODEWITHSUNDEEP
c#arraysexcel-interop
ThEpRoGrAmMiNgNoOb
ThEpRoGrAmMiNgNoOb

Reputation: 1294

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

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.

Upvotes: 0

Views: 876

Answers (2)

Hambone
Hambone

Reputation: 16377

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

Upvotes: 1

ThEpRoGrAmMiNgNoOb
ThEpRoGrAmMiNgNoOb

Reputation: 1294

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;

Upvotes: 1

Related Questions