How can i get actual used range for modified excels using Epplus?

Question

I am reading data from excel to datable using EPPlus.

After reading an excel sheet with 10 rows of record, I modified the excel sheet by removing existing data and kept data for only one row. But when I am reading the modified excel it still reading 10 rows (1 with value and remaining as null fields) to data table.

How can limit this? I am using following code for reading Excel.

using (var pck = new OfficeOpenXml.ExcelPackage())
{
    using (var stream = File.OpenRead(FilePath))
    {
        pck.Load(stream);
    }
    var ws = pck.Workbook.Worksheets.First();                   
    bool hasHeader = true; // adjust it accordingly(this is a simple approach)
    foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
    {
        DSClientTransmittal.Tables[0].Columns.Add(hasHeader ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column));
    }
    var startRow = hasHeader ? 2 : 1;
    for (var rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)
    {
        //var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];
        var wsRow = ws.Cells[rowNum, 1, rowNum, DSClientTransmittal.Tables[0].Columns.Count];
        var row = DSClientTransmittal.Tables[0].NewRow();
        foreach (var cell in wsRow)
        {
            try
            {
                object cellValue = cell.Value;
                //row[cell.Start.Column - 1] = cell.Text;
                row[cell.Start.Column - 1] = cellValue.ToString().Trim();
                //cell.Style.Numberformat.Format = "@";
                //row[cell.Start.Column - 1] = cell.Text;
            }
            catch (Exception ex) { }
        }
        DSClientTransmittal.Tables[0].Rows.Add(row);
    }
    pck.Dispose();
}   

When I was using Interop excel to read excel, same issue was overcame by clearformat() method like

ws.Columns.ClearFormats();
xlColCount = ws.UsedRange.Columns.Count;

Is there any equivalent for this in Epplus open xml? How can I get actual used range for modified excels?

Answer 1

There is no built-in way of indicating that a row shouldn't be accounted for when only deleting data in some cells.

Dimension is as close as you can get, but rows are included in the Dimension if any column contains data or if any row above or below contains data.

You could however try to find out if you should skip a row in the for loop. For example if you always delete data in the first 4 columns only, then you could try:

if(!ws.Cells[rowNum, 1, rowNum, 4].All(c => c.Value == null))
{
    //Continue adding the row to the table
}

The description isn't indicating the criteria for skipping a row, but you get the idea.