How to access XLSheet column data by name?

Question

My application is reading the excel file data using Interop libraries.My code is below.

string filepath = "C:\\SAddresses.xls";

XLApp=new Excel.Application();
XLBook = XLApp.Workbooks.Open(filepath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
XLSheet = (Excel.Worksheet)XLBook.Worksheets.get_Item(1);
cRange = XLSheet.UsedRange;


for (int rowcnt = 2; rowcnt < cRange.Rows.Count; rowcnt++)
{
    for (int colidx = 1; colidx < cRange.Columns.Count; colidx++)
    {
        colvalue+=((cRange.Cells[rowidx,colidx] as Excel.Range).Value2);
    }
}

In the above I am able to get the data using each row index and column index.But I want to read the column data using column name instead of column index.How to do this.

Answer 1

If you only want to convert the number in your loop into a column name (which is what I read from your question) then the following static method is what I use in all of my spreadsheet routines:

public static string ExcelColumnNumberToName(int columnNumber)
{
    int dividend = columnNumber;
    string columnName = string.Empty;
    int modulo;

    while (dividend > 0)
    {
        // Get the remainder of a division by 26 (we take one from the dividend as column "A" is not zero, but one).

        modulo = (dividend - 1) % 26;

        // Convert the remainder (plus 65 - the ASCII decimal code for "A") to a character and add it to the beginning of the string (since column names
        // increase by adding letters to the left hand side). 

        columnName = Convert.ToChar(65 + modulo).ToString() + columnName;

        // Set the dividend for the next iteration of the loop by removing the amount we just converted and dividing it by 26 (in effect treating it like
        // a base-26 number and dropping it by an order of magnitude)

        dividend = (int)((dividend - modulo) / 26);
    }

    return columnName;
}

You'd need to adjust how you refer to cells slightly. So instead of

cRange.Cells[rowidx,colidx]

You would use

cRange.get_Range(ExcelColumnNumberToName(colidx), rowidx)

That doesn't really give you any advantages over how you're currently doing it though. The reason I use it is to convert loop counters to column names for reporting purposes, so if you need it for something like that then it probably makes sense.

Answer 2

Here is an excerpt from an ExelTable class, I have been using for a while:

class ExcelTable
{
    public const int SignificantCharsInVariableName = 10;

    private string[] columnNames = null;
    private string[,] data = null;

    private string[] DefineColumnNames(string[,] R)
    {
        string[] names = new string[R.GetLength(1)];

        for (int i = 0; i < R.GetLength(1); i++)
        {
            names[i] = R[0, i].Trim();
        }

        return names;
    }


    public string GetValue(int row, string varName)
    {
        string s;
        int col;

        try
        {
            col = GetColumn(varName);

            s = data[row, col].Trim();
        }
        catch (Exception)
        {
            s = "???";
        }

        return s;
    }


    private int GetColumn(string name)
    {
        return GetColumn(name, this.columnNames, obligatory: true);
    }

    private int GetColumn(string name, string[] names, bool obligatory = false)
    {
        string nameStart;

        //  first try perfect match
        for (int i = 0; i < names.Length; i++)
            if (names[i].Equals(name))
                return i;

        //  2nd try to match the significant characters only
        nameStart = name.PadRight(SignificantCharsInVariableName, ' ').Substring(0, SignificantCharsInVariableName);
        for (int i = 0; i < names.Length; i++)
            if (names[i].StartsWith(nameStart))
                return i;

        if (obligatory)
        {
            throw new Exception("Undefined Excel sheet column '" + name + "'");
        }

        return -1;
    }

}

The data is organized in named columns. The name is the first cell in the top row of the data array. The sample code attempts to deal with inperfect name matches and other error situations. One could use more clever collections like Dictionary<string, int> to store the mapping between column name and column index.

My GetColumn() function allows non-obligatory columns. It is used by GetValue() to return a value in a given row "by name". Questionmarks are returned, if the column was not found.