Excel.php

Go to the documentation of this file.

<?php
namespace Magento\Framework\Convert;

use Magento\Framework\Filesystem\File\WriteInterface;

class Excel
{
    protected $_iterator = null;

    protected $_rowCallback = [];

    protected $_dataHeader = [];

    protected $_dataFooter = [];

    public function __construct(\Iterator $iterator, $rowCallback = [])
    {
        $this->_iterator = $iterator;
        $this->_rowCallback = $rowCallback;
    }

    protected function _getXmlHeader($sheetName = '')
    {
        if (empty($sheetName)) {
            $sheetName = 'Sheet 1';
        }

        $sheetName = htmlspecialchars($sheetName);

        $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>' .
            '<Worksheet ss:Name="' .
            $sheetName .
            '">' .
            '<Table>';

        if ($this->_dataHeader) {
            $xmlHeader .= $this->_getXmlRow($this->_dataHeader, false);
        }

        return $xmlHeader;
    }

    protected function _getXmlFooter()
    {
        $xmlFooter = '';

        if ($this->_dataFooter) {
            $xmlFooter = $this->_getXmlRow($this->_dataFooter, false);
        }

        $xmlFooter .= '</Table></Worksheet></Workbook>';

        return $xmlFooter;
    }

    protected function _getXmlRow($row, $useCallback)
    {
        if ($useCallback && $this->_rowCallback) {
            $row = call_user_func($this->_rowCallback, $row);
        }
        $xmlData = [];
        $xmlData[] = '<Row>';

        foreach ($row as $value) {
            $value = htmlspecialchars($value);
            $dataType = is_numeric($value) && $value[0] !== '+' && $value[0] !== '0' ? 'Number' : 'String';

            if (!is_string($value)) {
                $value = (string)$value;
            }
            if (isset($value[0]) && in_array($value[0], ['=', '+', '-'])) {
                $value = ' ' . $value;
                $dataType = 'String';
            }

            $value = str_replace("\r\n", '&#10;', $value);
            $value = str_replace("\r", '&#10;', $value);
            $value = str_replace("\n", '&#10;', $value);

            $xmlData[] = '<Cell><Data ss:Type="' . $dataType . '">' . $value . '</Data></Cell>';
        }
        $xmlData[] = '</Row>';

        return join('', $xmlData);
    }

    public function setDataHeader($data)
    {
        $this->_dataHeader = $data;
    }

    public function setDataFooter($data)
    {
        $this->_dataFooter = $data;
    }

    public function convert($sheetName = '')
    {
        $xml = $this->_getXmlHeader($sheetName);

        foreach ($this->_iterator as $dataRow) {
            $xml .= $this->_getXmlRow($dataRow, true);
        }
        $xml .= $this->_getXmlFooter();

        return $xml;
    }

    public function write(WriteInterface $stream, $sheetName = '')
    {
        $stream->write($this->_getXmlHeader($sheetName));

        foreach ($this->_iterator as $dataRow) {
            $stream->write($this->_getXmlRow($dataRow, true));
        }
        $stream->write($this->_getXmlFooter());
    }
}