Configure additional pivot column headers on PivotTable

Question

I'm using the NReco PivotData library to pivot on a date field so that the dates appear horizontally in the header. This works well, however, I'd like to add dates to fill in the gaps so that the results are more complete (by default the library only shows dates which are contained within a record).

For example, if I have a total of 2 records, the first on the 01/01/2018 and the second on the 10/01/2018, I will not have columns for all the dates in between.

I have got around by adding records for each date in my range with null values, however, this adds a blank row to the top of the results which isn't ideal.

In summary, I'd like to add "virtual" values for my Date dimension so that I can have a more complete result set by showing columns for dates that don't have any entries.

Code

using (var package = new ExcelPackage())
{
    var dimensionNames = new string[] { "Id", "Name", "Date", "Hours" };
    var aggregator = new SumAggregatorFactory("Hours");
    var rows = new Dictionary<string, object>()
    {
        { 1, "John Smith", new DateTime(2018, 1, 1), 5 },
        { 2, "John Doe", new DateTime(2018, 1, 10), 10 }
    };
    var pivotData = new PivotData(dimensionNames, aggregator, rows);
    var pivotTable = new PivotTable(new string[] { "Name" }, new string[] { "Date" }, pivotData);

    var worksheet = package.Workbook.Worksheets.Add("Report");
    var tableWriter = new PivotTableExcelWriter(worksheet);
    tableWriter.Write(pivotTable);
    package.SaveAs(...);
}

score 1 · Accepted Answer · answered Jun 07 '18 at 20:07

By default PivotData class creates new data point for non-exiting dimension keyswhen it is accessed with indexer, you can use this for creating 'empty' dates:

for (int i=1; i<=10; i++) {
  // if aggregator for specified keys doesn't exist, it will be added
  var aggr = pivotData[1, "John Smith", new Date(2018, 1, i), 5];
}

(you have to use real keys for dimensions except "Date" if you don't want to create additional rows/columns in the pivot table. You can get them from first real datapoint: object[] firstEntryKeys = pivotData.First().Key;)

This behavior is controlled by PivotData.LazyAdd property and can be disabled.

Configure additional pivot column headers on PivotTable

1 Answers1