1
|
|
|
<?php |
2
|
|
|
|
3
|
|
|
namespace Box\Spout\Writer\XLSX\Manager; |
4
|
|
|
|
5
|
|
|
use Box\Spout\Common\Exception\InvalidArgumentException; |
6
|
|
|
use Box\Spout\Common\Exception\IOException; |
7
|
|
|
use Box\Spout\Common\Helper\Escaper\XLSX as XLSXEscaper; |
8
|
|
|
use Box\Spout\Common\Helper\StringHelper; |
9
|
|
|
use Box\Spout\Common\Manager\OptionsManagerInterface; |
10
|
|
|
use Box\Spout\Writer\Common\Creator\EntityFactory; |
11
|
|
|
use Box\Spout\Writer\Common\Entity\Cell; |
12
|
|
|
use Box\Spout\Writer\Common\Entity\Options; |
13
|
|
|
use Box\Spout\Writer\Common\Entity\Row; |
14
|
|
|
use Box\Spout\Writer\Common\Entity\Style\Style; |
15
|
|
|
use Box\Spout\Writer\Common\Entity\Worksheet; |
16
|
|
|
use Box\Spout\Writer\Common\Helper\CellHelper; |
17
|
|
|
use Box\Spout\Writer\Common\Manager\WorksheetManagerInterface; |
18
|
|
|
use Box\Spout\Writer\XLSX\Manager\Style\StyleManager; |
19
|
|
|
|
20
|
|
|
/** |
21
|
|
|
* Class WorksheetManager |
22
|
|
|
* XLSX worksheet manager, providing the interfaces to work with XLSX worksheets. |
23
|
|
|
*/ |
24
|
|
|
class WorksheetManager implements WorksheetManagerInterface |
25
|
|
|
{ |
26
|
|
|
/** |
27
|
|
|
* Maximum number of characters a cell can contain |
28
|
|
|
* @see https://support.office.com/en-us/article/Excel-specifications-and-limits-16c69c74-3d6a-4aaf-ba35-e6eb276e8eaa [Excel 2007] |
29
|
|
|
* @see https://support.office.com/en-us/article/Excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3 [Excel 2010] |
30
|
|
|
* @see https://support.office.com/en-us/article/Excel-specifications-and-limits-ca36e2dc-1f09-4620-b726-67c00b05040f [Excel 2013/2016] |
31
|
|
|
*/ |
32
|
|
|
const MAX_CHARACTERS_PER_CELL = 32767; |
33
|
|
|
|
34
|
|
|
const SHEET_XML_FILE_HEADER = <<<'EOD' |
35
|
|
|
<?xml version="1.0" encoding="UTF-8" standalone="yes"?> |
36
|
|
|
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"> |
37
|
|
|
EOD; |
38
|
|
|
|
39
|
|
|
/** @var bool Whether inline or shared strings should be used */ |
40
|
|
|
protected $shouldUseInlineStrings; |
41
|
|
|
|
42
|
|
|
/** @var StyleManager Manages styles */ |
43
|
|
|
private $styleManager; |
44
|
|
|
|
45
|
|
|
/** @var SharedStringsManager Helper to write shared strings */ |
46
|
|
|
private $sharedStringsManager; |
47
|
|
|
|
48
|
|
|
/** @var XLSXEscaper Strings escaper */ |
49
|
|
|
private $stringsEscaper; |
50
|
|
|
|
51
|
|
|
/** @var StringHelper String helper */ |
52
|
|
|
private $stringHelper; |
53
|
|
|
|
54
|
|
|
/** @var EntityFactory Factory to create entities */ |
55
|
|
|
private $entityFactory; |
56
|
|
|
|
57
|
|
|
/** |
58
|
|
|
* WorksheetManager constructor. |
59
|
|
|
* |
60
|
|
|
* @param OptionsManagerInterface $optionsManager |
61
|
|
|
* @param StyleManager $styleManager |
62
|
|
|
* @param SharedStringsManager $sharedStringsManager |
63
|
|
|
* @param XLSXEscaper $stringsEscaper |
64
|
|
|
* @param StringHelper $stringHelper |
65
|
|
|
* @param EntityFactory $entityFactory |
66
|
|
|
*/ |
67
|
38 |
|
public function __construct( |
68
|
|
|
OptionsManagerInterface $optionsManager, |
69
|
|
|
StyleManager $styleManager, |
70
|
|
|
SharedStringsManager $sharedStringsManager, |
71
|
|
|
XLSXEscaper $stringsEscaper, |
72
|
|
|
StringHelper $stringHelper, |
73
|
|
|
EntityFactory $entityFactory |
74
|
|
|
) { |
75
|
38 |
|
$this->shouldUseInlineStrings = $optionsManager->getOption(Options::SHOULD_USE_INLINE_STRINGS); |
76
|
38 |
|
$this->styleManager = $styleManager; |
77
|
38 |
|
$this->sharedStringsManager = $sharedStringsManager; |
78
|
38 |
|
$this->stringsEscaper = $stringsEscaper; |
79
|
38 |
|
$this->stringHelper = $stringHelper; |
80
|
38 |
|
$this->entityFactory = $entityFactory; |
81
|
38 |
|
} |
82
|
|
|
|
83
|
|
|
/** |
84
|
|
|
* @return SharedStringsManager |
85
|
|
|
*/ |
86
|
34 |
|
public function getSharedStringsManager() |
87
|
|
|
{ |
88
|
34 |
|
return $this->sharedStringsManager; |
89
|
|
|
} |
90
|
|
|
|
91
|
|
|
/** |
92
|
|
|
* {@inheritdoc} |
93
|
|
|
*/ |
94
|
38 |
|
public function startSheet(Worksheet $worksheet) |
95
|
|
|
{ |
96
|
38 |
|
$sheetFilePointer = fopen($worksheet->getFilePath(), 'w'); |
97
|
38 |
|
$this->throwIfSheetFilePointerIsNotAvailable($sheetFilePointer); |
98
|
|
|
|
99
|
38 |
|
$worksheet->setFilePointer($sheetFilePointer); |
100
|
|
|
|
101
|
38 |
|
fwrite($sheetFilePointer, self::SHEET_XML_FILE_HEADER); |
102
|
38 |
|
fwrite($sheetFilePointer, '<sheetData>'); |
103
|
38 |
|
} |
104
|
|
|
|
105
|
|
|
/** |
106
|
|
|
* Checks if the sheet has been sucessfully created. Throws an exception if not. |
107
|
|
|
* |
108
|
|
|
* @param bool|resource $sheetFilePointer Pointer to the sheet data file or FALSE if unable to open the file |
109
|
|
|
* @throws IOException If the sheet data file cannot be opened for writing |
110
|
|
|
* @return void |
111
|
|
|
*/ |
112
|
38 |
|
private function throwIfSheetFilePointerIsNotAvailable($sheetFilePointer) |
113
|
|
|
{ |
114
|
38 |
|
if (!$sheetFilePointer) { |
115
|
|
|
throw new IOException('Unable to open sheet for writing.'); |
116
|
|
|
} |
117
|
38 |
|
} |
118
|
|
|
|
119
|
|
|
/** |
120
|
|
|
* {@inheritdoc} |
121
|
|
|
*/ |
122
|
30 |
|
public function addRow(Worksheet $worksheet, Row $row) |
123
|
|
|
{ |
124
|
30 |
|
if (!$row->isEmpty()) { |
125
|
30 |
|
$this->addNonEmptyRow($worksheet, $row); |
126
|
|
|
} |
127
|
|
|
|
128
|
29 |
|
$worksheet->setLastWrittenRowIndex($worksheet->getLastWrittenRowIndex() + 1); |
129
|
29 |
|
} |
130
|
|
|
|
131
|
|
|
/** |
132
|
|
|
* Adds non empty row to the worksheet. |
133
|
|
|
* |
134
|
|
|
* @param Worksheet $worksheet The worksheet to add the row to |
135
|
|
|
* @param Row $row The row to be written |
136
|
|
|
* @throws IOException If the data cannot be written |
137
|
|
|
* @throws InvalidArgumentException If a cell value's type is not supported |
138
|
|
|
* @return void |
139
|
|
|
*/ |
140
|
30 |
|
private function addNonEmptyRow(Worksheet $worksheet, Row $row) |
141
|
|
|
{ |
142
|
30 |
|
$cellIndex = 0; |
143
|
30 |
|
$rowStyle = $row->getStyle(); |
144
|
30 |
|
$rowIndex = $worksheet->getLastWrittenRowIndex() + 1; |
145
|
30 |
|
$numCells = count($row->getCells()); |
146
|
|
|
|
147
|
30 |
|
$rowXML = '<row r="' . $rowIndex . '" spans="1:' . $numCells . '">'; |
148
|
|
|
|
149
|
30 |
|
foreach ($row->getCells() as $cell) { |
150
|
30 |
|
$rowXML .= $this->applyStyleAndGetCellXML($cell, $rowStyle, $rowIndex, $cellIndex); |
151
|
29 |
|
$cellIndex++; |
152
|
|
|
} |
153
|
|
|
|
154
|
29 |
|
$rowXML .= '</row>'; |
155
|
|
|
|
156
|
29 |
|
$wasWriteSuccessful = fwrite($worksheet->getFilePointer(), $rowXML); |
157
|
29 |
|
if ($wasWriteSuccessful === false) { |
158
|
|
|
throw new IOException("Unable to write data in {$worksheet->getFilePath()}"); |
159
|
|
|
} |
160
|
29 |
|
} |
161
|
|
|
|
162
|
|
|
/** |
163
|
|
|
* Applies styles to the given style, merging the cell's style with its row's style |
164
|
|
|
* Then builds and returns xml for the cell. |
165
|
|
|
* |
166
|
|
|
* @param Cell $cell |
167
|
|
|
* @param Style $rowStyle |
168
|
|
|
* @param int $rowIndex |
169
|
|
|
* @param int $cellIndex |
170
|
|
|
* @throws InvalidArgumentException If the given value cannot be processed |
171
|
|
|
* @return string |
172
|
|
|
*/ |
173
|
30 |
|
private function applyStyleAndGetCellXML(Cell $cell, Style $rowStyle, $rowIndex, $cellIndex) |
174
|
|
|
{ |
175
|
|
|
// Apply styles - the row style is merged at this point |
176
|
30 |
|
$cell->applyStyle($rowStyle); |
177
|
30 |
|
$this->styleManager->applyExtraStylesIfNeeded($cell); |
178
|
30 |
|
$registeredStyle = $this->styleManager->registerStyle($cell->getStyle()); |
179
|
|
|
|
180
|
30 |
|
return $this->getCellXML($rowIndex, $cellIndex, $cell, $registeredStyle->getId()); |
181
|
|
|
} |
182
|
|
|
|
183
|
|
|
/** |
184
|
|
|
* Builds and returns xml for a single cell. |
185
|
|
|
* |
186
|
|
|
* @param int $rowIndex |
187
|
|
|
* @param int $cellNumber |
188
|
|
|
* @param Cell $cell |
189
|
|
|
* @param int $styleId |
190
|
|
|
* @throws InvalidArgumentException If the given value cannot be processed |
191
|
|
|
* @return string |
192
|
|
|
*/ |
193
|
30 |
|
private function getCellXML($rowIndex, $cellNumber, Cell $cell, $styleId) |
194
|
|
|
{ |
195
|
30 |
|
$columnIndex = CellHelper::getCellIndexFromColumnIndex($cellNumber); |
196
|
30 |
|
$cellXML = '<c r="' . $columnIndex . $rowIndex . '"'; |
197
|
30 |
|
$cellXML .= ' s="' . $styleId . '"'; |
198
|
|
|
|
199
|
30 |
|
if ($cell->isString()) { |
200
|
29 |
|
$cellXML .= $this->getCellXMLFragmentForNonEmptyString($cell->getValue()); |
201
|
4 |
|
} elseif ($cell->isBoolean()) { |
202
|
1 |
|
$cellXML .= ' t="b"><v>' . (int) ($cell->getValue()) . '</v></c>'; |
203
|
4 |
|
} elseif ($cell->isNumeric()) { |
204
|
1 |
|
$cellXML .= '><v>' . $cell->getValue() . '</v></c>'; |
205
|
4 |
|
} elseif ($cell->isEmpty()) { |
206
|
2 |
|
if ($this->styleManager->shouldApplyStyleOnEmptyCell($styleId)) { |
207
|
1 |
|
$cellXML .= '/>'; |
208
|
|
|
} else { |
209
|
|
|
// don't write empty cells that do no need styling |
210
|
|
|
// NOTE: not appending to $cellXML is the right behavior!! |
211
|
2 |
|
$cellXML = ''; |
212
|
|
|
} |
213
|
|
|
} else { |
214
|
2 |
|
throw new InvalidArgumentException('Trying to add a value with an unsupported type: ' . gettype($cell->getValue())); |
215
|
|
|
} |
216
|
|
|
|
217
|
29 |
|
return $cellXML; |
218
|
|
|
} |
219
|
|
|
|
220
|
|
|
/** |
221
|
|
|
* Returns the XML fragment for a cell containing a non empty string |
222
|
|
|
* |
223
|
|
|
* @param string $cellValue The cell value |
224
|
|
|
* @throws InvalidArgumentException If the string exceeds the maximum number of characters allowed per cell |
225
|
|
|
* @return string The XML fragment representing the cell |
226
|
|
|
*/ |
227
|
29 |
|
private function getCellXMLFragmentForNonEmptyString($cellValue) |
228
|
|
|
{ |
229
|
29 |
|
if ($this->stringHelper->getStringLength($cellValue) > self::MAX_CHARACTERS_PER_CELL) { |
230
|
|
|
throw new InvalidArgumentException('Trying to add a value that exceeds the maximum number of characters allowed in a cell (32,767)'); |
231
|
|
|
} |
232
|
|
|
|
233
|
29 |
|
if ($this->shouldUseInlineStrings) { |
234
|
26 |
|
$cellXMLFragment = ' t="inlineStr"><is><t>' . $this->stringsEscaper->escape($cellValue) . '</t></is></c>'; |
235
|
|
|
} else { |
236
|
3 |
|
$sharedStringId = $this->sharedStringsManager->writeString($cellValue); |
237
|
3 |
|
$cellXMLFragment = ' t="s"><v>' . $sharedStringId . '</v></c>'; |
238
|
|
|
} |
239
|
|
|
|
240
|
29 |
|
return $cellXMLFragment; |
241
|
|
|
} |
242
|
|
|
|
243
|
|
|
/** |
244
|
|
|
* {@inheritdoc} |
245
|
|
|
*/ |
246
|
34 |
|
public function close(Worksheet $worksheet) |
247
|
|
|
{ |
248
|
34 |
|
$worksheetFilePointer = $worksheet->getFilePointer(); |
249
|
|
|
|
250
|
34 |
|
if (!is_resource($worksheetFilePointer)) { |
251
|
|
|
return; |
252
|
|
|
} |
253
|
|
|
|
254
|
34 |
|
fwrite($worksheetFilePointer, '</sheetData>'); |
255
|
34 |
|
fwrite($worksheetFilePointer, '</worksheet>'); |
256
|
34 |
|
fclose($worksheetFilePointer); |
257
|
34 |
|
} |
258
|
|
|
} |
259
|
|
|
|