Read data from excel file Laravel Excel

Below is an excel sheet that I want to upload and I want to read the data like so:

'sn' => 1,
'surname' => 'amadien',
'fname' => 'Franklyn',
'oname' => null,
'reg_no' => '3030421470HG',
'subject' =>[
            'mathematics' => [5, 12, 64],
            'english_language' => [5, 6, 55],
            'christian_religious_studies' => [6, 13, 48]
            'agricultural_science' => [4, 18, 64]
          ]

Code:

try {
        Excel::load($request->file('result-file'), function ($reader) {

            foreach ($reader->toArray() as $row) {
                dd($row);
                //User::firstOrCreate($row);
            }
        });
        session()->flash('success', 'Users uploaded successfully.');
        return back();
    } catch (\Exception $e) {
        session()->flash('error', $e->getMessage());
        return back();
    }

But so far, I have not been able to. Thanks

The reason your first array contains null values is because the Excel reader is reading your sheet line by line.

The first row (other than the headings) has empty cells for sn, surname, etc., whereas the values you want are actually on the second (third) row.

Your second issue is that your headings contain subject at least 4 times, which is getting combined into a single heading by the reader.

Ideally, you would reformat your spreadsheet. If possible, having your headings as:

sn surname fname oname reg_no mathematics english_language (and so on).

Having your rows as:

1 surname forename oname 12345 5, 12, 64 5, 6, 55 (and so on).

Notice how I've combined the scores for each subject in a single cell, separated by a comma.

Now you will end up with an array similar to this:

'sn' => 1,
'surname' => 'amadien',
'fname' => 'Franklyn',
'oname' => null,
'reg_no' => '3030421470HG',
'mathematics' => '5, 12, 64',
'english_language' => '5, 6, 55',

If you wanted your subjects to still be in a nested array, you could do this:

$subjects = array_only($row, ['mathematics', 'english_language', ...]);

foreach ($subjects as $subject => $scores) {
    $subjects[$subject] = explode(',', $scores);
}

$row['subjects'] = $subjects;

If it is not possible to change the format of the spreadsheet, you will need to combine two rows together.

Assuming the rows always come in pairs, you could use array_chunk to get the two arrays you need (one per row) in one set, then map over and combine the results by building up a new array.

For example:

$rows = array_chunk($rows, 2);

array_map(function ($subset) {
    return [
        'sn' => $subset[0]['sn'],
        'surname' => $subset[0]['surname'],
        'fname' => $subset[0]['fname'],
        'subjects' => [
            ...
        ]
    ];
}, $rows);

However, that will quickly become messy. The format of the sheet is likely completely unsuitable for what you are trying to achieve.