|
1
|
|
|
<?php |
|
2
|
|
|
|
|
3
|
|
|
namespace Zhaqq\Xlsx\Writer; |
|
4
|
|
|
|
|
5
|
|
|
use Zhaqq\Xlsx\Support; |
|
6
|
|
|
|
|
7
|
|
|
/** |
|
8
|
|
|
* @property mixed filename |
|
9
|
|
|
* @property mixed sheetname |
|
10
|
|
|
* @property mixed xmlname |
|
11
|
|
|
* @property mixed rowCount |
|
12
|
|
|
* @property mixed columns |
|
13
|
|
|
* @property mixed mergeCells |
|
14
|
|
|
* @property mixed maxCellTagStart |
|
15
|
|
|
* @property mixed maxCellTagEnd |
|
16
|
|
|
* @property mixed autoFilter |
|
17
|
|
|
* @property mixed freezeRows |
|
18
|
|
|
* @property mixed freezeColumns |
|
19
|
|
|
* @property mixed finalized |
|
20
|
|
|
*/ |
|
21
|
|
|
class Sheet |
|
22
|
|
|
{ |
|
23
|
|
|
/** |
|
24
|
|
|
* @var WriterBufferInterface |
|
25
|
|
|
*/ |
|
26
|
|
|
public $fileWriter; |
|
27
|
|
|
|
|
28
|
|
|
/** |
|
29
|
|
|
* Sheet constructor. |
|
30
|
|
|
* |
|
31
|
|
|
* @param array $config |
|
32
|
|
|
* @param string|null $fileWriter |
|
33
|
|
|
*/ |
|
34
|
|
|
public function __construct(array $config, $fileWriter = null) |
|
35
|
|
|
{ |
|
36
|
|
|
if (empty($config)) { |
|
37
|
|
|
throw new \RuntimeException('sheet config must be array'); |
|
38
|
|
|
} |
|
39
|
|
|
$this->filename = $config['filename']; |
|
40
|
|
|
$this->sheetname = $config['sheetname']; |
|
41
|
|
|
$this->xmlname = $config['xmlname']; |
|
42
|
|
|
$this->rowCount = $config['row_count']; |
|
43
|
|
|
$this->columns = $config['columns']; |
|
44
|
|
|
$this->mergeCells = $config['merge_cells']; |
|
45
|
|
|
$this->maxCellTagStart = $config['max_cell_tag_start']; |
|
46
|
|
|
$this->maxCellTagEnd = $config['max_cell_tag_end']; |
|
47
|
|
|
$this->autoFilter = $config['auto_filter']; |
|
48
|
|
|
$this->freezeRows = $config['freeze_rows']; |
|
49
|
|
|
$this->freezeColumns = $config['freeze_columns']; |
|
50
|
|
|
$this->finalized = $config['finalized']; |
|
51
|
|
|
$this->setFileWriter($fileWriter); |
|
52
|
|
|
} |
|
53
|
|
|
|
|
54
|
|
|
/** |
|
55
|
|
|
* @param $rowNumber |
|
56
|
|
|
* @param $columnNumber |
|
57
|
|
|
* @param $value |
|
58
|
|
|
* @param $numFormatType |
|
59
|
|
|
* @param $cellStyleIdx |
|
60
|
|
|
*/ |
|
61
|
|
|
public function writeCell($rowNumber, $columnNumber, $value, $numFormatType, $cellStyleIdx) |
|
62
|
|
|
{ |
|
63
|
|
|
$cellName = Support::xlsCell($rowNumber, $columnNumber); |
|
64
|
|
|
$file = $this->getFileWriter(); |
|
65
|
|
|
|
|
66
|
|
|
if (!is_scalar($value) || '' === $value) { //objects, array, empty |
|
67
|
|
|
$file->write('<c r="'.$cellName.'" s="'.$cellStyleIdx.'"/>'); |
|
68
|
|
|
} elseif (is_string($value) && '=' == $value[0]) { |
|
69
|
|
|
// Support Formula |
|
70
|
|
|
$file->write('<c r="'.$cellName.'" s="'.$cellStyleIdx.'" t="s"><f>'. |
|
71
|
|
|
str_replace('{n}', $rowNumber + 1, substr(Support::xmlSpecialChars($value), 1)) |
|
72
|
|
|
.'</f></c>'); |
|
73
|
|
|
} else { |
|
74
|
|
|
switch ($numFormatType) { |
|
75
|
|
|
case 'n_date': |
|
76
|
|
|
$file->write('<c r="'.$cellName.'" s="'.$cellStyleIdx.'" t="n"><v>'. |
|
77
|
|
|
intval(Support::convertDateTime($value)).'</v></c>'); |
|
78
|
|
|
|
|
79
|
|
|
break; |
|
80
|
|
|
case 'n_datetime': |
|
81
|
|
|
$file->write('<c r="'.$cellName.'" s="'.$cellStyleIdx.'" t="n"><v>'. |
|
82
|
|
|
Support::convertDateTime($value).'</v></c>'); |
|
83
|
|
|
|
|
84
|
|
|
break; |
|
85
|
|
|
case 'n_numeric': |
|
86
|
|
|
$file->write('<c r="'.$cellName.'" s="'.$cellStyleIdx.'" t="n"><v>'. |
|
87
|
|
|
Support::xmlSpecialChars($value).'</v></c>'); //int,float,currency |
|
88
|
|
|
break; |
|
89
|
|
|
case 'n_string': |
|
90
|
|
|
$file->write('<c r="'.$cellName.'" s="'.$cellStyleIdx.'" t="inlineStr"><is><t>'. |
|
91
|
|
|
Support::xmlSpecialChars($value).'</t></is></c>'); |
|
92
|
|
|
|
|
93
|
|
|
break; |
|
94
|
|
|
case 'n_auto': |
|
95
|
|
|
default: //auto-detect unknown column types |
|
96
|
|
|
if (!is_string($value) || '0' == $value || ('0' != $value[0] && ctype_digit($value)) || preg_match("/^\-?(0|[1-9][0-9]*)(\.[0-9]+)?$/", $value) |
|
97
|
|
|
) { //int,float,currency |
|
98
|
|
|
$file->write( |
|
99
|
|
|
'<c r="'.$cellName.'" s="'.$cellStyleIdx.'" t="n"><v>'. |
|
100
|
|
|
Support::xmlSpecialChars($value).'</v></c>' |
|
101
|
|
|
); |
|
102
|
|
|
} else { //implied: ($cell_format=='string') |
|
103
|
|
|
$file->write( |
|
104
|
|
|
'<c r="'.$cellName.'" s="'.$cellStyleIdx.'" t="inlineStr"><is><t>'. |
|
105
|
|
|
Support::xmlSpecialChars($value).'</t></is></c>' |
|
106
|
|
|
); |
|
107
|
|
|
} |
|
108
|
|
|
|
|
109
|
|
|
break; |
|
110
|
|
|
} |
|
111
|
|
|
} |
|
112
|
|
|
} |
|
113
|
|
|
|
|
114
|
|
|
/** |
|
115
|
|
|
* @param array $colWidths |
|
116
|
|
|
* @param bool $isTabSelected |
|
117
|
|
|
*/ |
|
118
|
|
|
public function initContent(array $colWidths = [], $isTabSelected = false) |
|
119
|
|
|
{ |
|
120
|
|
|
$writer = $this->getFileWriter(); |
|
121
|
|
|
$tabSelected = $isTabSelected ? 'true' : 'false'; |
|
122
|
|
|
$maxCell = Support::xlsCell(Builder::EXCEL_2007_MAX_ROW, Builder::EXCEL_2007_MAX_COL); //XFE1048577 |
|
123
|
|
|
$writer->write('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'."\n"); |
|
124
|
|
|
$writer->write( |
|
125
|
|
|
<<<'EOF' |
|
126
|
|
|
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"><sheetPr filterMode="false"><pageSetUpPr fitToPage="false"/></sheetPr> |
|
127
|
|
|
EOF |
|
128
|
|
|
); |
|
129
|
|
|
$this->maxCellTagStart = $this->fileWriter->ftell(); |
|
130
|
|
|
$writer->write('<dimension ref="A1:'.$maxCell.'"/>'); |
|
131
|
|
|
$this->maxCellTagEnd = $this->fileWriter->ftell(); |
|
132
|
|
|
$writer->write('<sheetViews>'); |
|
133
|
|
|
$writer->write( |
|
134
|
|
|
<<<EOF |
|
135
|
|
|
<sheetView colorId="64" defaultGridColor="true" rightToLeft="false" showFormulas="false" showGridLines="true" showOutlineSymbols="true" showRowColHeaders="true" showZeros="true" tabSelected="$tabSelected" topLeftCell="A1" view="normal" windowProtection="false" workbookViewId="0" zoomScale="100" zoomScaleNormal="100" zoomScalePageLayoutView="100">' |
|
136
|
|
|
EOF |
|
137
|
|
|
); |
|
138
|
|
|
if ($this->freezeRows && $this->freezeColumns) { |
|
139
|
|
|
$this->writeFreezeRowsAndColumns(); |
|
140
|
|
|
} elseif ($this->freezeRows) { |
|
141
|
|
|
$this->writeFreezeRows(); |
|
142
|
|
|
} elseif ($this->freezeColumns) { |
|
143
|
|
|
$this->writeFreezeColumns(); |
|
144
|
|
|
} else { // not frozen |
|
145
|
|
|
$writer->write('<selection activeCell="A1" activeCellId="0" pane="topLeft" sqref="A1"/>'); |
|
146
|
|
|
} |
|
147
|
|
|
$writer->write('</sheetView></sheetViews><cols>'); |
|
148
|
|
|
$i = 0; |
|
149
|
|
|
if (!empty($colWidths)) { |
|
150
|
|
|
foreach ($colWidths as $colWidth) { |
|
151
|
|
|
$writer->write( |
|
152
|
|
|
'<col collapsed="false" hidden="false" max="'.($i + 1).'" min="'.($i + 1). |
|
153
|
|
|
'" style="0" customWidth="true" width="'.floatval($colWidth).'"/>' |
|
154
|
|
|
); |
|
155
|
|
|
++$i; |
|
156
|
|
|
} |
|
157
|
|
|
} |
|
158
|
|
|
$writer->write( |
|
159
|
|
|
'<col collapsed="false" hidden="false" max="1024" min="'.($i + 1). |
|
160
|
|
|
'" style="0" customWidth="false" width="11.5"/>'.'</cols><sheetData>' |
|
161
|
|
|
); |
|
162
|
|
|
} |
|
163
|
|
|
|
|
164
|
|
|
public function finallyContent() |
|
165
|
|
|
{ |
|
166
|
|
|
$this->fileWriter->write('</sheetData>'); |
|
167
|
|
|
|
|
168
|
|
|
if (!empty($this->mergeCells)) { |
|
169
|
|
|
$this->fileWriter->write('<mergeCells>'); |
|
170
|
|
|
foreach ($this->mergeCells as $range) { |
|
171
|
|
|
$this->fileWriter->write('<mergeCell ref="'.$range.'"/>'); |
|
172
|
|
|
} |
|
173
|
|
|
$this->fileWriter->write('</mergeCells>'); |
|
174
|
|
|
} |
|
175
|
|
|
|
|
176
|
|
|
$maxCell = Support::xlsCell($this->rowCount - 1, count($this->columns) - 1); |
|
177
|
|
|
|
|
178
|
|
|
if ($this->autoFilter) { |
|
179
|
|
|
$this->fileWriter->write('<autoFilter ref="A1:'.$maxCell.'"/>'); |
|
180
|
|
|
} |
|
181
|
|
|
|
|
182
|
|
|
$this->fileWriter->write('<printOptions headings="false" gridLines="false" gridLinesSet="true" horizontalCentered="false" verticalCentered="false"/>'); |
|
183
|
|
|
$this->fileWriter->write('<pageMargins left="0.5" right="0.5" top="1.0" bottom="1.0" header="0.5" footer="0.5"/>'); |
|
184
|
|
|
$this->fileWriter->write('<pageSetup blackAndWhite="false" cellComments="none" copies="1" draft="false" firstPageNumber="1" fitToHeight="1" fitToWidth="1" horizontalDpi="300" orientation="portrait" pageOrder="downThenOver" paperSize="1" scale="100" useFirstPageNumber="true" usePrinterDefaults="false" verticalDpi="300"/>'); |
|
185
|
|
|
$this->fileWriter->write('<headerFooter differentFirst="false" differentOddEven="false">'); |
|
186
|
|
|
$this->fileWriter->write('<oddHeader>&C&"Times New Roman,Regular"&12&A</oddHeader>'); |
|
187
|
|
|
$this->fileWriter->write('<oddFooter>&C&"Times New Roman,Regular"&12Page &P</oddFooter>'); |
|
188
|
|
|
$this->fileWriter->write('</headerFooter>'); |
|
189
|
|
|
$this->fileWriter->write('</worksheet>'); |
|
190
|
|
|
|
|
191
|
|
|
$maxCellTag = '<dimension ref="A1:'.$maxCell.'"/>'; |
|
192
|
|
|
$paddingLength = $this->maxCellTagEnd - $this->maxCellTagStart - strlen($maxCellTag); |
|
193
|
|
|
$this->fileWriter->fseek($this->maxCellTagStart); |
|
194
|
|
|
$this->fileWriter->write($maxCellTag.str_repeat(' ', $paddingLength)); |
|
195
|
|
|
$this->fileWriter->close(); |
|
196
|
|
|
$this->finalized = true; |
|
197
|
|
|
} |
|
198
|
|
|
|
|
199
|
|
|
protected function writeFreezeRowsAndColumns() |
|
200
|
|
|
{ |
|
201
|
|
|
$writer = $this->getFileWriter(); |
|
202
|
|
|
$writer->write( |
|
203
|
|
|
'<pane ySplit="'.$this->freezeRows.'" xSplit="'.$this->freezeColumns. |
|
204
|
|
|
'" topLeftCell="'.Support::xlsCell($this->freezeRows, $this->freezeColumns). |
|
205
|
|
|
'" activePane="bottomRight" state="frozen"/>'.'<selection activeCell="'.Support::xlsCell($this->freezeRows, 0). |
|
206
|
|
|
'" activeCellId="0" pane="topRight" sqref="'.Support::xlsCell($this->freezeRows, 0).'"/>'. |
|
207
|
|
|
'<selection activeCell="'.Support::xlsCell(0, $this->freezeColumns). |
|
208
|
|
|
'" activeCellId="0" pane="bottomLeft" sqref="'.Support::xlsCell(0, $this->freezeColumns).'"/>'. |
|
209
|
|
|
'<selection activeCell="'.Support::xlsCell($this->freezeRows, $this->freezeColumns). |
|
210
|
|
|
'" activeCellId="0" pane="bottomRight" sqref="'.Support::xlsCell($this->freezeRows, $this->freezeColumns).'"/>' |
|
211
|
|
|
); |
|
212
|
|
|
} |
|
213
|
|
|
|
|
214
|
|
|
protected function writeFreezeRows() |
|
215
|
|
|
{ |
|
216
|
|
|
$writer = $this->getFileWriter(); |
|
217
|
|
|
$writer->write( |
|
218
|
|
|
'<pane ySplit="'.$this->freezeRows.'" topLeftCell="'. |
|
219
|
|
|
Support::xlsCell($this->freezeRows, 0).'" activePane="bottomLeft" state="frozen"/>'. |
|
220
|
|
|
'<selection activeCell="'.Support::xlsCell($this->freezeRows, 0). |
|
221
|
|
|
'" activeCellId="0" pane="bottomLeft" sqref="'.Support::xlsCell($this->freezeRows, 0).'"/>' |
|
222
|
|
|
); |
|
223
|
|
|
} |
|
224
|
|
|
|
|
225
|
|
|
protected function writeFreezeColumns() |
|
226
|
|
|
{ |
|
227
|
|
|
$writer = $this->getFileWriter(); |
|
228
|
|
|
$writer->write( |
|
229
|
|
|
'<pane xSplit="'.$this->freezeColumns.'" topLeftCell="'. |
|
230
|
|
|
Support::xlsCell(0, $this->freezeColumns).'" activePane="topRight" state="frozen"/>'. |
|
231
|
|
|
'<selection activeCell="'.Support::xlsCell(0, $this->freezeColumns). |
|
232
|
|
|
'" activeCellId="0" pane="topRight" sqref="'.Support::xlsCell(0, $this->freezeColumns).'"/>' |
|
233
|
|
|
); |
|
234
|
|
|
} |
|
235
|
|
|
|
|
236
|
|
|
/** |
|
237
|
|
|
* @param string|null $fileWriter |
|
238
|
|
|
* |
|
239
|
|
|
* @return Sheet |
|
240
|
|
|
*/ |
|
241
|
|
|
public function setFileWriter($fileWriter = null) |
|
242
|
|
|
{ |
|
243
|
|
|
switch ($fileWriter) { |
|
244
|
|
|
case 'xlsx': |
|
245
|
|
|
default: |
|
246
|
|
|
// 当前只实现 XlsxWriterBuffer 写入方式 后期可添加redis等方案 继承WriterBufferInterface即可 |
|
247
|
|
|
$this->fileWriter = new XlsxWriterBuffer($this->filename); |
|
248
|
|
|
} |
|
249
|
|
|
|
|
250
|
|
|
return $this; |
|
251
|
|
|
} |
|
252
|
|
|
|
|
253
|
|
|
/** |
|
254
|
|
|
* @return WriterBufferInterface |
|
255
|
|
|
*/ |
|
256
|
|
|
public function getFileWriter() |
|
257
|
|
|
{ |
|
258
|
|
|
return $this->fileWriter; |
|
259
|
|
|
} |
|
260
|
|
|
} |
|
261
|
|
|
|
viliy /
xlsx-writer
