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.

• Deep Joshi: You can follow this answer to create and export file in excel format. magento.stackexchange.com/questions/284088/…
• Sabareesh: @DeepJoshi It achieve the backend can i need to work in frontend
• Msquare: for download try this link magento.stackexchange.com/a/291211
• Msquare: also check this link magento.stackexchange.com/a/291799

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

• Sabareesh: @jyjmhar123 i need to download in frontend for orders bro