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

C#: Getting the number of rows/columns with ExcelPackage

提问by Napoli

I need to read and write data from an Excel spreadsheet. Is there a method to finding out how many rows/columns a certain worksheet has using ExcelPackage? I have the following code:

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

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

I need to iterate through each cell this worksheet has and spit it into a fairly big table, but I don't want to print out blank cells or get an exception. Is there a method resembling worksheet.rowNumor colNum?

我需要遍历这个工作表中的每个单元格并将其吐到一个相当大的表格中，但我不想打印出空白单元格或得到一个例外。有没有类似于worksheet.rowNum或的方法colNum？

回答by BIDeveloper

I was using sheet.UsedRange on its own but noticed that some cells at the end of the range were blank but still included in the range.

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

This works, however for efficiency you might be better staring from the last row in the range and counting back to see where your data ends (as opposed to this snippet which starts from the first row!)

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

        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++;
            }
        }

回答by shahkalpesh

I have looked up a few websites using ExcelPackage library.
Also, the page on codeplex has a question on - how to get the number of rows/columns?

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

It seems there isn't a support of it. Sorry, the documentation is not available as well.
You will have to iterate on rows/columns (keeping in mind the max rows/columns the spreadsheet can hold) and check whether the cell contains any value.

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

See this link - http://web.archive.org/web/20110123164144/http://nayyeri.net/use-excelpackage-to-manipulate-open-xml-excel-files(original link dead)

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

回答by avid

I would start with the UsedRange property and then for each Cell in the final Row of the UsedRange do Cell.End(xlUp). This should get you the final cell for each column, the cell which has the maximum row index is the last cell in your true used range.

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

The UsedRange property can appear wrong because when cells are cleared but not deleted, the UsedRange property is not updated. To make the UsedRange property valid again simply select all the rows and columns after your last cell (so all the blank cells) and go edit->delete.

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

回答by Napoli

I just did the following loop to solve the problem. It works fine only if you know how many columns there will be beforehand. Otherwise it would take another loop iteration.

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

int totalCells = 0;
int totalRows = -1;

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

回答by Kwex

You can get the row and column count using ExcelPackage (EPPlus.dll version 3.0.0.2) as below:

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

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

回答by rebelphuong

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

回答by Mukesh Adhvaryu

Epplus does not have support for usedrange but you can have it using usedrange.cs Assuming that you have downloaded latest EPPlus source code, Make changes to Worksheet.cs : make the original one partial. and then create separate cs file named UsedRange.cs paste the code below in it and compile.

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

回答by EdsonF

This is what I do:

这就是我所做的：

To get the array of values on the workbook:

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

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

to get the range do the following:

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

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

回答by Leonardo Pinho

The best way to get the total of rows and columns is with these methods:

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

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