EPPlus - shift rows up after DeleteRow

Question

h3110 3v3ry0n3, especially EPPlus team!

I have an Excel template like this:

 _______________________________
|    Title 1    |    Title 2    |
|_______________|_______________|
| %Placeholder% | %Placeholder% |
|_______________|_______________|
| Special cell  | Special cell  |
|_______________|_______________|

Special cell - empty cell with some data validations and data lists.

So, this template has double purpose:

• to be filled with data programmatically (replace %placeholder%s) or
• to be filled by hand (by selecting lists in special cells)

In second case I invoke sheet.DeleteRow(1) and expect special row #3 to become row #2 (shift up), but this does not happen. So, the question is: is this a bug and I should post an issue or there is some workaround to force rows shift up?

Here're some thoughts. I guess, this could be caused by treating special cells as empty. May be, there is a way to copy data validations from row #3 to row #2? Also there is another similar question C# ExcelPackage (EPPlus) DeleteRow does not change sheet dimension? for more general case and it does not seem that deleted rows there are followed by empty lines.

Answer 1

Seems to be a known issue. If you look at the current version of ExcelWorksheet.cs in the source code

https://epplus.codeplex.com/SourceControl/latest#EPPlus/ExcelWorkbook.cs:

#region DeleteRow
/// <summary>
/// Delete the specified row from the worksheet.
/// </summary>
/// <param name="row">A row to be deleted</param>
public void DeleteRow(int row)
{
    DeleteRow(row, 1);
}
/// <summary>
/// Delete the specified row from the worksheet.
/// </summary>
/// <param name="rowFrom">The start row</param>
/// <param name="rows">Number of rows to delete</param>
public void DeleteRow(int rowFrom, int rows)
{
    CheckSheetType();
    if (rowFrom < 1 || rowFrom + rows > ExcelPackage.MaxRows)
    {
        throw(new ArgumentException("Row out of range. Spans from 1 to " + ExcelPackage.MaxRows.ToString(CultureInfo.InvariantCulture)));
    }
    lock (this)
    {
        _values.Delete(rowFrom, 0, rows, ExcelPackage.MaxColumns);
        _formulas.Delete(rowFrom, 0, rows, ExcelPackage.MaxColumns);
        _flags.Delete(rowFrom, 0, rows, ExcelPackage.MaxColumns);
        _commentsStore.Delete(rowFrom, 0, rows, ExcelPackage.MaxColumns);
        _hyperLinks.Delete(rowFrom, 0, rows, ExcelPackage.MaxColumns);
        _names.Delete(rowFrom, 0, rows, ExcelPackage.MaxColumns);

        Comments.Delete(rowFrom, 0, rows, ExcelPackage.MaxColumns);
        Workbook.Names.Delete(rowFrom, 0, rows, ExcelPackage.MaxColumns, n => n.Worksheet == this);

        AdjustFormulasRow(rowFrom, rows);
        FixMergedCellsRow(rowFrom, rows, true);

        foreach (var tbl in Tables)
        {
            tbl.Address = tbl.Address.DeleteRow(rowFrom, rows);
        }
        foreach (var ptbl in PivotTables)
        {
            if (ptbl.Address.Start.Row > rowFrom + rows)
            {
                ptbl.Address = ptbl.Address.DeleteRow(rowFrom, rows);
            }
        }
    }
}

There is no reference of updating to the public ExcelDataValidationCollection DataValidations object.

Looking at the current dev branch (now on github) seems it will be addressed in the next release whenever that is:

https://github.com/JanKallman/EPPlus/blob/master/EPPlus/ExcelWorksheet.cs#L2551

Which references the issue on codeplex:

https://epplus.codeplex.com/workitem/15573

So you could try to reproduce the functionality which may be difficult since it looks like they had to make changes in several places. Or you try to pull the latest from git and compile.