This project has moved and is read-only. For the latest updates, please go here.

Resolving column index to string

Oct 24, 2012 at 8:09 PM

Hey all,

So I was just trying to create a list validation object and found I needed to specify the address as a string address.  Before I realized I could just use ExcelWorksheet.Cells[row, column].Address, I wrote a static method for ExcelColumn to convert the integer to a string.  I thought it might be useful to hang onto.

/// <summary>
/// Converts the integer representation a column index to the string representation.
/// Note: Index must be between 1 and 16584 (inclusive), which is A to XFD, the max column.
/// </summary>
/// <param name="index">Column index, between 1 and 16584</param>
/// <returns></returns>
public static string ConvertToString(int index)
{
    // A to XFD
    if (index <= 0 || index > 16384)
        throw new ArgumentOutOfRangeException("index must be between 1 and 16384 (inclusive)");

    string result = string.Empty;
    while (index > 0)
    {
        int i = index % 26;
        index /= 26;
        result = (char)((int)'A' + (i - 1)) + result;
    }

    return result;
}

It may also be worth considering overloading the validation objects' constructors to take an address as an ExcelAddress object, as well as (int row, int col).

Luke

Oct 24, 2012 at 8:22 PM

Correction, it may be worth overloading the ExcelDataValidationCollection.Add___Validation methods to allow for ExcelAddress and integer values.  So, maybe something like...

public IExcelDataValidationList AddListValidation(ExcelAddress address)
{
    return AddListValidation(address.Address);
}

public IExcelDataValidationList AddListValidation(int row, int col)
{
    return AddListValidation(new ExcelAddress(row, col, row, col));
    //return AddListValidation(ExcelColumn.ConvertToString(col) + row.ToString());
}

public IExcelDataValidationList AddListValidation(int fromRow, int fromCol, int toRow, int toCol)
{
    return AddListValidation(new ExcelAddress(fromRow, fromCol, toRow, toCol));
    //return AddListValidation(String.Format("{0}{1}:{2}{3}",
    //    ExcelColumn.ConvertToString(fromCol), fromRow.ToString(), ExcelColumn.ConvertToString(toCol), toRow.ToString()));
}