Read data from excel file Laravel Excel

Question

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

where is the implementation? any code? any problem?

Ben
– Ben

2018-04-08 21:12:36 +00:00
Commented Apr 8, 2018 at 21:12 — Ben
– Ben, Commented Apr 8, 2018 at 21:12
updated with code and current output

BlackPearl
– BlackPearl

2018-04-08 21:22:23 +00:00
Commented Apr 8, 2018 at 21:22 — BlackPearl
– BlackPearl, Commented Apr 8, 2018 at 21:22

Sacha · Accepted Answer · 2018-04-08 22:35:46Z

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.

Nice one but there is a little issue, the subjects are dynamic, meaning I can't tell their positions and if there will be English Language or not. I can change the format of the spreadsheet to anything that works.

Collectives™ on Stack Overflow

Read data from excel file Laravel Excel

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related