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?

Peculiarize

How are you removing the data? – Thyrotoxicosis

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? – Psalterium

@PhilipBijker ,@Ernie : i select all the cells which required to remove and pressed delete key,not entire row – Peculiarize

Try deleting the entire row, this should lead to a correct dimension – Psalterium

@Philip Bijker: i am getting these excel from my clients,it is not possible to format all excels this way,that is the real problem.When i used open xml sdk also got the same problem. – Peculiarize

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. – Psalterium

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.

Psalterium

it is not possible to say which rows will be removed.instead of giving 4 placed ws.Dimension.End.Row will it work? – Peculiarize

You indicate the following: "i modified the excel sheet by removing existing data and kept data for only one row". What data is removed exactly? Those should be the criteria for treating the row as removed and thus skipping it in the for-loop. – Psalterium

it was like random test,no prior criteria for removing cell data,i have a excel template with sample data for bulk import.Most of the clients will edit(by removing some rows and all)that existing template and send – Peculiarize

Either the client needs to remove the entire row, or you need to know when to treat the row as removed. Otherwise you won't know which rows to handle, right? – Psalterium

yes,you are right. so i planned to change if (!ws.Cells[rowNum, 1, rowNum, DSClientTransmittal.Tables[0].Columns.Count].All(c => c.Value == null)) – Peculiarize

Correct. Alternatively you could use ws.Dimension.End.Column instead of DSClientTransmittal.Tables[0].Columns.Count. Either of these will skip the row if all cells in the row are empty. – Psalterium

Answer 2

To start with, I am not a C# programmer, but I think I have a solution that works using an Excel VBA script. You may be able to run this Excel VBA code with C, or get insight in how to accomplish the same thing with C+.

The problem you are having is related to the way Excel handles the working size of a worksheet. If you enter data in the 1 millionth row and then delete that cell, Excel still shows the worksheet as having 1 million rows.

I tested out this Excel VBA code and it successfully deleted all rows that were completely empty, and then reset the worksheet size.

Sub DelEmptyRowsResizeWorksheet()
  Dim i As Long, iLimit As Long
    iLimit = ActiveSheet.UsedRange.Rows.Count
  For i = iLimit To 1 Step -1
    If Application.CountA(Cells(i, 1).EntireRow) = 0 Then
       Cells(i, 1).EntireRow.Delete
    End If
  Next i
   iLimit = ActiveSheet.UsedRange.Rows.Count   ' resize the worksheet based on the last row with data
End Sub

To do this manually without a script, first delete all empty rows at the bottom (or columns on the right side) of a worksheet, save it, then close and reopen the workbook. I found that this also resets the Excel workbook size.

Urolith

This is completely unhelpful to the posted issue, and since the issue is trivial to solve in VBA, having the solution posted here is only a distraction. – Valoniah