Get Column number from cell value, openxml

Question

I have an Excel wherein i want to get the column number for eg the below image :

In the above image , i know that the records will appear on the 1st row , but i am unsure of the Column number. In above example the column value : "Quantity" appears on "D1". I know the row number how can i find the column number ("D" in the above case) using OPEN XML, as the column name quantity might appear anywhere in the excel and i need to find the corresponding values of only quantity.

Answer 1

Unfortunately there's not a single method you can call to find the correct cell. Instead you'll need to iterate over the cells to find the matching text. To complicate things slightly, the value in the cell is not always the actual text. Instead strings can be stored in the SharedStringTablePart and the value of the cell is an index into the contents of that table.

Something like the following should do what you're after:

private static string GetCellReference(string filename, string sheetName, int rowIndex, string textToFind)
{
    using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(filename, false))
    {
        WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
        //get the correct sheet
        Sheet sheet = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName).First();
        if (sheet != null)
        {
            WorksheetPart worksheetPart = workbookPart.GetPartById(sheet.Id) as WorksheetPart;

            SharedStringTablePart stringTable = workbookPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();

            SheetData sheetData = worksheetPart.Worksheet.Elements<SheetData>().First();

            Row row = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First();

            if (row != null)
            {
                foreach (Cell c in row.Elements<Cell>())
                {
                    string cellText;

                    if (c.DataType == CellValues.SharedString)
                    {
                        //the value will be a number which is an index into the shared strings table
                        int index = int.Parse(c.CellValue.InnerText);
                        cellText = stringTable.SharedStringTable.ElementAt(index).InnerText;
                    }
                    else
                    {
                        //just take the value from the cell (note this won't work for dates and other types)
                        cellText = c.CellValue.InnerText;
                    }

                    if (cellText == textToFind)
                    {
                        return c.CellReference;
                    }
                }
            }
        }
    }

    return null;
}

This can then be called like this:

string cellReference = GetCellReference(@"c:\temp\test.xlsx", "Sheet1", 1, "Quantity");
Console.WriteLine(cellReference); //prints D1 for your example

If you just want D rather than D1 you can use a simple regex to remove the numbers:

private static string GetColumnName(string cellReference)
{
    if (cellReference == null)
        return null;

    return Regex.Replace(cellReference, "[0-9]", "");
}

And then use it like this:

string cellReference = GetCellReference(@"c:\temp\test.xlsx", "Sheet1", 1, "Quantity");
Console.WriteLine(GetColumnName(cellReference)); //prints D for your example