openspout / openspout

Read and write spreadsheet files (CSV, XLSX and ODS), in a fast and scalable way

OpenSpout

OpenSpout is a community driven fork of box/spout, a PHP library to read and write spreadsheet files (CSV, XLSX and ODS), in a fast and scalable way. Unlike other file readers or writers, it is capable of processing very large files, while keeping the memory usage really low (less than 3MB).

Documentation

Documentation can be found at docs/.

Upgrade from box/spout:v3 to openspout/openspout:v3

1. Replace box/spout with openspout/openspout in your composer.json
2. Replace Box\Spout with OpenSpout in your code

Upgrade guide

Version 4 introduced new functionality but also some breaking changes. If you want to upgrade your OpenSpout codebase please consult the Upgrade guide.

Copyright and License

This is a fork of Box's Spout library: https://github.com/box/spout

Code until and directly descending from commit cc42c1d is copyright of Box, Inc. and licensed under the Apache License, Version 2.0:

https://github.com/openspout/openspout/blob/cc42c1d29fc5d29f07caeace99bd29dbb6d7c2f8/LICENSE

Code created, edited and released after the commit mentioned above is copyright of openspout Github organization and licensed under MIT License.

https://github.com/openspout/openspout/blob/main/LICENSE

openspout's Issues

`DateTimeCell` type attribute is not set when writing XLSX

When using openspout/openspout v4.8.0, submitting a DateTimeCell to the XLSX writer does not add the 'type' attribute to the generated XML. Instead the default fallback of a numeric type is used.

I've created a branch at https://github.com/benhurrell/openspout/tree/fix-cell-date-attribute that fixes this. I began writing a test to 'prove' the fix works, but have the following questions:

• None of the other type attribute mappings appear to be tested; therefore is a test for this fix necessary/helpful?
• If a test is required, are there any helper classes in the test suite I should look to use?

Thank you for your time.

Error while opening excel

Hi, Just noticed this.

I have loaded the excemple script and just added one row as per script below

include_once 'vendor/autoload.php';

use OpenSpout\Common\Entity\Cell;

$writer = new \OpenSpout\Writer\XLSX\Writer();
$writer->openToBrowser('testcarl.xlsx');
$cells = [
    Cell::fromValue('Carl'),
    Cell::fromValue('is'),
    Cell::fromValue('great!'),
];

$writer->close();`

After i download the file, and try opening in excel, following issue arrise:

I have tested this on PHP 8.1.3 and 8.1.7 clean php versions
Anyone have same issues like this or where do i need to look for changes?

When i reverted back to the original code from Box Sprout it was working correctlly?

Correct usage of HIPERLINK formula

Hello, nice project!

I'm having problem decorating text with the HIPERLINK formula.

The resulting spreadsheet got an @ symbol between the leading = signal and the HIPERLINK function definition.

Looks like other libraries also had this problem in the past, seems like Excel is understanding this is a formula that operates over an array 🤷‍♂️

Neither the "prepend _xlfn." trick worked.

Here the shortest snippet that is able to reproduce

        $writer = WriterEntityFactory::createXLSXWriter();
        $writer->openToFile('debug.xlsx');
        $cell = new Cell('=HIPERLINK("https://github.com/openspout/openspout", "OpenSpout is awesome!")');
        $writer->addRow(WriterEntityFactory::createRow([$cell]))->close();

openspout v3.4, PHP v7.4, Excel for macOS v16

thanks in advance.

styles.xml and workbook.xml are added twice to the archive on Windows

Using PHP 8.1.5 and OpenSpout 4.6.2 on Windows 10.

addFileToArchiveWithCompressionMethod() adds "xl\workbook.xml" and "xl\styles.xml"
then addFolderToArchive() re-adds them as "xl/workbook.xml" and "xl/styles.xml"
Notice the different directory separators.

$zip->locateName() in shouldSkipFile() fails to see them as duplicates and they are not overwritten either. The files end up in the zip archive twice. The end result is that at least Excel 2010 says that the file is corrupt (but is able to repair it). And of course the file is bigger than it should be.

I upgraded to OpenSpout from an older Spout 2.x, where this issue didn't exist. So then I noticed that if I revert this change in the FileSystemHelper.php, everything works fine again:

$this->zipHelper->addFileToArchive($zip, $this->rootFolder, self::XL_FOLDER_NAME.\DIRECTORY_SEPARATOR.self::WORKBOOK_XML_FILE_NAME);
$this->zipHelper->addFileToArchive($zip, $this->rootFolder, self::XL_FOLDER_NAME.\DIRECTORY_SEPARATOR.self::STYLES_XML_FILE_NAME);

=>

$this->zipHelper->addFileToArchive($zip, $this->rootFolder, self::XL_FOLDER_NAME . '/' . self::WORKBOOK_XML_FILE_NAME);
$this->zipHelper->addFileToArchive($zip, $this->rootFolder, self::XL_FOLDER_NAME . '/' . self::STYLES_XML_FILE_NAME);

Autoloader

Hello,
spout has an autoloader but I see openspout doesn't. Are you planning to add it to your distribution? Any suggestions if composer cannot be used?

Thanks!

$options->setColumnWidth() is one too small in resulting spreadsheet

Using v4.8.1:

I was setting column widths:
$options->setColumnWidth(17,1);
$options->setColumnWidth(4,2);
$options->setColumnWidth(119,3);

the resulting widths in the spreadsheet were 16, 3 and 118 (each short by 1).
I 'measured' with
=CELL("width", A1)

CSV Writer - TypeError

Hi,
I'm getting the following error when trying to create a CSV file:

File: /var/www/vendor/openspout/openspout/src/Writer/CSV/Writer.php - Line: 52

OpenSpout\Writer\CSV\Writer::OpenSpout\Writer\CSV\{closure}(): Argument #1 ($value) must be of type OpenSpout\Common\Entity\Cell\BooleanCell|OpenSpout\Common\Entity\Cell\EmptyCell|OpenSpout\Common\Entity\Cell\NumericCell|OpenSpout\Common\Entity\Cell\StringCell, OpenSpout\Common\Entity\Cell\FormulaCell given

Code:

$writer = new \OpenSpout\Writer\CSV\Writer();
$data = [...];
$rows[] = \OpenSpout\Common\Entity\Row::fromValues($data);
$writer->addRows($rows); // <-- fails here

may be bug options->FIELD use double\single quotes

Dear Dev
I use according to the documentation single quotes
$options->FIELD_ENCLOSURE='"' and an error appears,

ValueError: fgetcsv(): Argument #3 ($separator) must be a single character

but if I use double quotes
$options->FIELD_ENCLOSURE="\""; - there is no error

Http(s) support for open file

Hello,

I can't read files transmitted via http(s) protocol. Because file_exists and is_readable don't support http(s) protocol, it gets stuck in validation in the open method. Do you have a solution for this? Removing the validation may not be the right way, developing an alternative validation may be the solution.

openspout/src/Reader/AbstractReader.php

Lines 35 to 43 in cbf7531

	if (!$this->isPhpStream($filePath)) {
	// we skip the checks if the provided file path points to a PHP stream
	if (!file_exists($filePath)) {
	throw new IOException("Could not open {$filePath} for reading! File does not exist.");
	}
	if (!is_readable($filePath)) {
	throw new IOException("Could not open {$filePath} for reading! File is not readable.");
	}
	}

https://www.php.net/manual/en/wrappers.php

Error log:

[2022-12-18 22:28:04] local.ERROR: Could not open https://support.staffbase.com/hc/en-us/article_attachments/360009197031/username.csv for reading! File does not exist. {"exception":"[object]
[stacktrace]                                                                                                                                                                                   
#0 /var/www/vendor/spatie/simple-excel/src/SimpleExcelReader.php(309): OpenSpout\\Reader\\AbstractReader->open('https://support...')                                                           
#1 /var/www/vendor/spatie/simple-excel/src/SimpleExcelReader.php(165): Spatie\\SimpleExcel\\SimpleExcelReader->getSheet()                                                                      
#2 /var/www/app/Services/API/Partners/Inone/ProductListService.php(240): Spatie\\SimpleExcel\\SimpleExcelReader->getRows()

Usage of libxml_disable_entity_loader

This parameter is deprecated on PHP8, is there a way to use this package without this parameter disabled?

If I don't use the libxml_disable_entity_loader(false) I get an error while reading the file. Exactly like in original spout Github topic: box/spout#864

Any ideas?

Excel complains about corrupted XLSX files?

Hi, this issue is a sort of FAQ or placeholder for others experiencing Excel complaining about corrupted XLSX files and are looking for that problem here.

This is not related to the problem on windows that was fixed with release 4.7.0 (see #87, #85, #90 and #97).

I implemented a simple XLSX-export (or ODS) and Excel kept complaining even with the basic examples from the docs. 7-zip and LibreOffice didn't complain and could open the generated files whatsoever. After some lost hair it turned out that I simply forgot an exit; statement after $writer->close(); so that the zipped file ended with html code from the page containing the export.

A simple example would be:

<?php
if (isset($_GET['export'])) {
    $writer = new OpenSpout\Writer\XLSX\Writer();
    $writer->openToBrowser('example.xlsx');
    
    $writer->addRow(OpenSpout\Common\Entity\Row::fromValues([
        'foo',
        'bar',
    ]));
    
    // this sends the file to the browser, but does not terminate the script 
    $writer->close();
    
    // so don't forget this, otherwise the html below is attached to the exported file
    // or prevent other output after sending the file in any other way you like
    exit; 
}
?>
<!DOCTYPE html>
<html>
<head>
    <title>export example</title>
</head>
<body>
    <a href="?export">XLSX export</a>
</body>
</html>

P.S. Other libs, that send their created files directly to the browser, just do that exit afterwards on their own, terminating the whole script. I don't want to suggest that behavior for openspout, as from my point of view leaving that decision to the consuming party is the correct and humble way. But maybe the code examples in the docs could use that exit when openToBrowser() is used so that oblivious devs don't forget it.

[Question] Box\Spout\Writer\Common\Creator\Style\StyleBuilder alternative

Hello! I am working on integrating openspout into my project and I was previously using Box\Spout.

I am currently using Box\Spout\Writer\Common\Creator\Style\StyleBuilder to create excel sheets for download. Is there an alternative for this in Open Spout? I tried swapping the Box\Spout with OpenSpout but its giving me an unknown type error: Undefined type 'OpenSpout\Writer\Common\Creator\Style\StyleBuilder'.

Make RowIterator countable

A common thing to do when reading Excel files is finding out how much there is to read. Making the RowIterator countable can do that in an efficient way instead of making downstream do it.

Any reason for $row->toArray() to be @internal ?

Any reason to have ->toArray() as internal function ?

File in question vendor/openspout/openspout/src/Common/Entity/Row.php
line 137

    /**
     * @internal
     *
     * @return list<null|bool|DateInterval|DateTimeInterface|float|int|string> The row values, as array
     */
    public function toArray(): array
    {
        return array_map(static function (Cell $cell): null|bool|string|int|float|DateTimeInterface|DateInterval {
            return $cell->getValue();
        }, $this->cells);
    }

[QUESTION] - Is there a possibility to detect if a cell contains a hyperlink?

When having a hyperlink on a cell, only the text of the hyperlink is returned, but not the url.
Is there a chance to get the url as well?
Thank you!

Question about getCells and getCellAtIndex in 4.x

I didn't find an example of the getCells usage in the docs. (maybe I missed it).

I see the it is returning an object inside the getRowIterator but how to you just read the value of a specific cell index.

Something as simple as ....

$col = 1;
foreach ($sheet->getRowIterator() as $row) {
   $cells = $row->getCells();
    echo $cells[$col] . "\n";
}

Cell::fromValue() incorrectly detect a formula

Greetings beautiful people who made this bundle!

I've found today that Cell::fromValue() automatically consider any string starting by = as a formula.
While I totally understand why you did this, I believe it's not the way to handle it. I think that if people want to create a cell for a formula, they should use new FormulaCell() instead, since it's a more specific ude case.

Most people will use Cell::fromValue() with "basic" database data (string, int, date...). And an = sign could be used in many of "normal" field. For instance, in a phone number field, if someone miss the shift key to make a "+", it turns it into a "=" (yes, it actually happened to me).

But also in a "comment" field. Some people sometimes start their comment with a "=", for example when they explain something, or give the full meaning of an acronym, etc...

[Question] Speed up reading only what I need

I was testing this library to open some large files, and it take 3sec.

I don't know how xlsx file are structured and if is necessary, but if I open a file and I do a

        foreach ($reader->getSheetIterator() as $sheet) {
            dd($sheet);
        }

All the page is already readed under the hood:

Is there a way to open and read only the necessary page/rows without caching everything?

How to setup excel file properties

I just wanted to check if there is possibility of setting up file properties for excel files.

Prevent xlsx shared strings file based caching

I'm currently updating my spout implementation from version 3 to 4 and I stumbled about a undocumented (and maybe unintended) breaking change.

For XLSX Shared Strings you have the class OpenSpout\Reader\XLSX\Manager\SharedStringsCaching\CachingStrategyFactory. This class trys to decide whether to use the InMemoryStrategy or the FileBasedStrategy. Previously this factory class was not final and extendable. We used this to prevent the usage of the FileBasedStrategy because it slows down to whole process extremly - we would rather let the script die instead of slowing it down. So previously we just extended the CachingStrategyFactory with our own class:

class CachingStrategyFactory extends \OpenSpout\Reader\XLSX\Manager\SharedStringsCaching\CachingStrategyFactory
{
    /**
     * Returns the best shared strings caching strategy.
     *
     * @param int|null $sharedStringsUniqueCount Number of unique shared strings (NULL if unknown)
     * @param string $tempFolder Temporary folder where the temporary files to store shared strings will be stored
     * @param HelperFactory $helperFactory Factory to create helpers
     * @return CachingStrategyInterface The best caching strategy
     */
    public function createBestCachingStrategy($sharedStringsUniqueCount, $tempFolder, $helperFactory)
    {
        return new InMemoryStrategy($sharedStringsUniqueCount);
    }
}

In version 4 the CachingStrategyFactory is now final and don't has any usable interfaces which could be used instead. The only way I could hack the old behaviour together would be to create a CachingStrategyFactory with a fake memory limit - something like this:

$cachingStrategyFactory = new CachingStrategyFactory(new MemoryLimit('1tb'));
$reader =  new XlsxReader(null, $cachingStrategyFactory);

I currently see three options to resolve this:

1. Remove the final from the CachingStrategyFactory class again.
2. Create a CachingStrategyFactoryInterface and replace the typehints for CachingStrategyFactory with the interface. This shouldn't break anything.
3. Add an option to the CachingStrategyFactory to enforce any CachingStrategy.

Option 2 would probably the cleanest solution. Option 1 is by far the easiest and fastest.

New Options not working (bug)

Hi all.

I am add the next line:

use OpenSpout\Common\Entity\Row;

And instance:

$row = Row::fromValues([1,2])

But this not working, show me:

Undefined method 'fromValues'.

Can someone help me?

Thank's.

Slow FileBasedStrategy on Windows

This condition seems to be always passing on Windows, because $inMemoryTempFilePath is normalized using realpath() while the $tempFilePath is not.

variable	value
$this->inMemoryTempFilePath	C:\...\Temp\sharedstrings64676f752d764\sharedstrings0
$tempFilePath	C:\...\Temp\sharedstrings64676f752d764/sharedstrings0

Notice the incorrect directory separator (a slash rather than a backslash) ahead of the base name part.

This causes the temporary file to be re-loaded on every single shared string access, making it very slow, especially on XLSX's with a lot of shared strings.

Question on usage on project

Hello,

i have used Spout 3.3 for a while without composer. Now i have problems with Spout and wanted to switch to OpenSpout.
I installed composer, installed openspout and now I want to use it in my project.

My Code looks like the following:
use OpenSpout\Writer\Common\Creator\WriterEntityFactory;
use OpenSpout\Writer\XLSX\Writer;
use OpenSpout\Common\Entity\Cell;
use OpenSpout\Common\Entity\Row;

writer = new \OpenSpout\Writer\XLSX\Writer();

I always get the following error:
Fatal error: Uncaught Error: Class "OpenSpout\Writer\XLSX\Writer" not found in

Can you help me to switch to openspout. Maybe i am doing something wrong. If you need anything feel free to ask.

Thanks folks

Add reader factory

Hi,

(sorry by advance for my bad english)
I use often a custom reader factory with spout, for simple read case, like import, view,...

ex:ReaderFactory::createReader(string $file): ReaderInterface

The factory try guessing $file type with extention and mime type.

I am willing to post a PR if people are interested.

Getting row with cells style from previous report

hi. im trying to get the row with the cells styles. but when i get the row. the cells style return to defualt.
my method to get the row:

public function previousRows(string $currentSheetName): LazyCollection
    {
        $path = $this->path($this->previousReport);
        $reader = Reader::create($path)->getHeaders();
        $reader->open($path);

        return LazyCollection::make(function () use ($reader, $currentSheetName) {
            foreach ($reader->getSheetIterator() as $sheet) {
                /** @var Sheet $sheet */
                $sheetName = $sheet->getName();

                if ($sheetName === $currentSheetName) {
                    foreach ($sheet->getRowIterator() as $key => $row) {
                        yield $row;
                    }
                }
            }
        });
    }

when i print a cell:

OpenSpout\Common\Entity\Row {#1554 ▼
 #cells: array:29 [▼
   0 => OpenSpout\Common\Entity\Cell {#1762 ▼
     #value: "Day"
     #type: 1
     #style: OpenSpout\Common\Entity\Style\Style {#1763 ▼
       -id: null
       -fontBold: false
       -hasSetFontBold: false
       -fontItalic: false
       -hasSetFontItalic: false
       -fontUnderline: false
       -hasSetFontUnderline: false
       -fontStrikethrough: false
       -hasSetFontStrikethrough: false
       -fontSize: 11
       -hasSetFontSize: false
       -fontColor: "000000"
       -hasSetFontColor: false
       -fontName: "Arial"
       -hasSetFontName: false
       -shouldApplyFont: false
       -shouldApplyCellAlignment: false
       -cellAlignment: null
       -hasSetCellAlignment: false
       -shouldWrapText: false
       -hasSetWrapText: false
       -shouldShrinkToFit: false
       -hasSetShrinkToFit: false
       -border: null
       -shouldApplyBorder: false
       -backgroundColor: null
       -hasSetBackgroundColor: false
       -format: null
       -hasSetFormat: false
       -isRegistered: false
       -isEmpty: true
     }
   }

how the previous report looks like:

OpenSpout\Common\Entity\Row {#1741 ▼
  #cells: array:29 [▼
    0 => OpenSpout\Common\Entity\Cell {#1681 ▼
      #value: "Day"
      #type: 1
      #style: OpenSpout\Common\Entity\Style\Style {#1647 ▼
        -id: null
        -fontBold: false
        -hasSetFontBold: false
        -fontItalic: false
        -hasSetFontItalic: false
        -fontUnderline: false
        -hasSetFontUnderline: false
        -fontStrikethrough: false
        -hasSetFontStrikethrough: false
        -fontSize: 11
        -hasSetFontSize: false
        -fontColor: "000000"
        -hasSetFontColor: false
        -fontName: "Arial"
        -hasSetFontName: false
        -shouldApplyFont: false
        -shouldApplyCellAlignment: true
        -cellAlignment: "center"
        -hasSetCellAlignment: true
        -shouldWrapText: false
        -hasSetWrapText: false
        -shouldShrinkToFit: false
        -hasSetShrinkToFit: false
        -border: null
        -shouldApplyBorder: false
        -backgroundColor: "FFA07A"
        -hasSetBackgroundColor: true
        -format: null
        -hasSetFormat: false
        -isRegistered: false
        -isEmpty: false
      }
    }
    

for example background color went from FFA07A to null

How to write to certain column or cell ?

Can I write to, let's say, cell D3:D10 in a new sheet ? Column A to C will be left blank. Because as far as I know, openspout always start in A1.

How to make borders at the top, in the bottom and stroke of a cell

I write through a translator

Hello everybody.
Can you tell me how to make borders in several places of a cell?
Everything I try to do doesn't work.

$border = new Border(new BorderPart(Border::BOTTOM, Color::BLACK, Border::WIDTH_THIN, Border::STYLE_SOLID));
$border2 = new Border(new BorderPart(Border::TOP, Color::BLACK, Border::WIDTH_THIN, Border::STYLE_SOLID));
$razdelitelStyle = (new Style())->setBackgroundColor(Color::rgb(172, 227, 180))->setBorder($border)->setBorder($border2);

Also interested in how to stroke a cell.
If all this is possible, you can suggest how to do it and share it in the documentation, I think there will be many people with such a question.

Streaming XLSX

Hello. First of all thanks for the fantastic package!

I'm having a problem with streaming. Something unexpected is happening with xlsx while I can successfully stream csv. When I try to stream xlsx on the server, instead of starting to download the file byte by byte, it just waits and gives me the whole file in one go. That's why I'm having timeout issues. I'm not sure if this is expected or if there is a problem.

This works as expected:

$writer = new \OpenSpout\Writer\CSV\Writer();

$response = new StreamedResponse(function () use ($writer) {
    $writer->openToBrowser('data.csv');
    
    for ($i=0; $i < 1000000; $i++) {
        $cells = [
            Cell::fromValue(uniqid()),
            Cell::fromValue(uniqid()),
            Cell::fromValue(uniqid()),
        ];

        $row = new Row($cells);

        $writer->addRow($row);
    }

    $writer->close();
});

$response->headers->set('Content-Type', 'text/csv');

return $response;

But this doesn't work like csv:

$writer = new \OpenSpout\Writer\XLSX\Writer();

$response = new StreamedResponse(function () use ($writer) {
    $writer->openToBrowser('data.xlsx');

    for ($i=0; $i < 1000000; $i++) {
        $cells = [
            Cell::fromValue(uniqid()),
            Cell::fromValue(uniqid()),
            Cell::fromValue(uniqid()),
        ];

        $row = new Row($cells);

        $writer->addRow($row);
    }
    $writer->close();
});

$response->headers->set('Content-Type', 'application/vnd.ms-excel');

return $response;

Am I missing something?

Thanks.

Request to add encryption

I like and use this library very much.
It would be great, if there could be added a function to encrypt files for reading (as it has been in PHPExcel).
Someting like $writer->setPassword().

Format a column as a string

Hi!!

I am trying to format a whole column as a string, because it will be storing numbers that can start by 0.
Is there anyway to get this?

I was trying to use StyleBuilder but it only allows numbers.

Thank you!!

How to create Excel file with autofilter?

Hi! I can't find a way to mark the first row as the autofilter row.
Does it mean it's not possible with this library?

Add setCellVerticalAlignment() & allow setShouldWrapText() to be explicitly set as false

Does this fork already do the things in this PR, or is it worth re-targeting the PR at this fork instead? Getting frustrated at the lack of any signs of life from box/spout :)

box/spout#869

Inconsistent boolean values when writing to CSV

When a BooleanCell containing false is written to a CSV file the resulting value will be empty ('' to be exact), whereas a BooleanCell containing true will result in '1'. This is because all cell values are cast to strings here and PHP casts false to an empty string (reference). I would expect false to result in '0' in the CSV.

Now this could be solved in consumer code by explicitly casting booleans to integers, but ideally we'd fix this in the CSV writer. I couldn't find any tests covering this behaviour, so I'm unsure whether it's intentional or accidental.

What are your thoughts? Could this be a bug fix?

Dates become integers when saving xlsx

Dates become integers like 44949 and I can't figure out why.

I'm reading in one spreadsheet, then writing another one.

In the reader it seems ok:

I then use the array and write back to new row:

$rowFormValues = Row::fromValues($new_row);
$writer->addRow($rowFormValues);

I've also tried to use the code from example to write date but the same happens:

$row[2] = new \DateTimeImmutable('now');

I've tested this is in LibreOffice and Office 2013

Error "Undefined array key 1" when reading some xlsx

openspout/src/Reader/XLSX/RowIterator.php

Lines 299 to 303 in e01cab9

	$spans = $xmlReader->getAttribute(self::XML_ATTRIBUTE_SPANS); // returns '1:5' for instance
	if (null !== $spans) {
	[, $numberOfColumnsForRow] = explode(':', $spans);
	$numberOfColumnsForRow = (int) $numberOfColumnsForRow;
	}

Some xlsx files can be read normally by Office Excel, but openspout will return an empty string '' here $spans, causing it to pass the if judgment and then report an error “Undefined array key 1”.

https://github.com/box/spout/blob/84596668410bea89d21aa9867b91e1550e359329/src/Spout/Reader/XLSX/RowIterator.php#L258-L262

If the judgment method of the original version of Spout is modified, the file can be read normally.

Create release of v3 for PHP 8.2

Could you create a new release of v3 that allow the use of PHP 8.2
It's highly likely that v3 will just work on PHP 8.2

That would fix the tests of my dependant package: https://github.com/spatie/simple-excel

DateHelper::toExcel() doesn't handle minutes and seconds correctly

<?php

$dates = [
    "2022-03-22 13:00:00",
    "2022-03-22 13:05:00",
    "2022-03-22 13:10:00",
    "2022-03-22 13:12:14",
];

foreach ($dates as $date) {
    echo "{$date}: " . DateHelper::toExcel(new \DateTimeImmutable($date)) . PHP_EOL;
}

Output:

2022-03-22 13:00:00: 44642.541666667
2022-03-22 13:05:00: 44642.541666667
2022-03-22 13:10:00: 44642.541666667
2022-03-22 13:12:14: 44642.541666667

The outputted values are identical, there is no difference to account for the minutes or seconds.

This has the side-effect that any DateTimeImmutable passed as the value of a Cell is rendered incorrectly in Excel. I only noticed this because I added custom formatting that would display the minutes.

How to merge cell based on sheet?

The options only take effect on first sheet only. When i move into the loop, the excel became corrupted. How to achieve this?

$writer = WriterFactory::createFromFile($path);
$writer->getOptions()->mergeCells(0, 1, 3, 1);
$writer->openToFile($path);

for loop {
   // add row
   // $writer->addNewSheetAndMakeItCurrent();
}

$writer->close();

Unable to add options to ReaderFactory::createFromFile

Hi,
it looks like it is currently impossible to add options to the createFromFile function:

$options = new Options();
$options->SHOULD_PRESERVE_EMPTY_ROWS = true;
$reader = ReaderFactory::createFromFile($importPath);
$reader->open($importPath);

Is there another way of achieving this?

Thanks!

ODS throws `IteratorNotRewindableException`

I have this code

$reader = ReaderEntityFactory::createReaderFromFile($this->path);
        $reader->open($this->path);
        foreach ($reader->getSheetIterator() as $sheet) {
            if ($cell_a1 == $this->getCellA1($sheet)) {
                $rowNum = 0;
                foreach ($sheet->getRowIterator() as $row) {
                    $columnNum = 0;
                    foreach ($row->getCells() as $cell) {
                        $value = $this->parseValue($cell->getValue());
                        if (!is_null($value)) {
                            $data[$rowNum][$columnNum] = $value;
                        }
                        $columnNum++;
                    }
                    $rowNum++;
                }
            }
        }

With a xlsx file it works fine. With a ods it throws a OpenSpout\Reader\Exception\IteratorNotRewindableException

Both files are the same. Only the file format is different.

I dont understand it.

feat: allow reading from and writing to php resource

Hi!

Thanks for creating this fork. Migration was easy and with the types and additional features its a huge improvement over the original codebase.

I have a feature request; Is it possible to allow reading from and writing to a php resource? At least for CSV, I'm not sure if it's possible for XLSX (and other formats) as well.

I noticed the current CSV Reader already converts the filePath to a resource. Maybe it's as easy as changing

protected function openReader(string $filePath): void

to

protected function openReader(string | resource $filePath): void

and check which parameter type has passed?

As for the CSV Writer, I'm not sure if it is the same case?

final public function openToFile($outputFilePath): void

to

final public function openToFile(string | resource $outputFilePath): void

internal error: Huge input lookup

When reading a large excel file I am getting the warnings/error below. Any idea what would cause this?

Severity: Warning --> XMLReader::expand(): zip:///home/dev/tmp/usertemp645567ee73f18/2023.04_US_Tax_Summary_Extract_05.02.2023.xlsx#xl/worksheets/sheet1.xml:2: parser error : internal error: Huge input lookup /vendor/openspout/openspout/src/Reader/XLSX/RowIterator.php 322
Severity: Warning --> XMLReader::expand(): 0494</v></c><c r="G23314" t="s"><v>7313</v></c><c r="H23314" t="s"><v>56</v></c> /vendor/openspout/openspout/src/Reader/XLSX/RowIterator.php 322
Severity: Warning --> XMLReader::expand():                                                                                ^ /vendor/openspout/openspout/src/Reader/XLSX/RowIterator.php 322
Severity: Warning --> XMLReader::expand(): An Error Occurred while expanding /vendor/openspout/openspout/src/Reader/XLSX/RowIterator.php 322
Severity: error --> Exception: OpenSpout\Reader\XLSX\RowIterator::getCell(): Argument #1 ($node) must be of type DOMElement, bool given, called in /vendor/openspout/openspout/src/Reader/XLSX/RowIterator.php on line 323 /home/dev/public_html/application/vendor/openspout/openspout/src/Reader/XLSX/RowIterator.php 404

Skipping rows while reading

I tested a few files but it looks like the reader doesn't iterate all the rows.

I noticed only 20k rows were iterated from 100k rows.

With box/spout it works fine.

Is there any option documentation I'm missing?

XLSX date format incorrect when including escaped characters

I have an XLSX file with dates formatted:
yyyy-mm-dd\Thh:mm:ss

This is incorrectly converted to a PHP date format:
Y-m-dtH:i:s

The lowercase t should be maintained as an escaped uppercase T, like this:
\T

The code producing this comes from here:

openspout/src/Reader/XLSX/Helper/DateFormatHelper.php

Line 82 in e236da4

$transformedPart = strtolower($dateFormatPart);

openspout/src/Reader/XLSX/Helper/DateFormatHelper.php

Line 85 in e236da4

$transformedPart = str_replace('\\', '', $transformedPart);

Handling of time cells is not consistent for different readers

When reading a time cell in an ODS file, a DateIntervalCell is used. When reading a XLSX file, a DateTimeCell is used (and I don't know which date it has).
This makes it hard to write code that can support both XLSX and ODS as it forces to handle different cell types (both for reading and for writing). It would be great to have a common abstraction between those readers and writers to make the code common.

How to set column width for certain sheet only

Hello,

Is there any options to set column width for certain sheet only ?

MergeCells does not give expected result

Hi,

I want to merge cells in row 2, from column A : O.

In my code, I have:

use OpenSpout\Common\Entity\Row;
use OpenSpout\Writer\XLSX\Options;
use OpenSpout\Writer\XLSX\Writer;

$options = new Options();
$writer = new Writer($options);

$writer->addRow(Row::fromValues(['']));
$writer->addRow(Row::fromValues(['My-text']));

$options->mergeCells(
    0,
    2,
    15,
    2,
    $writer->getCurrentSheet()->getIndex(),
);

$writer->close();

Expected result:

Actual result:

I want to merge 15 cells horizontally, but it looks like it also merges 15 rows vertically?

Generated XLSX generated from Windows system has content issues

I'm evaluating Openspout to replace PHPExcel on a PHP 8.0.* system. I am creating a very simple test spreadsheet that has text in one row/one cell. The file generates and downloads correctly on the Ubuntu server but the same file generated from my Windows 11 development system has issues with some of the content when loaded. I can tell it to repair and it then appears to load correctly but this is obviously not ideal.

The code I use to generate the file is:

use OpenSpout\Writer\XLSX\Writer;
use OpenSpout\Common\Entity\Cell;
use OpenSpout\Common\Entity\Row;

$writer = new Writer();
$writer->openToBrowser('test.xlsx'); // stream data directly to the browser
$titleRow = new Row([
  Cell::fromValue('First Cell')
]);
$writer->addRow($titleRow);
$writer->close();

I have attached 2 files (labeled appropriately): a "good" version generated on the server and a "bad" version generated locally under Windows 11.

FWIW, I had to override the temp folder using sys_temp_dir in php.ini to even get to the point where it would work under Windows. Kept having issues with the c:\windows\temp folder.

Help?

test_bad.xlsx
test_good.xlsx

[Question] Set value to specific cell

How to set value to a specific cell on the sheet?

$sheet->setCellValue("A17", 'Total Gross Sales ($)');
$sheet->setCellValue("A18", '156.50');

I could not find anything in docs.

Reading and writing cell-comments

Hi,

First of all, thanks for starting this fork and the fact that the code is still actively maintained! My question: are there any plans to implement cell comments (reading and writing) for XLSX files? I am working on a project that has heavy dependencies on PHPOffice and I am rewriting it to use spout for the obvious benefits of speed and memory usage.

I did however notice that cell-comments are not supported, is that something that might be implemented in the future? I can also try to implement it myself and create a PR, but maybe somebody could give me some pointers on what the cleanest way would be to implement this?