EPPlus -按索引处理多列，而不是按字母顺序表示

本文关键字：顺序 表示 索引 处理 EPPlus | 更新日期: 2023-09-27 18:03:45

我在我的。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中有更好/更好的方法来做到这一点。有什么想法/建议吗?

谢谢! !

EPPlus -按索引处理多列，而不是按字母顺序表示

为了回答我自己的问题，为了帮助任何遇到这个问题的人，我最终创建了我自己的扩展方法Columns，它将列号转换为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

我用c#重写了@John Bustos给出的答案，然后意识到我想使用列本身而不是单元格范围。在下面的代码中，John的方法被重命名为"GetColumnCells"。

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;
}
相关文章：