magento 2 programmatically create Excel file with multiple sheets

Question

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

Answer 1

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