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?

Dimension seems to return the right range in all of my tests. Even after deleting rows and re-importing. Is Dimension indicating 10 rows in your situation after deleting? — Philip Bijker
@PhilipBijker ,@Ernie : i select all the cells which required to remove and pressed delete key,not entire row — AcAnanth
Try deleting the entire row, this should lead to a correct dimension — Philip Bijker
Rows are included in the Dimension if any column contains data or if any row above or below contains data. If this is your case, I'm afraid that writing custom logic for these null-rows is your only option. — Philip Bijker

Philip Bijker Philip Bijker · Accepted Answer · 2015-12-08T08:53:29

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.

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

2 Answers