box/spout使用记录

spout 升级命名3.0后变动较多,且官网挂了,记录下文档

包地址 https://packagist.org/packages/box/spout github地址 https://github.com/box/spout composer地址 composer require box/spout 

读写

//包空间有变动 ,只保留了部分示例性代码 use Box\Spout\Reader\Common\Creator\ReaderEntityFactory; use Box\Spout\Writer\Common\Creator\WriterEntityFactory; / * @param $filename 文件名 string * @param $admin 用户名 string * @param $headerValues 头标题 [] * @param $cellValues 表数据 [[],[]] * @return mixed */ public function createCSV($filename,$admin,$headerValues,$cellValues) { list($savePath,$salPath) = $this->createFile($filename,$admin); $writer = WriterEntityFactory::createCSVWriter(); $writer->openToFile($savePath); WriterEntityFactory::createWriterFromFile('.csv'); //这个单元格有自己的样式 // $cell1 = WriterEntityFactory::createCell('foowahhh',$cellStyle1); // $cell2 = WriterEntityFactory::createCell(,$cellStyle2); // $row2 = WriterEntityFactory::createRow([$cell1,$cell2]); $header = WriterEntityFactory::createRowFromArray($headerValues); $writer->addRows([$header]); foreach($cellValuesas $value){ $row = WriterEntityFactory::createRowFromArray($value); $writer->addRows([$row]); } $writer->close(); return $salPath; } public function createFile($fileName,$admin_nickname) { //返回相对路径和绝对路径 return [$savePath,$salPath]; } public function createXLSX() { $writer = WriterEntityFactory::createXLSXWriter(); } public function createODS() { $writer = WriterEntityFactory::createODSWriter(); } public function readCSV() { $reader = ReaderEntityFactory::createCSVReader(); } public function readXLSX() { $reader = ReaderEntityFactory::createXLSXReader(); } public function readODS() { $reader = ReaderEntityFactory::createODSReader(); } 

示例-样式-styles

文档 https://github.com/box/spout/tree/master/docs

//包空间有变动 use Box\Spout\Writer\Common\Creator\Style\StyleBuilder use Box\Spout\Writer\Common\Creator\Style\BorderBuilder use Box\Spout\Common\Entity\Style\Border use Box\Spout\Common\Entity\Style\BorderPart use Box\Spout\Common\Entity\Style\Color use Box\Spout\Common\Entity\Style\Style //支持字体，背景，边框以及对齐样式。 $cellStyle1 = (new StyleBuilder()) ->setFontBold() ->setFontSize(15) ->setFontColor(Color::BLUE) ->setShouldWrapText() ->setBackgroundColor(Color::YELLOW) ->build(); //向行添加边框需要一个Border对象 $border = (new BorderBuilder()) ->setBorderBottom(Color::GREEN, Border::WIDTH_THIN, Border::STYLE_DASHED) ->build(); $style = (new StyleBuilder()) ->setBorder($border) ->build(); //Spout将为所有创建的行使用默认样式。可以通过以下方式覆盖此样式： $defaultStyle = (new StyleBuilder()) ->setFontName('Arial') ->setFontSize(11) ->build(); 

部分文档内容

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 

Date/Time formatting

use Box\Spout\Reader\ReaderFactory; use Box\Spout\Common\Type; $reader = ReaderFactory::create(Type::XLSX); $reader->setShouldFormatDates(false); // default value $reader->setShouldFormatDates(true); // will return formatted dates 

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).