Get Index of last active columns per Row in Excel using Open XML

Question

How do i get the Index of the last active column in a row using Open Xml

i have this for row 1.

Dim activeCells As IEnumerable(Of DocumentFormat.OpenXml.Spreadsheet.Cell) =     row.Descendants(Of DocumentFormat.OpenXml.Spreadsheet.Cell)().Where(Function(c)      Not String.IsNullOrEmpty(c.InnerText))

Dim cell As DocumentFormat.OpenXml.Spreadsheet.Cell = activeCells.LastOrDefault()
Dim CellRef As String = cell.CellReference

This gives D1", but what i want is the index in this case "4". how do i go about this?

Answer 1

To convert the cell reference to a column index you could use something like the following (I've converted the code from the answer here which you've inspired me to write :)).

Private Shared Function GetColumnIndex(cellReference As String) As System.Nullable(Of Integer)
    If String.IsNullOrEmpty(cellReference) Then
        Return Nothing
    End If

    'remove digits
    Dim columnReference As String = Regex.Replace(cellReference.ToUpper(), "[\d]", String.Empty)

    Dim columnNumber As Integer = -1
    Dim mulitplier As Integer = 1

    'working from the end of the letters take the ASCII code less 64 (so A = 1, B =2...etc)
    'then multiply that number by our multiplier (which starts at 1)
    'multiply our multiplier by 26 as there are 26 letters
    For Each c As Char In columnReference.ToCharArray().Reverse()
        columnNumber += mulitplier * (CInt(c) - 64)

        mulitplier = mulitplier * 26
    Next

    'the result is zero based so return columnnumber + 1 for a 1 based answer
    'this will match Excel's COLUMN function
    Return columnNumber + 1
End Function

Note: the VB might not be idiomatic as I used the Telerik Converter to convert it from C# to VB.