Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to convert Excel sheet column names into numbers? [duplicate]

Tags:

c#

excel

I was wondering what is the best way to convert excel sheet column names into numbers.

I'm working with Excel Package, a good library to handle .xlsx documents. This library unfortunately doesn't have this functionality included.

OBS: The first column, A, corresponds to number 1 in this library.

like image 384
Victor Rodrigues Avatar asked May 11 '09 13:05

Victor Rodrigues


People also ask

How do you convert Excel column names into numbers?

Here's how: In your Excel, click File > Options. In the Excel Options dialog box, select Formulas in the left pane. Under Working with formulas, check the R1C1 reference style box, and click OK.

How do I convert text to numbers in Excel?

Select all the cells that you want to convert from text to numbers. Click on the yellow diamond shape icon that appears at the top right. From the menu that appears, select 'Convert to Number' option.


4 Answers

This function should work for an arbitrary length column name.

public static int GetColumnNumber(string name)
{
    int number = 0;
    int pow = 1;
    for (int i = name.Length - 1; i >= 0; i--)
    {
        number += (name[i] - 'A' + 1) * pow;
        pow *= 26;
    }

    return number;
}
like image 50
Noldorin Avatar answered Oct 22 '22 10:10

Noldorin


I had to deal with this a few months ago. The inverse - column index to column name - is fun, too, and becomes really messy if you try to solve it with a zero based index not recognizing that this complicates things. It could be so simple if it would be a normal polyadic numeral system ...

Here is a simplified version of my solution as a extension method without error handling and all that stuff.

public static Int32 ToOneBasedIndex(this String name)
{
    return name.ToUpper().
       Aggregate(0, (column, letter) => 26 * column + letter - 'A' + 1);
}
like image 29
Daniel Brückner Avatar answered Oct 22 '22 12:10

Daniel Brückner


I've been working with this for a while now and found this to work really good for columns that go beyond A-Z, or even beyond AA-ZZ... It's accomplished by breaking apart each character in the string and recursively calling itself to derive the DEC value of the ASCII character (less 64), then multiplying it by 26^n. A return value of long was used to overcome a potential limitation when n > 4.

    public long columnNumber(String columnName)
    {
        char[] chars = columnName.ToUpper().ToCharArray();

        return (long)(Math.Pow(26, chars.Count() - 1)) * 
            (System.Convert.ToInt32(chars[0]) - 64) + 
            ((chars.Count() > 2) ? columnNumber(columnName.Substring(1, columnName.Length - 1)) : 
            ((chars.Count() == 2) ? (System.Convert.ToInt32(chars[chars.Count() - 1]) - 64) : 0));
    }

Also, if you'd like to get the inverse (i.e. pass in the columnNumber and get the columnName, here's some code that works for that.

    public String columnName(long columnNumber)
    {
        StringBuilder retVal = new StringBuilder();
        int x = 0;

        for (int n = (int)(Math.Log(25*(columnNumber + 1))/Math.Log(26)) - 1; n >= 0; n--)
        {
            x = (int)((Math.Pow(26,(n + 1)) - 1) / 25 - 1);
            if (columnNumber > x)
                retVal.Append(System.Convert.ToChar((int)(((columnNumber - x - 1) / Math.Pow(26, n)) % 26 + 65)));
        }

        return retVal.ToString();
    }
like image 36
Sam Martinez Avatar answered Oct 22 '22 12:10

Sam Martinez


Source code:

namespace XLS
{
/// <summary>
/// Represents a single cell in a excell sheet
/// </summary>
public struct Cell
{
    private long row;
    private long column;
    private string columnAddress;
    private string address;
    private bool dataChange;

    /// <summary>
    /// Initializes a new instance of the XLS.Cell 
    /// class with the specified row and column of excel worksheet
    /// </summary>
    /// <param name="row">The row index of a cell</param>
    /// <param name="column">The column index of a cell</param>
    public Cell(long row, long column)
    {
        this.row = row;
        this.column = column;
        dataChange = true;
        address = string.Empty;
        columnAddress = string.Empty;
    }

    /// <summary>
    /// Initializes a new instance of the XLS.Cell
    /// class with the specified address of excel worksheet
    /// </summary>
    /// <param name="address">The adress of a cell</param>
    public Cell(string address)
    {
        this.address = address;
        dataChange = false;
        row = GetRow(address);
        columnAddress = GetColumnAddress(address);
        column = GetColumn(columnAddress);
    }

    /// <summary>
    /// Gets or sets the row of this XLS.Cell
    /// </summary>
    public long Row
    {
        get { return row <= 0 ? 1 : row; }
        set { row = value; dataChange = true; }
    }

    /// <summary>
    /// Gets or sets the column of this XLS.Cell
    /// </summary>
    public long Column
    {
        get { return column <= 0 ? 1 : column; }
        set { column = value; dataChange = true; }
    }

    /// <summary>
    /// Gets or sets the address of this XLS.Cell
    /// </summary>
    public string Address
    {
        get { return dataChange ? ToAddress() : address; }
        set
        {
            address = value;
            row = GetRow(address);
            column = GetColumn(address);
        }
    }

    /// <summary>
    /// Gets the column address of this XLS.Cell
    /// </summary>
    public string ColumnAddress
    {
        get { return GetColumnAddress(Address); }
        private set { columnAddress = value; }
    }

    #region Private Methods

    private static long GetRow(string address)
    {
        return long.Parse(address.Substring(GetStartIndex(address)));
    }

    private static string GetColumnAddress(string address)
    {
        return address.Substring(0, GetStartIndex(address)).ToUpperInvariant();
    }

    private static long GetColumn(string columnAddress)
    {
        char[] characters = columnAddress.ToCharArray();
        int sum = 0;
        for (int i = 0; i < characters.Length; i++)
        {
            sum *= 26;
            sum += (characters[i] - 'A' + 1);
        }

        return (long)sum;
    }

    private static int GetStartIndex(string address)
    {
        return address.IndexOfAny("123456789".ToCharArray());
    }

    private string ToAddress()
    {
        string indexToString = string.Empty;

        if (Column > 26)
        {
            indexToString = ((char)(65 + (int)((Column - 1) / 26) - 1)).ToString();
        }

        indexToString += (char)(65 + ((Column - 1) % 26));

        dataChange = false;
        return indexToString + Row;
    }

    #endregion
}

}

like image 1
Arijit Pal Chaudhury Avatar answered Oct 22 '22 12:10

Arijit Pal Chaudhury