How to get column index in Excel using excel-column-name format in C#

Question

I have found here http://lateral8.com/articles/2010/3/5/openxml-sdk-20-export-a-datatable-to-excel.aspx a great function

private string getColumnName(int columnIndex)
{
    int dividend = columnIndex;
    string columnName = String.Empty;
    int modifier;

    while (dividend > 0)
    {
        modifier = (dividend - 1) % 26;
        columnName = 
            Convert.ToChar(65 + modifier).ToString() + columnName;
        dividend = (int)((dividend - modifier) / 26);
    }

    return columnName;
}

I would like to do similar thing - provide column name and receive index. For example provide name 'AB' and receive as result index 28. How to do this?

UPDATE:

Surprising, I have found solution in the comments section

here https://stackoverflow.com/a/8739121/907732

and here https://stackoverflow.com/a/4888750/907732

Possible duplicate - stackoverflow.com/questions/10373561/… — JMK
– JMK, Commented Feb 26, 2013 at 11:28
I linked to a question about the inverse method, and yes AB should be 28 — JMK
– JMK, Commented Feb 26, 2013 at 11:32

singapore saravanan · Accepted Answer · 2013-07-10 12:49:09Z

0

public static string GetColumnName(int index)
{
    const string letters = "ZABCDEFGHIJKLMNOPQRSTUVWXY";

    int NextPos = (index / 26);
    int LastPos = (index % 26);
    if (LastPos == 0) NextPos--;

    if (index > 26)
        return GetColumnName(NextPos) + letters[LastPos];
    else
        return letters[LastPos] + "";
}

answered Jul 10, 2013 at 12:49

singapore saravanan

214 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to get column index in Excel using excel-column-name format in C#

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related