Converting Numbers to Excel Letter Column vb.net

问题:

I am trying to write data to excel files using vb.net. So I my function which converts number column into excel letter columns.

Public Function ConvertToLetter(ByRef iCol As Integer) As String

    Dim Reminder_Part As Integer = iCol Mod 26
    Dim Integer_Part As Integer = Int(iCol / 26)

    If Integer_Part = 0 Then
        ConvertToLetter = Chr(Reminder_Part + 64)
    ElseIf Integer_Part > 0 And Reminder_Part <> 0 Then
        ConvertToLetter = Chr(Integer_Part + 64) + Chr(Reminder_Part + 64)
    ElseIf Integer_Part > 0 And Reminder_Part = 0 Then
        ConvertToLetter = Chr(Integer_Part * 26 + 64)
    End If


End Function

The Function works ok with any other numbers.

For example,

• 1 => A
• 2 => B
• ...
• 26 => Z
• 27 => AA
• ...
• 51 => AY
• 52 => t (And here is when it start to went wrong) It is suppose to return AZ, but it returned t.

I couldn't figure out what part I made a mistake. Can someone help me or show me how to code a proper function of converting numbers to excel letter columns using vb.net.

回答1:

This should do what you want.

Private Function GetExcelColumnName(columnNumber As Integer) As String
    Dim dividend As Integer = columnNumber
    Dim columnName As String = String.Empty
    Dim modulo As Integer

    While dividend > 0
       modulo = (dividend - 1) Mod 26
       columnName = Convert.ToChar(65 + modulo).ToString() & columnName
       dividend = CInt((dividend - modulo) / 26)
   End While

   Return columnName
End Function

回答2:

This will work up to 52.

Public Function ConvertToLetterA(ByRef iCol As Integer) As String

        Select Case iCol
            Case 1 To 26
                Return Chr(iCol + 64)

            Case 27 To 52
                Return "A" & Chr(iCol - 26 + 64)

        End Select

End Function

On a side note, you can write XLSX files directly with EPPlus via .Net. You can use letter notation for columns if you wish, or you can use numbers.

回答3:

There are a couple flaws in the logic, the second else clause is not required and the operations should be zero based.

Public Function ConvertToLetter(ByRef iCol As Integer) As String
    Dim col As Integer = iCol - 1
    Dim Reminder_Part As Integer = col Mod 26
    Dim Integer_Part As Integer = Int(col / 26)

    If Integer_Part = 0 Then
        ConvertToLetter = Chr(Reminder_Part + 65)
    Else
        ConvertToLetter = Chr(Integer_Part + 64) + Chr(Reminder_Part + 65)
    End If


End Function