How to insert clickable hyperlink in excel?

[vrajroham]

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

[patrickbrouwers]

Show us how you tried it please and please us the github issue template like automatically provided.

[vrajroham]

   `$candidate; //Collection object from model
    foreach ($candidates as $key => $model) {
        if($model->grade == 'Z') {
            $model->grade="Reject";
        }
        $model->access_token=url('static-access/'.$model->access_token);`

I am just replacing the access_token with complete URL. Then converting collection object to array as below,

    `foreach ($candidates as $candidate) {
        $candidatesArray[] = $candidate->toArray();
    }`

Then creating sheet from array as below,

   `$sheet=$excel->sheet('sheet1', function($sheet) use ($candidatesArray,$jd) {
            $sheet->fromArray($candidatesArray, null, 'A1', false, false);
    });`

from this script it actually exports data as required but hyperlink is exported as text format not as clickable link. @patrickbrouwers

[patrickbrouwers]

I think you have to do it like this with PHPExcel native methods:

$sheet->getCell('E26')
    ->getHyperlink()
    ->setUrl('http://examle.com/uploads/cv/' . $cellValue)
    ->setTooltip('Click here to access file');

https://github.com/PHPOffice/PHPExcel/blob/develop/Documentation/markdown/Overview/08-Recipes.md#change-a-cell-into-a-clickable-url

[JCarlosR]

Thank you @patrickbrouwers, but setTooltip is not working (empty cell).
At least for the version "maatwebsite/excel": "~2.1.0".

I am using setValue to show something as a text.

Additionally my text is an ID and it was interpreted as a numeric field (with E notation). To fix it I am using:

->setValueExplicit($fbId, PHPExcel_Cell_DataType::TYPE_STRING)

[patrickbrouwers]

setTooltip is the text that is shown if you hover over the cell. You indeed need to do setValue or setValueExplicit before using that.

[TimothyDLewis]

I spent far too long trying to figure out why links were not being generated in my Excel file. Turns out that $sheet->cell("A1") and $sheet->getCell("A1") are not the same. I had many instances where I was doing something like the following:

$sheet->cell($cellLetter.$rowIndex, $url)
->getHyperlink()
->setUrl($url);

Which wasn't working, but also wasn't generating any errors, such as Method [...] doesn't exist for .... Changing my code to:

$sheet->getCell($cellLetter.$rowIndex) // A1, B2
->setValueExplicit("Click Here", \PHPExcel_Cell_DataType::TYPE_STRING);

$sheet->getStyle($cellLetter.$rowIndex)
->applyFromArray($urlStyle); // Blue, Bold

$sheet->getCell($cellLetter.$rowIndex)
->getHyperlink()
->setUrl($url);

Solved my issue. Might help someone in the future with the same problem.

[herarya]

solve my issue, this my code

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

thanks bro @TimothyDLewis

[OliverGrimsley]

@TimothyDLewis or @herarya could I trouble you for how you are iterating over the sheet? - maybe just post a bit more code as to the complete method you used? (i.e. getting $cellLetter.$rowIndex or $cell? )

I am exporting a simple array that has 5 columns from a database, - this is one sheet and it has a header row. Once I grab it, I have this code which works of course:

//$trademark = [an array of data with 5 columns and a header row];
Excel::create('trademarks', function($excel) use($trademarks) {
  $excel->sheet('trademarks', function($sheet) use($trademarks) {
     $sheet->fromArray($trademarks,null,'A1',false,false);
     //need to iterate over $sheet, get each row, and find column 1 in that row and set a URL
})->export('xlsx');

I would like to iterate over $sheet, and change one cell in each row (other than the first row) with a URL. I have tried up to a 3 deep foreach loop (i keep getting an error that the result is an object, an array or I get "PHPExcel_Exception Invalid cell coordinate CREATOR in Cell.php (line 590)", and I tried $sheet->each (which apparently is a method available only in loading in a file as it err's that there is no method each).

[halechen]

setUrl("sheet://'test'!A1")
but the file link is sheet://test!A1
why？

[mouyong]

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);

[gr1zix]

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);

[mouyong]

You are right. this is a better solution then me. @AlexMcDee

[amirgee007]

@TimothyDLewis or @herarya could I trouble you for how you are iterating over the sheet? - maybe just post a bit more code as to the complete method you used? (i.e. getting $cellLetter.$rowIndex or $cell? )

I am exporting a simple array that has 5 columns from a database, - this is one sheet and it has a header row. Once I grab it, I have this code which works of course:

//$trademark = [an array of data with 5 columns and a header row];
Excel::create('trademarks', function($excel) use($trademarks) {
  $excel->sheet('trademarks', function($sheet) use($trademarks) {
     $sheet->fromArray($trademarks,null,'A1',false,false);
     //need to iterate over $sheet, get each row, and find column 1 in that row and set a URL
})->export('xlsx');

I would like to iterate over $sheet, and change one cell in each row (other than the first row) with a URL. I have tried up to a 3 deep foreach loop (i keep getting an error that the result is an object, an array or I get "PHPExcel_Exception Invalid cell coordinate CREATOR in Cell.php (line 590)", and I tried $sheet->each (which apparently is a method available only in loading in a file as it err's that there is no method each).

GOT THIS TODAY after findings....

https://stackoverflow.com/questions/41053636/laravel-excel-export-each-cell-style

[mr-1024]

how return error message collection module?

[MohannadNaj]

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.

[panayotisk]

Another approach, that I ended up following, is to use Excel's hyperlink function.
For example I wanted to create a hyperlinks from sheet2 to cells on sheet1 (range A1:A20) having the same text:
In my sheet2

public function array(): array
    {
        $data = [
            ['Id'],
        ];
        foreach ($this->data as $id) {
            $idformula = '=HYPERLINK("#"&CELL("address", INDEX(Sheet1!A1:A20, MATCH("'.$id.'",Sheet1!A1:A20,0), 1)),"'.$id.'")';
            array_push($data, [$idformula]);
        }
        return $data;
    }

and to style them:

public function styles(Worksheet $sheet)
{
        return [
            'A' => ['font' => [
                'color' => ['rgb' => '0000FF'],
                'underline' => 'single',
            ]],
        ];
    }

[HassanZahirnia]

Anyone has issues with http links not working in MS Excel's read-only (protected) mode ?
I click on the link and it throws an "Can't download the information you requested" error.
But if I use Enable Editing it works.
I also noticed the label/tooltip does not get created when you're in protected mode.

This behavior is the same with both Excel's built-in hyperlink and PhpSpreadsheet's HyperLink.

[tolgatasci]

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);

if you don't use external link

my solution

public function map($driver): array
    {

        return [
            'custom_url://'.$driver->id.'||'.$driver->name.'||'.$driver->surname,
            $driver->name,
            $driver->surname,
            $driver->identification_number,
            $driver->irsaliye_seri_no,
            $driver->change_permission
        ];
    }
public function registerEvents(): array
    {

        return [
            AfterSheet::class    => function(AfterSheet $event) {

            $data = [];
                foreach ($event->sheet->getColumnIterator('A','A') as $row) {

                    foreach ($row->getCellIterator() as $cell) {
             
                         if (str_contains($cell->getValue(), 'custom_url://')) {

                             $value = str_replace('custom_url://','',$cell->getValue());

                             $user = explode('||',$value);
                             $cell->setValue($user[0]);
                             $cell->setHyperlink(new Hyperlink('sheet://\''.$user[1].' '.$user[2].'\'!A1', $user[0]));

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

            },
        ];
    }

[mrbig]

After some googling around I finally settled with the following solution: I have created a custom ValueBinder class that can transform arrays to a cell with a hyperlink. I can then activate it in the excel.php by setting the value_binder.default config value.

This way I can have different text and hyperlink without the need of costly postprocessing.

Example:

use PhpOffice\PhpSpreadsheet\Cell\Cell;
use PhpOffice\PhpSpreadsheet\Cell\DefaultValueBinder;

/***
 * This custom value binder support link creation in Excel
 * You should return an array with the following structure:
 * [
 *    'text' => 'The text to display',
 *    'url' => 'The url to link to',
 * ]
 * And the cell will be displayed as a link
 */
class CustomValueBinder extends DefaultValueBinder
{
    /**
     * @param  Cell  $cell  Cell to bind value to
     * @param  mixed  $value  Value to bind in cell
     * @return bool
     */
    public function bindValue(Cell $cell, $value)
    {
        if (is_array($value)) {
            
            if (isset($value['text']) && isset($value['url'])) {
                parent::bindValue($cell, $value['text']);
                $cell->getHyperlink()->setUrl($value['url']);
                return true;
            }

            $value = \json_encode($value);
        }

        return parent::bindValue($cell, $value);
    }
}