[Proposal]: add writing xlsx file with some styling

[Grummfy]

Describe the Proposal

hello,
as discussed through some discord with Norbert, I think it could be nice to have some witer for excell (xlsx) (using external libraries or not (with phpoffice/spreadsheet activate the cache! https://phpspreadsheet.readthedocs.io/en/latest/topics/memory_saving/)) .

In my use case I need

• writing some lines of rich text (basic style bold, italic, etc today I use html then use rich text converter for it)) that cover (merged cell) all the columns that will be filled. Exemple some title, some legal text, some list of filter parameters
• giving a list of columns + a generator as row filler + some of the cell of the row will contains some formated text (rich I assume)

In my case the columns make the link between the array key, the column and some extra formating (this is a text, this is a number, this is date) and eventually some cell style

Optional

• having simple style (from templates ?) for data themselve like odd and even row differenciator
• add extra metadata properties to the files (example of usage : confidential flag, person who ask the export, etc)

Notes

• by rich text I mean https://en.wikipedia.org/wiki/Rich_Text_Format
• by simple style I mean : some solid border color, some basic format (italic, bold, size, ...)

I hope you have enough information and it help to have a usage case

thanks a lot

API Adjustments

No response

Are you intending to also work on proposed change?

None

Are you interested in sponsoring this change?

None

Integration & Dependencies

No response

[norberttech]

@stloyd you did already some work regarding writing to excel but it wasn't covering all edge cases, correct?

You have that code somewhere maybe?

[stloyd]

No, there was no Excel (xlsx) loading/saving code done by me officially; it was a Google Sheet loader, but it was a basic one.

I have some small loader for Excel files in my internal project, but it was never exposed. I'm not too proud of it so it will remain that way.

Also, I'm not sure's purpose of the ETL tool is to do some RTE, as it's not helping in (machine) reading, it's even the opposite of that. While RTE is nice for humans, it's harder to work with for machines, so I would say that the Excel loader would still be a good fit for Flow, but any RTE playing should be out of scope and done after transitions, just my 2 cents on that.

[norberttech]

No, there was no Excel (xlsx) loading/saving code done by me officially; it was a #1609, but it was a basic one.

Ahh right!

I have some small loader for Excel files in my internal project, but it was never exposed. I'm not too proud of it so it will remain that way.

heh yea no worries, I'm going to work on a proper one since I also have one use case for it

Also, I'm not sure's purpose of the ETL tool is to do some RTE, as it's not helping in (machine) reading, it's even the opposite of that. While RTE is nice for humans, it's harder to work with for machines, so I would say that the Excel loader would still be a good fit for Flow, but any RTE playing should be out of scope and done after transitions, just my 2 cents on that.

I'm going to first take a look at what's even possible, formatting (except maybe merging cells together) should not impact reading as excel is keeping values and formatting separately but it's been a while since I look into this so will know more after an initial research.

[Grummfy]

note that for my use case, I have no problem to create a file with another lib, then complete it with flow for example.

[norberttech]

note that for my use case, I have no problem to create a file with another lib, then complete it with flow for example.

actually other way around should be easier:

1. create raw excel file with flow (with one or multiple sheets)
2. use excel library to apply styles

but I think there might be a nice way to combine those two operations into one for better DX

[stloyd]

Hey,

I was playing with this, and it's a bit difficult in two ways:

1. It's either a global setup:

use OpenSpout\Writer\XLSX\Options;
use OpenSpout\Writer\XLSX\Writer;

$options = new Options();
$options->setColumnWidth(30, 1); // set column no 1 to size 30
$options->setColumnWidthForRange(20, 2, 4); // set columns from 2-4 to size 20

$writer = new Writer($options);

2. Or individual per cell and per row created:

use OpenSpout\Common\Entity\Cell;
use OpenSpout\Common\Entity\Row;
use OpenSpout\Common\Entity\Style\Style;

$rows[] = new Row(
    [
        Cell::fromValue('name'),
        Cell::fromValue('value', (new Style)->setFontBold()),
    ],
    (new Style())->setShouldShrinkToFit() // set this on whole row
);

---

First, we can easily introduce those options (we should do this so we can pass more meta Excel details, but it's another story), but they are on writer level, so setting via $loader->withOptions() or something would be good to start. But one hard limit here: it's mostly just simple option like size, you can't set per row/cell settings (ie bold font).

For the second, we would need to introduce some kind of metadata, per row/cell, that could be read and used, but right now, I don't have anything in mind.

[norberttech]

For the second, we would need to introduce some kind of metadata, per row/cell, that could be read and used, but right now, I don't have anything in mind.

You might want to take a look at DbalMetadata for some inspiration, but more I think about it, more I'm leaning towards not adding this logic to the loader and letting clients to do it on existing document after it's generated.

I just feel that this might open an pandora box of edge cases.
Another thing we might consider, is some extension point in the Loader itself, that would let users inject their own code would let them generated programatically options/styles for each cell.

Something like CellStyleGenerator interface that would get a method for(Entry $entry) :? Style
(but it's just the quick idea, that extension point should be properly designed, maybe even with some default style generator that would make sure that at least data is formatted nicely)

[stloyd]

Sharing a simple version with the global options, so if you want, you can freely test it locally (uses OpenSpout, which is already in Flow):

<?php

declare(strict_types=1);

namespace App\Flow\Excel;

use Flow\ETL\Exception\InvalidArgumentException;
use Flow\ETL\FlowContext;
use Flow\ETL\Loader;
use Flow\ETL\Row\Entry;
use Flow\ETL\Rows;
use Flow\Filesystem\Path;
use Flow\Filesystem\Path\Option;
use OpenSpout\Common\Entity\Row;
use OpenSpout\Writer\XLSX\Options;
use OpenSpout\Writer\XLSX\Writer;

final class ExcelLoader implements Loader\FileLoader, Loader
{
    private readonly Path $path;

    private ?Options $options = null;

    public function __construct(Path $path)
    {
        if (!$path->isLocal()) {
            // We can't use resources (returned by \fopen) since they are not supported by the OpenSpout library.
            // They are not supported because the OpenSpout library utilizes the PHP built-in ZipArchive library, which doesn't support resources; it only supports local paths.
            throw new InvalidArgumentException('Only local filesystem paths are supported by ExcelLoader due to the limitation of underlying library.');
        }

        $this->path = $path->setOptionWhenEmpty(Option::CONTENT_TYPE, 'application/vnd.ms-excel');
    }

    public function destination(): Path
    {
        return $this->path;
    }

    public function load(Rows $rows, FlowContext $context): void
    {
        if (!$rows->count()) {
            return;
        }

        $writer = new Writer($this->options);
        $writer->openToFile($this->path->path());

        foreach ($rows as $row) {
            $writer->addRow(
                Row::fromValues(
                    $row->entries()->map(fn (Entry $entry): string => $entry->toString()),
                ),
            );
        }

        $writer->close();
    }

    public function withOptions(Options $options): self
    {
        $this->options = $options;

        return $this;
    }
}

Calling it like:

$path = Path::from(sys_get_temp_dir().'/'.$filename);

$options = new Options();
$options->setProperties(
    new Properties(
        title: 'Spreadsheet #1',
        creator: 'Flow PHP',
    ),
);
$options->setColumnWidth(30, 1);
$options->setColumnWidthForRange(20, 2, 4);

df()
    ->from(
        from_all(
            from_array([['Header #1', 'Header #2', 'Header #3']]),
            from_dbal_query(
                $connection,
                <<<'SQL'
SELECT ...
SQL,
                ['uuid' => $uuid]
            )
        )
    )
    ->collect()
    ->write(
        (new ExcelLoader($path))
            ->withOptions($options)
    )
    ->run()
;

dd(file_get_contents($path->path()));

[norberttech]

Nice! I really like how you combined from_array with query to provider headers!

from_all(
  from_array([['Header #1', 'Header #2', 'Header #3']]),
  from_dbal_query(
    $connection,
    $query,
    ['uuid' => $uuid]
  )
)