Overview

The ILookup<TKey, TElement> interface offers a robust solution for managing hierarchical data structures where a single key maps to multiple values. Extending IEnumerable<T>, it inherits all LINQ capabilities while providing O(1) indexing performance. This approach is superior to standard Dictionaries or Lists when dealing with one-to-many relationships, such as grouping administratvie regions by their parent area.

Data Grouping Strategy

Consider a scenario where regional data must be categorized by province before exporting. The process begins by retrieving the source records. Once fetched, the code determines the object's metadata dynamically to facilitate reflection-based processing later.

var rawData = service.FetchHierarchy(regionId, categoryType, startTime, endTime);

if (rawData.Any())
{
    var itemTypeInfo = rawData.First().GetType();
    
    // Extract unique identifiers for the grouping key
    var uniqueKeys = rawData
        .Select(x => (string)itemTypeInfo.GetProperty("Name").GetValue(x))
        .Distinct()
        .ToList();

    // Construct the ILookup
    // Key: Province Name | Value: List of subordinate cities
    var lookupGrouping = uniqueKeys
        .Select(name => new { 
            Id = name,
            SubRegions = service.GetSubdivisions(name, regionId, categoryType, startTime, endTime)
        })
        .ToLookup(g => g.Id, p => p.SubRegions.ToList());

    // Trigger the export process
    ExcelExporter.SaveReport(
        $"export_{DateTime.Now:yyyyMMddHHmmss}.xls", 
        lookupGrouping, 
        new[] { "Region Name", "Population Count" }
    );
}
else
{
    throw new NullReferenceException("No data available for export.");
}

Workbook Processing Implementation

The core export logic iterates through each entry in the ILookup. Each group becomes a dedicated worksheet named after its primary key. Reflection is utilized within the rendering loop to populate cells dynamically based on the source object's properties rather than hardcoding fields.

public static void SaveReport<TItem>(string outputFileName, ILookup<string, List<TItem>> groups, params string[] headerCols) 
    where TItem : class
{
    Stream streamResult;
    using (var workbook = new Workbook())
    {
        workbook.Worksheets.Clear();

        foreach (var group in groups)
        {
            // Create a new sheet for each grouping key
            var sheet = workbook.Worksheets.Add(group.Key);
            
            // Ensure there are items to write
            if (group.Count > 0 && group.First()?.Count > 0)
            {
                RenderSheet(sheet, group.First(), headerCols);
            }
        }

        streamResult = workbook.SaveToStream();
    }

    FileHandler.WriteStream(outputFileName, streamResult);
}

private static void RenderSheet<TItem>(Worksheet targetSheet, IEnumerable<TItem> rows, string[] columns) 
    where TItem : class
{
    var dataList = rows.ToList();
    int totalRows = dataList.Count;
    
    if (totalRows == 0) return;

    var cellCollection = targetSheet.Cells;
    var propertyInfoArray = dataList[0].GetType().GetProperties();
    
    // Initialize Headers
    for (int colIndex = 0; colIndex < columns.Length; colIndex++)
    {
        cellCollection[0, colIndex].PutValue(columns[colIndex]);
    }

    // Iterate rows and map properties
    int startRowIndex = 1;
    for (int rowIndex = 0; rowIndex < totalRows; rowIndex++)
    {
        var currentItem = dataList[rowIndex];
        
        for (int propIndex = 0; propIndex < columns.Length; propIndex++)
        {
            var value = propertyInfoArray[propIndex].GetValue(currentItem);
            cellCollection[startRowIndex + rowIndex, propIndex].PutValue(value);
        }
    }
}

This architecture decouples data retrieval from presentation formatting. By utilizing ILookup, the application ensures that related entities remain grouped logically, simplifying the iteration logic required for complex multi-sheet exports.