EPPlus - Working with multiple columns by index rather than alphabetically

Question

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

Suppose I wanted to format EG columns with a specific format. Using EPPlus, I know that I can do this as follows:

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

Now, I wonder, suppose I wanted to do the same thing, but referring to the columns by their index numbers and not by their alphabetical representation - in theory, something similar to this:

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

---

Now I know that this will work if I do something like this:

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

But I hope that in EPPlus there will be a better / better way to do this. Any thoughts / suggestions?

Thanks!!

Answer 1

As an answer to my own question, in order to help anyone who came to this, I created my own Columns extension method, which converts the column number to an 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 

Answer 2

I re-wrote @John Bustos answer in C #, and then realized that I wanted to work with the columns themselves, and not with a range of cells. The John method has been renamed "GetColumnCells" in the code below.

 public static List<ExcelColumn> GetColumns(ExcelWorksheet wks, int startColNum, int endColNum) { int d = startColNum; List<ExcelColumn> cols = new List<ExcelColumn>(); while (d <= endColNum) { cols.Add(wks.Column(d)); d++; } return cols; } public static ExcelRange GetColumnCells(ExcelWorksheet wks, int startColNum, int endColNum) { string startColName = GetColumnName(startColNum); string endColName = GetColumnName(endColNum); return wks.Cells[startColName + ":" + endColName]; } public static string GetColumnName(int colNum) { int d, m; string name = String.Empty; d = colNum; while (d > 0) { m = (d - 1) % 26; name = ((char)(65 + m)) + name; d = (d - m) / 26; } return name; }