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

Question

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:

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.rowNum or colNum?

Answer 1

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;

Answer 2

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?

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)

Answer 3

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;

Answer 4

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.

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

Answer 5

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.

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

Answer 6

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