1
|
|
|
<?php |
2
|
|
|
|
3
|
|
|
namespace Box\Spout\Writer\XLSX\Internal; |
4
|
|
|
|
5
|
|
|
use Box\Spout\Common\Exception\InvalidArgumentException; |
6
|
|
|
use Box\Spout\Common\Exception\IOException; |
7
|
|
|
use Box\Spout\Common\Helper\StringHelper; |
8
|
|
|
use Box\Spout\Writer\Common\Helper\CellHelper; |
9
|
|
|
use Box\Spout\Writer\Common\Internal\WorksheetInterface; |
10
|
|
|
|
11
|
|
|
/** |
12
|
|
|
* Class Worksheet |
13
|
|
|
* Represents a worksheet within a XLSX file. The difference with the Sheet object is |
14
|
|
|
* that this class provides an interface to write data |
15
|
|
|
* |
16
|
|
|
* @package Box\Spout\Writer\XLSX\Internal |
17
|
|
|
*/ |
18
|
|
|
class Worksheet implements WorksheetInterface |
19
|
|
|
{ |
20
|
|
|
/** |
21
|
|
|
* Maximum number of characters a cell can contain |
22
|
|
|
* @see https://support.office.com/en-us/article/Excel-specifications-and-limits-16c69c74-3d6a-4aaf-ba35-e6eb276e8eaa [Excel 2007] |
23
|
|
|
* @see https://support.office.com/en-us/article/Excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3 [Excel 2010] |
24
|
|
|
* @see https://support.office.com/en-us/article/Excel-specifications-and-limits-ca36e2dc-1f09-4620-b726-67c00b05040f [Excel 2013/2016] |
25
|
|
|
*/ |
26
|
|
|
const MAX_CHARACTERS_PER_CELL = 32767; |
27
|
|
|
|
28
|
|
|
const SHEET_XML_FILE_HEADER = <<<EOD |
29
|
|
|
<?xml version="1.0" encoding="UTF-8" standalone="yes"?> |
30
|
|
|
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"> |
31
|
|
|
EOD; |
32
|
|
|
|
33
|
|
|
/** @var \Box\Spout\Writer\Common\Sheet The "external" sheet */ |
34
|
|
|
protected $externalSheet; |
35
|
|
|
|
36
|
|
|
/** @var string Path to the XML file that will contain the sheet data */ |
37
|
|
|
protected $worksheetFilePath; |
38
|
|
|
|
39
|
|
|
/** @var \Box\Spout\Writer\XLSX\Helper\SharedStringsHelper Helper to write shared strings */ |
40
|
|
|
protected $sharedStringsHelper; |
41
|
|
|
|
42
|
|
|
/** @var \Box\Spout\Writer\XLSX\Helper\StyleHelper Helper to work with styles */ |
43
|
|
|
protected $styleHelper; |
44
|
|
|
|
45
|
|
|
/** @var bool Whether inline or shared strings should be used */ |
46
|
|
|
protected $shouldUseInlineStrings; |
47
|
|
|
|
48
|
|
|
/** @var \Box\Spout\Common\Escaper\XLSX Strings escaper */ |
49
|
|
|
protected $stringsEscaper; |
50
|
|
|
|
51
|
|
|
/** @var \Box\Spout\Common\Helper\StringHelper String helper */ |
52
|
|
|
protected $stringHelper; |
53
|
|
|
|
54
|
|
|
/** @var Resource Pointer to the sheet data file (e.g. xl/worksheets/sheet1.xml) */ |
55
|
|
|
protected $sheetFilePointer; |
56
|
|
|
|
57
|
|
|
/** @var int Index of the last written row */ |
58
|
|
|
protected $lastWrittenRowIndex = 0; |
59
|
|
|
|
60
|
|
|
/** |
61
|
|
|
* @param \Box\Spout\Writer\Common\Sheet $externalSheet The associated "external" sheet |
62
|
|
|
* @param string $worksheetFilesFolder Temporary folder where the files to create the XLSX will be stored |
63
|
|
|
* @param \Box\Spout\Writer\XLSX\Helper\SharedStringsHelper $sharedStringsHelper Helper for shared strings |
64
|
|
|
* @param \Box\Spout\Writer\XLSX\Helper\StyleHelper Helper to work with styles |
65
|
|
|
* @param bool $shouldUseInlineStrings Whether inline or shared strings should be used |
66
|
|
|
* @throws \Box\Spout\Common\Exception\IOException If the sheet data file cannot be opened for writing |
67
|
|
|
*/ |
68
|
129 |
|
public function __construct($externalSheet, $worksheetFilesFolder, $sharedStringsHelper, $styleHelper, $shouldUseInlineStrings) |
69
|
|
|
{ |
70
|
129 |
|
$this->externalSheet = $externalSheet; |
71
|
129 |
|
$this->sharedStringsHelper = $sharedStringsHelper; |
72
|
129 |
|
$this->styleHelper = $styleHelper; |
73
|
129 |
|
$this->shouldUseInlineStrings = $shouldUseInlineStrings; |
74
|
|
|
|
75
|
|
|
/** @noinspection PhpUnnecessaryFullyQualifiedNameInspection */ |
76
|
129 |
|
$this->stringsEscaper = \Box\Spout\Common\Escaper\XLSX::getInstance(); |
77
|
129 |
|
$this->stringHelper = new StringHelper(); |
78
|
|
|
|
79
|
129 |
|
$this->worksheetFilePath = $worksheetFilesFolder . '/' . strtolower($this->externalSheet->getName()) . '.xml'; |
80
|
129 |
|
$this->startSheet(); |
81
|
129 |
|
} |
82
|
|
|
|
83
|
|
|
/** |
84
|
|
|
* Prepares the worksheet to accept data |
85
|
|
|
* |
86
|
|
|
* @return void |
87
|
|
|
* @throws \Box\Spout\Common\Exception\IOException If the sheet data file cannot be opened for writing |
88
|
|
|
*/ |
89
|
129 |
|
protected function startSheet() |
90
|
|
|
{ |
91
|
129 |
|
$this->sheetFilePointer = fopen($this->worksheetFilePath, 'w'); |
92
|
129 |
|
$this->throwIfSheetFilePointerIsNotAvailable(); |
93
|
|
|
|
94
|
129 |
|
fwrite($this->sheetFilePointer, self::SHEET_XML_FILE_HEADER); |
95
|
129 |
|
fwrite($this->sheetFilePointer, '<sheetData>'); |
96
|
129 |
|
} |
97
|
|
|
|
98
|
|
|
/** |
99
|
|
|
* Checks if the book has been created. Throws an exception if not created yet. |
100
|
|
|
* |
101
|
|
|
* @return void |
102
|
|
|
* @throws \Box\Spout\Common\Exception\IOException If the sheet data file cannot be opened for writing |
103
|
|
|
*/ |
104
|
129 |
|
protected function throwIfSheetFilePointerIsNotAvailable() |
105
|
|
|
{ |
106
|
129 |
|
if (!$this->sheetFilePointer) { |
107
|
|
|
throw new IOException('Unable to open sheet for writing.'); |
108
|
|
|
} |
109
|
129 |
|
} |
110
|
|
|
|
111
|
|
|
/** |
112
|
|
|
* @return \Box\Spout\Writer\Common\Sheet The "external" sheet |
113
|
|
|
*/ |
114
|
102 |
|
public function getExternalSheet() |
115
|
|
|
{ |
116
|
102 |
|
return $this->externalSheet; |
117
|
|
|
} |
118
|
|
|
|
119
|
|
|
/** |
120
|
|
|
* @return int The index of the last written row |
121
|
|
|
*/ |
122
|
93 |
|
public function getLastWrittenRowIndex() |
123
|
|
|
{ |
124
|
93 |
|
return $this->lastWrittenRowIndex; |
125
|
|
|
} |
126
|
|
|
|
127
|
|
|
/** |
128
|
|
|
* @return int The ID of the worksheet |
129
|
|
|
*/ |
130
|
99 |
|
public function getId() |
131
|
|
|
{ |
132
|
|
|
// sheet index is zero-based, while ID is 1-based |
133
|
99 |
|
return $this->externalSheet->getIndex() + 1; |
134
|
|
|
} |
135
|
|
|
|
136
|
|
|
/** |
137
|
|
|
* Adds data to the worksheet. |
138
|
|
|
* |
139
|
|
|
* @param array $dataRow Array containing data to be written. Cannot be empty. |
140
|
|
|
* Example $dataRow = ['data1', 1234, null, '', 'data5']; |
141
|
|
|
* @param \Box\Spout\Writer\Style\Style $style Style to be applied to the row. NULL means use default style. |
142
|
|
|
* @return void |
143
|
|
|
* @throws \Box\Spout\Common\Exception\IOException If the data cannot be written |
144
|
|
|
* @throws \Box\Spout\Common\Exception\InvalidArgumentException If a cell value's type is not supported |
145
|
|
|
*/ |
146
|
93 |
|
public function addRow($dataRow, $style) |
147
|
|
|
{ |
148
|
93 |
|
if (!$this->isEmptyRow($dataRow)) { |
149
|
93 |
|
$this->addNonEmptyRow($dataRow, $style); |
150
|
87 |
|
} |
151
|
|
|
|
152
|
87 |
|
$this->lastWrittenRowIndex++; |
153
|
87 |
|
} |
154
|
|
|
|
155
|
|
|
/** |
156
|
|
|
* Returns whether the given row is empty |
157
|
|
|
* |
158
|
|
|
* @param array $dataRow Array containing data to be written. Cannot be empty. |
159
|
|
|
* Example $dataRow = ['data1', 1234, null, '', 'data5']; |
160
|
|
|
* @return bool Whether the given row is empty |
161
|
|
|
*/ |
162
|
93 |
|
private function isEmptyRow($dataRow) |
163
|
|
|
{ |
164
|
93 |
|
$numCells = count($dataRow); |
165
|
|
|
// using "reset()" instead of "$dataRow[0]" because $dataRow can be an associative array |
166
|
93 |
|
return ($numCells === 1 && CellHelper::isEmpty(reset($dataRow))); |
167
|
|
|
} |
168
|
|
|
|
169
|
|
|
/** |
170
|
|
|
* Adds non empty row to the worksheet. |
171
|
|
|
* |
172
|
|
|
* @param array $dataRow Array containing data to be written. Cannot be empty. |
173
|
|
|
* Example $dataRow = ['data1', 1234, null, '', 'data5']; |
174
|
|
|
* @param \Box\Spout\Writer\Style\Style $style Style to be applied to the row. NULL means use default style. |
175
|
|
|
* @return void |
176
|
|
|
* @throws \Box\Spout\Common\Exception\IOException If the data cannot be written |
177
|
|
|
* @throws \Box\Spout\Common\Exception\InvalidArgumentException If a cell value's type is not supported |
178
|
|
|
*/ |
179
|
93 |
|
private function addNonEmptyRow($dataRow, $style) |
180
|
|
|
{ |
181
|
93 |
|
$cellNumber = 0; |
182
|
93 |
|
$rowIndex = $this->lastWrittenRowIndex + 1; |
183
|
93 |
|
$numCells = count($dataRow); |
184
|
|
|
|
185
|
93 |
|
$rowXML = '<row r="' . $rowIndex . '" spans="1:' . $numCells . '">'; |
186
|
|
|
|
187
|
93 |
|
foreach($dataRow as $cellValue) { |
188
|
93 |
|
$rowXML .= $this->getCellXML($rowIndex, $cellNumber, $cellValue, $style->getId()); |
189
|
87 |
|
$cellNumber++; |
190
|
87 |
|
} |
191
|
|
|
|
192
|
87 |
|
$rowXML .= '</row>'; |
193
|
|
|
|
194
|
87 |
|
$wasWriteSuccessful = fwrite($this->sheetFilePointer, $rowXML); |
195
|
87 |
|
if ($wasWriteSuccessful === false) { |
196
|
|
|
throw new IOException("Unable to write data in {$this->worksheetFilePath}"); |
197
|
|
|
} |
198
|
87 |
|
} |
199
|
|
|
|
200
|
|
|
/** |
201
|
|
|
* Build and return xml for a single cell. |
202
|
|
|
* |
203
|
|
|
* @param int $rowIndex |
204
|
|
|
* @param int $cellNumber |
205
|
|
|
* @param mixed $cellValue |
206
|
|
|
* @param int $styleId |
207
|
|
|
* @return string |
208
|
|
|
* @throws InvalidArgumentException If the given value cannot be processed |
209
|
|
|
*/ |
210
|
93 |
|
private function getCellXML($rowIndex, $cellNumber, $cellValue, $styleId) |
211
|
|
|
{ |
212
|
93 |
|
$columnIndex = CellHelper::getCellIndexFromColumnIndex($cellNumber); |
213
|
93 |
|
$cellXML = '<c r="' . $columnIndex . $rowIndex . '"'; |
214
|
93 |
|
$cellXML .= ' s="' . $styleId . '"'; |
215
|
|
|
|
216
|
93 |
|
if (CellHelper::isNonEmptyString($cellValue)) { |
217
|
90 |
|
$cellXML .= $this->getCellXMLFragmentForNonEmptyString($cellValue); |
218
|
90 |
|
} else if (CellHelper::isBoolean($cellValue)) { |
219
|
3 |
|
$cellXML .= ' t="b"><v>' . intval($cellValue) . '</v></c>'; |
220
|
12 |
|
} else if (CellHelper::isNumeric($cellValue)) { |
221
|
3 |
|
$cellXML .= '><v>' . $cellValue . '</v></c>'; |
222
|
12 |
|
} else if (empty($cellValue)) { |
223
|
6 |
|
if ($this->styleHelper->shouldApplyStyleOnEmptyCell($styleId)) { |
224
|
3 |
|
$cellXML .= '/>'; |
225
|
3 |
|
} else { |
226
|
|
|
// don't write empty cells that do no need styling |
227
|
|
|
// NOTE: not appending to $cellXML is the right behavior!! |
228
|
6 |
|
$cellXML = ''; |
229
|
|
|
} |
230
|
6 |
|
} else { |
231
|
6 |
|
throw new InvalidArgumentException('Trying to add a value with an unsupported type: ' . gettype($cellValue)); |
232
|
|
|
} |
233
|
|
|
|
234
|
87 |
|
return $cellXML; |
235
|
|
|
} |
236
|
|
|
|
237
|
|
|
/** |
238
|
|
|
* Returns the XML fragment for a cell containing a non empty string |
239
|
|
|
* |
240
|
|
|
* @param string $cellValue The cell value |
241
|
|
|
* @return string The XML fragment representing the cell |
242
|
|
|
* @throws InvalidArgumentException If the string exceeds the maximum number of characters allowed per cell |
243
|
|
|
*/ |
244
|
90 |
|
private function getCellXMLFragmentForNonEmptyString($cellValue) |
245
|
|
|
{ |
246
|
90 |
|
if ($this->stringHelper->getStringLength($cellValue) > self::MAX_CHARACTERS_PER_CELL) { |
247
|
3 |
|
throw new InvalidArgumentException('Trying to add a value that exceeds the maximum number of characters allowed in a cell (32,767)'); |
248
|
|
|
} |
249
|
|
|
|
250
|
87 |
|
if ($this->shouldUseInlineStrings) { |
251
|
78 |
|
$cellXMLFragment = ' t="inlineStr"><is><t>' . $this->stringsEscaper->escape($cellValue) . '</t></is></c>'; |
252
|
78 |
|
} else { |
253
|
9 |
|
$sharedStringId = $this->sharedStringsHelper->writeString($cellValue); |
254
|
9 |
|
$cellXMLFragment = ' t="s"><v>' . $sharedStringId . '</v></c>'; |
255
|
|
|
} |
256
|
|
|
|
257
|
87 |
|
return $cellXMLFragment; |
258
|
|
|
} |
259
|
|
|
|
260
|
|
|
/** |
261
|
|
|
* Closes the worksheet |
262
|
|
|
* |
263
|
|
|
* @return void |
264
|
|
|
*/ |
265
|
99 |
|
public function close() |
266
|
|
|
{ |
267
|
99 |
|
if (!is_resource($this->sheetFilePointer)) { |
268
|
|
|
return; |
269
|
|
|
} |
270
|
|
|
|
271
|
99 |
|
fwrite($this->sheetFilePointer, '</sheetData>'); |
272
|
99 |
|
fwrite($this->sheetFilePointer, '</worksheet>'); |
273
|
99 |
|
fclose($this->sheetFilePointer); |
274
|
99 |
|
} |
275
|
|
|
} |
276
|
|
|
|