3

In my custom module I am able to save some data into an excel (xls) file by using magento Excel class:

$convert = new \Magento\Framework\Convert\Excel(new \ArrayIterator($lines));
$convert->setDataHeader($headers);
$convert->write($stream, $sheetName);

(see https://mage2.pro/t/topic/823 for further reference)

However, I'm not able to add more than one sheet in my xls file. Is there a way to create the xls with multiple sheets?

Thanks

4
  • I am not able to generate XLS file with the above so could you please share the whole code.
    – Rana
    Commented Nov 5, 2020 at 13:02
  • @Rana hi sorry for not replying to you sooner.. have you followed the link I placed in my post? That's should give you the whole code. That's the post I followed too and it worked for me
    – Lou Nik
    Commented Dec 17, 2020 at 9:47
  • Did you able to create multiple sheet @Lou Nik?
    – Haritha
    Commented May 21, 2021 at 8:56
  • @Haritha hi, sorry I couldn't find a solution
    – Lou Nik
    Commented May 24, 2021 at 8:21

1 Answer 1

2

Magento 2 has a provision to Export the data in Excel and CSV files. However, there is no option to create multiple sheets with the excel files and export the same. Let us see how to create multiple sheets with the Excel sheet and export it as a file.

Step 1: Create a Model file which extends the default magento \Magento\Framework\Convert\Excel class for implementation of multiple sheets

<?php
declare(strict_types=1);

namespace Vendor\File\Model\Framework;

/**
 * Excel file with multiple sheets
 *
 * Class Excel
 * @package Vendor\File\Model\Framework
 */
class Excel extends \Magento\Framework\Convert\Excel
{
    /**
     * Work Sheet
     *
     * @var string
     */
    protected $_dataWorkSheet;

    /**
     * Iterator data Array
     *
     * @var array
     */
    protected $_iteratorData;

    /**
     * Retrieve Excel XML Document Header XML Fragment
     *
     * @return string
     */
    protected function _getHeaderXml()
    {
        if($this->_dataWorkSheet) {
            $xmlHeader = '<' .
                '?xml version="1.0"?' .
                '><' .
                '?mso-application progid="Excel.Sheet"?' .
                '><Workbook' .
                ' xmlns="urn:schemas-microsoft-com:office:spreadsheet"' .
                ' xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"' .
                ' xmlns:x="urn:schemas-microsoft-com:office:excel"' .
                ' xmlns:x2="http://schemas.microsoft.com/office/excel/2003/xml"' .
                ' xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"' .
                ' xmlns:o="urn:schemas-microsoft-com:office:office"' .
                ' xmlns:html="http://www.w3.org/TR/REC-html40"' .
                ' xmlns:c="urn:schemas-microsoft-com:office:component:spreadsheet">' .
                '<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">' .
                '</OfficeDocumentSettings>' .
                '<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">' .
                '</ExcelWorkbook>';
            $xmlHeader .= $this->_dataWorkSheet;
            $xmlHeader .= '</Workbook>';
        } else {
            $xmlHeader = '';
        }
        return $xmlHeader;
    }

    /**
     * Set Multiple Work Book
     *
     * @param string $sheetName
     * @return string
     */
    protected function _getMultipleWorkBook($sheetName = '')
    {
        if (empty($sheetName)) {
            $sheetName = 'Sheet 1';
        }

        $this->_dataWorkSheet .= '<Worksheet ss:Name="' . $sheetName . '">' . '<Table>';

        if ($this->_dataHeader) {
            $this->_dataWorkSheet .= $this->_getXmlRow($this->_dataHeader, false);
        }
        if($this->_iteratorData) {
            foreach ($this->_iteratorData as $dataRow) {
                $this->_dataWorkSheet .= $this->_getXmlRow($dataRow, true);
            }
        }
        if ($this->_dataFooter) {
            $this->_dataWorkSheet .= $this->_getXmlRow($this->_dataFooter, false);
        }
        $this->_dataWorkSheet .= '</Table></Worksheet>';

        return $this->_dataWorkSheet;
    }

    /**
     * Set Data Header
     *
     * @param array $data
     * @return void
     */
    public function setIteratorData($data)
    {
        $this->_iteratorData = $data;
    }

    /**
     * Convert Data to Excel XML Document
     *
     * @return string
     */
    public function convertToMultipleSheets()
    {
        return $this->_getHeaderXml();
    }

    /**
     * Set the work sheet
     *
     * @param $sheetName
     * @return string
     */
    public function convertWorkSheet($sheetName)
    {
        return $this->_getMultipleWorkBook($sheetName);
    }
}

Step 2: Create a Controller for the Export and add the following code. Inject the class \Vendor\File\Model\Framework\Excel. Based on the requirement, change the dataprovider accordingly. Here I have used the Magento Order data as an example.

<?php
declare(strict_types=1);

namespace Vendor\Module\Controller\Order\History;

use Magento\Framework\App\Action\HttpGetActionInterface;
use Magento\Framework\App\Filesystem\DirectoryList;
use Magento\Framework\App\Response\Http\FileFactory;
use Magento\Framework\App\Response\RedirectInterface;
use Magento\Framework\App\ResponseInterface;
use Magento\Framework\Controller\ResultFactory;
use Magento\Framework\Controller\ResultInterface;
use Magento\Framework\Exception\FileSystemException;
use Magento\Framework\Exception\LocalizedException;
use Magento\Framework\Filesystem;
use Magento\Framework\Message\ManagerInterface;
use Magento\Framework\View\Result\Layout;
use Magento\Sales\Block\Order\History;
use Vendor\File\Model\Framework\ExcelFactory;
use Psr\Log\LoggerInterface;

/**
 * Class Export
 * @package Vendor\Module\Controller\Order\History
 */
class Export implements HttpGetActionInterface
{
    /**
     * @var FileFactory
     */
    private $fileFactory;

    /**
     * @var DirectoryList
     */
    private $directory;

    /**
     * @var History
     */
    private $orderHistory;

    /**
     * @var LoggerInterface
     */
    private $logger;

    /**
     * @var ManagerInterface
     */
    private $messageManager;

    /**
     * @var ResultFactory
     */
    private $resultFactory;
    /**
     * @var RedirectInterface
     */
    private $redirect;
    /**
     * @var ExcelFactory
     */
    private $excelProcessor;

    /**
     * Export constructor.
     *
     * @param FileFactory $fileFactory
     * @param Filesystem $filesystem
     * @param History $orderHistory
     * @param LoggerInterface $logger
     * @param ManagerInterface $messageManager
     * @param ResultFactory $resultFactory
     * @param RedirectInterface $redirect
     * @param ExcelFactory $excelProcessor
     * @throws FileSystemException
     */
    public function __construct(
        FileFactory $fileFactory,
        Filesystem $filesystem,
        History $orderHistory,
        LoggerInterface $logger,
        ManagerInterface $messageManager,
        ResultFactory $resultFactory,
        RedirectInterface $redirect,
        ExcelFactory $excelProcessor
    ) {
        $this->fileFactory = $fileFactory;
        $this->directory = $filesystem->getDirectoryWrite(DirectoryList::VAR_DIR);
        $this->orderHistory = $orderHistory;
        $this->logger = $logger;
        $this->messageManager = $messageManager;
        $this->resultFactory = $resultFactory;
        $this->redirect = $redirect;
        $this->excelProcessor = $excelProcessor;
    }

    /**
     * Process and export the customer order
     *
     * @return ResponseInterface|ResultInterface|Layout
     */
    public function execute()
    {
        try {
            return $this->fileFactory->create('Orders.xls', $this->getOrderExcelFile(), 'var');
        } catch (FileSystemException $e) {
            $this->messageManager->addErrorMessage(__('The contents from the export.csv file can\'t be read. Please try again later'));
            $this->logger->info($e->getMessage());
        } catch (LocalizedException $e) {
            $this->messageManager->addErrorMessage(__('Something went wrong. Please try again later'));
            $this->logger->info($e->getMessage());
        } catch (\Exception $e) {
            $this->messageManager->addErrorMessage(__('Something went wrong. Please try again later'));
            $this->logger->info($e->getMessage());
        }
        $resultRedirect = $this->resultFactory->create(ResultFactory::TYPE_REDIRECT);
        $resultRedirect->setPath($this->redirect->getRefererUrl());

        return $resultRedirect;
    }

    /**
     * Prepare data to export with Excel
     *
     * @return array
     * @throws FileSystemException
     * @throws LocalizedException
     */
    public function getOrderExcelFile()
    {
        $orderHeaders = ['ORDER NO', 'ORDERED DATE'];

        $orderProductHeaders = ['NAME', 'SKU'];

        $dataProvider = $this->orderHistory->getOrders();

        if ($dataProvider && count($dataProvider)) {
            list($orderData, $orderProductData) = $this->populateOrderData($dataProvider);
            $convert = $this->excelProcessor->create(
                [
                    'iterator' => new \ArrayIterator([])
                ]
            );

            $name = $this->randomString(10);
            $file = 'export/order' . $name . '.csv';

            $this->directory->create('export');
            $stream = $this->directory->openFile($file, 'w+');
            $stream->lock();

            // SHEET 1
            $convert->setDataHeader($orderHeaders);
            $convert->setIteratorData($orderData);
            $convert->convertWorkSheet('Order Data');

            // SHEET 2
            $convert->setDataHeader($orderProductHeaders);
            $convert->setIteratorData($orderProductData);
            $convert->convertWorkSheet('Order Items');

            // Convert the sheets to multiple
            $content = $convert->convertToMultipleSheets();
            $stream->write($content);
            $stream->unlock();
            $stream->close();

            return [
                'type' => 'filename',
                'value' => $file,
                'rm' => true  // can delete file after use
            ];
        } else {
            throw new LocalizedException(__('No order data to export.'));
        }
    }

    /**
     * Populate the order data
     *
     * @param $dataProvider
     * @return array[]
     */
    public function populateOrderData($dataProvider)
    {
        $orderData = $orderProductData = [];
        foreach ($dataProvider as $order) {
            $orderData[] = [
                $order->getRealOrderId(),
                $order->getCreatedAt()
            ];
            $items = $order->getItems();
            if (!empty($items)) {
                foreach ($items as $key => $item) {
                    $orderProductData[] = [
                        $item->getName(),
                        $item->getSku()
                    ];
                }
            }
        }

        return [$orderData, $orderProductData];
    }

    /**
     * Generate random string in php for file name
     * @param $length
     * @return string
     */
    public function randomString($length)
    {
        $key = '';
        $keys = array_merge(range(0, 9), range('a', 'z'));

        for ($i = 0; $i < $length; $i++) {
            $key .= $keys[array_rand($keys)];
        }

        return $key;
    }
}
1
  • 1
    Thank you so much for this!
    – Lou Nik
    Commented Jun 10, 2021 at 8:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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