Laravel-excel: 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);

[AlexMcDee]

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', ]], ]; }