Create the appearance of a "Table" with cells, from a Multi-Dimensional Collection

Question

I have the following manually created Excel "table" which I'm trying to duplicate in Laravel-Excel 3.1.

Here is what my collection looks like as json:

"missingData": {
    "2010": {
        "Jan": {
            "count": 45
        },
        "Feb": {
            "count": 70
        },
        "Mar": {
            "count": 91
        },
        "Apr": {
            "count": 176
        },
        "May": {
            "count": 152
        },
        "Jun": {
            "count": 91
        },
        "Jul": {
            "count": 153
        },
        "Aug": {
            "count": 46
        },
        "Sep": {
            "count": 56
        },
        "Oct": {
            "count": 76
        },
        "Nov": {
            "count": 68
        },
        "Dec": {
            "count": 62
        }
    },
    "2011": {
        "Jan": {
            "count": 130
        },
        "Feb": {
            "count": 289
        },
        "Mar": {
            "count": 166
        },
        "Apr": {
            "count": 106
        },
        "May": {
            "count": 124
        },
        "Jun": {
            "count": 230
        },
        "Jul": {
            "count": 295
        },
        "Aug": {
            "count": 203
        },
        "Sep": {
            "count": 162
        },
        "Oct": {
            "count": 221
        },
        "Nov": {
            "count": 199
        },
        "Dec": {
            "count": 234
        }
    },
    "2012": {
        "Jan": {
            "count": 136
        },
        "Feb": {
            "count": 190
        },
        "Mar": {
            "count": 211
        },
        "Apr": {
            "count": 140
        },
        "May": {
            "count": 171
        },
        "Jun": {
            "count": 436
        },
        "Jul": {
            "count": 392
        },
        "Aug": {
            "count": 192
        },
        "Sep": {
            "count": 20
        },
        "Oct": {
            "count": 270
        },
        "Nov": {
            "count": 261
        },
        "Dec": {
            "count": 125
        }
    },
    "2013": {
        "Jan": {
            "count": 116
        },
        "Feb": {
            "count": 115
        },
        "Mar": {
            "count": 132
        },
        "Apr": {
            "count": 90
        },
        "May": {
            "count": 125
        },
        "Jun": {
            "count": 97
        },
        "Jul": {
            "count": 130
        },
        "Aug": {
            "count": 130
        },
        "Sep": {
            "count": 121
        },
        "Oct": {
            "count": 218
        },
        "Nov": {
            "count": 103
        },
        "Dec": {
            "count": 109
        }
    },
    "2014": {
        "Jan": {
            "count": 201
        },
        "Feb": {
            "count": 104
        },
        "Mar": {
            "count": 146
        },
        "Apr": {
            "count": 231
        },
        "May": {
            "count": 146
        },
        "Jun": {
            "count": 163
        },
        "Jul": {
            "count": 165
        },
        "Aug": {
            "count": 145
        },
        "Sep": {
            "count": 123
        },
        "Oct": {
            "count": 118
        },
        "Nov": {
            "count": 130
        },
        "Dec": {
            "count": 124
        }
    },
    "2015": {
        "Jan": {
            "count": 283
        },
        "Feb": {
            "count": 71
        },
        "Mar": {
            "count": 123
        },
        "Apr": {
            "count": 518
        },
        "May": {
            "count": 109
        },
        "Jun": {
            "count": 91
        },
        "Jul": {
            "count": 119
        },
        "Aug": {
            "count": 95
        },
        "Sep": {
            "count": 122
        },
        "Oct": {
            "count": 126
        },
        "Nov": {
            "count": 183
        },
        "Dec": {
            "count": 81
        }
    },
    "2016": {
        "Jan": {
            "count": 148
        },
        "Feb": {
            "count": 236
        },
        "Mar": {
            "count": 87
        },
        "Apr": {
            "count": 73
        },
        "May": {
            "count": 77
        },
        "Jun": {
            "count": 62
        },
        "Jul": {
            "count": 40
        },
        "Aug": {
            "count": 28
        },
        "Sep": {
            "count": 38
        },
        "Oct": {
            "count": 92
        },
        "Nov": {
            "count": 99
        },
        "Dec": {
            "count": 301
        }
    },
    "2017": {
        "Jan": {
            "count": 32
        },
        "Feb": {
            "count": 36
        },
        "Mar": {
            "count": 24
        },
        "Apr": {
            "count": 51
        },
        "May": {
            "count": 32
        },
        "Jun": {
            "count": 38
        },
        "Jul": {
            "count": 65
        },
        "Aug": {
            "count": 135
        },
        "Sep": {
            "count": 50
        },
        "Oct": {
            "count": 40
        },
        "Nov": {
            "count": 271
        },
        "Dec": {
            "count": 52
        }
    },
    "2018": {
        "Jan": {
            "count": 60
        },
        "Feb": {
            "count": 37
        },
        "Mar": {
            "count": 38
        },
        "Apr": {
            "count": 73
        },
        "May": {
            "count": 42
        },
        "Jun": {
            "count": 28
        },
        "Jul": {
            "count": 66
        },
        "Aug": {
            "count": 61
        },
        "Sep": {
            "count": 165
        },
        "Oct": {
            "count": 100
        },
        "Nov": {
            "count": 194
        },
        "Dec": {
            "count": 148
        }
    },
    "2019": {
        "Jan": {
            "count": 101
        },
        "Feb": {
            "count": 61
        },
        "Mar": {
            "count": 153
        },
        "Apr": {
            "count": 79
        },
        "May": {
            "count": 51
        },
        "Jun": {
            "count": 129
        },
        "Jul": {
            "count": 17
        },
        "Aug": {
            "count": 18
        },
        "Sep": {
            "count": 31
        },
        "Oct": {
            "count": 27
        },
        "Nov": {
            "count": 25
        },
        "Dec": {
            "count": 104
        }
    },
    "2020": {
        "Jan": {
            "count": 39
        },
        "Feb": {
            "count": 128
        },
        "Mar": {
            "count": 0
        }
    }
}

Every year is a key, and every month I have data for is a property of that year with another property of count.

I haven't gotten very far:

use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use Maatwebsite\Excel\Concerns\WithHeadings;

class MissingWeatherExport implements ShouldAutoSize, FromCollection, WithHeadings
{
    use Exportable;

    protected $missingData;

    public function __construct($missingData)
    {
        $this->missingData = $missingData;
    }

    public function headings(): array
    {
        return $this->missingData->keys();
    }

    public function collection()
    {
        return $this->missingData;
    }

}

Any pointers to accomplish this in Laravel-Excel 3.1?

Answer 1

I took a step back and thought about formatting the data in a way Laravel-Excel would be able to understand out of the box.

I took the approach of flattening my array:

// Controller

// Get the years to use for columns. Add a space to account for month name in column 1
$yearColumns = $missingData->keys()->toArray();
array_unshift($yearColumns,"");

// See what months we returned for the first key of the collection to use as a template
$months = $missingData->first()->keys()->toArray();

// Each year has an index in this array, null if no data for that index
$monthRows = array();
foreach ($months as $month) {
    $monthConsolidated = $missingData->pluck($month)->flatten()->toArray();
    array_unshift($monthConsolidated,$month);
    array_push($monthRows, $monthConsolidated);
}

return Excel::download(new MissingWeatherExport($yearColumns, $monthRows), 'test.xlsx');;

// MissingWeatherExport
use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\FromArray;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use Maatwebsite\Excel\Concerns\WithHeadings;

class MissingWeatherExport implements ShouldAutoSize, FromArray, WithHeadings
{
    use Exportable;

    protected $yearColumns;
    protected $monthRows;

    public function __construct($yearColumns, $monthRows)
    {
        $this->yearColumns = $yearColumns;
        $this->monthRows = $monthRows;
    }

    public function headings(): array
    {
        return $this->yearColumns;
    }

    public function array(): array
    {
        return $this->monthRows;
    }

}

Output of test.xlsx: