XLS Export - lack of support on Numbers app on OSX due to incorrect file format

[cloudcanyon]

Feature Request

When I download a list in XLS format, the file uses formatting with and other HTML tags. When I try to open it with Numbers app on OSX (macOS), it gives me wrong content.

Ms Excel can handle the format with but it shouts about incorrect format anyway.

The request is to add a better support for XLS files. @VincentLanglet has helped me a lot on Slack and has suggested to use https://packagist.org/packages/box/spout package, which supports XLSX format. Files may have .xls extension and Numbers still can open the format created by box/spout. Ms Excel can do that on Windows and OSX as well.

The Writer file that I use is following (it may be replaced in the repository, just change getFormat() method).

<?php

namespace Sonata\Exporter\Writer;

use Box\Spout\Writer\Common\Creator\WriterEntityFactory;
use Sonata\Exporter\Writer\TypedWriterInterface;

class XlsWriter implements TypedWriterInterface
{
    /**
     * @var string
     */
    protected $filename;

    /**
     * @var bool
     */
    protected $showHeaders;

    /**
     * @var int
     */
    protected $position;

    /**
     * @var \Box\Spout\Writer\XLSX\Writer
     */
    protected $writer;

    /**
     * @throws \RuntimeException
     */
    public function __construct(string $filename, bool $showHeaders = true)
    {
        $this->filename = $filename;
        $this->showHeaders = $showHeaders;
        $this->position = 0;

        if (is_file($filename)) {
            throw new \RuntimeException(sprintf('The file %s already exist', $filename));
        }
    }

    /**
     * {@inheritdoc}
     */
    final public function getDefaultMimeType(): string
    {
        return 'application/vnd.ms-excel';
    }

    /**
     * {@inheritdoc}
     */
    final public function getFormat(): string
    {
        return 'xls-real';
    }

    /**
     * {@inheritdoc}
     */
    public function open()
    {
        $this->writer = WriterEntityFactory::createXLSXWriter();
        $this->writer->openToFile($this->filename);
    }

    /**
     * {@inheritdoc}
     */
    public function close()
    {
        $this->writer->close();
    }

    /**
     * {@inheritdoc}
     */
    public function write(array $data)
    {
        $this->init($data);

        $row = WriterEntityFactory::createRowFromArray(array_values($data));
        $this->writer->addRow($row);

        ++$this->position;
    }

    protected function init(array $data): void
    {
        if ($this->position > 0) {
            return;
        }

        if ($this->showHeaders) {
            $row = WriterEntityFactory::createRowFromArray(array_keys($data));
            $this->writer->addRow($row);
            ++$this->position;
        }
    }
}

[willemverspyck]

Hi @cloudcanyon @VincentLanglet. I'm having the same problem with these XLS files. I have now created my own XlsxWriter based on https://github.com/PHPOffice/PhpSpreadsheet (I could saved me some time to use above example, but we already using the PhpSpreadsheet package). Would it be an idea that make an PR with "PHPOffice/PhpSpreadsheet" as dev dependency? These Xlsx have also support for filters and the correct field type (Boolean, String, Numbers, Date). I only have to refactor a bit to make it compatible with PHP 7.3. And add some tests.

<?php

declare(strict_types=1);

namespace App\Writer;

use DateTime;
use DateTimeInterface;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Shared\Date;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Writer\Exception as WriterException;
use RuntimeException;
use Sonata\Exporter\Writer\TypedWriterInterface;
use Symfony\Component\DependencyInjection\Attribute\Autoconfigure;

/**
 *
 */
#[Autoconfigure(tags: ['sonata.exporter.writer'], bind: ['$filename' => '%sonata.exporter.writer.xls.filename%'])]
final class XlsxWriter implements TypedWriterInterface
{
    /**
     * @var string
     */
    private string $filename;

    /**
     * @var bool
     */
    private bool $showHeaders;

    /**
     * @var bool
     */
    private bool $showFilters;

    /**
     * @var Spreadsheet
     */
    private Spreadsheet $spreadsheet;

    /**
     * @var Worksheet
     */
    private Worksheet $worksheet;

    /**
     * @var int
     */
    private int $position;

    /**
     * @throws RuntimeException
     */
    public function __construct(string $filename, bool $showHeaders = true, bool $showFilters = true)
    {
        $this->filename = $filename;
        $this->showHeaders = $showHeaders;
        $this->showFilters = $showFilters;
        $this->position = 1;

        if (is_file($filename)) {
            throw new RuntimeException(sprintf('The file %s already exist', $filename));
        }
    }

    /**
     * @return string
     */
    public function getDefaultMimeType(): string
    {
        return 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';
    }

    /**
     * @return string
     */
    public function getFormat(): string
    {
        return 'xlsx';
    }

    /**
     *
     */
    public function open(): void
    {
        $this->spreadsheet = new Spreadsheet();

        $this->worksheet = $this->spreadsheet->getActiveSheet();
    }

    /**
     * @throws WriterException
     */
    public function close(): void
    {
        if ($this->showHeaders && $this->showFilters) {
            $this->worksheet->setAutoFilter($this->worksheet->calculateWorksheetDimension());
            $this->worksheet->setSelectedCellByColumnAndRow(1, 1);
        }

        $excelWriter = IOFactory::createWriter($this->spreadsheet, 'Xlsx');
        $excelWriter->save($this->filename);
    }

    /**
     * @param array $data
     */
    public function write(array $data): void
    {
        if (1 === $this->position && $this->showHeaders) {
            $this->addHeaders($data);

            ++$this->position;
        }

        $column = 1;

        foreach ($data as $value) {
            $dataFormat = $this->getDataFormat($value);
            $dataValue = $this->getDataValue($value);

            if (null !== $dataFormat) {
                $this->worksheet->getStyleByColumnAndRow($column, $this->position)
                    ->getNumberFormat()
                    ->setFormatCode($dataFormat);
            }

            $dataType = $this->getDataType($value);

            $this->worksheet->setCellValueExplicitByColumnAndRow($column, $this->position, $dataValue, $dataType);

            ++$column;
        }

        ++$this->position;
    }

    /**
     * @param array $data
     */
    private function addHeaders(array $data): void
    {
        $column = 1;

        foreach (array_keys($data) as $value) {
            $this->worksheet->setCellValueExplicitByColumnAndRow($column, $this->position, $value, DataType::TYPE_STRING);

            ++$column;
        }
    }

    /**
     * Get the dataType of the cell
     *
     * @param mixed $value
     *
     * @return string
     */
    private function getDataType($value): string
    {
        if (null === $value) {
            return DataType::TYPE_NULL;
        }

        if (is_bool($value)) {
            return DataType::TYPE_BOOL;
        }

        if ($this->getDateTime($value) instanceof DateTimeInterface) {
            return DataType::TYPE_NUMERIC;
        }

        if (is_string($value)) {
            return DataType::TYPE_STRING;
        }

        return DataType::TYPE_NUMERIC;
    }

    /**
     * Get the dataType of the cell

     * @param mixed $value
     *
     * @return mixed
     */
    private function getDataValue($value)
    {
        if (null === $value) {
            return null;
        }

        $dateTime = $this->getDateTime($value);

        if ($dateTime instanceof DateTimeInterface) {
            return Date::PHPToExcel($dateTime);
        }

        return $value;
    }

    /**
     * @param mixed $value
     *
     * @return string|null
     */
    private function getDataFormat($value): ?string
    {
        $dateTime = $this->getDateTime($value);

        if ($dateTime instanceof DateTimeInterface) {
            return sprintf('%s hh:mm:ss', NumberFormat::FORMAT_DATE_DDMMYYYY);
        }

        return null;
    }

    /**
     * @param mixed $value
     *
     * @return DateTimeInterface|null
     */
    private function getDateTime($value): ?DateTimeInterface
    {
        if (is_string($value)) {
            $dateTime = DateTime::createFromFormat(DateTimeInterface::RFC1123, $value);

            if ($dateTime instanceof DateTimeInterface) {
                return $dateTime;
            }
        }

        return null;
    }
}

[VincentLanglet]

Would it be an idea that make an PR with "PHPOffice/PhpSpreadsheet" as dev dependency?

Sure