-1

I am creating a custom admin grid (using Bock not UI Component) with custom collection. In that grid, I have a column of product image.

Please see below screenshot:

enter image description here

There are two way to export grid data(Excel and CSV), When I export the grid data into excel format(file.xlsx), It's showing whole image tag(like: <img src="http://127.0.0.1/poserp/pub/media/catalog/product/1/2/124.png" width="67" height = "67" />) in product image column.

Please see below screenshot:

enter image description here

Here, I want to display real image in exported Excel file only as it's shown below screenshot:

enter image description here

Here I am using default magento excel functionality for exporting the grid data into excel format in my controller file(exportExcel.php).

public function execute() {
    $fileName = 'file.xlsx';

    $content = $this->_view->getLayout()->createBlock(
                    \Vendorname\Modulename\Block\Adminhtml\Report\Grid::class
            )->getExcelFile();

    return $this->fileFactory->create($fileName, $content, DirectoryList::VAR_DIR);
 }

Please give me an idea that how can I achieve it. Any help will be appreciated.

  • If you use default product import function then got proper image in product grid page. – Soundararajan m Aug 1 at 13:19
  • @Soundararajanm I am asking about export the data not import. – Faisal Sheikh Aug 1 at 13:22
1

I think you need to override vendor/magento/module-backend/Block/Widget/Grid/Export.php this File and check getExcelFile function

And also see this exmple for adding image in Excel File

Adding Image to the Excel

  • After implementing this code, images are still not showing in excel file. – Faisal Sheikh Aug 1 at 13:40
  • please check answer – Rk Rathod Aug 1 at 13:59
  • Now I am able to get image in exported excel file. Thank you for the suggestion. – Faisal Sheikh Aug 2 at 12:40
  • if you dont mind please attach code.. most welcome.. happy coding.. :) – Rk Rathod Aug 2 at 12:54
  • I have posted, you can see now. – Faisal Sheikh Aug 2 at 13:18
-1

Finally, I got the image in my exported Excel file. Here is my exportExcel.php (Controller) code:

<?php

namespace Vendorname\Modulename\Controller\Adminhtml\Report;

use Magento\Framework\App\Filesystem\DirectoryList;

class exportExcel extends \Magento\Backend\App\Action {

    /**
     * @var \Magento\Backend\Model\Session
     */
    protected $_adminSession;

    /**
     * @var \Vendorname\Modulename\Block\Adminhtml\Report\Grid
     */
    protected $_gridData;
    protected $resultPageFactory;
    protected $fileFactory;
    protected $_resultFactory;
    protected $_xlsx;
    protected $resultPage;


    /**
     * 
     * @param \Magento\Backend\App\Action\Context $context
     * @param \Magento\Framework\View\Result\PageFactory $resultPageFactory
     */
    public function __construct(
    \Magento\Backend\App\Action\Context $context, 
            \Vendorname\Modulename\Block\Adminhtml\Report\Grid $gridData, 
            \Magento\Backend\Model\Session $adminSession, 
            \Magento\Framework\View\Result\PageFactory $resultPageFactory,
            \Magento\Framework\Controller\ResultFactory $resultFactory, 
            \Magento\Framework\App\Response\Http\FileFactory $fileFactory, 
            \Magento\Framework\Filesystem\DirectoryList $directoryList, 
            \Magento\Framework\Filesystem $filesystem, 
            \Magento\Framework\Data\CollectionFactory $collectionFactory,
            \PhpOffice\PhpSpreadsheet\Writer\Xlsx $xlsx, 
            \Magento\Catalog\Model\ProductRepository $productRepository, 
            \Magento\Store\Model\StoreManagerInterface $storeManager, 
            \Magento\Catalog\Helper\Image $productImageHelper
    ) {
        parent::__construct($context);
        $this->_gridData = $gridData;
        $this->_adminSession = $adminSession;
        $this->resultPageFactory = $resultPageFactory;
        $this->_fileFactory = $fileFactory;
        $this->_directoryList = $directoryList;
        $this->_filesystem = $filesystem;
        $this->_collectionFactory = $collectionFactory;
        $this->_xlsx = $xlsx;
        $this->_productRepository = $productRepository;
        $this->_storeManager = $storeManager;
        $this->_productImageHelper = $productImageHelper;
        $this->_resultFactory = $resultFactory;
        DEFINE('DS', DIRECTORY_SEPARATOR);
    }

    /**
     * 
     * @return type
     */
    public function execute() {

          try {
            $websiteId = 0;
            $productIds = [];
            $resultRedirect = $this->_resultFactory->create(\Magento\Framework\Controller\ResultFactory::TYPE_REDIRECT);
            $dataArray = $this->_adminSession->getData('ExportArray');
            $totalData = [];
            $i = 0;
            foreach($dataArray as $item){

                $totalData[$i]['image'] = '';
                $totalData[$i]['id'] = $item['id'];
                $totalData[$i]['name'] = $item['name'];
                $totalData[$i]['barcode'] = $item['barcode'];
                $totalData[$i]['retailprice'] = $item['retailprice'];
                $totalData[$i]['sku'] = $item['sku'];
                $totalData[$i]['store'] = $item['store'];
                $totalData[$i]['minqty'] = $item['minqty'];
                $totalData[$i]['maxqty'] = $item['maxqty'];
                $totalData[$i]['availqty'] = $item['availqty'];
                $i++;
            }

            $headerColumns = array('0' => array('Product Image', 'Product ID', 'Product Name', 'Barcode', 'Retail Price', 'SKU', 'Store', 'Min QTY', 'Max QTY', 'Available QTY'));

            if (count($lowStockExportArray) == 0) {
                $this->messageManager->addNoticeMessage(__('There is no data to export!'));
                $resultRedirect->setUrl($this->_redirect->getRefererUrl());
                return $resultRedirect;
            }
            $excelData = array_merge($headerColumns, $totalData);
            $productIds = $this->getProductIds();
            $imageData = $this->processDataForXlsxImage($websiteId, $productIds);
            $xlsxFileName = 'file.xlsx';
            $xlsxFilePath = $this->getFilePath($xlsxFileName);
            $this->generateXlsx($excelData, $xlsxFilePath);
            $this->addImageToXlsx($imageData, $xlsxFilePath);            
            $this->downloadXlsx($xlsxFilePath);
        } catch (\Exception $ex) {
            $this->messageManager->addErrorMessage($ex->getMessage());
        }
    }

    public function getFilePath($fileName) {
        return $this->_directoryList->getPath(\Magento\Framework\App\Filesystem\DirectoryList::VAR_DIR) . "/folder_name/" . $fileName;
    }

    public function generateXlsx($excelData, $filePath) {
        $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        $sheet->setTitle('Excel File');
        $sheet->fromArray($excelData);
        $writer = $this->_xlsx->setSpreadsheet($spreadsheet);
        $writer->save($filePath);
    }

    public function downloadXlsx($xlsxFilePath) {
        if (file_exists($xlsxFilePath)) {
            header('Content-Description: File Transfer');
            header('Content-Type: application/vnd.ms-excel');
            header('Content-Disposition: attachment; filename=' . basename($xlsxFilePath));
            header('Expires: 0');
            header('Cache-Control: must-revalidate');
            header('Pragma: public');
            header('Content-Length: ' . filesize($xlsxFilePath));
            ob_clean();
            flush();
            readfile($xlsxFilePath);
        }
    }

    public function getProductIds() {
        $data = [];
        $arrayExport = $this->_adminSession->getData('ExportArray');
        if (count($arrayExport) > 0) {
            foreach ($arrayExport as $value) {
                $data[] = $value['id'];
            }
            return $data;
        }
        return $data;
    }

    public function processDataForXlsxImage($store, $productIds) {
        $result = [];
        $mediaDirectory = $this->getMediaPath();
        foreach ($productIds as $productId) {
            $product = $this->_productRepository->getById($productId);
            $imageUrl = $this->_productImageHelper->init($product, 'product_page_image_thumbnail')->setImageFile($product->getThumbnail())->resize(70, 70)->getUrl();
            $result[] = $this->getImagePath($imageUrl, $mediaDirectory);
        }
        return $result;
    }

    public function addImageToXlsx($imageData, $xlsxFilePath) {
        if (count($imageData)) {
            $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReaderForFile($xlsxFilePath);
            $inputFileType = \PhpOffice\PhpSpreadsheet\IOFactory::identify($xlsxFilePath);
            $reader->setReadDataOnly(true);
            $spreadsheet = $reader->load($xlsxFilePath);
            $cellNo = 1;
            foreach ($imageData as $image) {
                if ($cellNo >= 1 && $image != '') {
                    $extension = pathinfo($image, PATHINFO_EXTENSION);
                    if ($extension == 'png') {
                        $gdImage = imagecreatefrompng($image);
                    }
                    if ($extension == 'jpg' || $extension == 'jpeg') {
                        $gdImage = imagecreatefromjpeg($image);
                    }
                    if ($extension == 'gif') {
                        $gdImage = imagecreatefromgif($image);
                    }
                    $this->drawImage($gdImage, $xlsxFilePath, $spreadsheet, $inputFileType, $cellNo);
                }
                $cellNo++;
            }
        }
    }

    public function getImagePath($imageUrl, $mediaDirectory) {
        if ($imageUrl != '') {
            if ($this->fileExists($imageUrl)) {
                return $imageUrl;
            } else {
                return $mediaDirectory . 'catalog/product/placeholder/' . $this->getPlaceholderImage();
            }
        }
        return '';
    }

    public function getPlaceholderImage() {
        return $this->_storeManager->getStore()->getConfig('catalog/placeholder/image_placeholder');
    }

    public function fileExists($url) {
        $ch = curl_init($url);
        curl_setopt($ch, CURLOPT_NOBODY, true);
        curl_exec($ch);
        $code = curl_getinfo($ch, CURLINFO_HTTP_CODE);

        if ($code == 200) {
            $status = true;
        } else {
            $status = false;
        }
        curl_close($ch);
        return $status;
    }

    public function drawImage($gdImage, $filePath, $spreadsheet, $inputFileType, $cellNo) {
        $cellNo = $cellNo + 1;
        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, $inputFileType);
        $spreadsheet->setActiveSheetIndex(0);
        $activeSheet = $spreadsheet->getActiveSheet();
        $objDrawing = new \PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing();
        $objDrawing->setImageResource($gdImage);
        $objDrawing->setRenderingFunction(\PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing::RENDERING_PNG);
        $objDrawing->setMimeType(\PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing::MIMETYPE_DEFAULT);
        $objDrawing->setCoordinates('A' . $cellNo);
        $objDrawing->setOffsetX(0);
        $objDrawing->setOffsetY(0);
        $objDrawing->setHeight(96);
        $objDrawing->setWidth(96);
        if (empty((array) $objDrawing->getWorksheet())) {
            $objDrawing->setWorksheet($activeSheet);
        }
        $activeSheet->getRowDimension($cellNo)->setRowHeight(72);
        $activeSheet->getColumnDimension('A')->setWidth(13.18);
        $activeSheet->getStyle('B1:J' . $spreadsheet->setActiveSheetIndex(0)->getHighestRow())->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_TOP);
        $activeSheet->getStyle('B1:J' . $spreadsheet->setActiveSheetIndex(0)->getHighestRow())->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT);
        $activeSheet->getColumnDimension('B')->setAutoSize(TRUE);
        foreach (range('C', 'J') as $columnID) {
            $activeSheet->getColumnDimension($columnID)->setAutoSize(false);
            $activeSheet->getColumnDimension($columnID)->setWidth(15);
            $activeSheet->getStyle('B1:J' . $spreadsheet->setActiveSheetIndex(0)->getHighestRow())->getAlignment()->setWrapText(true);
        }

        $writer->save($filePath);
    }

    public function getMediaPath() {
        return $this->_storeManager->getStore()->getBaseUrl(\Magento\Framework\UrlInterface::URL_TYPE_MEDIA);
    }

}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.