我在.Net项目中使用EPPlus将一些数据输出到Excel工作表中.
假设我想格式化具有特定格式的列E-G.使用EPPlus,我知道我可以这样做：
wks.Cells("E:G").Style.Numberformat.Format = ...
现在,我想知道,假设我想做同样的事情,但是通过他们的索引号而不是字母表示来引用列 – 理论上看起来像这样：
wks.Columns("5:7").Style.Numberformat.Format = ...
现在,我知道如果我做了这样的事情会有效：
wks.Cells(1,5,wks.Dimension.End.Row,7).Style.Numberformat.Format = ...
但我希望在EPPlus中有更好/更好的方法.有什么想法/建议？
谢谢！！
解决方法
作为我自己的问题的答案,为了帮助任何人,我最终创建了自己的扩展方法列,将列号转换为ExcelRange对象：
''' <summary>
''' Allows you to reference a column by its numeric index rather than its alphabetic representation
''' </summary>
''' <param name="colNum">The index of the column to reference on in the worksheet.</param>
<System.Runtime.CompilerServices.Extension()> _
Public Function Columns(ByVal wks As ExcelWorksheet,ByVal colNum As Integer) As ExcelRange
Dim ColName As String = ReturnColumnName(colNum)
Return wks.Cells(ColName & ":" & ColName)
End Function
''' <summary>
''' Allows you to reference a column by its numeric index rather than its alphabetic representation
''' </summary>
''' <param name="StartColNum">The start col num.</param>
''' <param name="EndColNum">The end col num.</param>
<System.Runtime.CompilerServices.Extension()> _
Public Function Columns(ByVal wks As ExcelWorksheet,ByVal StartColNum As Integer,ByVal EndColNum As Integer) As ExcelRange
Dim StartColName As String = ReturnColumnName(StartColNum)
Dim EndColName As String = ReturnColumnName(EndColNum)
Return wks.Cells(StartColName & ":" & EndColName)
End Function
Private Function ReturnColumnName(ByVal colNum As Integer) As String
Dim d As Integer
Dim m As Integer
Dim Name As String
d = colNum
Name = ""
do while (d > 0)
m = (d - 1) Mod 26
Name = Chr(65 + m) + Name
d = Int((d - m) / 26)
Loop
Return Name
End Function