SolvedLaravel Excel How to insert clickable hyperlink in excel?

I tried inserting hyperlink in sheet, but it is just in text format. How can I insert clickable Hyperlink?

18 Answers

✔️Accepted Answer

when use v3.*, u can like this to set hyper link.

<?php
namespace App\Exports;

use Excel;
use Maatwebsite\Excel\Sheet;
use Maatwebsite\Excel\Events\AfterSheet;
use Maatwebsite\Excel\Concerns\WithMapping;
use Maatwebsite\Excel\Concerns\WithHeadings;
use PhpOffice\PhpSpreadsheet\Cell\Hyperlink;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

class CommentsExport implements FromCollection, WithHeadings, WithMapping, ShouldAutoSize
{
    // ...
    public function custom()
    {
        Excel::extend(static::class, function (CommentsExport $export, Sheet $sheet) {
            /** @var Worksheet $sheet */
            foreach ($sheet->getColumnIterator('H') as $row) {
                foreach ($row->getCellIterator() as $cell) {
                    if (str_contains($cell->getValue(), '://')) {
                        $cell->setHyperlink(new Hyperlink($cell->getValue(), '点击查看图片'));
                    }
                }
            }
        }, AfterSheet::class);
    }
    // ...
}
(new CommentsExport)->custom());
Excel::download(CommentsExport::class);

Helpful solution, but you can do it more understandable with refactored

<?php
namespace App\Exports;

use Excel;
use Maatwebsite\Excel\Sheet;

// add event support
use Maatwebsite\Excel\Concerns\WithEvents;

use Maatwebsite\Excel\Events\AfterSheet;
use Maatwebsite\Excel\Concerns\WithMapping;
use Maatwebsite\Excel\Concerns\WithHeadings;
use PhpOffice\PhpSpreadsheet\Cell\Hyperlink;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

class CommentsExport implements FromCollection, WithHeadings, WithMapping, ShouldAutoSize, WithEvents
{
    // ...
    /**
     * @return array
     */
    public function registerEvents(): array
    {
        return [
            AfterSheet::class    => function(AfterSheet $event) {
                /** @var Worksheet $sheet */
                foreach ($event->sheet->getColumnIterator('H') as $row) {
                    foreach ($row->getCellIterator() as $cell) {
                        if (str_contains($cell->getValue(), '://')) {
                            $cell->setHyperlink(new Hyperlink($cell->getValue(), 'Read'));

                             // Upd: Link styling added
                             $event->sheet->getStyle($cell->getCoordinate())->applyFromArray([
                                'font' => [
                                    'color' => ['rgb' => '0000FF'],
                                    'underline' => 'single'
                                ]
                            ]);
                        }
                    }
                }
            },
        ];
    }
    // ...
}

Usage, just:

Excel::download(CommentsExport::class);

Other Answers:

Just a quick note for anyone coming here from google, Thank you @mouyong @AlexMcDee for the provided solution.

While using $sheet->getColumnIterator('H'), it's important to specify the second parameter, which is the end column $sheet->getColumnIterator('H', 'H').

Without the end column, the iterator will iterate over each column until the last column.

I noticed this when I wanted to make a hyperlink for a column in the middle of the sheet not the last column as usual.

when use v3.*, u can like this to set hyper link.

<?php
namespace App\Exports;

use Excel;
use Maatwebsite\Excel\Sheet;
use Maatwebsite\Excel\Events\AfterSheet;
use Maatwebsite\Excel\Concerns\WithMapping;
use Maatwebsite\Excel\Concerns\WithHeadings;
use PhpOffice\PhpSpreadsheet\Cell\Hyperlink;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

class CommentsExport implements FromCollection, WithHeadings, WithMapping, ShouldAutoSize
{
    // ...
    public function custom()
    {
        Excel::extend(static::class, function (CommentsExport $export, Sheet $sheet) {
            /** @var Worksheet $sheet */
            foreach ($sheet->getColumnIterator('H') as $row) {
                foreach ($row->getCellIterator() as $cell) {
                    if (str_contains($cell->getValue(), '://')) {
                        $cell->setHyperlink(new Hyperlink($cell->getValue(), '点击查看图片'));
                    }
                }
            }
        }, AfterSheet::class);
    }
    // ...
}
(new CommentsExport)->custom());
Excel::download(CommentsExport::class);

solve my issue, this my code

$sheet->getCell($cell) ->getHyperlink() ->setUrl($url); $sheet->getStyle($cell) ->applyFromArray(array( 'font' => array( 'color' => ['rgb' => '0000FF'], 'underline' => 'single' ) ));

screen shot 2017-07-19 at 2 47 40 pm

thanks bro @TimothyDLewis

More Issues: