Convert Excel Cell coords to x,y ( C# )

In C#, I need code that will convert an Excel column / row coordinate to zero-based x,y (ordinal) values.  Assume no VSTO or Interop support.

I need to support the following:

A1 (0, 0)
A500  (500, 0)
AA1   (0, 26)
AA500 (500, 26)
XFD5 (5, 16384)   (which is as far as I can go, column-wise)


*** NOTE:  in Excel you can obtain the column value with formula:

=COLUMN(XFD5)

But like I said, I need to be able to do this programmatically in C# without the benefit of Excel Interop or VSTO.

Thank you!
LVL 5
Tom KnowltonWeb developerAsked:
Who is Participating?
 
Fernando SotoRetiredCommented:
This should do what you need.
using System.Text.RegularExpressions;

// Test Excel Cell
string excelCell = "A500";
// Get the Excel column name
string colName = Regex.Split(excelCell, @"\d")[0].ToUpper();
// Get the Excel row number
long y = 0;
long.TryParse(Regex.Match(excelCell, @"\d+").Value, out y);
// Get column index as long int 
int x = colName.Select((c, i) =>
    ((c - 'A' + 1) * ((int)Math.Pow(26, colName.Length - i - 1)))).Sum();

Open in new window

0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
the row is the numeric portion.
The column name is the
from:
https://stackoverflow.com/questions/10073710/parse-cell-location-string-into-row-column  (gets the name and number separately) and
https://stackoverflow.com/questions/667802/what-is-the-algorithm-to-convert-an-excel-column-letter-into-its-number (converts column name to a number)

        	       string address ="AA2";
           string col;
           int ColNum;
           int row;

          StringBuilder sb = new StringBuilder( );
        int i= 0;
		
           for( ; i < address.Length
                   && ! Char.IsDigit( address[ i ] ) 
                ; ++i )
           {
               sb.Append( address[ i ] );
           }

           col = sb.ToString( );
          ColNum = ExcelColumnNameToNumber(col);
           sb.Clear( );

           for ( ; i < address.Length
                    && Char.IsDigit( address[ i ] )
                 ; ++i )
           {
               sb.Append( address[ i ] );
           }

           row = Int32.Parse( sb.ToString( ) );
		
		Console.WriteLine(string.Format("({0},{1})", row, ColNum));
						   
		
    }
public static int ExcelColumnNameToNumber(string columnName)
{
    if (string.IsNullOrEmpty(columnName)) throw new ArgumentNullException("columnName");

    columnName = columnName.ToUpperInvariant();

    int sum = 0;

    for (int i = 0; i < columnName.Length; i++)
    {
        sum *= 25;
        sum += (columnName[i] - 'A' + 1);
    }

    return sum;
}

Open in new window


Dot Net Fiddle: https://dotnetfiddle.net/ny4Ei5
0
 
Tom KnowltonWeb developerAuthor Commented:
Thank you
0
 
Fernando SotoRetiredCommented:
👍
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.