|
1
|
|
|
<?php |
|
|
|
|
|
|
2
|
|
|
|
|
3
|
|
|
namespace Samples\Sample14; |
|
4
|
|
|
|
|
5
|
|
|
use PhpOffice\PhpSpreadsheet\IOFactory; |
|
6
|
|
|
use PhpOffice\PhpSpreadsheet\Reader\IReadFilter; |
|
7
|
|
|
use PhpOffice\PhpSpreadsheet\Spreadsheet; |
|
8
|
|
|
|
|
9
|
|
|
require __DIR__ . '/../Header.php'; |
|
10
|
|
|
|
|
11
|
|
|
$inputFileType = 'Csv'; |
|
12
|
|
|
$inputFileName = __DIR__ . '/sampleData/example2.csv'; |
|
13
|
|
|
|
|
14
|
|
|
/** Define a Read Filter class implementing IReadFilter */ |
|
15
|
|
View Code Duplication |
class ChunkReadFilter implements IReadFilter |
|
|
|
|
|
|
16
|
|
|
{ |
|
17
|
|
|
private $startRow = 0; |
|
18
|
|
|
private $endRow = 0; |
|
19
|
|
|
|
|
20
|
|
|
/** |
|
21
|
|
|
* Set the list of rows that we want to read. |
|
22
|
|
|
* |
|
23
|
|
|
* @param mixed $startRow |
|
24
|
|
|
* @param mixed $chunkSize |
|
25
|
|
|
*/ |
|
26
|
|
|
public function setRows($startRow, $chunkSize) |
|
27
|
|
|
{ |
|
28
|
|
|
$this->startRow = $startRow; |
|
29
|
|
|
$this->endRow = $startRow + $chunkSize; |
|
30
|
|
|
} |
|
31
|
|
|
|
|
32
|
|
|
public function readCell($column, $row, $worksheetName = '') |
|
33
|
|
|
{ |
|
34
|
|
|
// Only read the heading row, and the rows that are configured in $this->_startRow and $this->_endRow |
|
35
|
|
|
if (($row == 1) || ($row >= $this->startRow && $row < $this->endRow)) { |
|
36
|
|
|
return true; |
|
37
|
|
|
} |
|
38
|
|
|
|
|
39
|
|
|
return false; |
|
40
|
|
|
} |
|
41
|
|
|
} |
|
42
|
|
|
|
|
43
|
|
|
$helper->log('Loading file ' . pathinfo($inputFileName, PATHINFO_BASENAME) . ' using IOFactory with a defined reader type of ' . $inputFileType); |
|
44
|
|
|
// Create a new Reader of the type defined in $inputFileType |
|
45
|
|
|
$reader = IOFactory::createReader($inputFileType); |
|
46
|
|
|
|
|
47
|
|
|
// Define how many rows we want to read for each "chunk" |
|
48
|
|
|
$chunkSize = 100; |
|
49
|
|
|
// Create a new Instance of our Read Filter |
|
50
|
|
|
$chunkFilter = new ChunkReadFilter(); |
|
51
|
|
|
|
|
52
|
|
|
// Tell the Reader that we want to use the Read Filter that we've Instantiated |
|
53
|
|
|
// and that we want to store it in contiguous rows/columns |
|
54
|
|
|
$reader->setReadFilter($chunkFilter) |
|
55
|
|
|
->setContiguous(true); |
|
56
|
|
|
|
|
57
|
|
|
// Instantiate a new PhpSpreadsheet object manually |
|
58
|
|
|
$spreadsheet = new Spreadsheet(); |
|
59
|
|
|
|
|
60
|
|
|
// Set a sheet index |
|
61
|
|
|
$sheet = 0; |
|
62
|
|
|
// Loop to read our worksheet in "chunk size" blocks |
|
63
|
|
|
/** $startRow is set to 2 initially because we always read the headings in row #1 * */ |
|
64
|
|
|
for ($startRow = 2; $startRow <= 240; $startRow += $chunkSize) { |
|
65
|
|
|
$helper->log('Loading WorkSheet #' . ($sheet + 1) . ' using configurable filter for headings row 1 and for rows ' . $startRow . ' to ' . ($startRow + $chunkSize - 1)); |
|
66
|
|
|
// Tell the Read Filter, the limits on which rows we want to read this iteration |
|
67
|
|
|
$chunkFilter->setRows($startRow, $chunkSize); |
|
68
|
|
|
|
|
69
|
|
|
// Increment the worksheet index pointer for the Reader |
|
70
|
|
|
$reader->setSheetIndex($sheet); |
|
71
|
|
|
// Load only the rows that match our filter into a new worksheet in the PhpSpreadsheet Object |
|
72
|
|
|
$reader->loadIntoExisting($inputFileName, $spreadsheet); |
|
73
|
|
|
// Set the worksheet title (to reference the "sheet" of data that we've loaded) |
|
74
|
|
|
// and increment the sheet index as well |
|
75
|
|
|
$spreadsheet->getActiveSheet()->setTitle('Country Data #' . (++$sheet)); |
|
76
|
|
|
} |
|
77
|
|
|
|
|
78
|
|
|
$helper->log($spreadsheet->getSheetCount() . ' worksheet' . (($spreadsheet->getSheetCount() == 1) ? '' : 's') . ' loaded'); |
|
79
|
|
|
$loadedSheetNames = $spreadsheet->getSheetNames(); |
|
80
|
|
View Code Duplication |
foreach ($loadedSheetNames as $sheetIndex => $loadedSheetName) { |
|
|
|
|
|
|
81
|
|
|
$helper->log('<b>Worksheet #' . $sheetIndex . ' -> ' . $loadedSheetName . '</b>'); |
|
82
|
|
|
$spreadsheet->setActiveSheetIndexByName($loadedSheetName); |
|
83
|
|
|
$sheetData = $spreadsheet->getActiveSheet()->toArray(null, false, false, true); |
|
84
|
|
|
var_dump($sheetData); |
|
|
|
|
|
|
85
|
|
|
} |
|
86
|
|
|
|
PHPOffice /
PhpSpreadsheet
Loading history...
The PSR-1: Basic Coding Standard recommends that a file should either introduce new symbols, that is classes, functions, constants or similar, or have side effects. Side effects are anything that executes logic, like for example printing output, changing ini settings or writing to a file.
The idea behind this recommendation is that merely auto-loading a class should not change the state of an application. It also promotes a cleaner style of programming and makes your code less prone to errors, because the logic is not spread out all over the place.
To learn more about the PSR-1, please see the PHP-FIG site on the PSR-1.