How can i get the Cell address from excel

Question

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

Answer 1

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).

Answer 2

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

Answer 3

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

Answer 4

Cells(2, 3).Address

Answer 5

I'm using zero-based row and column index. So I had to adapt the code of prior answers to the following. It took some trial and error to get this right for all possible addresses i.E. A1, Z2, AA2, ZZ10, ...

    public string GetAddress(int col, int row)
    {
        col++;
        StringBuilder sb = new StringBuilder();
        do
        {
            col--;
            sb.Insert(0, (char)('A' + (col % 26)));
            col /= 26;

        } while (col > 0);
        sb.Append(row + 1);
        return sb.ToString();
    }

Answer 6

public string GetCellAddress(int row, int col) {
        return (char)((int)'A' + (row - 1)) + col.ToString();
}