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(12345444,$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();

部分文档内容

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(true

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!

Date/Time formatting

When reading a spreadsheet containing dates or times, Spout returns the values by default as DateTime objects.
It is possible to change this behavior and have a formatted date returned instead (e.g. “2016-11-29 1:22 AM”). The format of the date corresponds to what is specified in the spreadsheet.

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