Export grid with images - YII2 gridview widget

Question

I am exporting the data in Yii2 using ExportMenu widget of Kartik.

When I export the xls, I can see the rows but without images, the images are shown via <img> tag and the images are in one of the upload directories.

This is how my table looks like:

That's how The export code is written:

   echo \kartik\export\ExportMenu::widget([
        'dataProvider' => $dataProvider,
        'columns' =>$exportColumns
    ]);

Answer 1

The widget does not export images directly, but exposes the instance of Spreadsheet. Through this object you can access the sheet and attach the images.

So you can achieve this in various ways; the simplest and cleanest is to use the onRenderDataCell parameter, in the widget configuration array. This accepts an anonymous function that receives the following parameters:

function(PhpOffice\PHP\Spreadsheet\Cell $cell, $content, $model, $key, $index, kartik\export\Export\Menu $widget) {   }

and is invoked for each cell added into the XLS.

For each one, check if it is an image: with a regular expression, the img tag, the existence of the file, the extension, the mime-type, etc.

Using the $cell object, you can add the images to specific cells. Below is a complete example:

<?php

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;


use kartik\export\ExportMenu;
use kartik\grid\GridView;
use yii\data\ArrayDataProvider;
use yii\helpers\Html;

// example data
$dataProvider = new ArrayDataProvider([
    'allModels' => [
        [
            'id' => 1,
            'name' => "Mario",
            'image' => 'img/mario.jpeg',
        ],
        [
            'id' => 2,
            'name' => "Luigi",
            'image' => 'img/luigi.jpg',
        ],
    ]
]);

// grid columns
$gridColumns = [
    'id',
    'name',
    [
        'attribute' => 'image',
        'content' => function($model) {
            return Html::img($model['image']);
        },
        'value' => function($model) {
            return $model['image'];
        },
        'format' => 'raw'
    ]
];

// export menu
echo ExportMenu::widget([
        'dataProvider' => $dataProvider,
        'columns' => $gridColumns,
        'onRenderDataCell' => function(PhpOffice\PhpSpreadsheet\Cell\Cell $cell, $content, $model, $key, $index, kartik\export\ExportMenu $widget) {
            $column = $cell->getColumn();
            $columnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($column) - 1;
            $value = array_values($model)[$columnIndex];
            if(file_exists($value)) {   // change the condition as you prefer
                /* @var PhpOffice\PhpSpreadsheet\Worksheet\Worksheet $worksheet */
                $firstRow = 2;  // skip header row
                $imageName = "Image name";      // Add a name
                $imageDescription = "Image description";    // Add a description
                $padding = 5;
                $imageWidth = 60;   // Image width
                $imageHeight = 60;  // Image height
                $cellID = $column . ($index + $firstRow);   // Get cell identifier
                $worksheet = $cell->getWorksheet();
                $worksheet->getRowDimension($index + $firstRow)->setRowHeight($imageHeight + ($padding * 2));
                //$worksheet->getColumnDimension($column)->setAutoSize(false);
                //$worksheet->getColumnDimension($column)->setWidth($imageWidth + ($padding * 2));
                $drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
                $drawing->setName($imageName);
                $drawing->setDescription($imageDescription);
                $drawing->setPath($value); // put your path and image here
                $drawing->setCoordinates($cellID);
                //$drawing->setOffsetX(200);
                $drawing->setWidth($imageWidth);
                $drawing->setHeight($imageHeight);
                //$drawing->setWidthAndHeight($imageWidth, $imageHeight);
                $drawing->setWorksheet($worksheet);

            }
        },
        'dropdownOptions' => [
            'label' => 'Export All',
            'class' => 'btn btn-secondary'
        ]
    ]) . "<hr>\n";

// grid view
echo GridView::widget([
    'dataProvider' => $dataProvider,
    'columns' => $gridColumns,
]);

Alternatively you can extend the ExportMenu class and use a similar technique (with the Spreadsheet object) and guarantee native support for exporting images (you will need to maintain the class in case of updates).

RESULT