EPPlus - Working with multiple columns by index rather than Alphabetical Representation

Question

I am using EPPlus in my .Net project to output some data into an Excel worksheet.

Suppose I wanted to format Columns E-G with a specific format. Using EPPlus, I know I can do it as follows:

wks.Cells("E:G").Style.Numberformat.Format = ...

Now, I'm wondering, suppose I wanted to do the same, but by referencing the columns by their index numbers rather than their alphabetical representation - In theory something looking like this:

wks.Columns("5:7").Style.Numberformat.Format = ...

---

Now, I know that it would work if I did something like this:

wks.Cells(1,5,wks.Dimension.End.Row,7).Style.Numberformat.Format = ...

But I'm hoping there's a better / nicer way to do this in EPPlus. Any thoughts / suggestions?

Thanks!!

Answer 1

As an answer to my own question for the sake of helping anyone who comes by this, I ended up creating my own Extension Method Columns that would convert the column number into a ExcelRange object:

''' <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