How do i iterate through rows in an excel table using epplus ?

How do i iterate through rows in an excel table using epplus ?

Asked on January 11, 2019 in Excel.
Add Comment


  • 3 Answer(s)

    This solution is more excellent than using Interop objects.

    var package = new ExcelPackage(new FileInfo("sample.xlsx"));
     
    ExcelWorksheet workSheet = package.Workbook.Worksheets[0];
    var start = workSheet.Dimension.Start;
    var end = workSheet.Dimension.End;
    for (int row = start.Row; row <= end.Row; row++)
    { // Row by row...
      for (int col = start.Column; col <= end.Column; col++)
      { // ... Cell by cell...
        object cellValue = workSheet.Cells[row, col].Text; // This got me the actual value I needed.
      }
    }
    Answered on January 11, 2019.
    Add Comment

        The following command is to get the whole row while ExcelRange after it can be either iterated or for LINQ:

    for (var rowNum = 1; rowNum <= sheet.Dimension.End.Row; rowNum++)
    {
        var row = sheet.Cells[string.Format("{0}:{0}", rowNum)];
        // just an example, you want to know if all cells of this row are empty
        bool allEmpty = row.All(c => string.IsNullOrWhiteSpace(c.Text));
        if (allEmpty) continue; // skip this row
        // ...
    }
    
    Answered on January 11, 2019.
    Add Comment

        By using this approach .Worksheet property of a table and basis its cells. and here the production procedure for this intention, and it will creates a order of dictionaries measuring column name to cell value:

    Try this following code :

    public static IEnumerable<IDictionary<string, object>> GetRows(this ExcelTable table)
    {
       var addr = table.Address;
       var cells = table.WorkSheet.Cells;
     
       var firstCol = addr.Start.Column;
     
       var firstRow = addr.Start.Row;
       if (table.ShowHeader)
         firstRow++;
       var lastRow = addr.End.Row;
     
       for (int r = firstRow; r <= lastRow; r++)
       {
          yield return Enumerable.Range(0, table.Columns.Count)
            .ToDictionary(x => table.Columns[x].Name, x => cells[r, firstCol + x].Value);
       }
    }
    
    Answered on January 11, 2019.
    Add Comment


  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.