Get hyperlink URL in Laravel Excel 3.1 using Excel::import

Question

I am importing Excel Sheet using Excel::import Maatwebsite / Laravel Excel 3.1.

Controller

$sheet = Excel::toArray(new UsersImport(), $request->file('stock_file'), 
    null,\Maatwebsite\Excel\Excel::XLSX);

UserImport.php

use App\Models\User;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithCalculatedFormulas;
use Maatwebsite\Excel\Cell;
use Maatwebsite\Excel\Row;
use Maatwebsite\Excel\Concerns\OnEachRow;

class UsersImport implements ToModel, WithCalculatedFormulas, SkipsEmptyRows
{

    /**
     * @param  array  $row
     *
     * @return \Illuminate\Database\Eloquent\Model|null
     */
    public function model(array $row)
    {
        return new User([
            //
        ]);
    }

    public function sheets(): array
    {
        return ['0'];
    }

    public function collection(Collection $rows)
    {
    $sheet_data = $rows->toArray();
    }
}

Result

  Array
  (
 [0] => Array
    (
        [0] => Array
            (
                 [0] => 1
                 [1] => Image
                 [2] => View
                 [3] => 7.84
                 [4] => 7.87
            )

Expected

Array
(
[0] => Array
    (
        [0] => Array
            (
                 [0] => 1
                 [1] => https://google.com
                 [2] => https://laravel.com
                 [3] => 7.84
                 [4] => 7.87
             )

How do we replicate this same issue into our system ? could you please atleast share the screenshot of your excel sheet rows what are you uploading ? how are you getting that "Image" and "View" ? — Vipertecpro, Sep 10 '22 at 21:42
@Nileshpatel Your excel sheet and your expected output is different so i replace column D and E with `7.84` value — Vipertecpro, Oct 17 '22 at 12:50

Onur Uslu · Answer 1 · 2022-10-17T19:04:58.427

3

The solution is in a comment in your code.

$url = $cellPHPOffice->getHyperlink()->getUrl(); // Cell URL: works ONLY with excel.imports.read_only => false

Before calling the Excel facade, you must set the false value to the excel.imports.read_only config.

Controller:


config(['excel.imports.read_only' => false]);

$usersImport = new UsersImport();

Excel::import($usersImport, '*****REPLACE_HERE_WITH_FILE_PATH*****');

dd($usersImport->getCells());

UsersImport:

<?php

namespace App\Imports;

use Maatwebsite\Excel\Cell;
use Maatwebsite\Excel\Concerns\OnEachRow;
use Maatwebsite\Excel\Concerns\SkipsEmptyRows;
use Maatwebsite\Excel\Row;

class UsersImport implements OnEachRow, SkipsEmptyRows
{
    protected $cells = [];

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

    public function onRow(Row $row)
    {
        $cells = [];

        foreach ($row->getDelegate()->getCellIterator() as $cell) {
            $cellObj = new Cell($cell);
            $cellPHPOffice = $cellObj->getDelegate();

            if ($cellPHPOffice->hasHyperlink()) {
                $cells[] = $cellPHPOffice->getHyperlink()->getUrl();
            } else {
                $cells[] = $cellPHPOffice->getValue();
            }
        }

        $this->cells[] = $cells;
    }
}

Note: I used the "SkipsEmptyRows" interface. You can remove it if you want.

edited Oct 17 '22 at 19:04

answered Oct 14 '22 at 23:41

Onur Uslu

1,044
1
7
11

1

Yeap i just tested this and it's working fine just set to false and it works. – Vipertecpro Oct 15 '22 at 06:52
I just set same but it not working. – Nilesh patel Oct 15 '22 at 10:52
1

@Nileshpatel I forget the changing the facade call. The "onRow" method in `UsersImport` doesn't run with the `Excel::toArray(...)` call. The correct way is using the `Excel::import(...)` method. Can you try to call `Excel::import(...)` instead of `Excel::toArray(...)` in your controller? (Note: I updated the answer) – Onur Uslu Oct 15 '22 at 11:36
@OnurUslu is there any way to get result in array? – Nilesh patel Oct 17 '22 at 12:18
@Nileshpatel Yes, you can by storing the data in a field of the "UsersImport" object. You can look at my answer to see a sample. I updated my answer. – Onur Uslu Oct 17 '22 at 19:05
@OnurUslu is this affect on performance while reading 20000 row. – Nilesh patel Oct 18 '22 at 11:36
@Nileshpatel I didn't test it, but I reviewed the package's source code, and I think it doesn't cause a performance loss. – Onur Uslu Oct 18 '22 at 13:41
@OnurUslu import take massive time and it affect in performance. toArray is to much fast. is there any way to get link in toArray. – Nilesh patel Oct 20 '22 at 17:14
@Nileshpatel I made a benchmark test to see the difference in completion time between the "import and toArray" methods. The excel file that I use has approximately 26.000 rows and five columns. In each row, three cells have standard texts, and the other two have standard links. The completion time difference between the two is less than %5. I made the tests on Apple M1 Max processor. You can reach the benchmark codes from [here](https://hastebin.com/udowenomor.php). If the time difference is much more than %5 percent in your use case, the problem may not be related to the "import" method. – Onur Uslu Oct 22 '22 at 20:59

Vipertecpro · Answer 2 · 2022-10-17T12:47:57.693

This is how i achived it

config/excel.php

<?php

use Maatwebsite\Excel\Excel;

return [
    'exports' => [

        ....................

    'imports'            => [

        /*
        |--------------------------------------------------------------------------
        | Read Only
        |--------------------------------------------------------------------------
        |
        | When dealing with imports, you might only be interested in the
        | data that the sheet exists. By default we ignore all styles,
        | however if you want to do some logic based on style data
        | you can enable it by setting read_only to false.
        |
        */
        'read_only' => false,
        ....................
];

Run this command php artisan optimize

routes/api.php

Route::get('importUsers',function(Request $request){
    try {
        return Excel::import(new UsersImport(), 'users.xlsx', null,\Maatwebsite\Excel\Excel::XLSX);
    }catch (Exception $exception){
        dd($exception);
    }
})->name('importUsers');

Path : app/Imports/UsersImport.php

<?php
namespace App\Imports;

use Maatwebsite\Excel\Row;
use Maatwebsite\Excel\Concerns\OnEachRow;

class UsersImport implements OnEachRow
{

    public function onRow(Row $row)
    {
        $calculatedValue = [];
        foreach ($row->getCellIterator() as $cell) {
            if($cell->hasHyperlink() === true){
                $calculatedValue[] = $cell->getHyperlink()->getUrl();
            }else{
              $calculatedValue[] = $cell->getCalculatedValue();
            }
        }
        dd($calculatedValue);
        dd('STOP');
    }
}

public/users.xlsx

Output

import take massive time and it affect in performance. toArray is to much fast. is there any way to get link in toArray. — Nilesh patel, Oct 20 '22 at 13:18

Get hyperlink URL in Laravel Excel 3.1 using Excel::import

2 Answers2