Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How can i get the Cell address from excel

Tags:

c#

excel

How can i get the Cell address from excel given a row and column number for example

row 2 and col 3 should return C2... Please help

like image 748
Sathish Avatar asked Apr 13 '10 10:04

Sathish


3 Answers

I'm not a big user of VSTO C# - I usually opt for VBA. However, the following pair of functions might be useful for you. Unsure if they can be streamlined some more:

public string RangeAddress(Excel.Range rng)
{
    return rng.get_AddressLocal(false, false, Excel.XlReferenceStyle.xlA1,
           missing, missing);
}
public string CellAddress(Excel.Worksheet sht, int row, int col)
{
    return RangeAddress(sht.Cells[row, col]);
}

The RangeAddress will take any Excel range and throw you back the address. If you want the absolute style (with dollar signs, e.g. $C$3) then you have to change the first two parameters of the get_AddressLocal call to true, true.

To get the cell address from a row/col pair, you can use CellAddress. It does need a sheet to get the address. However, you could swap in (Excel.Worksheet)ActiveSheet if you don't want to provide a sheet (this may or may not work, depending on what you have open in your VSTO session).

like image 171
Joel Goodwin Avatar answered Nov 02 '22 08:11

Joel Goodwin


Just an improvement, the col-- was in the wrong place

static string GetCellAddress(int row, int col)
    {
        StringBuilder sb = new StringBuilder();

       do
        {
            col--;
            sb.Insert(0, (char)('A' + (col % 26)));
            col /= 26;
        } while (col > 0);
        sb.Append(row);
        return sb.ToString();
    }
like image 3
Gabriel Rasdale Avatar answered Nov 02 '22 08:11

Gabriel Rasdale


This one (untested) should also work with column addresses over that are over 26:

using System.Text;

public string GetCellAddress(int row, int col) {
    StringBuilder sb = new StringBuilder();
    col--;
    do {
        sb.Insert(0, (char)('A' + (col % 26)));
        col /= 26;
    } while (col > 0);
    sb.Append(row);
    return sb.ToString();
}

Corrected: the column address was backwards

like image 2
Matti Virkkunen Avatar answered Nov 02 '22 08:11

Matti Virkkunen