将数字转换为 Excel 字母列 vb.net
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31974538/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Converting Numbers to Excel Letter Column vb.net
提问by bill
I am trying to write data to excel files using vb.net. So I my function which converts number column into excel letter columns.
我正在尝试使用 vb.net 将数据写入 excel 文件。所以我的函数将数字列转换为 excel 字母列。
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.
- 1 => A
- 2 => B
- ...
- 26 => Z
- 27 => AA
- ...
- 51 => 是
- 52 => t（这是它开始出错的时候）假设返回 AZ，但它返回了 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.
我无法弄清楚我犯了错误的哪个部分。有人可以帮助我或向我展示如何使用 vb.net 编写将数字转换为 excel 字母列的正确函数。
回答by Garrann
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
回答by wdosanjos
There are a couple flaws in the logic, the second else clause is not required and the operations should be zero based.
逻辑上有一些缺陷，第二个 else 子句不是必需的，操作应该是从零开始的。
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
回答by Charles
This will work up to 52.
这将工作到 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 EPPlusvia .Net. You can use letter notation for columns if you wish, or you can use numbers.
附带说明一下，您可以通过 .Net直接使用EPPlus编写 XLSX 文件。如果您愿意，您可以对列使用字母表示法，也可以使用数字。
