How to generate an Excel file (speadsheet)?

dmitrii_fediuk · February 25, 2016, 5:53pm

API

Use the \Magento\Framework\Convert\Excel class:

Usage examples

Example 1

magento/magento2/blob/e0ed4bad/app/code/Magento/Backend/Block/Widget/Grid/Export.php#L431-L433


$collection = $this->_getRowCollection();


$convert = new \Magento\Framework\Convert\Excel($collection->getIterator(), [$this, 'getRowRecord']);

github.com

magento/magento2/blob/e0ed4bad/app/code/Magento/Backend/Block/Widget/Grid/Export.php#L435-L455


$name = md5(microtime());
$file = $this->_path . '/' . $name . '.xml';


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


$convert->setDataHeader($this->_getExportHeaders());
if ($this->getCountTotals()) {
    $convert->setDataFooter($this->_getExportTotals());
}


$convert->write($stream, $sheetName);
$stream->unlock();
$stream->close();


return [
    'type' => 'filename',
    'value' => $file,
    'rm' => true  // can delete file after use
];

Example 2

github.com

magento/magento2/blob/e0ed4bad/app/code/Magento/Backend/Block/Widget/Grid/Export.php#L496-L497


$convert = new \Magento\Framework\Convert\Excel(new \ArrayIterator($data));
return $convert->convert('single_sheet');

Example 3

github.com

magento/magento2/blob/e0ed4bad/app/code/Magento/Backend/Block/Widget/Grid/Extended.php#L1146-L1149


$convert = new \Magento\Framework\Convert\Excel(
    $this->getCollection()->getIterator(),
    [$this, 'getRowRecord']
);

github.com

magento/magento2/blob/e0ed4bad/app/code/Magento/Backend/Block/Widget/Grid/Extended.php#L1151-L1171


$name = md5(microtime());
$file = $this->_path . '/' . $name . '.xml';


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


$convert->setDataHeader($this->_getExportHeaders());
if ($this->getCountTotals()) {
    $convert->setDataFooter($this->_getExportTotals());
}


$convert->write($stream, $sheetName);
$stream->unlock();
$stream->close();


return [
    'type' => 'filename',
    'value' => $file,
    'rm' => true // can delete file after use
];

Example 4

github.com

magento/magento2/blob/e0ed4bad/app/code/Magento/Backend/Block/Widget/Grid/Extended.php#L1216-L1217


$convert = new \Magento\Framework\Convert\Excel(new \ArrayIterator($data));
return $convert->convert('single_sheet');