C#: Excel Package를 사용한 행/열 수 가져오기

C#: Excel Package를 사용한 행/열 수 가져오기

엑셀 스프레드시트의 데이터를 읽고 써야 합니다.Excel Package를 사용하여 특정 워크시트에 몇 개의 행/열이 있는지 확인할 수 있는 방법이 있습니까?다음 코드가 있습니다.

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

이 워크시트가 있는 각 셀을 반복하여 꽤 큰 테이블로 뱉어야 하지만 빈 셀을 인쇄하거나 예외를 두고 싶지 않습니다.worksheet.rowNum ★★★★★★★★★★★★★★★★★」colNum

Excel Package(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 do Cell의 마지막 행에 있는 각 셀에 대해 설명합니다.종료(xlUp).그러면 각 열의 최종 셀이 표시됩니다. 최대 행 인덱스가 있는 셀은 실제 사용 범위 내의 마지막 셀입니다.

셀이 지워져도 삭제되지 않으면 UsedRange 속성이 갱신되지 않기 때문에 UsedRange 속성이 잘못 표시될 수 있습니다.Used Range 속성을 다시 활성화하려면 마지막 셀 뒤에 있는 모든 행과 열을 선택하고 edit-> delete로 이동합니다.

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;

시트를 쓰고 있었어요.Used Range 자체이지만 범위 끝에 있는 일부 셀은 공백이지만 범위 내에 아직 포함되어 있음을 알 수 있습니다.

이 방법은 효과적이지만 효율성을 높이려면 범위 내 마지막 행부터 응시하고 데이터가 어디에서 끝나는지 다시 카운트하는 것이 좋습니다(이 스니펫은 첫 번째 행부터 시작됩니다).

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

Excel 패키지입니다.
또한 코드플렉스 페이지에는 다음과 같은 질문이 있습니다./ 의의의 수를 ??? ???

을 사용하다죄송합니다, 문서도 사용할 수 없습니다.
행/열에 반복해야 합니다(스프레드시트에 포함할 수 있는 최대 행/열에 유의). 셀에 값이 포함되어 있는지 확인해야 합니다.

이 링크를 참조해 주세요.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 소스 코드를 다운로드한 경우 [Make changes to Worksheet.cs : make original ](원래 EPplus 소스 코드를 부분 변경)을 사용합니다.그런 다음 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>
}
}

언급URL : https://stackoverflow.com/questions/1715926/c-getting-the-number-of-rows-columns-with-excelpackage