LoadFromCollection does not increment column index when processing EPPlusFormulaColumn, causing incorrect column mapping

[rafgithub]

EPPlus usage

Personal use

Environment

Windows

Epplus version

8.2.1

Spreadsheet application

No response

Description

Problem

When using EPPlusFormulaColumn attributes in the LoadFromCollection method, the column index (col variable) is not incremented after processing formula columns. This causes incorrect column mapping and data misalignment when formula columns are positioned at non-linear indices in the collection.

Root Cause

In the SetValuesAndFormulas method of the LoadFromCollection<T> class, the code handles three different cases for each column:

1. Path-based columns: The col variable is properly incremented after setting the value
2. Formula columns: The formula is added to the formulaCells dictionary, but col is NOT incremented
3. FormulaR1C1 columns: Similar to formula columns, col is NOT incremented

This inconsistency leads to column index misalignment when processing subsequent columns.

Code Location

File: LoadFromCollection.cs
Method: SetValuesAndFormulas

Affected Code

foreach (ColumnInfo columnInfo in columns)
{
    object obj = null;
    if (columnInfo.Path != null && !columnInfo.Path.IsFormulaColumn && columnInfo.Path.Depth > 0)
    {
        obj = columnInfo.Path.GetLastMemberValue(item, _bindingFlags);
        // ... processing ...
        if (transpose)
        {
            values[col++, row] = obj;  // col IS incremented
        }
        else
        {
            values[row, col++] = obj;  // col IS incremented
        }
    }
    else if (!string.IsNullOrEmpty(columnInfo.Formula))
    {
        formulaCells[columnInfo.Index] = new FormulaCell
        {
            Formula = columnInfo.Formula
        };
        // col is NOT incremented - BUG!
    }
    else if (!string.IsNullOrEmpty(columnInfo.FormulaR1C1))
    {
        formulaCells[columnInfo.Index] = new FormulaCell
        {
            FormulaR1C1 = columnInfo.FormulaR1C1
        };
        // col is NOT incremented - BUG!
    }
}

Impact

When a class contains formula columns mixed with regular data columns, the column indices become misaligned, resulting in:

• Data being written to incorrect columns
• Formula columns referencing wrong cell positions
• Corrupted Excel output with data appearing in unexpected locations
• Potential data loss or overwriting of values

Example Scenario:

[EpplusTable(TableStyle = TableStyles.Dark1, PrintHeaders = true)]
[EpplusFormulaTableColumn(Order = 6, NumberFormat = "€#,##0.00", Header = "Tax amount", FormulaR1C1 = "RC[-2] * RC[-1]", TotalsRowFunction = RowFunctions.Sum)]
public class Actor
{
    [EpplusTableColumn(Order = 0, NumberFormat = "yyyy-MM-dd")]
    public DateTime Birthdate { get; set; }

    [EpplusTableColumn(Order = 1, Header = "First name")]
    public string FirstName { get; set; }

    [EpplusTableColumn(Order = 2)]
    public string MiddleName { get; set; }

    [EpplusTableColumn(Order = 3)]
    public string LastName { get; set; }

    [EpplusTableColumn(Order = 4, NumberFormat = "€#,##0.00")]
    public double Salary { get; set; }

    [EpplusTableColumn(Order = 5, NumberFormat = "0%")]
    public double Tax { get; set; }

    // Formula column at Order 6: "Tax amount" = Salary * Tax

    [EpplusTableColumn(Order = 7)]
    public string FullName { get; set; }  // This will be never written!

    [EpplusTableColumn(Order = 8)]
    public string Comments { get; set; }  // This will be written to column 7!

[JanKallman]

Thanks for the detailed issue description. We will have a look at it and provide a fix.

[rafgithub]

Thanks a lot for the quick fix. Any ETA for the release?

[swmal]

@rafgithub within a week or two.