使用Open XML获取Excel中每行的最后活动列的索引 [英] Get Index of last active columns per Row in Excel using Open XML

问题描述

如何使用Open Xml获取行中最后一个活动列的索引

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

我在第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

这给出了D1"，但是在这种情况下，我想要的是索引"4".我该如何处理?

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

推荐答案

要将单元格引用转换为列索引，可以使用如下所示的内容(我已经将代码转换为

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

注意:由于我使用 Telerik Converter 对其进行转换，因此VB可能不是惯用的从C#到VB.

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

这篇关于使用Open XML获取Excel中每行的最后活动列的索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！