C#：使用 ExcelPackage 获取行数/列数

Question

我需要从 Excel 电子表格中读取和写入数据。有没有一种方法可以使用 ExcelPackage 找出某个工作表有多少行/列？我有以下代码：

FileInfo newFile = new FileInfo(@"C:\example.xlsx");
using (ExcelPackage xlPackage = new ExcelPackage(newFile)) 
{
    ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets[1];
}

我需要迭代该工作表的每个单元格并将其吐入一个相当大的表中，但我不想打印出空白单元格或得到异常。是否有类似 worksheet.rowNum 或 colNum 的方法？

Answer 1

您可以使用 ExcelPackage（EPPlus.dll 版本 3.0.0.2）获取行数和列数，如下所示：

  var rowCnt = worksheet.Dimension.End.Row;
  var colCnt = worksheet.Dimension.End.Column;

Answer 2

这就是我所做的：

要获取工作簿上的值数组：

object[,] valueArray = sheet.Cells.GetValue<object[,]>();

要获取范围，请执行以下操作：

int rangeMaxRows = sheet.Dimension.End.Row; 
int rangeMaxColumns = sheet.Dimension.End.Column;

Answer 3

我将从UsedRange 属性开始，然后对UsedRange 最后一行中的每个单元格执行Cell.End(xlUp)。这应该为您提供每列的最终单元格，具有最大行索引的单元格是您真正使用的范围中的最后一个单元格。

UseRange 属性可能会出现错误，因为当单元格被清除但未删除时，UsedRange 属性不会更新。要使UsedRange属性再次有效，只需选择最后一个单元格之后的所有行和列（因此所有空白单元格），然后进行编辑->删除。

Answer 4

int row = _excelSheet.Rows.CurrentRegion.EntireRow.Count;
int col = _excelSheet.Columns.CurrentRegion.EntireColumn.Count;

Answer 5

我只是做了以下循环来解决问题。仅当您事先知道有多少列时，它才能正常工作。否则将需要另一个循环迭代。

int totalCells = 0;
int totalRows = -1;

do
{
     totalRows++;
} while (worksheet.Cell(totalRows + 1, 1).Value != @"");
totalCells = totalRows * 12;

Answer 6

我单独使用sheet.UsedRange，但注意到范围末尾的一些单元格是空白的，但仍然包含在范围内。

这是可行的，但是为了提高效率，您可能最好从范围中的最后一行开始并倒数以查看数据结束的位置（而不是从第一行开始的此片段！）

        int count = 0;
        E.Range excelRange = sheet.UsedRange;
        object[,] valueArray = (object[,])excelRange.get_Value(E.XlRangeValueDataType.xlRangeValueDefault);
        if (valueArray.GetUpperBound(0) > 1)
        {
            for (int i = 0; i < valueArray.GetUpperBound(0) + 2; i++)
            {
                if (valueArray[i + 2, 1] == null)
                    break;
                else
                    count++;
            }
        }

Answer 7

我使用 ExcelPackage 库查找了一些网站。
另外，codeplex 上的页面有一个问题 - 如何获取行/列数？

好像没有支持啊抱歉，该文档也不可用。
您必须迭代行/列（记住电子表格可以容纳的最大行/列）并检查单元格是否包含任何值。

请参阅此链接 - http://web.archive.org/web/20110123164144/http://nayyeri.net/use-excelpackage-to-manipulate-open-xml-excel-files（原始链接已失效）

Answer 8

获取行和列总数的最佳方法是使用以下方法：

int col = sheet.Dimension.Columns;
int row = sheet.Dimension.Rows;

Answer 9

Epplus 不支持usedrange，但您可以使用usedrange.cs 支持它
假设您已经下载了最新的EPPlus源代码，
对 Worksheet.cs 进行更改：使原始工作表部分。
然后创建名为UsedRange.cs的单独的cs文件，将以下代码粘贴到其中并编译。

namespace OfficeOpenXml
{
   using System;
   using System.Collections.Generic;
   using System.Text;
   using OfficeOpenXml.Style;
   using System.Data;
/// <summary>
/// This class provides easy access to used range objects such as
/// UsedRows, UsedColumns, UsedCells, UsedRow, UsedColumn etc.
/// Authored by Mukesh Adhvaryu
/// </summary>
public sealed class UsedRange : ExcelRange,IEnumerable<UsedRange>
{
    #region local variables
    int elementIndex=-1, cursor=-1, position=-1;
    UsedRangeElement element, parentElement;
    public const long MaxCells =(long) ExcelPackage.MaxRows *  
(long)ExcelPackage.MaxColumns;
    #endregion

    #region constructors
    /// <summary>
    /// this constructor is private because its accessibility outside can cause mess
    /// </summary>
    /// <param name="sheet"></param>
    /// <param name="element"></param>
    /// <param name="elementIndex"></param>
    /// <param name="cursor"></param>
    UsedRange(ExcelWorksheet sheet, UsedRangeElement element, int elementIndex, int cursor)
        : base(sheet)
    {
        this.element = element;
        switch (element)
        {
            case UsedRangeElement.Rows:
            case UsedRangeElement.Columns:
            case UsedRangeElement.Cells:
                parentElement = UsedRangeElement.Range;
                break;
            case UsedRangeElement.Row:
                parentElement = UsedRangeElement.Rows;
                break;
            case UsedRangeElement.Column:
                parentElement = UsedRangeElement.Columns;
                break;
            case UsedRangeElement.Cell:
                parentElement = UsedRangeElement.Cells;
                break;
            case UsedRangeElement.RowCell:
                parentElement = UsedRangeElement.Row;
                break;
            case UsedRangeElement.ColumnCell:
                parentElement = UsedRangeElement.Column;
                break;
            default:
                parentElement = 0;
                break;
        }
        this.elementIndex = elementIndex;
        this.cursor = cursor;
        SetRange();
    }

    /// <summary>
    /// this constructor is private because its accessibility outside can cause mess
    /// </summary>
    /// <param name="sheet"></param>
    /// <param name="element"></param>
    /// <param name="elementIndex"></param>
    UsedRange(ExcelWorksheet sheet, UsedRangeElement element, int elementIndex)
        : this(sheet, element, elementIndex, -1) { }

    /// <summary>
    /// this constructor is private because its accessibility outside can cause mess
    /// </summary>
    /// <param name="sheet"></param>
    /// <param name="element"></param>
    UsedRange(ExcelWorksheet sheet, UsedRangeElement element)
        : this(sheet, element, -1, -1) { }

    /// <summary>
    /// this constructor used only to create cellcollection range
    /// since cellindex can be very large long value considering rows * columns =no of cells in worksheet
    /// this constructor is private because its accessibility outside can cause mess
    /// </summary>
    /// <param name="sheet"></param>
    /// <param name="cellIndex"></param>
    UsedRange(ExcelWorksheet sheet, long cellIndex)
        : base(sheet)
    {
        this.element = UsedRangeElement.Cell;
        this.parentElement = UsedRangeElement.Cells;
        CellToAddress(cellIndex);
        SetRange();
    }
    #endregion

    #region indexers & properties
    /// <summary>
    /// Returns element at a given index 
    /// </summary>
    /// <param name="index"></param>
    /// <returns></returns>
    public UsedRange this[int index]
    {
        get
        {
            if (index >= Count || index < 0) throw new IndexOutOfRangeException();
            switch (element)
            {
                case UsedRangeElement.Rows:
                    ValidateRow(index);
                    return new UsedRange(_worksheet, UsedRangeElement.Row, index);
                case UsedRangeElement.Columns:
                    ValidateCol(index);
                    return new UsedRange(_worksheet, UsedRangeElement.Column, index);
                case UsedRangeElement.Cells:
                    ValidateCell(index);
                    return new UsedRange(_worksheet, index);
                case UsedRangeElement.Row:
                    return new UsedRange(_worksheet, UsedRangeElement.RowCell, elementIndex, index);
                case UsedRangeElement.Column:
                    return new UsedRange(_worksheet, UsedRangeElement.ColumnCell, elementIndex, index);
                default:
                    return this;
            }
        }
    }

    /// <summary>
    /// Returns particular Cell at a given index
    /// </summary>
    /// <param name="index"></param>
    /// <returns></returns>
    public UsedRange this[long index]
    {
        get
        {
            ValidateCell(index);
            return new UsedRange(_worksheet, index);
        }
    }

    /// <summary>
    /// Returns count of elements in this collection
    /// </summary>
    public int Count
    {
        get
        {
            switch (element)
            {
                case UsedRangeElement.Rows:
                case UsedRangeElement.Column:
                    return _toRow - _fromRow + 1;
                case UsedRangeElement.Columns:
                case UsedRangeElement.Row:
                    return _toCol - _fromCol + 1;
                case UsedRangeElement.Cells:
                case UsedRangeElement.Range:
                    return (_toRow - _fromRow + 1) * (_toCol - _fromCol + 1);
                default:
                    return 1;
            }
        }
    }

    /// <summary>
    /// Returns type of this element collection
    /// </summary>
    public UsedRangeElement Element
    {
        get { return element; }
    }

    /// <summary>
    /// Returns parent type of element this collection
    /// </summary>
    public UsedRangeElement ParentElement
    {
        get { return parentElement; }
    }
    #endregion

    #region private methods
    /// <summary>
    /// Validates row index for row collection
    /// added by mukesh
    /// </summary>
    /// <param name="Row"></param>
    private void ValidateRow(int Row)
    {
        if (Row < 0 || Row > ExcelPackage.MaxRows)
        {
            throw (new ArgumentException("Row out of range"));
        }
    }

    /// <summary>
    /// Validates column index for column collection
    /// added by mukesh
    /// </summary>
    /// <param name="Col"></param>
    private void ValidateCol(int Col)
    {
        if (Col < 0 || Col > ExcelPackage.MaxColumns)
        {
            throw (new ArgumentException("Column out of range"));
        }
    }

    /// <summary>
    /// Validates cell index for cell collection
    /// added by mukesh
    /// </summary>
    /// <param name="Cell"></param>
    private void ValidateCell(long Cell)
    {
        if (Cell <0 || Cell > UsedRange.MaxCells)
        {
            throw (new ArgumentException("Cell out of range"));
        }

    }

    /// <summary>
    /// converts cell index into a point consists of row and column index.
    /// added by mukesh
    /// </summary>
    /// <param name="Cell"></param>
    private void CellToAddress(long Cell)
    {
        long rc = ((_worksheet._cells[_worksheet._cells.Count - 1] as ExcelCell).Row
                    - (_worksheet._cells[0] as ExcelCell).Row) + 1;
        long cc = _worksheet._maxCol - _worksheet._minCol + 1;
        elementIndex = (int)(Cell / cc) + 1;
        cursor = (int)(Cell % cc) + 1;
    }

    /// <summary>
    /// This method is added by mukesh
    /// </summary>
    /// <returns>
    /// Excel Range Object
    /// </returns>
    ExcelRange SetRange()
    {
        switch (element)
        {
            case UsedRangeElement.Rows:
            case UsedRangeElement.Columns:
            case UsedRangeElement.Cells:
                return this[(_worksheet._cells[0] as ExcelCell).Row, _worksheet._minCol,
                (this._worksheet._cells[_worksheet._cells.Count - 1] as ExcelCell).Row,
                _worksheet._maxCol];

            case UsedRangeElement.Row:
                return this[elementIndex + 1, _worksheet._minCol, elementIndex + 1, _worksheet._maxCol];

            case UsedRangeElement.Column:
                return this[(_worksheet._cells[0] as ExcelCell).Row, elementIndex + 1,
                (_worksheet._cells[_worksheet._cells.Count - 1] as ExcelCell).Row, elementIndex + 1];
            case UsedRangeElement.RowCell:
            case UsedRangeElement.Cell:
                return this[elementIndex + 1, cursor + 1];
            case UsedRangeElement.ColumnCell:
                return this[cursor + 1, elementIndex + 1];
            default:
                return this;
        }
    }
    #endregion

    #region internal static methods
    /// <summary>
    /// these static methods will be used to return row collection from worksheet
    /// added by mukesh
    /// </summary>
    /// <param name="sheet"></param>
    /// <returns></returns>
    internal static UsedRange RowCollection(ExcelWorksheet sheet)
    {
        return new UsedRange(sheet, UsedRangeElement.Rows);
    }

    /// <summary>
    /// these static methods will be used to return column collection from worksheet
    /// added by mukesh
    /// </summary>
    /// <param name="sheet"></param>
    /// <returns></returns>
    internal static UsedRange ColumnCollection(ExcelWorksheet sheet)
    {
        return new UsedRange(sheet, UsedRangeElement.Columns);
    }

    /// <summary>
    /// these static methods will be used to return cell collection from worksheet
    /// added by mukesh
    /// </summary>
    /// <param name="sheet"></param>
    /// <returns></returns>
    internal static UsedRange CellCollection(ExcelWorksheet sheet)
    {
        return new UsedRange(sheet, UsedRangeElement.Cells);
    }
    #endregion

    #region ienumerable implementation
    public new IEnumerator<UsedRange> GetEnumerator()
    {
        position = -1;
        for (int i = 0; i < Count; i++)
        {
            ++position;
            yield return this[i];
        }
    }
    System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator()
    {
        return this.GetEnumerator();
    }
    #endregion

    /// <summary>
    /// Determine Type of Used range element. 
    /// Being used to return RowCollection, ColumnCollection, CellCollection or single Row, Column or Cell
    /// added by mukesh
    /// </summary>
    public enum UsedRangeElement
    {
        Range, Rows, Columns, Cells,
        Row, Column, Cell, RowCell, ColumnCell
    }
}

public sealed partial class ExcelWorksheet : XmlHelper
{
    /// <summary>
    /// Provides access to a range of used rows
    /// </summary>  
    public UsedRange UsedRows
    {
        get
        {
            return UsedRange.RowCollection(this);
        }
    }
    /// <summary>
    /// Provides access to a range of used columns. added by mukesh
    /// </summary>  
    public UsedRange UsedColumns
    {
        get
        {
            return UsedRange.ColumnCollection(this);
        }
    }
    /// <summary>
    /// Provides access to a range of used cells. added by mukesh
    /// </summary>  
    public UsedRange UsedCells
    {
        get
        {
            return UsedRange.CellCollection(this);
        }
    }
    /// <summary>
    /// UsedRange object of the worksheet. added by mukesh
    /// this range contains used Top left cell to Bottom right.
    /// If the worksheet has no cells, null is returned
    /// </summary>
}
}