How to export data from Magento 2 in XLS & XLSX formats
Recently, we’ve come across a

Bear in mind that we can help you with any import and export tasks that take place in Magento 2.
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 spreadsheets 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
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
<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> |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
<?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'); } } |
Thirdly, the “ConvertToXls.php” file is required. Create it in app/code/Namespace/Module/Model/Export with the following code inside:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 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 /** * 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); } } } |
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.
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.
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.
