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->_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(60, 60)->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);
    }

}