EPPlus v4 Worksheet.InsertRow >1024 bug

Question

Found that when inserting > 1024 row with EPPlus v4 (v4.5.3.3), the formula in cells under new inserted rows is be corrupted, and one original row disappeared.

Unfortunately the EPPlus v4 git is archieved as they moved from LGPL to Commercial licensing model in v5.

With a trial on NPOI, it can't be an option as it lacks of some critical functions like ShiftColumn/DeleteColumn, and can't support Excel with Table, formula-using ConditionalFormatting, and with backward-compatible attribute added by MSExcel.

Anyone EPPlus v4 users faced the same situation and found a good solution?

Answer 1

Before having a better answer, this is a workaround Extension method I used for this case:

/// <summary>
/// Providing Extensions to EPPlus/Excel
/// </summary>
public static class EPPlusExcelUtil
{
    public const int EPPlusMaxRowInsertPerAction = 1024;

    /// <summary>
    /// This is a workaround to handle the case inserting > 1024 rows.
    /// <remarks>EPPlus v4 issue until v4.5.3.3</remarks>
    /// </summary>
    /// <param name="ws"></param>
    /// <param name="rowFrom">The position of the new row</param>
    /// <param name="rows">Number of rows to insert.</param>
    /// <param name="copyStylesFromRow">Copy Styles from this row. Applied to all inserted rows</param>
    public static void InsertRowEnhanced(this ExcelWorksheet ws, int rowFrom, int rows, int copyStylesFromRow = 0)
    {
        int batchOf1k = rows / EPPlusMaxRowInsertPerAction;
        for (int i = 0; i < batchOf1k; i++)
        {
            ws.InsertRow(rowFrom, EPPlusMaxRowInsertPerAction, copyStylesFromRow);
        }

        int rowsToInsertAfter1k = rows % EPPlusMaxRowInsertPerAction;
        if (rowsToInsertAfter1k != 0)
        {
            ws.InsertRow(rowFrom, rowsToInsertAfter1k, copyStylesFromRow);
        }
    }

}