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

Convert A1 address to integer row and column?

Jan 31, 2013 at 4:48 PM
What is the best way to convert A1, E14 etc address to a row x and y?

thanks
Jan 31, 2013 at 9:56 PM
Edited Jan 31, 2013 at 10:18 PM
Here is an example that takes integer row and integer column values to create Excel "A1" type cell address to create a formula that uses a range of cells B2 to B65 -> = SUM(B2:B65)

excel_worksheet_1.Cells[67, 2].Formula = string.Format("SUM({0}:{1})", ExcelCellBase.GetAddress(2, 2), ExcelCellBase.GetAddress(65, 2));

Is this what you are looking for?
Jan 31, 2013 at 10:01 PM
Thanks but I do not see it. I have E14 address and want to get the underlying x,y in integer.

The GetAdress only seems to accept integers and not strings.
Jan 31, 2013 at 10:35 PM
I found a solution at http://stackoverflow.com/questions/13994121/get-cells-row-number-using-epplus

So this will work:

ExcelRange cella = excel_worksheet_0.Cells["E14"];
Int32 cell_row = cella.Start.Row; // returns 14
Int32 cell_column = cella.Start.Column; // returns 5
Jan 31, 2013 at 11:08 PM
This is simpler:
  Int32 cellb_row = excel_worksheet_0.Cells["E14"].Start.Row; // returns 14
  Int32 cellb_column = excel_worksheet_0.Cells["E14"].Start.Column; // returns 5
Jan 31, 2013 at 11:09 PM
That worked great - thanks. This is not related but when I have found it and try to write a string value like this:

ws.Cells(startRow, curCol).Value = col

Then Save. But nothing is updated in the document. Any ideas?

I tried to hard code with ws.Cells(1, 1).Value = "test" but nothing was updated.
Feb 1, 2013 at 3:40 AM
Edited Feb 1, 2013 at 3:41 AM
Text that is not a number and will not be used for numeric\date like calculations should be assigned to a cell as:

worksheet.Cells(1,1).Text = a_string;

Otherwise assigning a string of text to a cell as a value:

worksheet.Cells(1,1).Value = a_string;

Will put a string into a worksheet cell but Excel will flag it as an error 'The number in the cell is formatted as text or preceded by an apostrophe'

..?
Feb 2, 2013 at 10:26 AM
Thank you for all your help and answers!