Memperkenalkan Spout Alternatif PHPExcel yang lambat

Assalamu'alaikum, Kali ini om mugen mau berbagi trik buat om - om yang bergelut dibidang IT terus om om ada masalah tentang bikin report ke Excel dari Database menggunakan PHP,

kebayangkan berapa jam om om harus nunggu sampe keluar report nya? keburu kiamat deh, hehe

nah berikut ada suatu Class besutan box.com namanya "SPOUT : Read and write spreadsheet files (CSV, XLSX and ODS), in a fast and scalable way ".

silahkan Download terlebih dahulu dile dibawah ini :

Spout : http://adf.ly/1WpBr5

Composser : http://adf.ly/1WpC0O

bisa diartiin sendirikan artinya apa hehe , nah berikut kita lihat kecepatan nya .

Type	Action	2,000 rows (6,000 cells)	200,000 rows (600,000 cells)	2,000,000 rows (6,000,000 cells)
CSV	Read	< 1 second	4 seconds	2-3 minutes
	Write	< 1 second	2 seconds	2-3 minutes
XLSX	Read inline strings	< 1 second	35-40 seconds	18-20 minutes
	Read shared strings	1 second	1-2 minutes	35-40 minutes
	Write	1 second	20-25 seconds	8-10 minutes
ODS	Read	1 second	1-2 minutes	5-6 minutes
	Write	< 1 second	35-40 seconds	5-6 minutes

nah class ini bisa digunakan karena sifatnya OpenSource jadi ya kalo sedikit bingung sih wajar ya, hehe

terus gmna pakainya , begini ya .

Spout

Spout is a PHP library to read and write spreadsheet files (CSV, XLSX and ODS), in a fast and scalable way. Contrary to other file readers or writers, it is capable of processing very large files while keeping the memory usage really low (less than 10MB).

Join the community and come discuss about Spout: 

Installation

Composer (recommended)

Spout can be installed directly from Composer.

Run the following command:

$ composer require box/spout

Manual installation

If you can't use Composer, no worries! You can still install Spout manually.

Before starting, make sure your system meets the requirements.

1. Download the source code from the Releases page
2. Extract the downloaded content into your project.
3. Add this code to the top controller (index.php) or wherever it may be more appropriate:

require_once '[PATH/TO]/src/Spout/Autoloader/autoload.php'; // don't forget to change the path!

Requirements

• PHP version 5.4.0 or higher
• PHP extension php_zip enabled
• PHP extension php_xmlreader enabled
• PHP extension php_simplexml enabled

Basic usage

Reader

Regardless of the file type, the interface to read a file is always the same:

use Box\Spout\Reader\ReaderFactory;
use Box\Spout\Common\Type;

$reader = ReaderFactory::create(Type::XLSX); // for XLSX files
//$reader = ReaderFactory::create(Type::CSV); // for CSV files
//$reader = ReaderFactory::create(Type::ODS); // for ODS files

$reader->open($filePath);

foreach ($reader->getSheetIterator() as $sheet) {
    foreach ($sheet->getRowIterator() as $row) {
        // do stuff with the row
    }
}

$reader->close();

If there are multiple sheets in the file, the reader will read all of them sequentially.

Writer

As with the reader, there is one common interface to write data to a file:

use Box\Spout\Writer\WriterFactory;
use Box\Spout\Common\Type;

$writer = WriterFactory::create(Type::XLSX); // for XLSX files
//$writer = WriterFactory::create(Type::CSV); // for CSV files
//$writer = WriterFactory::create(Type::ODS); // for ODS files

$writer->openToFile($filePath); // write data to a file or to a PHP stream
//$writer->openToBrowser($fileName); // stream data directly to the browser

$writer->addRow($singleRow); // add a row at a time
$writer->addRows($multipleRows); // add multiple rows at a time

$writer->close();

For XLSX and ODS files, the number of rows per sheet is limited to 1,048,576. By default, once this limit is reached, the writer will automatically create a new sheet and continue writing data into it.

Advanced usage

If you are looking for how to perform some common, more advanced tasks with Spout, please take a look at the Wiki. It contains code snippets, ready to be used.

Configuring the CSV reader and writer

It is possible to configure both the CSV reader and writer to specify the field separator as well as the field enclosure:

use Box\Spout\Reader\ReaderFactory;
use Box\Spout\Common\Type;

$reader = ReaderFactory::create(Type::CSV);
$reader->setFieldDelimiter('|');
$reader->setFieldEnclosure('@');

Additionally, if you need to read non UTF-8 files, you can specify the encoding of your file this way:

$reader->setEncoding('UTF-16LE');

The writer always generate CSV files encoded in UTF-8, with a BOM.

Configuring the XLSX and ODS writers

Row styling

It is possible to apply some formatting options to a row. Spout supports fonts as well as alignment styles.

use Box\Spout\Common\Type;
use Box\Spout\Writer\WriterFactory;
use Box\Spout\Writer\Style\StyleBuilder;
use Box\Spout\Writer\Style\Color;

$style = (new StyleBuilder())
           ->setFontBold()
           ->setFontSize(15)
           ->setFontColor(Color::BLUE)
           ->setShouldWrapText()
           ->build();

$writer = WriterFactory::create(Type::XLSX);
$writer->openToFile($filePath);

$writer->addRowWithStyle($singleRow, $style); // style will only be applied to this row
$writer->addRow($otherSingleRow); // no style will be applied
$writer->addRowsWithStyle($multipleRows, $style); // style will be applied to all given rows

$writer->close();

Unfortunately, Spout does not support all the possible formatting options yet. But you can find the most important ones:

Category	Property	API
Font	Bold	StyleBuilder::setFontBold()
	Italic	StyleBuilder::setFontItalic()
	Underline	StyleBuilder::setFontUnderline()
	Strikethrough	StyleBuilder::setFontStrikethrough()
	Font name	StyleBuilder::setFontName('Arial')
	Font size	StyleBuilder::setFontSize(14)
	Font color	StyleBuilder::setFontColor(Color::BLUE) StyleBuilder::setFontColor(Color::rgb(0, 128, 255))
Alignment	Wrap text	StyleBuilder::setShouldWrapText()

New sheet creation

It is also possible to change the behavior of the writer when the maximum number of rows (1,048,576) have been written in the current sheet:

use Box\Spout\Writer\WriterFactory;
use Box\Spout\Common\Type;

$writer = WriterFactory::create(Type::ODS);
$writer->setShouldCreateNewSheetsAutomatically(true); // default value
$writer->setShouldCreateNewSheetsAutomatically(false); // will stop writing new data when limit is reached

Using custom temporary folder

Processing XLSX and ODS files require temporary files to be created. By default, Spout will use the system default temporary folder (as returned by sys_get_temp_dir()). It is possible to override this by explicitly setting it on the reader or writer:

use Box\Spout\Writer\WriterFactory;
use Box\Spout\Common\Type;

$writer = WriterFactory::create(Type::XLSX);
$writer->setTempFolder($customTempFolderPath);

Strings storage (XLSX writer)

XLSX files support different ways to store the string values:

• Shared strings are meant to optimize file size by separating strings from the sheet representation and ignoring strings duplicates (if a string is used three times, only one string will be stored)
• Inline strings are less optimized (as duplicate strings are all stored) but is faster to process

In order to keep the memory usage really low, Spout does not optimize strings when using shared strings. It is nevertheless possible to use this mode.

use Box\Spout\Writer\WriterFactory;
use Box\Spout\Common\Type;

$writer = WriterFactory::create(Type::XLSX);
$writer->setShouldUseInlineStrings(true); // default (and recommended) value
$writer->setShouldUseInlineStrings(false); // will use shared strings

Note on Apple Numbers and iOS support

Apple's products (Numbers and the iOS previewer) don't support inline strings and display empty cells instead. Therefore, if these platforms need to be supported, make sure to use shared strings!

Playing with sheets

When creating a XLSX or ODS file, it is possible to control which sheet the data will be written into. At any time, you can retrieve or set the current sheet:

$firstSheet = $writer->getCurrentSheet();
$writer->addRow($rowForSheet1); // writes the row to the first sheet

$newSheet = $writer->addNewSheetAndMakeItCurrent();
$writer->addRow($rowForSheet2); // writes the row to the new sheet

$writer->setCurrentSheet($firstSheet);
$writer->addRow($anotherRowForSheet1); // append the row to the first sheet

It is also possible to retrieve all the sheets currently created:

$sheets = $writer->getSheets();

If you rely on the sheet's name in your application, you can access it and customize it this way:

// Accessing the sheet name when reading
foreach ($reader->getSheetIterator() as $sheet) {
    $sheetName = $sheet->getName();
}

// Accessing the sheet name when writing
$sheet = $writer->getCurrentSheet();
$sheetName = $sheet->getName();

// Customizing the sheet name when writing
$sheet = $writer->getCurrentSheet();
$sheet->setName('My custom name');

Please note that Excel has some restrictions on the sheet's name:

• it must not be blank
• it must not exceed 31 characters
• it must not contain these characters: \ / ? * : [ or ]
• it must not start or end with a single quote
• it must be unique

Handling these restrictions is the developer's responsibility. Spout does not try to automatically change the sheet's name, as one may rely on this name to be exactly what was passed in.

Fluent interface

Because fluent interfaces are great, you can use them with Spout:

use Box\Spout\Writer\WriterFactory;
use Box\Spout\Common\Type;

$writer = WriterFactory::create(Type::XLSX);
$writer->setTempFolder($customTempFolderPath)
       ->setShouldUseInlineStrings(true)
       ->openToFile($filePath)
       ->addRow($headerRow)
       ->addRows($dataRows)
       ->close();

Running tests

On the master branch, only unit and functional tests are included. The performance tests require very large files and have been excluded. If you just want to check that everything is working as expected, executing the tests of the master branch is enough.

If you want to run performance tests, you will need to checkout the perf-tests branch. Multiple test suites can then be run, depending on the expected output:

• phpunit - runs the whole test suite (unit + functional + performance tests)
• phpunit --exclude-group perf-tests - only runs the unit and functional tests
• phpunit --group perf-tests - only runs the performance tests

For information, the performance tests take about 30 minutes to run (processing 1 million rows files is not a quick thing).

Performance tests status: 

Terimakasih Gan/ Sist Undah sembat Baca Artikel tentang :
Judul: Memperkenalkan Spout Alternatif PHPExcel yang lambat
Rating: 100% based on 99998 ratings. 5 user reviews.
Ditulis Oleh Tarikh Agustia
Semoga informasi mengenai Memperkenalkan Spout Alternatif PHPExcel yang lambat bisa memberikan manfaat bagi Anda. Jangan lupa Komentar Anda sangat dibutuhkan, di bawah ini.