LINQ Pivot to Excel File

Question

I have the following class

public class DaysCoverReport
{
    public string SupplierCode { get; set; }
    public string CustomerCode { get; set; }
    public DateTime StartDate { get; set; }
    public int Dur { get; set;}
}

I've created a list by parsing a .csv file:

IEnumerable<DaysCoverReport> daysCoverList = 
           DaysCoverReport.ParseDaysCoverReport(file).ToList();

I want to pivot the list by StartDate and sum Dur for each date, and output the following to an Excel workbook via the EPPlus library:

                              Start Date
Customer Code    Item Desc.    7/16/2014    7/17/2014    7/18/2014
1234-6789        Test Item A          10            2            3
45-9003          Test Item B           5            1            8

I assume the best method would be to pivot the data using LINQ and pass the pivoted data to EPPlus via the following:

worksheet.Cells["A1"].LoadFromCollection(pivotedDataList, true)

Here is what I have so far:

var query = (from d in daysCoverList
                group d by new {d.ItemDescription, d.CustomerCode, d.StartDate}
                into grp
                select new
                {
                    grp.Key.CustomerCode,
                    grp.Key.ItemDescription,
                    grp.Key.StartDate,
                    Dur = grp.Sum(d => d.Dur)
                }).ToList();

But it's not exactly what I want, as the list contains 2600 rows, whereas if I perform this operation in Excel, it consolidates down to ~10 rows. I think I might need to perform one more operation on the query object.

EDIT: Using @a-h's answer below, below is the full solution. Note that this solution includes a grand total row and column and is probably not optimized as I'm fairly new to LINQ.

IEnumerable<DaysCoverReport> daysCoverList = DaysCoverReport.ParseDaysCoverReport(file).ToList();

//Group by Customer Code and Item Description
var grouped = daysCoverList.GroupBy(d => new
{
    d.CustomerCode,
    d.ItemDescription
})
.Select(grp => new
{
    grp.Key.CustomerCode,
    grp.Key.ItemDescription,
    GroupedByDate = grp.ToLookup(g => g.StartDate.Date, g => g.Dur),
    DurSum = grp.Sum(g => g.Dur)
})
.OrderBy(grp => grp.CustomerCode)
.ToList();

var totalsByDate = daysCoverList.GroupBy(d => new
{
    d.StartDate.Date,
})
.Select(d => new
{
   d.Key.Date,
   GroupedByDate = d.ToLookup(g => g.StartDate.Date, g => g.Dur),
})
.ToList();

//Get distinct list of dates for pivot columns
var columns = grouped
   .SelectMany(grp => grp.GroupedByDate.Select(g => g.Key))
    .Distinct()
    .OrderBy(d => d).ToList();

using (ExcelPackage package = new ExcelPackage(new FileInfo(saveFileName)))
{
    ExcelWorksheet pivot = package.Workbook.Worksheets.Add("Traffic Light Report");

    pivot.Cells["A1"].Value = "Sum of Dur";
    pivot.Cells["A1"].Style.Font.Bold = true;
    pivot.Cells["C1"].Value = "Start Date";
    pivot.Cells["C1"].Style.Font.Bold = true;

    for(int i = 1; i <= columns.Count + 3; i++)
    {
        var headerCell = pivot.Cells[2, i];

        if (i == 1)
            headerCell.Value = "Customer Code";
        else if (i == 2)
           headerCell.Value = "Item Description";
        else if (i == columns.Count + 3)
            headerCell.Value = "Grand Total";
        else
            headerCell.Value = columns[i - 3].ToString("MM/dd/yyyy");

        headerCell.Style.Font.Bold = true;
    }

    int j = 3;
    foreach (var line in grouped)
    {
        for (int i = 1; i <= columns.Count + 3; i++)
        {
            var cell = pivot.Cells[j, i];

            if (i == 1)
                cell.Value = line.CustomerCode;
            else if (i == 2)
                cell.Value = line.ItemDescription;
            else if (i == columns.Count + 3)
                cell.Value = line.DurSum;
            else
                cell.Value = line.GroupedByDate.Contains(columns[i - 3]) ? line.GroupedByDate[columns[i - 3]].Sum() : 0;
         }

        j++;
    }

    //Write total row
    var totalRowCell = pivot.Cells[j, 1];
    totalRowCell.Value = "Grand Total";
    totalRowCell.Style.Font.Bold = true;

    int k = 3;
    foreach (var date in totalsByDate)
    {
        totalRowCell = pivot.Cells[j, k];
        totalRowCell.Value = date.GroupedByDate.Contains(columns[k - 3]) ? date.GroupedByDate[columns[k - 3]].Sum() : 0;

         k++;
    }

    pivot.Cells[j, k].Value = daysCoverList.Sum(d => d.Dur);

   //Apply conditional formatting for Traffic Light
    ExcelAddress formatRangeAddress = new ExcelAddress(3, 3, grouped.Count + 2, columns.Count + 2);

    var red = pivot.ConditionalFormatting.AddLessThanOrEqual(formatRangeAddress);
    red.Style.Fill.BackgroundColor.Color = System.Drawing.Color.Red;
    red.Formula = "0";

    var amber = pivot.ConditionalFormatting.AddBetween(formatRangeAddress);
    amber.Style.Fill.BackgroundColor.Color = System.Drawing.Color.Orange;
    amber.Formula = "0";
    amber.Formula2 = "15";

    var green = pivot.ConditionalFormatting.AddBetween(formatRangeAddress);
    green.Style.Fill.BackgroundColor.Color = System.Drawing.Color.Green;
    green.Formula = "15";
    green.Formula2 = "45";

    var blue = pivot.ConditionalFormatting.AddGreaterThan(formatRangeAddress);
    blue.Style.Fill.BackgroundColor.Color = System.Drawing.Color.Blue;
    blue.Formula = "45";

    pivot.Cells[pivot.Dimension.Address].AutoFitColumns();

    package.Save();
}

Answer 1

Here's my quick hack at a solution. I've used AutoFixture at the top to create test data.

The first grouping is by Customer Code and Item Description, then, this is converted into a lookup based on the Date, and taking only the "Dur". This allows the Sum to be retrieved by key later on in the code.

I've included a set of writing out to tab separated values which should be similar to writing to Excel problem.

void Main()
{
    var fixture = new Fixture();

    var daysCoverList = fixture.CreateMany<DaysCoverReport>(10);

    // Group by customer code and description.
    var grouped = daysCoverList.GroupBy(dcl => new 
    { 
        CustomerCode = dcl.CustomerCode, 
        ItemDescription = dcl.ItemDescription, 
    })
    .Select(grp => new 
    { 
        CustomerCode = grp.Key.CustomerCode, 
        ItemDescription = grp.Key.ItemDescription, 
        GroupedByDate = grp.ToLookup(g => g.StartDate.Date, g => g.Dur)
    });

    var columns = grouped
        .SelectMany(grp => grp.GroupedByDate.Select(g => g.Key))
        .Distinct()
        .OrderBy(d => d);

    // Write column headings.
    Console.Write("Customer Code\t");
    Console.Write("Item Desc.\t");
    foreach(var dateColumn in columns)
    {
        Console.Write(dateColumn.ToString() + "\t");
    }
    Console.WriteLine();

    // Write values.
    foreach(var line in grouped)
    {
        Console.Write(line.CustomerCode);
        Console.Write("\t");
        Console.Write(line.ItemDescription);
        Console.Write("\t");

        foreach(var dateColumn in columns)
        {
            if(line.GroupedByDate.Contains(dateColumn))
            {
                Console.Write(line.GroupedByDate[dateColumn].Sum());
            }
            else
            {
                Console.Write(0);
            }

            Console.Write("\t");
        }
        Console.WriteLine();
    }
}

public class DaysCoverReport
{
    public string SupplierCode { get; set; }
    public string CustomerCode { get; set; }
    public DateTime StartDate { get; set; }
    public int Dur { get; set;}
    public string ItemDescription { get;set;}
}