Weird behavior with local functions and shared variables

Question

I'm generating an Excel file for a budget and there is a weird behavior with the local functions. The problem seems to be with the shared variable rowIndex. For some reason, the total formula overwrites the content of the first cell of each section but the "Total active" label does not overwrite the account name. Can someone explain what is causing this weird behavior?

Code:

private void Export()
{
    var workbook = new Aspose.Cells.Workbook();
    var worksheet = workbook.Worksheets[0];

    int rowIndex = 0;

    var number = 0;
    var data = new List<dynamic>
    {
        new { Name = $"Account {++number}", Amount = 50, IsIncome = true, Active = true },
        new { Name = $"Account {++number}", Amount = 25, IsIncome = true, Active = false },
        new { Name = $"Account {++number}", Amount = 75, IsIncome = false, Active = true },
        new { Name = $"Account {++number}", Amount = 100, IsIncome = false, Active = false },
        new { Name = $"Account {++number}", Amount = 60, IsIncome = true, Active = false },
        new { Name = $"Account {++number}", Amount = 90, IsIncome = false, Active = true },
        new { Name = $"Account {++number}", Amount = 40, IsIncome = true, Active = true },
        new { Name = $"Account {++number}", Amount = 20, IsIncome = false, Active = false }
    };

    WriteExportSection("INCOME", data.Where(i => i.IsIncome).ToList());
    WriteExportSection("EXPENSES", data.Where(i => !i.IsIncome).ToList());

    workbook.Save("budget.xlsx");

    IEnumerable<int> WriteRowValues(IList<dynamic> items)
    {
        foreach(var item in items)
        {
            worksheet.Cells[rowIndex, 0].Value = item.Name;
            worksheet.Cells[rowIndex, 1].Value = item.Amount;
            worksheet.Cells[rowIndex, 2].Value = item.Active;
            if (item.Active)
            {
                yield return rowIndex;
            }
            rowIndex++;
        }
    }

    void WriteExportSection(string name, IList<dynamic> items)
    {
        worksheet.Cells[rowIndex, 0].Value = name;
        rowIndex++;
        worksheet.Cells[rowIndex, 0].Value = "Account";
        worksheet.Cells[rowIndex, 1].Value = "Amount";
        worksheet.Cells[rowIndex, 2].Value = "Is active";
        rowIndex++;
        var activeRows = WriteRowValues(items);
        rowIndex++;
        worksheet.Cells[rowIndex, 0].Value = "Total active";
        worksheet.Cells[rowIndex, 1].Formula = "SUM(" + string.Join(",", activeRows.Select(r => worksheet.Cells[r, 1].Name)) + ")";
        rowIndex++;
    }
}

Chris Sinclair · Accepted Answer · 2019-08-29T17:42:56.693

This is because WriteRowValues is using deferred execution. Because it's using yield, it does not execute until you start iterating it with the expression:

activeRows.Select(r => worksheet.Cells[r, 1].Name))

As such, the rowIndex variable isn't incremented until you already start outputting the total rows. These lines outputting the totals:

worksheet.Cells[rowIndex, 0].Value = "Total active";
worksheet.Cells[rowIndex, 1].Formula = "SUM(" + string.Join(",", activeRows.Select(r => worksheet.Cells[r, 1].Name)) + ")";

Still have rowIndex at their original, unincremented values. That is, the "first row" for data.

The simplest fix is to just force execution immediately:

var activeRows = WriteRowValues(items).ToList();

A more proper fix is probably to just ditch the deferred execution altogether since it doesn't seem that you're taking advantage of it, and it could cause issues later (say if you end up iterating it twice):

List<int> WriteRowValues(IList<dynamic> items)
{
    var activeIndexes = new List<int>();
    foreach(var item in items)
    {
        worksheet.Cells[rowIndex, 0].Value = item.Name;
        worksheet.Cells[rowIndex, 1].Value = item.Amount;
        worksheet.Cells[rowIndex, 2].Value = item.Active;
        if (item.Active)
        {
            activeIndexes.Add(rowIndex);
        }
        rowIndex++;
    }
    return activeIndexes;
}

Weird behavior with local functions and shared variables

1 Answers1