| [ Index ] |
PHP Cross Reference of Unnamed Project |
[Summary view] [Print] [Text view]
1 # Spout 2 3 [](https://packagist.org/packages/box/spout) 4 [](http://opensource.box.com/badges) 5 [](https://travis-ci.org/box/spout) 6 [](https://scrutinizer-ci.com/g/box/spout/?branch=master) 7 [](https://packagist.org/packages/box/spout) 8 [](https://packagist.org/packages/box/spout) 9 10 Spout is a PHP library to read and write spreadsheet files (CSV, XLSX and ODS), in a fast and scalable way. 11 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). 12 13 Join the community and come discuss about Spout: [](https://gitter.im/box/spout?utm_source=badge&utm_medium=badge&utm_campaign=pr-badge) 14 15 ## Installation 16 17 ### Composer (recommended) 18 19 Spout can be installed directly from [Composer](https://getcomposer.org/). 20 21 Run the following command: 22 ``` 23 $ composer require box/spout 24 ``` 25 26 ### Manual installation 27 28 If you can't use Composer, no worries! You can still install Spout manually. 29 30 > Before starting, make sure your system meets the [requirements](#requirements). 31 32 1. Download the source code from the [Releases page](https://github.com/box/spout/releases) 33 2. Extract the downloaded content into your project. 34 3. Add this code to the top controller (index.php) or wherever it may be more appropriate: 35 ```php 36 require_once '[PATH/TO]/src/Spout/Autoloader/autoload.php'; // don't forget to change the path! 37 ``` 38 39 40 ## Requirements 41 42 * PHP version 5.4.0 or higher 43 * PHP extension `php_zip` enabled 44 * PHP extension `php_xmlreader` enabled 45 * PHP extension `php_simplexml` enabled 46 47 48 ## Basic usage 49 50 ### Reader 51 52 Regardless of the file type, the interface to read a file is always the same: 53 54 ```php 55 use Box\Spout\Reader\ReaderFactory; 56 use Box\Spout\Common\Type; 57 58 $reader = ReaderFactory::create(Type::XLSX); // for XLSX files 59 //$reader = ReaderFactory::create(Type::CSV); // for CSV files 60 //$reader = ReaderFactory::create(Type::ODS); // for ODS files 61 62 $reader->open($filePath); 63 64 foreach ($reader->getSheetIterator() as $sheet) { 65 foreach ($sheet->getRowIterator() as $row) { 66 // do stuff with the row 67 } 68 } 69 70 $reader->close(); 71 ``` 72 73 If there are multiple sheets in the file, the reader will read all of them sequentially. 74 75 ### Writer 76 77 As with the reader, there is one common interface to write data to a file: 78 79 ```php 80 use Box\Spout\Writer\WriterFactory; 81 use Box\Spout\Common\Type; 82 83 $writer = WriterFactory::create(Type::XLSX); // for XLSX files 84 //$writer = WriterFactory::create(Type::CSV); // for CSV files 85 //$writer = WriterFactory::create(Type::ODS); // for ODS files 86 87 $writer->openToFile($filePath); // write data to a file or to a PHP stream 88 //$writer->openToBrowser($fileName); // stream data directly to the browser 89 90 $writer->addRow($singleRow); // add a row at a time 91 $writer->addRows($multipleRows); // add multiple rows at a time 92 93 $writer->close(); 94 ``` 95 96 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. 97 98 99 ## Advanced usage 100 101 If you are looking for how to perform some common, more advanced tasks with Spout, please take a look at the [Wiki](https://github.com/box/spout/wiki). It contains code snippets, ready to be used. 102 103 ### Configuring the CSV reader and writer 104 105 It is possible to configure both the CSV reader and writer to specify the field separator as well as the field enclosure: 106 ```php 107 use Box\Spout\Reader\ReaderFactory; 108 use Box\Spout\Common\Type; 109 110 $reader = ReaderFactory::create(Type::CSV); 111 $reader->setFieldDelimiter('|'); 112 $reader->setFieldEnclosure('@'); 113 $reader->setEndOfLineCharacter("\r"); 114 ``` 115 116 Additionally, if you need to read non UTF-8 files, you can specify the encoding of your file this way: 117 ```php 118 $reader->setEncoding('UTF-16LE'); 119 ``` 120 121 The writer always generate CSV files encoded in UTF-8, with a BOM. 122 123 124 ### Configuring the XLSX and ODS writers 125 126 #### Row styling 127 128 It is possible to apply some formatting options to a row. Spout supports fonts as well as alignment styles. 129 130 ```php 131 use Box\Spout\Common\Type; 132 use Box\Spout\Writer\WriterFactory; 133 use Box\Spout\Writer\Style\StyleBuilder; 134 use Box\Spout\Writer\Style\Color; 135 136 $style = (new StyleBuilder()) 137 ->setFontBold() 138 ->setFontSize(15) 139 ->setFontColor(Color::BLUE) 140 ->setShouldWrapText() 141 ->build(); 142 143 $writer = WriterFactory::create(Type::XLSX); 144 $writer->openToFile($filePath); 145 146 $writer->addRowWithStyle($singleRow, $style); // style will only be applied to this row 147 $writer->addRow($otherSingleRow); // no style will be applied 148 $writer->addRowsWithStyle($multipleRows, $style); // style will be applied to all given rows 149 150 $writer->close(); 151 ``` 152 153 Unfortunately, Spout does not support all the possible formatting options yet. But you can find the most important ones: 154 155 Category | Property | API 156 ----------|---------------|--------------------------------------- 157 Font | Bold | `StyleBuilder::setFontBold()` 158 | Italic | `StyleBuilder::setFontItalic()` 159 | Underline | `StyleBuilder::setFontUnderline()` 160 | Strikethrough | `StyleBuilder::setFontStrikethrough()` 161 | Font name | `StyleBuilder::setFontName('Arial')` 162 | Font size | `StyleBuilder::setFontSize(14)` 163 | Font color | `StyleBuilder::setFontColor(Color::BLUE)`<br>`StyleBuilder::setFontColor(Color::rgb(0, 128, 255))` 164 Alignment | Wrap text | `StyleBuilder::setShouldWrapText()` 165 166 167 #### New sheet creation 168 169 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: 170 ```php 171 use Box\Spout\Writer\WriterFactory; 172 use Box\Spout\Common\Type; 173 174 $writer = WriterFactory::create(Type::ODS); 175 $writer->setShouldCreateNewSheetsAutomatically(true); // default value 176 $writer->setShouldCreateNewSheetsAutomatically(false); // will stop writing new data when limit is reached 177 ``` 178 179 #### Using custom temporary folder 180 181 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: 182 ```php 183 use Box\Spout\Writer\WriterFactory; 184 use Box\Spout\Common\Type; 185 186 $writer = WriterFactory::create(Type::XLSX); 187 $writer->setTempFolder($customTempFolderPath); 188 ``` 189 190 #### Strings storage (XLSX writer) 191 192 XLSX files support different ways to store the string values: 193 * 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) 194 * Inline strings are less optimized (as duplicate strings are all stored) but is faster to process 195 196 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. 197 ```php 198 use Box\Spout\Writer\WriterFactory; 199 use Box\Spout\Common\Type; 200 201 $writer = WriterFactory::create(Type::XLSX); 202 $writer->setShouldUseInlineStrings(true); // default (and recommended) value 203 $writer->setShouldUseInlineStrings(false); // will use shared strings 204 ``` 205 206 > ##### Note on Apple Numbers and iOS support 207 > 208 > 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! 209 210 211 ### Playing with sheets 212 213 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: 214 ```php 215 $firstSheet = $writer->getCurrentSheet(); 216 $writer->addRow($rowForSheet1); // writes the row to the first sheet 217 218 $newSheet = $writer->addNewSheetAndMakeItCurrent(); 219 $writer->addRow($rowForSheet2); // writes the row to the new sheet 220 221 $writer->setCurrentSheet($firstSheet); 222 $writer->addRow($anotherRowForSheet1); // append the row to the first sheet 223 ``` 224 225 It is also possible to retrieve all the sheets currently created: 226 ```php 227 $sheets = $writer->getSheets(); 228 ``` 229 230 If you rely on the sheet's name in your application, you can access it and customize it this way: 231 ```php 232 // Accessing the sheet name when reading 233 foreach ($reader->getSheetIterator() as $sheet) { 234 $sheetName = $sheet->getName(); 235 } 236 237 // Accessing the sheet name when writing 238 $sheet = $writer->getCurrentSheet(); 239 $sheetName = $sheet->getName(); 240 241 // Customizing the sheet name when writing 242 $sheet = $writer->getCurrentSheet(); 243 $sheet->setName('My custom name'); 244 ``` 245 246 > Please note that Excel has some restrictions on the sheet's name: 247 > * it must not be blank 248 > * it must not exceed 31 characters 249 > * it must not contain these characters: \ / ? * : [ or ] 250 > * it must not start or end with a single quote 251 > * it must be unique 252 > 253 > 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. 254 255 256 ### Fluent interface 257 258 Because fluent interfaces are great, you can use them with Spout: 259 ```php 260 use Box\Spout\Writer\WriterFactory; 261 use Box\Spout\Common\Type; 262 263 $writer = WriterFactory::create(Type::XLSX); 264 $writer->setTempFolder($customTempFolderPath) 265 ->setShouldUseInlineStrings(true) 266 ->openToFile($filePath) 267 ->addRow($headerRow) 268 ->addRows($dataRows) 269 ->close(); 270 ``` 271 272 273 ## Running tests 274 275 On the `master` branch, only unit and functional tests are included. The performance tests require very large files and have been excluded. 276 If you just want to check that everything is working as expected, executing the tests of the `master` branch is enough. 277 278 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: 279 280 * `phpunit` - runs the whole test suite (unit + functional + performance tests) 281 * `phpunit --exclude-group perf-tests` - only runs the unit and functional tests 282 * `phpunit --group perf-tests` - only runs the performance tests 283 284 For information, the performance tests take about 30 minutes to run (processing 1 million rows files is not a quick thing). 285 286 > Performance tests status: [](https://travis-ci.org/box/spout) 287 288 289 ## Frequently Asked Questions 290 291 #### How can Spout handle such large data sets and still use less than 10MB of memory? 292 293 When writing data, Spout is streaming the data to files, one or few lines at a time. That means that it only keeps in memory the few rows that it needs to write. Once written, the memory is freed. 294 295 Same goes with reading. Only one row at a time is stored in memory. A special technique is used to handle shared strings in XLSX, storing them - if needed - into several small temporary files that allows fast access. 296 297 #### How long does it take to generate a file with X rows? 298 299 Here are a few numbers regarding the performance of Spout: 300 301 | Type | Action | 2,000 rows (6,000 cells) | 200,000 rows (600,000 cells) | 2,000,000 rows (6,000,000 cells) | 302 |------|-------------------------------|--------------------------|------------------------------|----------------------------------| 303 | CSV | Read | < 1 second | 4 seconds | 2-3 minutes | 304 | | Write | < 1 second | 2 seconds | 2-3 minutes | 305 | XLSX | Read<br>*inline strings* | < 1 second | 35-40 seconds | 18-20 minutes | 306 | | Read<br>*shared strings* | 1 second | 1-2 minutes | 35-40 minutes | 307 | | Write | 1 second | 20-25 seconds | 8-10 minutes | 308 | ODS | Read | 1 second | 1-2 minutes | 5-6 minutes | 309 | | Write | < 1 second | 35-40 seconds | 5-6 minutes | 310 311 #### Does Spout support charts or formulas? 312 313 No. This is a compromise to keep memory usage low. Charts and formulas requires data to be kept in memory in order to be used. 314 So the larger the file would be, the more memory would be consumed, preventing your code to scale well. 315 316 317 ## Support 318 319 Need to contact us directly? Email oss@box.com and be sure to include the name of this project in the subject. 320 321 You can also ask questions, submit new features ideas or discuss about Spout in the chat room:<br> 322 [](https://gitter.im/box/spout?utm_source=badge&utm_medium=badge&utm_campaign=pr-badge) 323 324 ## Copyright and License 325 326 Copyright 2015 Box, Inc. All rights reserved. 327 328 Licensed under the Apache License, Version 2.0 (the "License"); 329 you may not use this file except in compliance with the License. 330 You may obtain a copy of the License at 331 332 http://www.apache.org/licenses/LICENSE-2.0 333 334 Unless required by applicable law or agreed to in writing, software 335 distributed under the License is distributed on an "AS IS" BASIS, 336 WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 337 See the License for the specific language governing permissions and 338 limitations under the License.
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
| Generated: Thu Aug 11 10:00:09 2016 | Cross-referenced by PHPXref 0.7.1 |