This project has moved. For the latest updates, please go here.

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

Feb 9, 2011 at 7:15 AM

Hi,

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?

Thanks

Coordinator
Feb 9, 2011 at 4:31 PM
Edited Feb 9, 2011 at 7:12 PM

There is...

Getting the address in A1 notation:

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

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:

"SUM(RC[26]:R[1]C[27])"

 

Edit:

"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 6:05 AM

Thank you

May 20, 2011 at 1: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

Chandan

Coordinator
May 20, 2011 at 4:47 PM

I'll add the following overload:

IXLAddress.ToString(XLReferenceStyle referenceStyle)