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