我需要从Excel电子表格中读写数据。使用ExcelPackage，是否有一种方法可以找出特定工作表有多少行/列？我有以下代码：
FileInfo newFile = new FileInfo(@"C:\example.xlsx");
using (ExcelPackage xlPackage = new ExcelPackage(newFile))
{
ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets[1];
}
我需要遍历该工作表的每个单元格并将其放入一个相当大的表中，但我不想打印空单元格或引发异常。是否有类似于worksheet.rowNum或colNum的方法？
您可以使用ExcelPackage（EPPlus.dll版本3.0.0.2）获取行数和列数，示例如下：
var rowCnt = worksheet.Dimension.End.Row;
var colCnt = worksheet.Dimension.End.Column;
这是我的工作内容：
获取工作簿上数值数组的方法：
object[,] valueArray = sheet.Cells.GetValue<object[,]>();
获取范围，请按照以下步骤进行:
int rangeMaxRows = sheet.Dimension.End.Row;
int rangeMaxColumns = sheet.Dimension.End.Column;
我会从UsedRange属性开始，然后针对UsedRange的最后一行中的每个单元格使用Cell.End(xlUp)方法。这样可以得到每列的最后一个单元格，其中具有最大行索引的单元格是真实使用范围的最后一个单元格。
由于当清除单元格但未删除时，UsedRange属性可能会出现错误。为使UsedRange属性再次有效，只需选择最后一个单元格之后的所有行和列（即所有空单元格），然后进行编辑->删除操作即可。
int row = _excelSheet.Rows.CurrentRegion.EntireRow.Count;
int col = _excelSheet.Columns.CurrentRegion.EntireColumn.Count;
我刚刚使用以下循环解决了这个问题。只有当您事先知道将有多少列时，它才能正常工作。否则，它需要进行另一个循环迭代。
int totalCells = 0;
int totalRows = -1;
do
{
totalRows++;
} while (worksheet.Cell(totalRows + 1, 1).Value != @"");
totalCells = totalRows * 12;
我之前一直使用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++;
}
}
我已经查了一些使用ExcelPackage库的网站。
此外，codeplex上的页面有一个问题，如何获取行/列的数量？
似乎没有支持它。 抱歉，文档也不可用。
您将不得不迭代行/列（记住电子表格可以容纳的最大行数/列数），并检查单元格是否包含任何值。
参见此链接- http://web.archive.org/web/20110123164144/http://nayyeri.net/use-excelpackage-to-manipulate-open-xml-excel-files （原始链接失效）
int col = sheet.Dimension.Columns;
int row = sheet.Dimension.Rows;
Epplus不支持usedrange，但是你可以使用usedrange.cs来实现。 假设你已经下载了最新的EPPlus源代码， 对Worksheet.cs进行更改：将原始文件partial化。 然后创建一个名为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>
}
}
string.IsNullOrEmpty(workSheet.Cells[i, 1].Text)。 - Petr Tomášek