How to export data from Magento 2 in XLS & XLSX formats

<?php

/**

* Copyright © 2013-2017 Magento, Inc. All rights reserved.

* See COPYING.txt for license details.

*/

namespace NamespaceModuleModelExport;

use MagentoUiModelExportMetadataProvider;

use MagentoUiModelExportSearchResultIteratorFactory;

use MagentoFrameworkApiSearchDocumentInterface;

use MagentoFrameworkApiSearchSearchResultInterface;

use MagentoFrameworkAppFilesystemDirectoryList;

use MagentoFrameworkConvertExcel;

use MagentoFrameworkConvertExcelFactory;

use MagentoFrameworkExceptionLocalizedException;

use MagentoFrameworkFilesystem;

use MagentoFrameworkFilesystemDirectoryWriteInterface;

use MagentoUiComponentMassActionFilter;

/**

* Class ConvertToXls

*/

class ConvertToXls

{

    /**

     * @var WriteInterface

     */

    protected $directory;

    /**

     * @var MetadataProvider

     */

    protected $metadataProvider;

    /**

     * @var ExcelFactory

     */

    protected $excelFactory;

    /**

     * @var array

     */

    protected $options;

    /**

     * @var SearchResultIteratorFactory

     */

    protected $iteratorFactory;

    /**

     * @var array

     */

    protected $fields;

    /**

     * @param Filesystem $filesystem

     * @param Filter $filter

     * @param MetadataProvider $metadataProvider

     * @param ExcelFactory $excelFactory

     * @param SearchResultIteratorFactory $iteratorFactory

     */

    public function __construct(

        Filesystem $filesystem,

        Filter $filter,

        MetadataProvider $metadataProvider,

        ExcelFactory $excelFactory,

        SearchResultIteratorFactory $iteratorFactory

    ) {

        $this->filter = $filter;

        $this->directory = $filesystem->getDirectoryWrite(DirectoryList::VAR_DIR);

        $this->metadataProvider = $metadataProvider;

        $this->excelFactory = $excelFactory;

        $this->iteratorFactory = $iteratorFactory;

    }

    /**

     * Returns Filters with options

     *

     * @return array

     */

    protected function getOptions()

    {

        if (!$this->options) {

            $this->options = $this->metadataProvider->getOptions();

        }

        return $this->options;

    }

    /**

     * Returns DB fields list

     *

     * @return array

     */

    protected function getFields()

    {

        if (!$this->fields) {

            $component = $this->filter->getComponent();

            $this->fields = $this->metadataProvider->getFields($component);

        }

        return $this->fields;

    }

    /**

     * Returns row data

     *

     * @param DocumentInterface $document

     * @return array

     */

    public function getRowData(DocumentInterface $document)

    {

        return $this->metadataProvider->getRowData($document, $this->getFields(), $this->getOptions());

    }

    /**

     * Returns XML file

     *

     * @return array

     * @throws LocalizedException

     */

    public function getXlsFile()

    {

        $component = $this->filter->getComponent();

        $name = md5(microtime());

        $file = ‘export/’. $component->getName() . $name . ‘.xls’;

        $this->filter->prepareComponent($component);

        $this->filter->applySelectionOnTargetProvider();

        $component->getContext()->getDataProvider()->setLimit(0, 0);

        /** @var SearchResultInterface $searchResult */

        $searchResult = $component->getContext()->getDataProvider()->getSearchResult();

        /** @var DocumentInterface[] $searchResultItems */

        $searchResultItems = $searchResult->getItems();

        $this->prepareItems($component->getName(), $searchResultItems);

        /** @var SearchResultIterator $searchResultIterator */

        $searchResultIterator = $this->iteratorFactory->create([‘items’ => $searchResultItems]);

        /** @var Excel $excel */

        $excel = $this->excelFactory->create([

            ‘iterator’ => $searchResultIterator,

            ‘rowCallback’=> [$this, ‘getRowData’],

        ]);

        $this->directory->create(‘export’);

        $stream = $this->directory->openFile($file, ‘w+’);

        $stream->lock();

        $excel->setDataHeader($this->metadataProvider->getHeaders($component));

        $excel->write($stream, $component->getName() . ‘.xls’);

        $stream->unlock();

        $stream->close();

        return [

            ‘type’ => ‘filename’,

            ‘value’ => $file,

            ‘rm’ => true  // can delete file after use

        ];

    }

    /**

     * @param string $componentName

     * @param array $items

     * @return void

     */

    protected function prepareItems($componentName, array $items = [])

    {

        foreach ($items as $document) {

            $this->metadataProvider->convertDate($document, $componentName);

        }

    }

}