将数字转换为Excel字母列vb.net [英] Converting Numbers to Excel Letter Column vb.net

问题描述

我正在尝试使用vb.net将数据写入excel文件.因此，我将数字列转换为excel字母列的函数.

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.

例如

• 1 => A
• 2 => B
• ...
• 26 => Z
• 27 => AA
• ...
• 51 => AY
• 52 => t(这里是开始出错的地方)假定返回AZ，但返回了t.

我不知道我在哪部分弄错了.有人可以帮助我还是告诉我如何使用vb.net编码将数字转换为excel字母列的适当功能.

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.

推荐答案

这应该做您想要的.

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

这篇关于将数字转换为Excel字母列vb.net的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！