How create and download excel file in magento 2.3

Question

I need to create excel file at front-end and need to download bu there is no any reference please help with some solution.

Answer 1

Using this way you can get images in Excel file.:

<?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-&gt;_gridData = $gridData;
    $this-&gt;_adminSession = $adminSession;
    $this-&gt;resultPageFactory = $resultPageFactory;
    $this-&gt;_fileFactory = $fileFactory;
    $this-&gt;_directoryList = $directoryList;
    $this-&gt;_filesystem = $filesystem;
    $this-&gt;_collectionFactory = $collectionFactory;
    $this-&gt;_xlsx = $xlsx;
    $this-&gt;_productRepository = $productRepository;
    $this-&gt;_storeManager = $storeManager;
    $this-&gt;_productImageHelper = $productImageHelper;
    $this-&gt;_resultFactory = $resultFactory;
    DEFINE('DS', DIRECTORY_SEPARATOR);
}

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

      try {
        $websiteId = 0;
        $productIds = [];
        $resultRedirect = $this-&gt;_resultFactory-&gt;create(\Magento\Framework\Controller\ResultFactory::TYPE_REDIRECT);
        $dataArray = $this-&gt;_adminSession-&gt;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' =&gt; array('Product Image', 'Product ID', 'Product Name', 'Barcode', 'Retail Price', 'SKU', 'Store', 'Min QTY', 'Max QTY', 'Available QTY'));

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

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

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

    $sheet-&gt;setTitle('Excel File');
    $sheet-&gt;fromArray($excelData);
    $writer = $this-&gt;_xlsx-&gt;setSpreadsheet($spreadsheet);
    $writer-&gt;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-&gt;_adminSession-&gt;getData('ExportArray');
    if (count($arrayExport) &gt; 0) {
        foreach ($arrayExport as $value) {
            $data[] = $value['id'];
        }
        return $data;
    }
    return $data;
}

public function processDataForXlsxImage($store, $productIds) {
    $result = [];
    $mediaDirectory = $this-&gt;getMediaPath();
    foreach ($productIds as $productId) {
        $product = $this-&gt;_productRepository-&gt;getById($productId);
        $imageUrl = $this-&gt;_productImageHelper-&gt;init($product, 'product_page_image_thumbnail')-&gt;setImageFile($product-&gt;getThumbnail())-&gt;resize(60, 60)-&gt;getUrl();
        $result[] = $this-&gt;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-&gt;setReadDataOnly(true);
        $spreadsheet = $reader-&gt;load($xlsxFilePath);
        $cellNo = 1;
        foreach ($imageData as $image) {
            if ($cellNo &gt;= 1 &amp;&amp; $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-&gt;drawImage($gdImage, $xlsxFilePath, $spreadsheet, $inputFileType, $cellNo);
            }
            $cellNo++;
        }
    }
}

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

public function getPlaceholderImage() {
    return $this-&gt;_storeManager-&gt;getStore()-&gt;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-&gt;setActiveSheetIndex(0);
    $activeSheet = $spreadsheet-&gt;getActiveSheet();
    $objDrawing = new \PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing();
    $objDrawing-&gt;setImageResource($gdImage);
    $objDrawing-&gt;setRenderingFunction(\PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing::RENDERING_PNG);
    $objDrawing-&gt;setMimeType(\PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing::MIMETYPE_DEFAULT);
    $objDrawing-&gt;setCoordinates('A' . $cellNo);
    $objDrawing-&gt;setOffsetX(0);
    $objDrawing-&gt;setOffsetY(0);
    $objDrawing-&gt;setHeight(96);
    $objDrawing-&gt;setWidth(96);
    if (empty((array) $objDrawing-&gt;getWorksheet())) {
        $objDrawing-&gt;setWorksheet($activeSheet);
    }
    $activeSheet-&gt;getRowDimension($cellNo)-&gt;setRowHeight(72);
    $activeSheet-&gt;getColumnDimension('A')-&gt;setWidth(13.18);
    $activeSheet-&gt;getStyle('B1:J' . $spreadsheet-&gt;setActiveSheetIndex(0)-&gt;getHighestRow())-&gt;getAlignment()-&gt;setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_TOP);
    $activeSheet-&gt;getStyle('B1:J' . $spreadsheet-&gt;setActiveSheetIndex(0)-&gt;getHighestRow())-&gt;getAlignment()-&gt;setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT);
    $activeSheet-&gt;getColumnDimension('B')-&gt;setAutoSize(TRUE);
    foreach (range('C', 'J') as $columnID) {
        $activeSheet-&gt;getColumnDimension($columnID)-&gt;setAutoSize(false);
        $activeSheet-&gt;getColumnDimension($columnID)-&gt;setWidth(15);
        $activeSheet-&gt;getStyle('B1:J' . $spreadsheet-&gt;setActiveSheetIndex(0)-&gt;getHighestRow())-&gt;getAlignment()-&gt;setWrapText(true);
    }

    $writer-&gt;save($filePath);
}

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


}