https://blog.csdn.net/lovelessdream/article/details/88424029
https://blog.csdn.net/supergao222/article/details/78629965

CSV 的配置

按你的需求配置 CSV 读取器和写入器:

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

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

另外,如果你需要读取非UTF-8 文件,可以这样指定文件编码:

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

默认地, 写入器会以 UTF-8 生成 CSV 文件,并带有BOM。不想要 BOM:

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

$writer = WriterFactory::create(Type::CSV);
$writer->setShouldAddBOM(false);

XLSX 和 ODS 的配置

新建表

It is possible to change the behavior of the writers when the maximum number of rows (1,048,576) has been written in the current sheet. By default, a new sheet is automatically created so that writing can keep going but that may not always be preferable.

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 requires 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 BoxSpoutWriterWriterFactory;
use BoxSpoutCommonType;

$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 de-duplicate strings when using shared strings. It is nevertheless possible to use this mode.

use BoxSpoutWriterWriterFactory;
use BoxSpoutCommonType;

$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 BoxSpoutReaderReaderFactory;
use BoxSpoutCommonType;

$reader = ReaderFactory::create(Type::XLSX);
$reader->setShouldFormatDates(false); // default value
$reader->setShouldFormatDates(true); // will return formatted dates
Styling rows
It is possible to apply some formatting options to a row. Spout supports fonts, background, borders as well as alignment styles.

use BoxSpoutCommonType;
use BoxSpoutWriterWriterFactory;
use BoxSpoutWriterStyleStyleBuilder;
use BoxSpoutWriterStyleColor;

$style = (new StyleBuilder())

       ->setFontBold()
       ->setFontSize(15)
       ->setFontColor(Color::BLUE)
       ->setShouldWrapText()
       ->setBackgroundColor(Color::YELLOW)
       ->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();
Adding borders to a row requires a Border object.

use BoxSpoutCommonType;
use BoxSpoutWriterStyleBorder;
use BoxSpoutWriterStyleBorderBuilder;
use BoxSpoutWriterStyleColor;
use BoxSpoutWriterStyleStyleBuilder;
use BoxSpoutWriterWriterFactory;

$border = (new BorderBuilder())

->setBorderBottom(Color::GREEN, Border::WIDTH_THIN, Border::STYLE_DASHED)
->build();

$style = (new StyleBuilder())

->setBorder($border)
->build();

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

$writer->addRowWithStyle(['Border Bottom Green Thin Dashed'], $style);

$writer->close();
Spout will use a default style for all created rows. This style can be overridden this way:

$defaultStyle = (new StyleBuilder())

            ->setFontName('Arial')
            ->setFontSize(11)
            ->build();

$writer = WriterFactory::create(Type::XLSX);
$writer->setDefaultRowStyle($defaultStyle)

   ->openToFile($filePath);

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(true|false)
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.

Finally, it is possible to know which sheet was active when the spreadsheet was last saved. This can be useful if you are only interested in processing the one sheet that was last focused.

foreach ($reader->getSheetIterator() as $sheet) {

// only process data for the active sheet
if ($sheet->isActive()) {
    // do something...
}

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

use BoxSpoutWriterWriterFactory;
use BoxSpoutCommonType;

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

   ->setShouldUseInlineStrings(true)
   ->openToFile($filePath)
   ->addRow($headerRow)
   ->addRows($dataRows)
   ->close();

标签: none