How to get xl notation (eg:AA2) from position (eg:27,2)

Feb 9, 2011 at 6:15 AM


I was wondering if it is possible to get xl notation such as AA1,AB2 etc from coordinates like (27,1)  and (28,2).

my scenario is similar to the following

say i want to list 40+ products, column wise and just below each product name there is purchase cost, margin and selling price.

now i want to calculate selling price using xl formula like this '=SUM(AA2:AA3)'

In my program i am using integers to locate cells like this         worksheet.Cell(27,4).value="xyz"

to set the formula i need excel notation like AA1 etc right?

so i was wondering if there is an easy way like this '=SUM(    cell(27,2)    ,    cell(27,3)    )'

where cell(x,y) will automatically translate its coordinate to xl notation       


Please tell me how to do this. is there a way to do this with ClosedXML already?


Feb 9, 2011 at 3:31 PM
Edited Feb 9, 2011 at 6:12 PM

There is...

Getting the address in A1 notation:

worksheet.Cell(1, 27).Address.ToString()
worksheet.Cell(2, 28).Address.ToString()

Using this information to set a formula in A1 notation:

var cell1 = worksheet.Cell(1, 27);
var cell2 = worksheet.Cell(2, 28);
worksheet.Cell(1, 1).FormulaA1 = String.Format("SUM({0}:{1})", cell1.Address.ToString(), cell2.Address.ToString());

One thing you may not be aware of is that you can go from A1 to R1C1 notation and vice-versa. So in the previous example after setting the FormulaA1 if you call the property worksheet.Cell(1, 1).FormulaR1C1 and  you will get:




"to set the formula i need excel notation like AA1 etc right?"

You can set the formulas in either A1 or R1C1 notation, whichever is more convenient for you.

Feb 11, 2011 at 5:05 AM

Thank you

May 20, 2011 at 12:09 PM

Hi MDeLeon,

As per your code we find the cell address from worksheet.Cell(1, 27).Address.ToString() return "AA1"

Is there any solution send parameter "AA1" return result 1,27?

thanks in advance


May 20, 2011 at 3:47 PM

I'll add the following overload:

IXLAddress.ToString(XLReferenceStyle referenceStyle)