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

July 1, 2021 - E-Commerce, Magento 2

Magento 2 export CLI

The default import and export capabilities of Magento 2 are fairly limited. Depending on the platform’s editions, they vary a little bit but neither of the existing versions lets you work with XLS and XLSX files by default. However, you can implement the missing functionality with a few lines of code or a third-party solution.

In the following article, we depict the background of XLSX and XLS files and try to figure out what’s wrong with Magento 2 regarding them. You will find the existing solution to the problem posted on StackExchange. Since it is quite complex and not suitable for most merchants, we also provide a more user-friendly way of exporting data from Magento 2 in XLS & XLSX formats.

Recently, we’ve come across a question by user00247 where they ask how to export data from Magento 2 in an XLS format. Another StackExchange user proposes a solution that is provided below. Since it is aimed at technical users only, we’ve decided to elaborate the provided answer with an alternative, shedding light on how to export data from Magento 2 in XLS & XLSX formats for non-technical users. You will discover the difference between the two approaches and pick the one that is more suitable for you.

Bear in mind that we can help you with any import and export tasks that take place in Magento 2. Contact us for further information.

Table of contents

1 Understanding xls and xlsx
2 How to export XLS files from Magento 2
3 How to export XLSX files from Magento 2
4 Magento 2 XLSX Export FAQ

Understanding xls and xlsx

XLS is a standard file extension for a spreadsheet file format created for storing Microsoft Excel documents, known as the Binary Interchange File Format (BIFF). In its turn, XLSX is another kind of Microsoft Excel spreadsheet that features calculation, graphing tools, pivot tables, and macro programming. As you might know, the latter is the latest version of Microsoft Excel while the former is the older one. Thus, XLS is associated with the 2003 version of Excel, while XLSX is related to versions since 2007.

It is also necessary to mention that XLSX and XLS files store information differently. As we’ve just mentioned, XLS is based on BIFF, keeping all the data ordered in a binary format. As for XLSX, it incorporates the Office Open XML format, storing all the data in a text file that utilizes XML for defining parameters.

Also, the performance of the two file types differs. XLS files are faster than XLSX when you use complex formulas and large sets of data.

It is also worth mentioning that XLS files are supported in all Excel versions while XLSX sheets can be processed in Microsoft Excel versions 2007 and onwards.

From the perspective of Magento 2 data export, XLS and XLSX are quite similar and interchangeable. If you want to process data files in Excel 2007+ you can freely use XLSX instead of XLS. If your editor is older, you either need to run the XLS export or convert XLSX into XLS. So, how to export data from Magento 2 via XLS?

How to export XLS files from Magento 2

Nilesh Gosai proposes the following solution to export data from Magento 2 via XLS files:

Firstly, he recommends adding the following code in (“app/code/NameSpace/Module/view/adminhtml/ui_component/”) listing the XML file between <listingToolbar> and </listingToolbar> tags:

<exportButton class="Magento\Ui\Component\ExportButton">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="options" xsi:type="array">
                        <item name="cvs" xsi:type="array">
                            <item name="value" xsi:type="string">csv</item>
                            <item name="label" xsi:type="string" translate="true">CSV</item>
                            <item name="url" xsi:type="string">mui/export/gridToCsv</item>
                        </item>
                        <item name="xml" xsi:type="array">
                            <item name="value" xsi:type="string">xml</item>
                            <item name="label" xsi:type="string" translate="true">Excel XML</item>
                            <item name="url" xsi:type="string">mui/export/gridToXml</item>
                        </item>
                        <item name="xls" xsi:type="array">
                            <item name="value" xsi:type="string">xls</item>
                            <item name="label" xsi:type="string" translate="true">Excel XLS</item>
                            <item name="url" xsi:type="string">{{module}}/export/gridToXls</item>
                        </item>
                    </item>
                </item>
            </argument>
        </exportButton>

<item name="url" xsi:type="string">mui/export/gridToCsv</item>

</item>

<item name="label" xsi:type="string" translate="true">Excel XML</item>

<item name="url" xsi:type="string">mui/export/gridToXml</item>

</item>

<item name="label" xsi:type="string" translate="true">Excel XLS</item>

<item name="url" xsi:type="string">{{module}}/export/gridToXls</item>

</item>

</argument>

</exportButton>

Secondly, it is necessary to create the “GridToXls.php” file in app/code/Namespace/Module/Controller/Adminhtml/Export. Add the following code to the file:

<?php
/**
 * Copyright © 2013-2017 Magento, Inc. All rights reserved.
 * See COPYING.txt for license details.
 */
namespace Namespace\Module\Controller\Adminhtml\Export;

use Magento\Backend\App\Action;
use Magento\Backend\App\Action\Context;
use Namespace\Module\Model\Export\ConvertToXls;
use Magento\Framework\App\Response\Http\FileFactory;

/**
 * Class Render
 */
class GridToXls extends Action
{
    /**
     * @var ConvertToXls
     */
    protected $converter;

    /**
     * @var FileFactory
     */
    protected $fileFactory;

    /**
     * @param Context $context
     * @param ConvertToXml $converter
     * @param FileFactory $fileFactory
     */
    public function __construct(
        Context $context,
        ConvertToXls $converter,
        FileFactory $fileFactory
    ) {
        parent::__construct($context);
        $this->converter = $converter;
        $this->fileFactory = $fileFactory;
    }

    /**
     * Export data provider to XML
     *
     * @throws \Magento\Framework\Exception\LocalizedException
     * @return \Magento\Framework\App\ResponseInterface
     */
    public function execute()
    {
        return $this->fileFactory->create('export.xls', $this->converter->getXlsFile(), 'var');
    }
}

<?php

/**

* See COPYING.txt for license details.

namespace Namespace\Module\Controller\Adminhtml\Export;

use Magento\Backend\App\Action;

use Magento\Backend\App\Action\Context;

use Namespace\Module\Model\Export\ConvertToXls;

use Magento\Framework\App\Response\Http\FileFactory;

/**

* Class Render

class GridToXls extends Action

{

/**

* @var ConvertToXls

protected $converter;

/**

* @var FileFactory

protected $fileFactory;

/**

* @param Context $context

* @param ConvertToXml $converter

* @param FileFactory $fileFactory

public function __construct(

Context $context,

ConvertToXls $converter,

FileFactory $fileFactory

) {

parent::__construct($context);

$this->converter = $converter;

$this->fileFactory = $fileFactory;

}

/**

* Export data provider to XML

* @throws \Magento\Framework\Exception\LocalizedException

* @return \Magento\Framework\App\ResponseInterface

public function execute()

{

return $this->fileFactory->create('export.xls', $this->converter->getXlsFile(), 'var');

}

Thirdly, the “ConvertToXls.php” file is required. Create it in app/code/Namespace/Module/Model/Export with the following code inside:

<?php
/**
 * Copyright © 2013-2017 Magento, Inc. All rights reserved.
 * See COPYING.txt for license details.
 */
namespace Namespace\Module\Model\Export;

use Magento\Ui\Model\Export\MetadataProvider;
use Magento\Ui\Model\Export\SearchResultIteratorFactory;
use Magento\Framework\Api\Search\DocumentInterface;
use Magento\Framework\Api\Search\SearchResultInterface;
use Magento\Framework\App\Filesystem\DirectoryList;
use Magento\Framework\Convert\Excel;
use Magento\Framework\Convert\ExcelFactory;
use Magento\Framework\Exception\LocalizedException;
use Magento\Framework\Filesystem;
use Magento\Framework\Filesystem\Directory\WriteInterface;
use Magento\Ui\Component\MassAction\Filter;

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

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

<?php

/**

* See COPYING.txt for license details.

namespace Namespace\Module\Model\Export;

use Magento\Ui\Model\Export\MetadataProvider;

use Magento\Ui\Model\Export\SearchResultIteratorFactory;

use Magento\Framework\Api\Search\DocumentInterface;

use Magento\Framework\Api\Search\SearchResultInterface;

use Magento\Framework\App\Filesystem\DirectoryList;

use Magento\Framework\Convert\Excel;

use Magento\Framework\Convert\ExcelFactory;

use Magento\Framework\Exception\LocalizedException;

use Magento\Framework\Filesystem;

use Magento\Framework\Filesystem\Directory\WriteInterface;

use Magento\Ui\Component\MassAction\Filter;

/**

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

}

Since this approach doesn’t look very merchant-friendly, we’d also like to draw your attention to an alternative solution with XLSX files instead of XLS.

How to export XLSX files from Magento 2

You need a third-party extension to enable the XLSX export in Magento 2. Meet the Improved Import & Export extension! It not only lets you transfer XLSX files from your e-commerce website but also supports such standards as:

CSV;
XML;
JSON;
ODS;
ZIP & TAR.

Besides, the module offers extensive mapping capabilities. You can freely export data in a format suitable for your external integrations. Although you cannot transfer Magento 2 attributes straight to a third-party system, since it has its unique standards and requirements, the Improved Import & Export lets you do that. You either apply a mapping preset or match native Magento 2 attributes to ones utilized by the platform you connect your website to.

Magento 2 attribute mapping import and export

Various data editing capabilities enable you to merge and split data, add suffixes and prefixes, create missing attributes on the fly, etc.

Besides, the Improved Import & Export extension supports data transfer automation. The module launches data transfers according to schedule. At the same time, you can leverage various triggers. Thus, you can export data from Magento 2 in XLSX daily, weekly, or monthly as well as after a particular event – a new order is placed – takes place.

Magento 2 all product types import and export

And for various complex integrations, Improved Import & Export utilizes API connections, creating a robust data flow between your e-commerce store and an external system.

Last but not least, our module has a set of plug-ins that simplify particular integrations. For instance, SAP Business One Integration Add-on for Magento 2 lets you synchronize the two systems within just a few clicks.

For further information about our extension, follow this link:

Get Improved Import & Export Magento 2 Extension

You can find more export and import tips for Magento 2 in our cookbook.

Magento 2 XLSX Export FAQ

How to export XLSX files from Magento 2 automatically?

The Improved Import & Export Magento 2 extension provides the ability to automate data transfers from your e-commerce website. The module relies on a cron scheduler so that you can choose predefined intervals or create custom ones for your export jobs. The same functionality is available for import processes.

How to export XLSX files suitable for external systems?

Our module offers robust mapping functionality. It lets you apply changes to the exported data right within the admin. As a result, you create output suitable for any system. The same tools are available for import processes. Improved Import & Export lets you match any external attributes to the ones used in Magento 2. The best thing is that you can save the created schemes for further data transfers.

Is it possible to import XLSX files to Magento 2?

Yes, you can use the Improved Import & Export extension for both import and export processes associated with XLSX files.

What entities can I export from Magento 2 via XLSX files?

You can transfer all the core entities via XLSX files to Magento 2 with Improved Import & Export. The extension supports products, categories, customers, advanced pricing, product attributes, orders, cms pages, cms blocks, catalog & cart rules, gift cards, reviews, url rewrites, search terms and synonyms, widgets, page hierarchy, newsletter subscribers, and other. Besides, you can export B2B entities using the corresponding add-on. Feel free to transfer company accounts, requisition lists, quotes, and others via XLSX from Magento 2. Note that you can import all this data to Magento 2 via XLSX using the same extension.

What file sources can I use to export XLSX files from Magento 2?

The Improved Import & Export extension lets you export XLSX files to FTP/SFTP, Google Drive, Dropbox, and OneDrive. Witn it comes to import processes, it is possible to transfer XLSX files from the same destinations as well as using direct URLs or uploading files straight to Magento 2.

What other file formats are supported?

With the Improved Import & Export module, you can transfer data using not only XLSX files but also CSV, XML, ODS, and JSON. All these data standards are supported in import and export processes.

What alternative ways of data transfers are possible?

With the Improved Import & Export Magento 2 module you can avoid file transfers due to support for Google Sheets. The extension lets you import data right from this service. Besides, the plugin lets you establish API connections with external systems, creating a seamless data flow between your Magento 2 website and any platform beyond.

Post Tags:export magento 2 Magento 2 Developer’s Cookbook XLS XLSX

← Previous Post Next Post →

Magento 2 import and export COOKBOOK index post. Use cases and instructions

This is an index blog post for Magento 2 import and export use cases. In this post you will find… Read more…
Import/Export cookbook: How to automate Magento 2 product catalog update from Google Sheets, FTP/SFTP, Google Drive, OneDrive, Dropbox

Working with suppliers requires you to regularly update your Magento 2 product catalog with new prices, stock updates, and sometimes… Read more…
Import/Export cookbook: How to update Magento 2 product prices during import quickly, without editing the CSV table

If you are a dropshipper or want to follow the inflation and increase product prices for your whole Magento 2… Read more…
Import/Export cookbook: How to update Magento 2 product price and quantity using import and export. HINT: it’s fast and easy

Improved Import & Export Magento 2 extension documentation is huge! I know. It is aimed to cover every aspect of… Read more…
How to Export Products, Orders, Customers, & Other Magento 2 Entities to Remote FTP & SFTP Server

Unfortunately, Magento 2 doesn’t provide any opportunities to export products, orders, customers, or other Magento 2 entities to a remote… Read more…
How to Automate Magento 2 Import & Export

We have very good news for you: it’s no longer necessary to import/export files to/from Magento 2 manually. And below… Read more…
The Best Improved Import/Export Extensions for Magento 2

Improved import & export Magento 2 extensions are designed to simplify some routine Magento tasks. They revamp the default abilities… Read more…
How to improve Magento 2 import and export speed?

Have you ever wondered why Magento 2 import and export processes are so slow? If you move data to and… Read more…
Firebear Improved Import & Export Plugin for Shopware

Our Improved Import & Export module is one of the most fully featured and powerful data transfer solutions in the… Read more…
Magento 2 search terms and synonyms, and how to import and export them

Magento 2 lets store admins optimize the customer search queries by composing lists of search terms and synonyms. While it… Read more…
The Complete Guide to Magento 2 Related Products, Up-sells, and Cross-sells, and how to import them

To sell effectively one must learn to predict and analyze. What are people looking for at my store? How can… Read more…
How to export a custom entity from Magento 2

Let’s assume that you’ve created a custom entity in your Magento 2 backend. However, you cannot either import or export… Read more…
How to export customers with custom attributes in Magento 2

We continue sifting the endless StackExchange desert in an attempt to discover the most helpful discussion. This time, our attention… Read more…
How to import and export CMS/Static Blocks to Magento 2

It is hard to overestimate the importance of Magento 2 CMS pages for an average e-commerce website. They play a… Read more…
Commerce Extensions Import Export Bulk Orders Invoices Shipments Magento 2 Module

With the help of this Magento 2 extension, you will easily move all orders and order-related data to your Magento… Read more…
Magento Order Export Module by Xtento

In this post, we explain how to export all sales data from Magento 2 and 1 in any file format to… Read more…
How to Export Products from Magento 2 via CLI

Although most Magento 2 administrators prefer working with their e-commerce website strength in the admin area, there are still many… Read more…
Amasty Export Orders Extension for Magento 2 and 1

If you are looking for the most powerful migration tool for Magento 2 or Magento 1, check out our Improved Import Magento… Read more…
How to export custom SKU column from Magento 2 with all content

How often do you face export processes that the default Magento 2 instruments cannot cope with? We regularly witness the… Read more…
How to Export and Import Magento 2 Categories

Unfortunately, Magento 2 doesn't let you import and export categories by default. Although the platform's capabilities associated with data transfers… Read more…

Understanding xls and xlsx

How to export XLS files from Magento 2

How to export XLSX files from Magento 2

Magento 2 XLSX Export FAQ

How to export XLSX files from Magento 2 automatically?

How to export XLSX files suitable for external systems?

Is it possible to import XLSX files to Magento 2?

What entities can I export from Magento 2 via XLSX files?

What file sources can I use to export XLSX files from Magento 2?

What other file formats are supported?

What alternative ways of data transfers are possible?

Related posts