This PR introduces a Row object in all writers and implements Cell styling for #182.

I left the public writer methods

WriterAbstract::addRow
WriterAbstract::addRows
WriterAbstract::addRowWithStyle
WriterAbstract::addRowsWithStyle

intact - so all the existing tests can pass. However the signatured changed. This can be considered a BC Break. Because the legacy data arrays are transformed into Row objects - it is hacky. When we should drop the support for arrays - it should get better.

• A Row object holds the cells and styles of a row.
• From that point on (after the Writer) - all signatures like(array $dataRows, $style) have been refactored to (Row $row).
• With the newly created Row object the *WithStyle mehods can be marked as @deprecated or can be deleted.
• This also made managing the row style in the Writer superfluous.
• I added a WriterAbstract::withRow method that accepts a closure.
• Registering the styles is now done right before a Cell is written to the output.
• The default style is merged into the row style - and the row style is merged into the cell style.
• The tests pass - more can follow when we agree that this is the "right" way to proceed.

Example:

use Box\Spout\Common\Type;
use Box\Spout\Writer\Common\Creator\Style\BorderBuilder;
use Box\Spout\Writer\Common\Creator\Style\StyleBuilder;
use Box\Spout\Writer\Common\Entity\Cell;
use Box\Spout\Writer\Common\Entity\Row;
use Box\Spout\Writer\Common\Entity\Style\Color;
use Box\Spout\Writer\WriterFactory;


$defaultStyleBorderBottom = (new StyleBuilder())
    ->setBorder((new BorderBuilder())->setBorderBottom()->build())
    ->build();

$rowStyleItalicBold = (new StyleBuilder())
    ->setFontItalic()
    ->setFontBold()
    ->build();

$greenLight = (new StyleBuilder())
    ->setBackgroundColor(Color::GREEN)
    ->build();

$yellowLight = (new StyleBuilder())
    ->setBackgroundColor(Color::YELLOW)
    ->build();

$redLight = (new StyleBuilder())
    ->setBackgroundColor(Color::RED)
    ->build();

/** @var \Box\Spout\Writer\XLSX\Writer $writer */
$writer = WriterFactory::create(Type::XLSX);
$writer->setDefaultRowStyle($defaultStyleBorderBottom);
$writer->openToFile(__DIR__ . '/test.xlsx');

$row = new Row();
$row
    ->addCell(new Cell('G', $greenLight))
    ->addCell(new Cell('Y', $yellowLight))
    ->addCell(new Cell('R', $redLight))
    ->setStyle($rowStyleItalicBold);

$writer->addRow($row);

$writer->close();

This yields:

or:

$writer->withRow(function(Row $row) use ($greenLight, $yellowLight, $redLight, $rowStyleItalicBold) {
    return $row
        ->addCell(new Cell('G', $greenLight))
        ->addCell(new Cell('Y', $yellowLight))
        ->addCell(new Cell('R', $redLight))
        ->setStyle($rowStyleItalicBold);
});

hello, i get an error when try to read xlsx. i try the code to read xlsx like in basic example but i get this error

Warning: XMLReader::open(zip://Test/pegawaibaru.XLSX#xl/sharedStrings.xml): failed to open stream: operation failed in C:\xampp\htdocs\web pln\libs\Spout\Reader\XLSX\Helper\SharedStringsHelper.php on line 86

Warning: XMLReader::open(): Unable to open source data in C:\xampp\htdocs\web pln\libs\Spout\Reader\XLSX\Helper\SharedStringsHelper.php on line 86

Fatal error: Uncaught exception 'Box\Spout\Common\Exception\IOException' with message 'Could not open Test/pegawaibaru.XLSX for reading! (Could not open "xl/sharedStrings.xml".)' in C:\xampp\htdocs\web pln\libs\Spout\Reader\AbstractReader.php:105 Stack trace: #0 C:\xampp\htdocs\web pln\atesting.php(64): Box\Spout\Reader\AbstractReader->open('Test/pegawaibar...') #1 {main} thrown in C:\xampp\htdocs\web pln\libs\Spout\Reader\AbstractReader.php on line 105

i use spout version 2.5.0 and php version 5.6.3 when i replace spout with version 2.4.4 it works, it takes 235 sec to read 7323 rows and 46 col. what should i do to fix it version 2.5.0?

I just updated my php to 5.6.25 and my Excel to 15.0.4849.1003 and now my spout box generate xlsx files don't open. I keep getting a "We found a problem with some content in 'Filename'. Do you want us to try to recover as much as we can? If you trust the source of this workbook click Yes." popup message... and when I click Yes... I get a "The file is corrupt and cannot be opened" message. Is there anything I can do to enable a trace to help resolve the issue. Or if would you like a copy of the generated file I can provide that.

Thanks

When I do some quick manual test, the v3 branch seems a lot slower? Or am I missing something obvious? In a quick test (50.000 rows with 20 columns with 20 char strings), v2 is about 20 secs while v3 is 30 seconds. That is a 50% increase..

v2:

$writer = WriterFactory::create(Type::XLSX);
$writer->openToFile('output/spout2.xlsx');
$writer->addRows($data);
$writer->close();

v3:

$writer = WriterEntityFactory::createWriter(Type::XLSX);
$writer->openToFile('output/spout3.xlsx');
foreach ($data as $row) {
    $writer->addRow(WriterEntityFactory::createRowFromArray($row));
}
$writer->close();

Probably because you have to create Row objects for each row (which create Cell objects for each key)? So in this testcase 50.000 array_maps creating 1 million objects..

Edit: Full files here: https://gist.github.com/barryvdh/08576d8b8d7a8d171338737851e7ccf4

Hello,

I just create .xlsx files with this code:

        $writer = WriterFactory::create( Type::XLSX );

        $writer->openToBrowser( $filename );

        // Headers
        $writer->addRow( array( 'Order #', 'Customer name', 'Total ordered', 'Date' ) );

        // Then a foreach
        $writer->addRow( array( (int) 0000, 'Customer name', (float) 23.12, '20-01-2016' ) );
        $writer->close()

And thats it. After opening this file in Excel 2013 I get the message that the file is corrupted and needs to be repaired. Also the float 'Total ordered' is not a float in Excel at all.

How can I prevent the needs to be repaired message of every file?

Thanks!

Quick fix for issue #205, #182 and #151; Also added a feature being able register the style to the sheet without having to call addRowWithStyle (required to add it to the cell as a single style)

Edit: I know it's a dirty fix. Will update this to tidy it up and support default format by using constants but would like this to be in the master so it's available to everyone.

Edit 2: For now its only for XLSX, since I dont have OpenOffice available atm

While reading excel sheet we are having a weird issue. We get the excel file from the external system. It runs fine if run unedited but throws following exception when we edit and save the same file. Even if we just add extra space or delete a sheet, the file throws the same exception.

Shared string not found for index: 1432

0 /opt/lampp/htdocs/mr/app/Vendor/Box/Spout/Reader/XLSX.php(247): Box\Spout\Reader\Helper\XLSX\SharedStringsHelper->getStringAtIndex(1432)

1 /opt/lampp/htdocs/mr/app/Vendor/Box/Spout/Reader/AbstractReader.php(131): Box\Spout\Reader\XLSX->read()

It displays random value which is not even in the current sheet. Note that we tested just by running the sample code provided under "How to read a XLSX file?" in documentation.

Any idea what may be going wrong?

windows wouldnt open paths like:'xl/sharedStrings.xml', for me need to be 'xl\sharedStrings.xml' otherwise it will drop:

XMLReader::open(zip://...20150506052548.xlsx#xl/sharedStrings.xml): failed to open stream: operation failed in ...\vendor\box\spout\src\Spout\Reader\Helper\XLSX\SharedStringsHelper.php on line 97

…fields, then a double-quote appearing inside a field must be escaped by preceding it with another double quote".

So we are changing the calls to fgetcsv and fputcsv on GlobalFunctionsHelper in order to send the Escape Character as a parameter. To be RFC compliant, we always send the same character as the Field Enclosure character.

Hi team!

Im working with huge files, will be great if we can set a "range" to read xlsx/csv/ods rows and split our processes in batches, I think that in current version is not possible do it? Do you intend to add this improvement in the future? I can pay for it...

Thanks!!

Hi, I came across this feature and found out that calling the method with the boolean argument gives opposite result. Meaning: $reader->setShouldFormatDates(false); // Formatting is enabled $reader->setShouldFormatDates(true); // Formatting is disabled

This misinterpretation is also documented here.

I have an excel file I'm trying to loop through. So far, I'm able to navigate to the worksheet I have to work on. I'm currently trying to loop through the data rows I have, and so far, this works for me:

    foreach ($sheet->getRowIterator() as $row) {
        $cells = $row->getCells();
        foreach ($cells as $cell){
            $cell_value = $cell->getValue();
            echo "Cell Value = $cell_value<br>\n";
        }
    }

For the most part, this works a treat because most of the values are strings or numbers.

However, I've noticed that it doesn't work for date.

For example, I used print_r on the $cells variable just to double check and here's what I got:

    [4] => Box\Spout\Common\Entity\Cell Object
        (
            [value:protected] => DateTime Object
                (
                    [date] => 1963-05-13 00:00:00.000000
                    [timezone_type] => 3
                    [timezone] => UTC
                )
         }

The value is being read properly but as opposed to the other data rows I have, the value is a datetime object and not a string, and this presents an error.

I saw the setShouldFormatDate property in the documentation. While I'm able to use this with getValue() to get the formatted value in Excel (usually a dd-MMM-yy format like 11 APR 22), I'd like to be able to get this in the yyyy-mm-dd format.

Is there a way to do this within the library? Or do I just get the formatted value and then use PHP's built in function to convert the formatted date to my desired format?

Thank you.

If I try to write to a compressed CSV file, then I get an exception using the file wrapper compress.zlib://

I think it has to do with the seekable file mode used here: $this->filePointer = $this->globalFunctionsHelper->fopen($this->outputFilePath, 'wb+');

Simply removing the + from the file mode seems to solve the problem but I'm not sure if box/spout needs it somewhere for something.

B.t.w. this is the exception and errors I get when I open a file with a compress.zlib wrapper using the latest v2 release:

PHP Warning:  fopen(compress.zlib://art.csv.gz): failed to open stream: operation failed in lib/box/spout/src/Spout/Common/Helper/GlobalFunctionsHelper.php on line 25
PHP Fatal error:  Uncaught Box\Spout\Common\Exception\IOException: File pointer has not be opened in lib/box/spout/src/Spout/Writer/AbstractWriter.php:175

Hello,

I am looking for format date cell on export XLSX but i have ### in my cell ( with a good date formatting: filters and cell content are ok, it's just a wrong cell text )

I generate my cell like this:

public function formatDate(\DateTime $date)
    {
        $format = $this->localeFormat->getYearMonthDayFormat();

        return WriterEntityFactory::createCell(Date::PHPToExcel($date), (new StyleBuilder())->setFormat('dd/mm/YYYY')->build());
    }

I also tried this date format: m/d/yy h:mm and it's didn't working.

Thanks

I'm using version 3.3.0. I experienced this exception and I've seen it reported in another ticket #713 where someone tried to pass an array as value to a cell entity: "Trying to add a value with an unsupported type: NULL"

The wrong type is reported because

Box\Spout\Common\Entity->getValue() has this line: return !$this->isError() ? $this->value : null;

and Box\Spout\Writer\XLSX\Manager->getCellXML() throws the exception doing this: throw new InvalidArgumentException('Trying to add a value with an unsupported type: ' . \gettype($cell->getValue()));

so of course it'll always report NULL as type.

So perhaps the cell's getValue() should return the actual value even if it is an unsupported type, but everything reading the cell should check isError() first. That's one way. Another way would be to to create another version of getValue() by another name (e.g. getActualValue()) and use that in the exception message.

That'll save users a lot of confusion.

Some return types have already been added here https://github.com/box/spout/commit/e75f6f73012b81fd5fee6107d0af9e86c458448e to get rid of the deprecated warnings in PHP 8.1. This PR should fix the remaining ones.

Fixes https://github.com/box/spout/issues/846