|
1
|
|
|
<?php |
|
2
|
|
|
|
|
3
|
|
|
namespace luya\helpers; |
|
4
|
|
|
|
|
5
|
|
|
use ZipArchive; |
|
6
|
|
|
|
|
7
|
|
|
/* |
|
8
|
|
|
* @license MIT License |
|
9
|
|
|
* |
|
10
|
|
|
* @see https://raw.githubusercontent.com/mk-j/PHP_XLSXWriter/master/xlsxwriter.class.php |
|
11
|
|
|
* */ |
|
12
|
|
|
|
|
13
|
|
|
class XLSXWriter |
|
14
|
|
|
{ |
|
15
|
|
|
//http://www.ecma-international.org/publications/standards/Ecma-376.htm |
|
16
|
|
|
//http://officeopenxml.com/SSstyles.php |
|
17
|
|
|
//------------------------------------------------------------------ |
|
18
|
|
|
//http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010073849.aspx |
|
19
|
|
|
const EXCEL_2007_MAX_ROW = 1048576; |
|
20
|
|
|
const EXCEL_2007_MAX_COL = 16384; |
|
21
|
|
|
//------------------------------------------------------------------ |
|
22
|
|
|
protected $title; |
|
23
|
|
|
protected $subject; |
|
24
|
|
|
protected $author; |
|
25
|
|
|
protected $company; |
|
26
|
|
|
protected $description; |
|
27
|
|
|
protected $keywords = array(); |
|
28
|
|
|
|
|
29
|
|
|
protected $current_sheet; |
|
30
|
|
|
protected $sheets = array(); |
|
31
|
|
|
protected $temp_files = array(); |
|
32
|
|
|
protected $cell_styles = array(); |
|
33
|
|
|
protected $number_formats = array(); |
|
34
|
|
|
|
|
35
|
|
|
public function __construct() |
|
36
|
|
|
{ |
|
37
|
|
|
if (!ini_get('date.timezone')) { |
|
38
|
|
|
//using date functions can kick out warning if this isn't set |
|
39
|
|
|
date_default_timezone_set('UTC'); |
|
40
|
|
|
} |
|
41
|
|
|
$this->addCellStyle($number_format = 'GENERAL', $style_string = null); |
|
42
|
|
|
$this->addCellStyle($number_format = 'GENERAL', $style_string = null); |
|
43
|
|
|
$this->addCellStyle($number_format = 'GENERAL', $style_string = null); |
|
44
|
|
|
$this->addCellStyle($number_format = 'GENERAL', $style_string = null); |
|
45
|
|
|
} |
|
46
|
|
|
|
|
47
|
|
|
public function setTitle($title = '') |
|
48
|
|
|
{ |
|
49
|
|
|
$this->title = $title; |
|
50
|
|
|
} |
|
51
|
|
|
|
|
52
|
|
|
public function setSubject($subject = '') |
|
53
|
|
|
{ |
|
54
|
|
|
$this->subject = $subject; |
|
55
|
|
|
} |
|
56
|
|
|
|
|
57
|
|
|
public function setAuthor($author = '') |
|
58
|
|
|
{ |
|
59
|
|
|
$this->author = $author; |
|
60
|
|
|
} |
|
61
|
|
|
|
|
62
|
|
|
public function setCompany($company = '') |
|
63
|
|
|
{ |
|
64
|
|
|
$this->company = $company; |
|
65
|
|
|
} |
|
66
|
|
|
|
|
67
|
|
|
public function setKeywords($keywords = '') |
|
68
|
|
|
{ |
|
69
|
|
|
$this->keywords = $keywords; |
|
|
|
|
|
|
70
|
|
|
} |
|
71
|
|
|
|
|
72
|
|
|
public function setDescription($description = '') |
|
73
|
|
|
{ |
|
74
|
|
|
$this->description = $description; |
|
75
|
|
|
} |
|
76
|
|
|
|
|
77
|
|
|
public function setTempDir($tempdir = '') |
|
78
|
|
|
{ |
|
79
|
|
|
$this->tempdir = $tempdir; |
|
|
|
|
|
|
80
|
|
|
} |
|
81
|
|
|
|
|
82
|
|
|
public function __destruct() |
|
83
|
|
|
{ |
|
84
|
|
|
if (!empty($this->temp_files)) { |
|
85
|
|
|
foreach ($this->temp_files as $temp_file) { |
|
86
|
|
|
@unlink($temp_file); |
|
|
|
|
|
|
87
|
|
|
} |
|
88
|
|
|
} |
|
89
|
|
|
} |
|
90
|
|
|
|
|
91
|
|
|
protected function tempFilename() |
|
92
|
|
|
{ |
|
93
|
|
|
$tempdir = !empty($this->tempdir) ? $this->tempdir : sys_get_temp_dir(); |
|
94
|
|
|
$filename = tempnam($tempdir, "xlsx_writer_"); |
|
95
|
|
|
$this->temp_files[] = $filename; |
|
96
|
|
|
return $filename; |
|
97
|
|
|
} |
|
98
|
|
|
|
|
99
|
|
|
public function writeToStdOut() |
|
100
|
|
|
{ |
|
101
|
|
|
$temp_file = $this->tempFilename(); |
|
102
|
|
|
self::writeToFile($temp_file); |
|
103
|
|
|
readfile($temp_file); |
|
104
|
|
|
} |
|
105
|
|
|
|
|
106
|
|
|
public function writeToString() |
|
107
|
|
|
{ |
|
108
|
|
|
$temp_file = $this->tempFilename(); |
|
109
|
|
|
self::writeToFile($temp_file); |
|
110
|
|
|
$string = file_get_contents($temp_file); |
|
111
|
|
|
return $string; |
|
112
|
|
|
} |
|
113
|
|
|
|
|
114
|
|
|
public function writeToFile($filename) |
|
115
|
|
|
{ |
|
116
|
|
|
foreach ($this->sheets as $sheet_name => $sheet) { |
|
117
|
|
|
self::finalizeSheet($sheet_name);//making sure all footers have been written |
|
118
|
|
|
} |
|
119
|
|
|
|
|
120
|
|
|
if (file_exists($filename)) { |
|
121
|
|
|
if (is_writable($filename)) { |
|
122
|
|
|
@unlink($filename); //if the zip already exists, remove it |
|
|
|
|
|
|
123
|
|
|
} else { |
|
124
|
|
|
self::log("Error in " . __CLASS__ . "::" . __FUNCTION__ . ", file is not writeable."); |
|
125
|
|
|
return; |
|
126
|
|
|
} |
|
127
|
|
|
} |
|
128
|
|
|
$zip = new ZipArchive(); |
|
129
|
|
|
if (empty($this->sheets)) { |
|
130
|
|
|
self::log("Error in " . __CLASS__ . "::" . __FUNCTION__ . ", no worksheets defined."); |
|
131
|
|
|
return; |
|
132
|
|
|
} |
|
133
|
|
|
if (!$zip->open($filename, ZipArchive::CREATE)) { |
|
134
|
|
|
self::log("Error in " . __CLASS__ . "::" . __FUNCTION__ . ", unable to create zip."); |
|
135
|
|
|
return; |
|
136
|
|
|
} |
|
137
|
|
|
|
|
138
|
|
|
$zip->addEmptyDir("docProps/"); |
|
139
|
|
|
$zip->addFromString("docProps/app.xml", self::buildAppXML()); |
|
140
|
|
|
$zip->addFromString("docProps/core.xml", self::buildCoreXML()); |
|
141
|
|
|
|
|
142
|
|
|
$zip->addEmptyDir("_rels/"); |
|
143
|
|
|
$zip->addFromString("_rels/.rels", self::buildRelationshipsXML()); |
|
144
|
|
|
|
|
145
|
|
|
$zip->addEmptyDir("xl/worksheets/"); |
|
146
|
|
|
foreach ($this->sheets as $sheet) { |
|
147
|
|
|
$zip->addFile($sheet->filename, "xl/worksheets/" . $sheet->xmlname); |
|
148
|
|
|
} |
|
149
|
|
|
$zip->addFromString("xl/workbook.xml", self::buildWorkbookXML()); |
|
150
|
|
|
$zip->addFile( |
|
151
|
|
|
$this->writeStylesXML(), |
|
152
|
|
|
"xl/styles.xml" |
|
153
|
|
|
); //$zip->addFromString("xl/styles.xml" , self::buildStylesXML() ); |
|
|
|
|
|
|
154
|
|
|
$zip->addFromString("[Content_Types].xml", self::buildContentTypesXML()); |
|
155
|
|
|
|
|
156
|
|
|
$zip->addEmptyDir("xl/_rels/"); |
|
157
|
|
|
$zip->addFromString("xl/_rels/workbook.xml.rels", self::buildWorkbookRelsXML()); |
|
158
|
|
|
$zip->close(); |
|
159
|
|
|
} |
|
160
|
|
|
|
|
161
|
|
|
protected function initializeSheet( |
|
162
|
|
|
$sheet_name, |
|
163
|
|
|
$col_widths = array(), |
|
164
|
|
|
$auto_filter = false, |
|
165
|
|
|
$freeze_rows = false, |
|
166
|
|
|
$freeze_columns = false |
|
167
|
|
|
) { |
|
168
|
|
|
//if already initialized |
|
169
|
|
|
if ($this->current_sheet == $sheet_name || isset($this->sheets[$sheet_name])) { |
|
170
|
|
|
return; |
|
171
|
|
|
} |
|
172
|
|
|
|
|
173
|
|
|
$sheet_filename = $this->tempFilename(); |
|
174
|
|
|
$sheet_xmlname = 'sheet' . (count($this->sheets) + 1) . ".xml"; |
|
175
|
|
|
$this->sheets[$sheet_name] = (object)array( |
|
176
|
|
|
'filename' => $sheet_filename, |
|
177
|
|
|
'sheetname' => $sheet_name, |
|
178
|
|
|
'xmlname' => $sheet_xmlname, |
|
179
|
|
|
'row_count' => 0, |
|
180
|
|
|
'file_writer' => new XLSXWriter_BuffererWriter($sheet_filename), |
|
181
|
|
|
'columns' => array(), |
|
182
|
|
|
'merge_cells' => array(), |
|
183
|
|
|
'max_cell_tag_start' => 0, |
|
184
|
|
|
'max_cell_tag_end' => 0, |
|
185
|
|
|
'auto_filter' => $auto_filter, |
|
186
|
|
|
'freeze_rows' => $freeze_rows, |
|
187
|
|
|
'freeze_columns' => $freeze_columns, |
|
188
|
|
|
'finalized' => false, |
|
189
|
|
|
); |
|
190
|
|
|
$sheet = &$this->sheets[$sheet_name]; |
|
191
|
|
|
$tabselected = count($this->sheets) == 1 ? 'true' : 'false';//only first sheet is selected |
|
192
|
|
|
$max_cell = XLSXWriter::xlsCell(self::EXCEL_2007_MAX_ROW, self::EXCEL_2007_MAX_COL);//XFE1048577 |
|
193
|
|
|
$sheet->file_writer->write('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' . "\n"); |
|
194
|
|
|
$sheet->file_writer->write('<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">'); |
|
195
|
|
|
$sheet->file_writer->write('<sheetPr filterMode="false">'); |
|
196
|
|
|
$sheet->file_writer->write('<pageSetUpPr fitToPage="false"/>'); |
|
197
|
|
|
$sheet->file_writer->write('</sheetPr>'); |
|
198
|
|
|
$sheet->max_cell_tag_start = $sheet->file_writer->ftell(); |
|
199
|
|
|
$sheet->file_writer->write('<dimension ref="A1:' . $max_cell . '"/>'); |
|
200
|
|
|
$sheet->max_cell_tag_end = $sheet->file_writer->ftell(); |
|
201
|
|
|
$sheet->file_writer->write('<sheetViews>'); |
|
202
|
|
|
$sheet->file_writer->write('<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">'); |
|
203
|
|
|
if ($sheet->freeze_rows && $sheet->freeze_columns) { |
|
204
|
|
|
$sheet->file_writer->write('<pane ySplit="' . $sheet->freeze_rows . '" xSplit="' . $sheet->freeze_columns . '" topLeftCell="' . self::xlsCell( |
|
205
|
|
|
$sheet->freeze_rows, |
|
206
|
|
|
$sheet->freeze_columns |
|
207
|
|
|
) . '" activePane="bottomRight" state="frozen"/>'); |
|
208
|
|
|
$sheet->file_writer->write('<selection activeCell="' . self::xlsCell( |
|
209
|
|
|
$sheet->freeze_rows, |
|
210
|
|
|
0 |
|
211
|
|
|
) . '" activeCellId="0" pane="topRight" sqref="' . self::xlsCell($sheet->freeze_rows, 0) . '"/>'); |
|
212
|
|
|
$sheet->file_writer->write('<selection activeCell="' . self::xlsCell( |
|
213
|
|
|
0, |
|
214
|
|
|
$sheet->freeze_columns |
|
215
|
|
|
) . '" activeCellId="0" pane="bottomLeft" sqref="' . self::xlsCell( |
|
216
|
|
|
0, |
|
217
|
|
|
$sheet->freeze_columns |
|
218
|
|
|
) . '"/>'); |
|
219
|
|
|
$sheet->file_writer->write('<selection activeCell="' . self::xlsCell( |
|
220
|
|
|
$sheet->freeze_rows, |
|
221
|
|
|
$sheet->freeze_columns |
|
222
|
|
|
) . '" activeCellId="0" pane="bottomRight" sqref="' . self::xlsCell( |
|
223
|
|
|
$sheet->freeze_rows, |
|
224
|
|
|
$sheet->freeze_columns |
|
225
|
|
|
) . '"/>'); |
|
226
|
|
|
} elseif ($sheet->freeze_rows) { |
|
227
|
|
|
$sheet->file_writer->write('<pane ySplit="' . $sheet->freeze_rows . '" topLeftCell="' . self::xlsCell( |
|
228
|
|
|
$sheet->freeze_rows, |
|
229
|
|
|
0 |
|
230
|
|
|
) . '" activePane="bottomLeft" state="frozen"/>'); |
|
231
|
|
|
$sheet->file_writer->write('<selection activeCell="' . self::xlsCell( |
|
232
|
|
|
$sheet->freeze_rows, |
|
233
|
|
|
0 |
|
234
|
|
|
) . '" activeCellId="0" pane="bottomLeft" sqref="' . self::xlsCell( |
|
235
|
|
|
$sheet->freeze_rows, |
|
236
|
|
|
0 |
|
237
|
|
|
) . '"/>'); |
|
238
|
|
|
} elseif ($sheet->freeze_columns) { |
|
239
|
|
|
$sheet->file_writer->write('<pane xSplit="' . $sheet->freeze_columns . '" topLeftCell="' . self::xlsCell( |
|
240
|
|
|
0, |
|
241
|
|
|
$sheet->freeze_columns |
|
242
|
|
|
) . '" activePane="topRight" state="frozen"/>'); |
|
243
|
|
|
$sheet->file_writer->write('<selection activeCell="' . self::xlsCell( |
|
244
|
|
|
0, |
|
245
|
|
|
$sheet->freeze_columns |
|
246
|
|
|
) . '" activeCellId="0" pane="topRight" sqref="' . self::xlsCell( |
|
247
|
|
|
0, |
|
248
|
|
|
$sheet->freeze_columns |
|
249
|
|
|
) . '"/>'); |
|
250
|
|
|
} else { // not frozen |
|
251
|
|
|
$sheet->file_writer->write('<selection activeCell="A1" activeCellId="0" pane="topLeft" sqref="A1"/>'); |
|
252
|
|
|
} |
|
253
|
|
|
$sheet->file_writer->write('</sheetView>'); |
|
254
|
|
|
$sheet->file_writer->write('</sheetViews>'); |
|
255
|
|
|
$sheet->file_writer->write('<cols>'); |
|
256
|
|
|
$i = 0; |
|
257
|
|
|
if (!empty($col_widths)) { |
|
258
|
|
|
foreach ($col_widths as $column_width) { |
|
259
|
|
|
$sheet->file_writer->write('<col collapsed="false" hidden="false" max="' . ($i + 1) . '" min="' . ($i + 1) . '" style="0" customWidth="true" width="' . floatval($column_width) . '"/>'); |
|
260
|
|
|
$i++; |
|
261
|
|
|
} |
|
262
|
|
|
} |
|
263
|
|
|
$sheet->file_writer->write('<col collapsed="false" hidden="false" max="1024" min="' . ($i + 1) . '" style="0" customWidth="false" width="11.5"/>'); |
|
264
|
|
|
$sheet->file_writer->write('</cols>'); |
|
265
|
|
|
$sheet->file_writer->write('<sheetData>'); |
|
266
|
|
|
} |
|
267
|
|
|
|
|
268
|
|
|
private function addCellStyle($number_format, $cell_style_string) |
|
269
|
|
|
{ |
|
270
|
|
|
$number_format_idx = self::add_to_list_get_index($this->number_formats, $number_format); |
|
271
|
|
|
$lookup_string = $number_format_idx . ";" . $cell_style_string; |
|
272
|
|
|
$cell_style_idx = self::add_to_list_get_index($this->cell_styles, $lookup_string); |
|
273
|
|
|
return $cell_style_idx; |
|
274
|
|
|
} |
|
275
|
|
|
|
|
276
|
|
|
private function initializeColumnTypes($header_types) |
|
277
|
|
|
{ |
|
278
|
|
|
$column_types = array(); |
|
279
|
|
|
foreach ($header_types as $v) { |
|
280
|
|
|
$number_format = self::numberFormatStandardized($v); |
|
281
|
|
|
$number_format_type = self::determineNumberFormatType($number_format); |
|
282
|
|
|
$cell_style_idx = $this->addCellStyle($number_format, $style_string = null); |
|
283
|
|
|
$column_types[] = array( |
|
284
|
|
|
'number_format' => $number_format,//contains excel format like 'YYYY-MM-DD HH:MM:SS' |
|
285
|
|
|
'number_format_type' => $number_format_type, //contains friendly format like 'datetime' |
|
286
|
|
|
'default_cell_style' => $cell_style_idx, |
|
287
|
|
|
); |
|
288
|
|
|
} |
|
289
|
|
|
return $column_types; |
|
290
|
|
|
} |
|
291
|
|
|
|
|
292
|
|
|
public function writeSheetHeader($sheet_name, array $header_types, $col_options = null) |
|
293
|
|
|
{ |
|
294
|
|
|
if (empty($sheet_name) || empty($header_types) || !empty($this->sheets[$sheet_name])) { |
|
295
|
|
|
return; |
|
296
|
|
|
} |
|
297
|
|
|
|
|
298
|
|
|
$suppress_row = isset($col_options['suppress_row']) ? intval($col_options['suppress_row']) : false; |
|
299
|
|
|
if (is_bool($col_options)) { |
|
300
|
|
|
self::log("Warning! passing $suppress_row=false|true to writeSheetHeader() is deprecated, this will be removed in a future version."); |
|
301
|
|
|
$suppress_row = intval($col_options); |
|
302
|
|
|
} |
|
303
|
|
|
$style = &$col_options; |
|
304
|
|
|
|
|
305
|
|
|
$col_widths = isset($col_options['widths']) ? (array)$col_options['widths'] : array(); |
|
306
|
|
|
$auto_filter = isset($col_options['auto_filter']) ? intval($col_options['auto_filter']) : false; |
|
307
|
|
|
$freeze_rows = isset($col_options['freeze_rows']) ? intval($col_options['freeze_rows']) : false; |
|
308
|
|
|
$freeze_columns = isset($col_options['freeze_columns']) ? intval($col_options['freeze_columns']) : false; |
|
309
|
|
|
self::initializeSheet($sheet_name, $col_widths, $auto_filter, $freeze_rows, $freeze_columns); |
|
|
|
|
|
|
310
|
|
|
$sheet = &$this->sheets[$sheet_name]; |
|
311
|
|
|
$sheet->columns = $this->initializeColumnTypes($header_types); |
|
312
|
|
|
if (!$suppress_row) { |
|
|
|
|
|
|
313
|
|
|
$header_row = array_keys($header_types); |
|
314
|
|
|
|
|
315
|
|
|
$sheet->file_writer->write('<row collapsed="false" customFormat="false" customHeight="false" hidden="false" ht="12.1" outlineLevel="0" r="' . (1) . '">'); |
|
316
|
|
|
foreach ($header_row as $c => $v) { |
|
317
|
|
|
$cell_style_idx = empty($style) ? $sheet->columns[$c]['default_cell_style'] : $this->addCellStyle( |
|
318
|
|
|
'GENERAL', |
|
319
|
|
|
json_encode(isset($style[0]) ? $style[$c] : $style) |
|
320
|
|
|
); |
|
321
|
|
|
$this->writeCell($sheet->file_writer, 0, $c, $v, $number_format_type = 'n_string', $cell_style_idx); |
|
322
|
|
|
} |
|
323
|
|
|
$sheet->file_writer->write('</row>'); |
|
324
|
|
|
$sheet->row_count++; |
|
325
|
|
|
} |
|
326
|
|
|
$this->current_sheet = $sheet_name; |
|
327
|
|
|
} |
|
328
|
|
|
|
|
329
|
|
|
public function writeSheetRow($sheet_name, array $row, $row_options = null) |
|
330
|
|
|
{ |
|
331
|
|
|
if (empty($sheet_name)) { |
|
332
|
|
|
return; |
|
333
|
|
|
} |
|
334
|
|
|
|
|
335
|
|
|
self::initializeSheet($sheet_name); |
|
336
|
|
|
$sheet = &$this->sheets[$sheet_name]; |
|
337
|
|
|
if (count($sheet->columns) < count($row)) { |
|
338
|
|
|
$default_column_types = $this->initializeColumnTypes(array_fill( |
|
339
|
|
|
$from = 0, |
|
340
|
|
|
$until = count($row), |
|
341
|
|
|
'GENERAL' |
|
342
|
|
|
));//will map to n_auto |
|
343
|
|
|
$sheet->columns = array_merge((array)$sheet->columns, $default_column_types); |
|
344
|
|
|
} |
|
345
|
|
|
|
|
346
|
|
|
if (!empty($row_options)) { |
|
347
|
|
|
$ht = isset($row_options['height']) ? floatval($row_options['height']) : 12.1; |
|
348
|
|
|
$customHt = isset($row_options['height']) ? true : false; |
|
349
|
|
|
$hidden = isset($row_options['hidden']) ? (bool)($row_options['hidden']) : false; |
|
350
|
|
|
$collapsed = isset($row_options['collapsed']) ? (bool)($row_options['collapsed']) : false; |
|
351
|
|
|
$sheet->file_writer->write('<row collapsed="' . ($collapsed) . '" customFormat="false" customHeight="' . ($customHt) . '" hidden="' . ($hidden) . '" ht="' . ($ht) . '" outlineLevel="0" r="' . ($sheet->row_count + 1) . '">'); |
|
352
|
|
|
} else { |
|
353
|
|
|
$sheet->file_writer->write('<row collapsed="false" customFormat="false" customHeight="false" hidden="false" ht="12.1" outlineLevel="0" r="' . ($sheet->row_count + 1) . '">'); |
|
354
|
|
|
} |
|
355
|
|
|
|
|
356
|
|
|
$style = &$row_options; |
|
357
|
|
|
$c = 0; |
|
358
|
|
|
foreach ($row as $v) { |
|
359
|
|
|
$number_format = $sheet->columns[$c]['number_format']; |
|
360
|
|
|
$number_format_type = $sheet->columns[$c]['number_format_type']; |
|
361
|
|
|
$cell_style_idx = empty($style) ? $sheet->columns[$c]['default_cell_style'] : $this->addCellStyle( |
|
362
|
|
|
$number_format, |
|
363
|
|
|
json_encode(isset($style[0]) ? $style[$c] : $style) |
|
364
|
|
|
); |
|
365
|
|
|
$this->writeCell($sheet->file_writer, $sheet->row_count, $c, $v, $number_format_type, $cell_style_idx); |
|
366
|
|
|
$c++; |
|
367
|
|
|
} |
|
368
|
|
|
$sheet->file_writer->write('</row>'); |
|
369
|
|
|
$sheet->row_count++; |
|
370
|
|
|
$this->current_sheet = $sheet_name; |
|
371
|
|
|
} |
|
372
|
|
|
|
|
373
|
|
|
public function countSheetRows($sheet_name = '') |
|
374
|
|
|
{ |
|
375
|
|
|
$sheet_name = $sheet_name ?: $this->current_sheet; |
|
376
|
|
|
return array_key_exists($sheet_name, $this->sheets) ? $this->sheets[$sheet_name]->row_count : 0; |
|
377
|
|
|
} |
|
378
|
|
|
|
|
379
|
|
|
protected function finalizeSheet($sheet_name) |
|
380
|
|
|
{ |
|
381
|
|
|
if (empty($sheet_name) || $this->sheets[$sheet_name]->finalized) { |
|
382
|
|
|
return; |
|
383
|
|
|
} |
|
384
|
|
|
|
|
385
|
|
|
$sheet = &$this->sheets[$sheet_name]; |
|
386
|
|
|
|
|
387
|
|
|
$sheet->file_writer->write('</sheetData>'); |
|
388
|
|
|
|
|
389
|
|
|
if (!empty($sheet->merge_cells)) { |
|
390
|
|
|
$sheet->file_writer->write('<mergeCells>'); |
|
391
|
|
|
foreach ($sheet->merge_cells as $range) { |
|
392
|
|
|
$sheet->file_writer->write('<mergeCell ref="' . $range . '"/>'); |
|
393
|
|
|
} |
|
394
|
|
|
$sheet->file_writer->write('</mergeCells>'); |
|
395
|
|
|
} |
|
396
|
|
|
|
|
397
|
|
|
$max_cell = self::xlsCell($sheet->row_count - 1, count($sheet->columns) - 1); |
|
398
|
|
|
|
|
399
|
|
|
if ($sheet->auto_filter) { |
|
400
|
|
|
$sheet->file_writer->write('<autoFilter ref="A1:' . $max_cell . '"/>'); |
|
401
|
|
|
} |
|
402
|
|
|
|
|
403
|
|
|
$sheet->file_writer->write('<printOptions headings="false" gridLines="false" gridLinesSet="true" horizontalCentered="false" verticalCentered="false"/>'); |
|
404
|
|
|
$sheet->file_writer->write('<pageMargins left="0.5" right="0.5" top="1.0" bottom="1.0" header="0.5" footer="0.5"/>'); |
|
405
|
|
|
$sheet->file_writer->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"/>'); |
|
406
|
|
|
$sheet->file_writer->write('<headerFooter differentFirst="false" differentOddEven="false">'); |
|
407
|
|
|
$sheet->file_writer->write('<oddHeader>&C&"Times New Roman,Regular"&12&A</oddHeader>'); |
|
408
|
|
|
$sheet->file_writer->write('<oddFooter>&C&"Times New Roman,Regular"&12Page &P</oddFooter>'); |
|
409
|
|
|
$sheet->file_writer->write('</headerFooter>'); |
|
410
|
|
|
$sheet->file_writer->write('</worksheet>'); |
|
411
|
|
|
|
|
412
|
|
|
$max_cell_tag = '<dimension ref="A1:' . $max_cell . '"/>'; |
|
413
|
|
|
$padding_length = $sheet->max_cell_tag_end - $sheet->max_cell_tag_start - strlen($max_cell_tag); |
|
414
|
|
|
$sheet->file_writer->fseek($sheet->max_cell_tag_start); |
|
415
|
|
|
$sheet->file_writer->write($max_cell_tag . str_repeat(" ", $padding_length)); |
|
416
|
|
|
$sheet->file_writer->close(); |
|
417
|
|
|
$sheet->finalized = true; |
|
418
|
|
|
} |
|
419
|
|
|
|
|
420
|
|
|
public function markMergedCell($sheet_name, $start_cell_row, $start_cell_column, $end_cell_row, $end_cell_column) |
|
421
|
|
|
{ |
|
422
|
|
|
if (empty($sheet_name) || $this->sheets[$sheet_name]->finalized) { |
|
423
|
|
|
return; |
|
424
|
|
|
} |
|
425
|
|
|
|
|
426
|
|
|
self::initializeSheet($sheet_name); |
|
427
|
|
|
$sheet = &$this->sheets[$sheet_name]; |
|
428
|
|
|
|
|
429
|
|
|
$startCell = self::xlsCell($start_cell_row, $start_cell_column); |
|
430
|
|
|
$endCell = self::xlsCell($end_cell_row, $end_cell_column); |
|
431
|
|
|
$sheet->merge_cells[] = $startCell . ":" . $endCell; |
|
432
|
|
|
} |
|
433
|
|
|
|
|
434
|
|
|
public function writeSheet(array $data, $sheet_name = '', array $header_types = array()) |
|
435
|
|
|
{ |
|
436
|
|
|
$sheet_name = empty($sheet_name) ? 'Sheet1' : $sheet_name; |
|
437
|
|
|
$data = empty($data) ? array(array('')) : $data; |
|
438
|
|
|
if (!empty($header_types)) { |
|
439
|
|
|
$this->writeSheetHeader($sheet_name, $header_types); |
|
440
|
|
|
} |
|
441
|
|
|
foreach ($data as $i => $row) { |
|
442
|
|
|
$this->writeSheetRow($sheet_name, $row); |
|
443
|
|
|
} |
|
444
|
|
|
$this->finalizeSheet($sheet_name); |
|
445
|
|
|
} |
|
446
|
|
|
|
|
447
|
|
|
protected function writeCell( |
|
448
|
|
|
XLSXWriter_BuffererWriter &$file, |
|
449
|
|
|
$row_number, |
|
450
|
|
|
$column_number, |
|
451
|
|
|
$value, |
|
452
|
|
|
$num_format_type, |
|
453
|
|
|
$cell_style_idx |
|
454
|
|
|
) { |
|
455
|
|
|
$cell_name = self::xlsCell($row_number, $column_number); |
|
456
|
|
|
|
|
457
|
|
|
if (!is_scalar($value) || $value === '') { //objects, array, empty |
|
|
|
|
|
|
458
|
|
|
$file->write('<c r="' . $cell_name . '" s="' . $cell_style_idx . '"/>'); |
|
459
|
|
|
} elseif (is_string($value) && $value{0} == '=') { |
|
460
|
|
|
$file->write('<c r="' . $cell_name . '" s="' . $cell_style_idx . '" t="s"><f>' . self::xmlspecialchars($value) . '</f></c>'); |
|
461
|
|
|
} elseif ($num_format_type == 'n_date') { |
|
462
|
|
|
$file->write('<c r="' . $cell_name . '" s="' . $cell_style_idx . '" t="n"><v>' . intval(self::convert_date_time($value)) . '</v></c>'); |
|
463
|
|
|
} elseif ($num_format_type == 'n_datetime') { |
|
464
|
|
|
$file->write('<c r="' . $cell_name . '" s="' . $cell_style_idx . '" t="n"><v>' . self::convert_date_time($value) . '</v></c>'); |
|
465
|
|
View Code Duplication |
} elseif ($num_format_type == 'n_numeric') { |
|
466
|
|
|
$file->write('<c r="' . $cell_name . '" s="' . $cell_style_idx . '" t="n"><v>' . self::xmlspecialchars($value) . '</v></c>');//int,float,currency |
|
467
|
|
|
} elseif ($num_format_type == 'n_string') { |
|
468
|
|
|
$file->write('<c r="' . $cell_name . '" s="' . $cell_style_idx . '" t="inlineStr"><is><t>' . self::xmlspecialchars($value) . '</t></is></c>'); |
|
469
|
|
|
} elseif ($num_format_type == 'n_auto' || 1) { //auto-detect unknown column types |
|
470
|
|
|
if (!is_string($value) || $value == '0' || ($value[0] != '0' && ctype_digit($value)) || preg_match( |
|
471
|
|
|
"/^\-?(0|[1-9][0-9]*)(\.[0-9]+)?$/", |
|
472
|
|
|
$value |
|
473
|
|
|
) |
|
474
|
|
|
) { |
|
475
|
|
|
$file->write('<c r="' . $cell_name . '" s="' . $cell_style_idx . '" t="n"><v>' . self::xmlspecialchars($value) . '</v></c>');//int,float,currency |
|
476
|
|
View Code Duplication |
} else { //implied: ($cell_format=='string') |
|
|
|
|
|
|
477
|
|
|
$file->write('<c r="' . $cell_name . '" s="' . $cell_style_idx . '" t="inlineStr"><is><t>' . self::xmlspecialchars($value) . '</t></is></c>'); |
|
478
|
|
|
} |
|
479
|
|
|
} |
|
480
|
|
|
} |
|
481
|
|
|
|
|
482
|
|
|
protected function styleFontIndexes() |
|
483
|
|
|
{ |
|
484
|
|
|
static $border_allowed = array('left', 'right', 'top', 'bottom'); |
|
485
|
|
|
static $border_style_allowed = array( |
|
486
|
|
|
'thin', |
|
487
|
|
|
'medium', |
|
488
|
|
|
'thick', |
|
489
|
|
|
'dashDot', |
|
490
|
|
|
'dashDotDot', |
|
491
|
|
|
'dashed', |
|
492
|
|
|
'dotted', |
|
493
|
|
|
'double', |
|
494
|
|
|
'hair', |
|
495
|
|
|
'mediumDashDot', |
|
496
|
|
|
'mediumDashDotDot', |
|
497
|
|
|
'mediumDashed', |
|
498
|
|
|
'slantDashDot' |
|
499
|
|
|
); |
|
500
|
|
|
static $horizontal_allowed = array('general', 'left', 'right', 'justify', 'center'); |
|
501
|
|
|
static $vertical_allowed = array('bottom', 'center', 'distributed', 'top'); |
|
502
|
|
|
$default_font = array('size' => '10', 'name' => 'Arial', 'family' => '2'); |
|
503
|
|
|
$fills = array('', '');//2 placeholders for static xml later |
|
504
|
|
|
$fonts = array('', '', '', '');//4 placeholders for static xml later |
|
505
|
|
|
$borders = array('');//1 placeholder for static xml later |
|
506
|
|
|
$style_indexes = array(); |
|
507
|
|
|
foreach ($this->cell_styles as $i => $cell_style_string) { |
|
508
|
|
|
$semi_colon_pos = strpos($cell_style_string, ";"); |
|
509
|
|
|
$number_format_idx = substr($cell_style_string, 0, $semi_colon_pos); |
|
510
|
|
|
$style_json_string = substr($cell_style_string, $semi_colon_pos + 1); |
|
511
|
|
|
$style = @json_decode($style_json_string, $as_assoc = true); |
|
512
|
|
|
|
|
513
|
|
|
$style_indexes[$i] = array('num_fmt_idx' => $number_format_idx);//initialize entry |
|
514
|
|
|
if (isset($style['border']) && is_string($style['border'])) {//border is a comma delimited str |
|
515
|
|
|
$border_value['side'] = array_intersect(explode(",", $style['border']), $border_allowed); |
|
|
|
|
|
|
516
|
|
|
if (isset($style['border-style']) && in_array($style['border-style'], $border_style_allowed)) { |
|
517
|
|
|
$border_value['style'] = $style['border-style']; |
|
|
|
|
|
|
518
|
|
|
} |
|
519
|
|
View Code Duplication |
if (isset($style['border-color']) && is_string($style['border-color']) && $style['border-color'][0] == '#') { |
|
520
|
|
|
$v = substr($style['border-color'], 1, 6); |
|
521
|
|
|
$v = strlen($v) == 3 ? $v[0] . $v[0] . $v[1] . $v[1] . $v[2] . $v[2] : $v;// expand cf0 => ccff00 |
|
522
|
|
|
$border_value['color'] = "FF" . strtoupper($v); |
|
523
|
|
|
} |
|
524
|
|
|
$style_indexes[$i]['border_idx'] = self::add_to_list_get_index($borders, json_encode($border_value)); |
|
525
|
|
|
} |
|
526
|
|
View Code Duplication |
if (isset($style['fill']) && is_string($style['fill']) && $style['fill'][0] == '#') { |
|
527
|
|
|
$v = substr($style['fill'], 1, 6); |
|
528
|
|
|
$v = strlen($v) == 3 ? $v[0] . $v[0] . $v[1] . $v[1] . $v[2] . $v[2] : $v;// expand cf0 => ccff00 |
|
529
|
|
|
$style_indexes[$i]['fill_idx'] = self::add_to_list_get_index($fills, "FF" . strtoupper($v)); |
|
530
|
|
|
} |
|
531
|
|
View Code Duplication |
if (isset($style['halign']) && in_array($style['halign'], $horizontal_allowed)) { |
|
532
|
|
|
$style_indexes[$i]['alignment'] = true; |
|
533
|
|
|
$style_indexes[$i]['halign'] = $style['halign']; |
|
534
|
|
|
} |
|
535
|
|
View Code Duplication |
if (isset($style['valign']) && in_array($style['valign'], $vertical_allowed)) { |
|
536
|
|
|
$style_indexes[$i]['alignment'] = true; |
|
537
|
|
|
$style_indexes[$i]['valign'] = $style['valign']; |
|
538
|
|
|
} |
|
539
|
|
|
if (isset($style['wrap_text'])) { |
|
540
|
|
|
$style_indexes[$i]['alignment'] = true; |
|
541
|
|
|
$style_indexes[$i]['wrap_text'] = (bool)$style['wrap_text']; |
|
542
|
|
|
} |
|
543
|
|
|
|
|
544
|
|
|
$font = $default_font; |
|
545
|
|
|
if (isset($style['font-size'])) { |
|
546
|
|
|
$font['size'] = floatval($style['font-size']);//floatval to allow "10.5" etc |
|
547
|
|
|
} |
|
548
|
|
|
if (isset($style['font']) && is_string($style['font'])) { |
|
549
|
|
|
if ($style['font'] == 'Comic Sans MS') { |
|
550
|
|
|
$font['family'] = 4; |
|
551
|
|
|
} |
|
552
|
|
|
if ($style['font'] == 'Times New Roman') { |
|
553
|
|
|
$font['family'] = 1; |
|
554
|
|
|
} |
|
555
|
|
|
if ($style['font'] == 'Courier New') { |
|
556
|
|
|
$font['family'] = 3; |
|
557
|
|
|
} |
|
558
|
|
|
$font['name'] = strval($style['font']); |
|
559
|
|
|
} |
|
560
|
|
|
if (isset($style['font-style']) && is_string($style['font-style'])) { |
|
561
|
|
View Code Duplication |
if (strpos($style['font-style'], 'bold') !== false) { |
|
562
|
|
|
$font['bold'] = true; |
|
563
|
|
|
} |
|
564
|
|
View Code Duplication |
if (strpos($style['font-style'], 'italic') !== false) { |
|
565
|
|
|
$font['italic'] = true; |
|
566
|
|
|
} |
|
567
|
|
View Code Duplication |
if (strpos($style['font-style'], 'strike') !== false) { |
|
568
|
|
|
$font['strike'] = true; |
|
569
|
|
|
} |
|
570
|
|
View Code Duplication |
if (strpos($style['font-style'], 'underline') !== false) { |
|
571
|
|
|
$font['underline'] = true; |
|
572
|
|
|
} |
|
573
|
|
|
} |
|
574
|
|
View Code Duplication |
if (isset($style['color']) && is_string($style['color']) && $style['color'][0] == '#') { |
|
575
|
|
|
$v = substr($style['color'], 1, 6); |
|
576
|
|
|
$v = strlen($v) == 3 ? $v[0] . $v[0] . $v[1] . $v[1] . $v[2] . $v[2] : $v;// expand cf0 => ccff00 |
|
577
|
|
|
$font['color'] = "FF" . strtoupper($v); |
|
578
|
|
|
} |
|
579
|
|
|
if ($font != $default_font) { |
|
580
|
|
|
$style_indexes[$i]['font_idx'] = self::add_to_list_get_index($fonts, json_encode($font)); |
|
581
|
|
|
} |
|
582
|
|
|
} |
|
583
|
|
|
return array('fills' => $fills, 'fonts' => $fonts, 'borders' => $borders, 'styles' => $style_indexes); |
|
584
|
|
|
} |
|
585
|
|
|
|
|
586
|
|
|
protected function writeStylesXML() |
|
587
|
|
|
{ |
|
588
|
|
|
$r = self::styleFontIndexes(); |
|
589
|
|
|
$fills = $r['fills']; |
|
590
|
|
|
$fonts = $r['fonts']; |
|
591
|
|
|
$borders = $r['borders']; |
|
592
|
|
|
$style_indexes = $r['styles']; |
|
593
|
|
|
|
|
594
|
|
|
$temporary_filename = $this->tempFilename(); |
|
595
|
|
|
$file = new XLSXWriter_BuffererWriter($temporary_filename); |
|
596
|
|
|
$file->write('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' . "\n"); |
|
597
|
|
|
$file->write('<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">'); |
|
598
|
|
|
$file->write('<numFmts count="' . count($this->number_formats) . '">'); |
|
599
|
|
|
foreach ($this->number_formats as $i => $v) { |
|
600
|
|
|
$file->write('<numFmt numFmtId="' . (164 + $i) . '" formatCode="' . self::xmlspecialchars($v) . '" />'); |
|
601
|
|
|
} |
|
602
|
|
|
//$file->write( '<numFmt formatCode="GENERAL" numFmtId="164"/>'); |
|
|
|
|
|
|
603
|
|
|
//$file->write( '<numFmt formatCode="[$$-1009]#,##0.00;[RED]\-[$$-1009]#,##0.00" numFmtId="165"/>'); |
|
|
|
|
|
|
604
|
|
|
//$file->write( '<numFmt formatCode="YYYY-MM-DD\ HH:MM:SS" numFmtId="166"/>'); |
|
|
|
|
|
|
605
|
|
|
//$file->write( '<numFmt formatCode="YYYY-MM-DD" numFmtId="167"/>'); |
|
|
|
|
|
|
606
|
|
|
$file->write('</numFmts>'); |
|
607
|
|
|
|
|
608
|
|
|
$file->write('<fonts count="' . (count($fonts)) . '">'); |
|
609
|
|
|
$file->write('<font><name val="Arial"/><charset val="1"/><family val="2"/><sz val="10"/></font>'); |
|
610
|
|
|
$file->write('<font><name val="Arial"/><family val="0"/><sz val="10"/></font>'); |
|
611
|
|
|
$file->write('<font><name val="Arial"/><family val="0"/><sz val="10"/></font>'); |
|
612
|
|
|
$file->write('<font><name val="Arial"/><family val="0"/><sz val="10"/></font>'); |
|
613
|
|
|
|
|
614
|
|
|
foreach ($fonts as $font) { |
|
615
|
|
|
if (!empty($font)) { //fonts have 4 empty placeholders in array to offset the 4 static xml entries above |
|
616
|
|
|
$f = json_decode($font, true); |
|
617
|
|
|
$file->write('<font>'); |
|
618
|
|
|
$file->write('<name val="' . htmlspecialchars($f['name']) . '"/><charset val="1"/><family val="' . intval($f['family']) . '"/>'); |
|
619
|
|
|
$file->write('<sz val="' . intval($f['size']) . '"/>'); |
|
620
|
|
|
if (!empty($f['color'])) { |
|
621
|
|
|
$file->write('<color rgb="' . strval($f['color']) . '"/>'); |
|
622
|
|
|
} |
|
623
|
|
|
if (!empty($f['bold'])) { |
|
624
|
|
|
$file->write('<b val="true"/>'); |
|
625
|
|
|
} |
|
626
|
|
|
if (!empty($f['italic'])) { |
|
627
|
|
|
$file->write('<i val="true"/>'); |
|
628
|
|
|
} |
|
629
|
|
|
if (!empty($f['underline'])) { |
|
630
|
|
|
$file->write('<u val="single"/>'); |
|
631
|
|
|
} |
|
632
|
|
|
if (!empty($f['strike'])) { |
|
633
|
|
|
$file->write('<strike val="true"/>'); |
|
634
|
|
|
} |
|
635
|
|
|
$file->write('</font>'); |
|
636
|
|
|
} |
|
637
|
|
|
} |
|
638
|
|
|
$file->write('</fonts>'); |
|
639
|
|
|
|
|
640
|
|
|
$file->write('<fills count="' . (count($fills)) . '">'); |
|
641
|
|
|
$file->write('<fill><patternFill patternType="none"/></fill>'); |
|
642
|
|
|
$file->write('<fill><patternFill patternType="gray125"/></fill>'); |
|
643
|
|
|
foreach ($fills as $fill) { |
|
644
|
|
|
if (!empty($fill)) { //fills have 2 empty placeholders in array to offset the 2 static xml entries above |
|
645
|
|
|
$file->write('<fill><patternFill patternType="solid"><fgColor rgb="' . strval($fill) . '"/><bgColor indexed="64"/></patternFill></fill>'); |
|
646
|
|
|
} |
|
647
|
|
|
} |
|
648
|
|
|
$file->write('</fills>'); |
|
649
|
|
|
|
|
650
|
|
|
$file->write('<borders count="' . (count($borders)) . '">'); |
|
651
|
|
|
$file->write('<border diagonalDown="false" diagonalUp="false"><left/><right/><top/><bottom/><diagonal/></border>'); |
|
652
|
|
|
foreach ($borders as $border) { |
|
653
|
|
|
if (!empty($border)) { //fonts have an empty placeholder in the array to offset the static xml entry above |
|
654
|
|
|
$pieces = json_decode($border, true); |
|
655
|
|
|
$border_style = !empty($pieces['style']) ? $pieces['style'] : 'hair'; |
|
656
|
|
|
$border_color = !empty($pieces['color']) ? '<color rgb="' . strval($pieces['color']) . '"/>' : ''; |
|
657
|
|
|
$file->write('<border diagonalDown="false" diagonalUp="false">'); |
|
658
|
|
|
foreach (array('left', 'right', 'top', 'bottom') as $side) { |
|
659
|
|
|
$show_side = in_array($side, $pieces['side']) ? true : false; |
|
660
|
|
|
$file->write($show_side ? "<$side style=\"$border_style\">$border_color</$side>" : "<$side/>"); |
|
661
|
|
|
} |
|
662
|
|
|
$file->write('<diagonal/>'); |
|
663
|
|
|
$file->write('</border>'); |
|
664
|
|
|
} |
|
665
|
|
|
} |
|
666
|
|
|
$file->write('</borders>'); |
|
667
|
|
|
|
|
668
|
|
|
$file->write('<cellStyleXfs count="20">'); |
|
669
|
|
|
$file->write('<xf applyAlignment="true" applyBorder="true" applyFont="true" applyProtection="true" borderId="0" fillId="0" fontId="0" numFmtId="164">'); |
|
670
|
|
|
$file->write('<alignment horizontal="general" indent="0" shrinkToFit="false" textRotation="0" vertical="bottom" wrapText="false"/>'); |
|
671
|
|
|
$file->write('<protection hidden="false" locked="true"/>'); |
|
672
|
|
|
$file->write('</xf>'); |
|
673
|
|
|
$file->write('<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="1" numFmtId="0"/>'); |
|
674
|
|
|
$file->write('<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="1" numFmtId="0"/>'); |
|
675
|
|
|
$file->write('<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="2" numFmtId="0"/>'); |
|
676
|
|
|
$file->write('<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="2" numFmtId="0"/>'); |
|
677
|
|
|
$file->write('<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"/>'); |
|
678
|
|
|
$file->write('<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"/>'); |
|
679
|
|
|
$file->write('<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"/>'); |
|
680
|
|
|
$file->write('<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"/>'); |
|
681
|
|
|
$file->write('<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"/>'); |
|
682
|
|
|
$file->write('<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"/>'); |
|
683
|
|
|
$file->write('<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"/>'); |
|
684
|
|
|
$file->write('<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"/>'); |
|
685
|
|
|
$file->write('<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"/>'); |
|
686
|
|
|
$file->write('<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"/>'); |
|
687
|
|
|
$file->write('<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="1" numFmtId="43"/>'); |
|
688
|
|
|
$file->write('<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="1" numFmtId="41"/>'); |
|
689
|
|
|
$file->write('<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="1" numFmtId="44"/>'); |
|
690
|
|
|
$file->write('<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="1" numFmtId="42"/>'); |
|
691
|
|
|
$file->write('<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="1" numFmtId="9"/>'); |
|
692
|
|
|
$file->write('</cellStyleXfs>'); |
|
693
|
|
|
|
|
694
|
|
|
$file->write('<cellXfs count="' . (count($style_indexes)) . '">'); |
|
695
|
|
|
//$file->write( '<xf applyAlignment="false" applyBorder="false" applyFont="false" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="164" xfId="0"/>'); |
|
|
|
|
|
|
696
|
|
|
//$file->write( '<xf applyAlignment="false" applyBorder="false" applyFont="false" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="165" xfId="0"/>'); |
|
|
|
|
|
|
697
|
|
|
//$file->write( '<xf applyAlignment="false" applyBorder="false" applyFont="false" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="166" xfId="0"/>'); |
|
|
|
|
|
|
698
|
|
|
//$file->write( '<xf applyAlignment="false" applyBorder="false" applyFont="false" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="167" xfId="0"/>'); |
|
|
|
|
|
|
699
|
|
|
foreach ($style_indexes as $v) { |
|
700
|
|
|
$applyAlignment = isset($v['alignment']) ? 'true' : 'false'; |
|
701
|
|
|
$wrapText = !empty($v['wrap_text']) ? 'true' : 'false'; |
|
702
|
|
|
$horizAlignment = isset($v['halign']) ? $v['halign'] : 'general'; |
|
703
|
|
|
$vertAlignment = isset($v['valign']) ? $v['valign'] : 'bottom'; |
|
704
|
|
|
$applyBorder = isset($v['border_idx']) ? 'true' : 'false'; |
|
705
|
|
|
$applyFont = 'true'; |
|
706
|
|
|
$borderIdx = isset($v['border_idx']) ? intval($v['border_idx']) : 0; |
|
707
|
|
|
$fillIdx = isset($v['fill_idx']) ? intval($v['fill_idx']) : 0; |
|
708
|
|
|
$fontIdx = isset($v['font_idx']) ? intval($v['font_idx']) : 0; |
|
709
|
|
|
//$file->write('<xf applyAlignment="'.$applyAlignment.'" applyBorder="'.$applyBorder.'" applyFont="'.$applyFont.'" applyProtection="false" borderId="'.($borderIdx).'" fillId="'.($fillIdx).'" fontId="'.($fontIdx).'" numFmtId="'.(164+$v['num_fmt_idx']).'" xfId="0"/>'); |
|
|
|
|
|
|
710
|
|
|
$file->write('<xf applyAlignment="' . $applyAlignment . '" applyBorder="' . $applyBorder . '" applyFont="' . $applyFont . '" applyProtection="false" borderId="' . ($borderIdx) . '" fillId="' . ($fillIdx) . '" fontId="' . ($fontIdx) . '" numFmtId="' . (164 + $v['num_fmt_idx']) . '" xfId="0">'); |
|
711
|
|
|
$file->write(' <alignment horizontal="' . $horizAlignment . '" vertical="' . $vertAlignment . '" textRotation="0" wrapText="' . $wrapText . '" indent="0" shrinkToFit="false"/>'); |
|
712
|
|
|
$file->write(' <protection locked="true" hidden="false"/>'); |
|
713
|
|
|
$file->write('</xf>'); |
|
714
|
|
|
} |
|
715
|
|
|
$file->write('</cellXfs>'); |
|
716
|
|
|
$file->write('<cellStyles count="6">'); |
|
717
|
|
|
$file->write('<cellStyle builtinId="0" customBuiltin="false" name="Normal" xfId="0"/>'); |
|
718
|
|
|
$file->write('<cellStyle builtinId="3" customBuiltin="false" name="Comma" xfId="15"/>'); |
|
719
|
|
|
$file->write('<cellStyle builtinId="6" customBuiltin="false" name="Comma [0]" xfId="16"/>'); |
|
720
|
|
|
$file->write('<cellStyle builtinId="4" customBuiltin="false" name="Currency" xfId="17"/>'); |
|
721
|
|
|
$file->write('<cellStyle builtinId="7" customBuiltin="false" name="Currency [0]" xfId="18"/>'); |
|
722
|
|
|
$file->write('<cellStyle builtinId="5" customBuiltin="false" name="Percent" xfId="19"/>'); |
|
723
|
|
|
$file->write('</cellStyles>'); |
|
724
|
|
|
$file->write('</styleSheet>'); |
|
725
|
|
|
$file->close(); |
|
726
|
|
|
return $temporary_filename; |
|
727
|
|
|
} |
|
728
|
|
|
|
|
729
|
|
|
protected function buildAppXML() |
|
730
|
|
|
{ |
|
731
|
|
|
$app_xml = ""; |
|
732
|
|
|
$app_xml .= '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' . "\n"; |
|
733
|
|
|
$app_xml .= '<Properties xmlns="http://schemas.openxmlformats.org/officeDocument/2006/extended-properties" xmlns:vt="http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes">'; |
|
734
|
|
|
$app_xml .= '<TotalTime>0</TotalTime>'; |
|
735
|
|
|
$app_xml .= '<Company>' . self::xmlspecialchars($this->company) . '</Company>'; |
|
736
|
|
|
$app_xml .= '</Properties>'; |
|
737
|
|
|
return $app_xml; |
|
738
|
|
|
} |
|
739
|
|
|
|
|
740
|
|
|
protected function buildCoreXML() |
|
741
|
|
|
{ |
|
742
|
|
|
$core_xml = ""; |
|
743
|
|
|
$core_xml .= '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' . "\n"; |
|
744
|
|
|
$core_xml .= '<cp:coreProperties xmlns:cp="http://schemas.openxmlformats.org/package/2006/metadata/core-properties" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:dcmitype="http://purl.org/dc/dcmitype/" xmlns:dcterms="http://purl.org/dc/terms/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">'; |
|
745
|
|
|
$core_xml .= '<dcterms:created xsi:type="dcterms:W3CDTF">' . date("Y-m-d\TH:i:s.00\Z") . '</dcterms:created>';//$date_time = '2014-10-25T15:54:37.00Z'; |
|
|
|
|
|
|
746
|
|
|
$core_xml .= '<dc:title>' . self::xmlspecialchars($this->title) . '</dc:title>'; |
|
747
|
|
|
$core_xml .= '<dc:subject>' . self::xmlspecialchars($this->subject) . '</dc:subject>'; |
|
748
|
|
|
$core_xml .= '<dc:creator>' . self::xmlspecialchars($this->author) . '</dc:creator>'; |
|
749
|
|
|
if (!empty($this->keywords)) { |
|
750
|
|
|
$core_xml .= '<cp:keywords>' . self::xmlspecialchars(implode( |
|
751
|
|
|
", ", |
|
752
|
|
|
(array)$this->keywords |
|
753
|
|
|
)) . '</cp:keywords>'; |
|
754
|
|
|
} |
|
755
|
|
|
$core_xml .= '<dc:description>' . self::xmlspecialchars($this->description) . '</dc:description>'; |
|
756
|
|
|
$core_xml .= '<cp:revision>0</cp:revision>'; |
|
757
|
|
|
$core_xml .= '</cp:coreProperties>'; |
|
758
|
|
|
return $core_xml; |
|
759
|
|
|
} |
|
760
|
|
|
|
|
761
|
|
|
protected function buildRelationshipsXML() |
|
762
|
|
|
{ |
|
763
|
|
|
$rels_xml = ""; |
|
764
|
|
|
$rels_xml .= '<?xml version="1.0" encoding="UTF-8"?>' . "\n"; |
|
765
|
|
|
$rels_xml .= '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">'; |
|
766
|
|
|
$rels_xml .= '<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>'; |
|
767
|
|
|
$rels_xml .= '<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="docProps/core.xml"/>'; |
|
768
|
|
|
$rels_xml .= '<Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties" Target="docProps/app.xml"/>'; |
|
769
|
|
|
$rels_xml .= "\n"; |
|
770
|
|
|
$rels_xml .= '</Relationships>'; |
|
771
|
|
|
return $rels_xml; |
|
772
|
|
|
} |
|
773
|
|
|
|
|
774
|
|
|
protected function buildWorkbookXML() |
|
775
|
|
|
{ |
|
776
|
|
|
$i = 0; |
|
777
|
|
|
$workbook_xml = ""; |
|
778
|
|
|
$workbook_xml .= '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' . "\n"; |
|
779
|
|
|
$workbook_xml .= '<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">'; |
|
780
|
|
|
$workbook_xml .= '<fileVersion appName="Calc"/><workbookPr backupFile="false" showObjects="all" date1904="false"/><workbookProtection/>'; |
|
781
|
|
|
$workbook_xml .= '<bookViews><workbookView activeTab="0" firstSheet="0" showHorizontalScroll="true" showSheetTabs="true" showVerticalScroll="true" tabRatio="212" windowHeight="8192" windowWidth="16384" xWindow="0" yWindow="0"/></bookViews>'; |
|
782
|
|
|
$workbook_xml .= '<sheets>'; |
|
783
|
|
|
foreach ($this->sheets as $sheet_name => $sheet) { |
|
784
|
|
|
$sheetname = self::sanitize_sheetname($sheet->sheetname); |
|
785
|
|
|
$workbook_xml .= '<sheet name="' . self::xmlspecialchars($sheetname) . '" sheetId="' . ($i + 1) . '" state="visible" r:id="rId' . ($i + 2) . '"/>'; |
|
786
|
|
|
$i++; |
|
787
|
|
|
} |
|
788
|
|
|
$workbook_xml .= '</sheets>'; |
|
789
|
|
|
$workbook_xml .= '<definedNames>'; |
|
790
|
|
|
foreach ($this->sheets as $sheet_name => $sheet) { |
|
791
|
|
|
if ($sheet->auto_filter) { |
|
792
|
|
|
$sheetname = self::sanitize_sheetname($sheet->sheetname); |
|
793
|
|
|
$workbook_xml .= '<definedName name="_xlnm._FilterDatabase" localSheetId="0" hidden="1">\'' . self::xmlspecialchars($sheetname) . '\'!$A$1:' . self::xlsCell( |
|
794
|
|
|
$sheet->row_count - 1, |
|
795
|
|
|
count($sheet->columns) - 1, |
|
796
|
|
|
true |
|
797
|
|
|
) . '</definedName>'; |
|
798
|
|
|
$i++; |
|
799
|
|
|
} |
|
800
|
|
|
} |
|
801
|
|
|
$workbook_xml .= '</definedNames>'; |
|
802
|
|
|
$workbook_xml .= '<calcPr iterateCount="100" refMode="A1" iterate="false" iterateDelta="0.001"/></workbook>'; |
|
803
|
|
|
return $workbook_xml; |
|
804
|
|
|
} |
|
805
|
|
|
|
|
806
|
|
|
protected function buildWorkbookRelsXML() |
|
807
|
|
|
{ |
|
808
|
|
|
$i = 0; |
|
809
|
|
|
$wkbkrels_xml = ""; |
|
810
|
|
|
$wkbkrels_xml .= '<?xml version="1.0" encoding="UTF-8"?>' . "\n"; |
|
811
|
|
|
$wkbkrels_xml .= '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">'; |
|
812
|
|
|
$wkbkrels_xml .= '<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/>'; |
|
813
|
|
|
foreach ($this->sheets as $sheet_name => $sheet) { |
|
814
|
|
|
$wkbkrels_xml .= '<Relationship Id="rId' . ($i + 2) . '" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/' . ($sheet->xmlname) . '"/>'; |
|
815
|
|
|
$i++; |
|
816
|
|
|
} |
|
817
|
|
|
$wkbkrels_xml .= "\n"; |
|
818
|
|
|
$wkbkrels_xml .= '</Relationships>'; |
|
819
|
|
|
return $wkbkrels_xml; |
|
820
|
|
|
} |
|
821
|
|
|
|
|
822
|
|
|
protected function buildContentTypesXML() |
|
823
|
|
|
{ |
|
824
|
|
|
$content_types_xml = ""; |
|
825
|
|
|
$content_types_xml .= '<?xml version="1.0" encoding="UTF-8"?>' . "\n"; |
|
826
|
|
|
$content_types_xml .= '<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">'; |
|
827
|
|
|
$content_types_xml .= '<Override PartName="/_rels/.rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>'; |
|
828
|
|
|
$content_types_xml .= '<Override PartName="/xl/_rels/workbook.xml.rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>'; |
|
829
|
|
|
foreach ($this->sheets as $sheet_name => $sheet) { |
|
830
|
|
|
$content_types_xml .= '<Override PartName="/xl/worksheets/' . ($sheet->xmlname) . '" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>'; |
|
831
|
|
|
} |
|
832
|
|
|
$content_types_xml .= '<Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>'; |
|
833
|
|
|
$content_types_xml .= '<Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>'; |
|
834
|
|
|
$content_types_xml .= '<Override PartName="/docProps/app.xml" ContentType="application/vnd.openxmlformats-officedocument.extended-properties+xml"/>'; |
|
835
|
|
|
$content_types_xml .= '<Override PartName="/docProps/core.xml" ContentType="application/vnd.openxmlformats-package.core-properties+xml"/>'; |
|
836
|
|
|
$content_types_xml .= "\n"; |
|
837
|
|
|
$content_types_xml .= '</Types>'; |
|
838
|
|
|
return $content_types_xml; |
|
839
|
|
|
} |
|
840
|
|
|
|
|
841
|
|
|
//------------------------------------------------------------------ |
|
842
|
|
|
/* |
|
843
|
|
|
* @param $row_number int, zero based |
|
844
|
|
|
* @param $column_number int, zero based |
|
845
|
|
|
* @param $absolute bool |
|
846
|
|
|
* @return Cell label/coordinates, ex: A1, C3, AA42 (or if $absolute==true: $A$1, $C$3, $AA$42) |
|
847
|
|
|
* */ |
|
848
|
|
|
public static function xlsCell($row_number, $column_number, $absolute = false) |
|
849
|
|
|
{ |
|
850
|
|
|
$n = $column_number; |
|
851
|
|
|
for ($r = ""; $n >= 0; $n = intval($n / 26) - 1) { |
|
852
|
|
|
$r = chr($n % 26 + 0x41) . $r; |
|
853
|
|
|
} |
|
854
|
|
|
if ($absolute) { |
|
855
|
|
|
return '$' . $r . '$' . ($row_number + 1); |
|
856
|
|
|
} |
|
857
|
|
|
return $r . ($row_number + 1); |
|
858
|
|
|
} |
|
859
|
|
|
|
|
860
|
|
|
//------------------------------------------------------------------ |
|
861
|
|
|
public static function log($string) |
|
862
|
|
|
{ |
|
863
|
|
|
file_put_contents( |
|
864
|
|
|
"php://stderr", |
|
865
|
|
|
date("Y-m-d H:i:s:") . rtrim(is_array($string) ? json_encode($string) : $string) . "\n" |
|
866
|
|
|
); |
|
867
|
|
|
} |
|
868
|
|
|
|
|
869
|
|
|
//------------------------------------------------------------------ |
|
870
|
|
|
public static function sanitize_filename( |
|
871
|
|
|
$filename |
|
872
|
|
|
) //http://msdn.microsoft.com/en-us/library/aa365247%28VS.85%29.aspx |
|
873
|
|
|
{ |
|
874
|
|
|
$nonprinting = array_map('chr', range(0, 31)); |
|
875
|
|
|
$invalid_chars = array('<', '>', '?', '"', ':', '|', '\\', '/', '*', '&'); |
|
876
|
|
|
$all_invalids = array_merge($nonprinting, $invalid_chars); |
|
877
|
|
|
return str_replace($all_invalids, "", $filename); |
|
878
|
|
|
} |
|
879
|
|
|
|
|
880
|
|
|
//------------------------------------------------------------------ |
|
881
|
|
|
public static function sanitize_sheetname($sheetname) |
|
882
|
|
|
{ |
|
883
|
|
|
static $badchars = '\\/?*:[]'; |
|
884
|
|
|
static $goodchars = ' '; |
|
885
|
|
|
$sheetname = strtr($sheetname, $badchars, $goodchars); |
|
886
|
|
|
$sheetname = substr($sheetname, 0, 31); |
|
887
|
|
|
$sheetname = trim(trim(trim($sheetname), "'"));//trim before and after trimming single quotes |
|
888
|
|
|
return !empty($sheetname) ? $sheetname : 'Sheet' . ((rand() % 900) + 100); |
|
889
|
|
|
} |
|
890
|
|
|
|
|
891
|
|
|
//------------------------------------------------------------------ |
|
892
|
|
|
public static function xmlspecialchars($val) |
|
893
|
|
|
{ |
|
894
|
|
|
//note, badchars does not include \t\n\r (\x09\x0a\x0d) |
|
895
|
|
|
static $badchars = "\x00\x01\x02\x03\x04\x05\x06\x07\x08\x0b\x0c\x0e\x0f\x10\x11\x12\x13\x14\x15\x16\x17\x18\x19\x1a\x1b\x1c\x1d\x1e\x1f\x7f"; |
|
896
|
|
|
static $goodchars = " "; |
|
897
|
|
|
return strtr( |
|
898
|
|
|
htmlspecialchars($val, ENT_QUOTES | ENT_XML1), |
|
899
|
|
|
$badchars, |
|
900
|
|
|
$goodchars |
|
901
|
|
|
);//strtr appears to be faster than str_replace |
|
902
|
|
|
} |
|
903
|
|
|
|
|
904
|
|
|
//------------------------------------------------------------------ |
|
905
|
|
|
public static function array_first_key(array $arr) |
|
906
|
|
|
{ |
|
907
|
|
|
reset($arr); |
|
908
|
|
|
$first_key = key($arr); |
|
909
|
|
|
return $first_key; |
|
910
|
|
|
} |
|
911
|
|
|
|
|
912
|
|
|
//------------------------------------------------------------------ |
|
913
|
|
|
private static function determineNumberFormatType($num_format) |
|
914
|
|
|
{ |
|
915
|
|
|
$num_format = preg_replace("/\[(Black|Blue|Cyan|Green|Magenta|Red|White|Yellow)\]/i", "", $num_format); |
|
916
|
|
|
if ($num_format == 'GENERAL') { |
|
917
|
|
|
return 'n_auto'; |
|
918
|
|
|
} |
|
919
|
|
|
if ($num_format == '@') { |
|
920
|
|
|
return 'n_string'; |
|
921
|
|
|
} |
|
922
|
|
|
if ($num_format == '0') { |
|
923
|
|
|
return 'n_numeric'; |
|
924
|
|
|
} |
|
925
|
|
|
if (preg_match("/[H]{1,2}:[M]{1,2}/i", $num_format)) { |
|
926
|
|
|
return 'n_datetime'; |
|
927
|
|
|
} |
|
928
|
|
|
if (preg_match("/[M]{1,2}:[S]{1,2}/i", $num_format)) { |
|
929
|
|
|
return 'n_datetime'; |
|
930
|
|
|
} |
|
931
|
|
|
if (preg_match("/[Y]{2,4}/i", $num_format)) { |
|
932
|
|
|
return 'n_date'; |
|
933
|
|
|
} |
|
934
|
|
|
if (preg_match("/[D]{1,2}/i", $num_format)) { |
|
935
|
|
|
return 'n_date'; |
|
936
|
|
|
} |
|
937
|
|
|
if (preg_match("/[M]{1,2}/i", $num_format)) { |
|
938
|
|
|
return 'n_date'; |
|
939
|
|
|
} |
|
940
|
|
|
if (preg_match("/$/", $num_format)) { |
|
941
|
|
|
return 'n_numeric'; |
|
942
|
|
|
} |
|
943
|
|
|
if (preg_match("/%/", $num_format)) { |
|
944
|
|
|
return 'n_numeric'; |
|
945
|
|
|
} |
|
946
|
|
|
if (preg_match("/0/", $num_format)) { |
|
947
|
|
|
return 'n_numeric'; |
|
948
|
|
|
} |
|
949
|
|
|
return 'n_auto'; |
|
950
|
|
|
} |
|
951
|
|
|
|
|
952
|
|
|
//------------------------------------------------------------------ |
|
953
|
|
|
private static function numberFormatStandardized($num_format) |
|
954
|
|
|
{ |
|
955
|
|
|
if ($num_format == 'money') { |
|
956
|
|
|
$num_format = 'dollar'; |
|
957
|
|
|
} |
|
958
|
|
|
if ($num_format == 'number') { |
|
959
|
|
|
$num_format = 'integer'; |
|
960
|
|
|
} |
|
961
|
|
|
|
|
962
|
|
|
if ($num_format == 'string') { |
|
963
|
|
|
$num_format = '@'; |
|
964
|
|
|
} else { |
|
965
|
|
|
if ($num_format == 'integer') { |
|
966
|
|
|
$num_format = '0'; |
|
967
|
|
|
} else { |
|
968
|
|
|
if ($num_format == 'date') { |
|
969
|
|
|
$num_format = 'YYYY-MM-DD'; |
|
970
|
|
|
} else { |
|
971
|
|
|
if ($num_format == 'datetime') { |
|
972
|
|
|
$num_format = 'YYYY-MM-DD HH:MM:SS'; |
|
973
|
|
|
} else { |
|
974
|
|
|
if ($num_format == 'price') { |
|
975
|
|
|
$num_format = '#,##0.00'; |
|
976
|
|
|
} else { |
|
977
|
|
|
if ($num_format == 'dollar') { |
|
978
|
|
|
$num_format = '[$$-1009]#,##0.00;[RED]-[$$-1009]#,##0.00'; |
|
979
|
|
|
} else { |
|
980
|
|
|
if ($num_format == 'euro') { |
|
981
|
|
|
$num_format = '#,##0.00 [$€-407];[RED]-#,##0.00 [$€-407]'; |
|
982
|
|
|
} |
|
983
|
|
|
} |
|
984
|
|
|
} |
|
985
|
|
|
} |
|
986
|
|
|
} |
|
987
|
|
|
} |
|
988
|
|
|
} |
|
989
|
|
|
$ignore_until = ''; |
|
990
|
|
|
$escaped = ''; |
|
991
|
|
|
for ($i = 0, $ix = strlen($num_format); $i < $ix; $i++) { |
|
992
|
|
|
$c = $num_format[$i]; |
|
993
|
|
|
if ($ignore_until == '' && $c == '[') { |
|
994
|
|
|
$ignore_until = ']'; |
|
995
|
|
|
} else { |
|
996
|
|
|
if ($ignore_until == '' && $c == '"') { |
|
997
|
|
|
$ignore_until = '"'; |
|
998
|
|
|
} else { |
|
999
|
|
|
if ($ignore_until == $c) { |
|
1000
|
|
|
$ignore_until = ''; |
|
1001
|
|
|
} |
|
1002
|
|
|
} |
|
1003
|
|
|
} |
|
1004
|
|
|
if ($ignore_until == '' && ($c == ' ' || $c == '-' || $c == '(' || $c == ')') && ($i == 0 || $num_format[$i - 1] != '_')) { |
|
1005
|
|
|
$escaped .= "\\" . $c; |
|
1006
|
|
|
} else { |
|
1007
|
|
|
$escaped .= $c; |
|
1008
|
|
|
} |
|
1009
|
|
|
} |
|
1010
|
|
|
return $escaped; |
|
1011
|
|
|
} |
|
1012
|
|
|
|
|
1013
|
|
|
//------------------------------------------------------------------ |
|
1014
|
|
|
public static function add_to_list_get_index(&$haystack, $needle) |
|
1015
|
|
|
{ |
|
1016
|
|
|
$existing_idx = array_search($needle, $haystack, $strict = true); |
|
1017
|
|
|
if ($existing_idx === false) { |
|
1018
|
|
|
$existing_idx = count($haystack); |
|
1019
|
|
|
$haystack[] = $needle; |
|
1020
|
|
|
} |
|
1021
|
|
|
return $existing_idx; |
|
1022
|
|
|
} |
|
1023
|
|
|
|
|
1024
|
|
|
//------------------------------------------------------------------ |
|
1025
|
|
|
public static function convert_date_time($date_input) //thanks to Excel::Writer::XLSX::Worksheet.pm (perl) |
|
1026
|
|
|
{ |
|
1027
|
|
|
$days = 0; # Number of days since epoch |
|
|
|
|
|
|
1028
|
|
|
$seconds = 0; # Time expressed as fraction of 24h hours in seconds |
|
1029
|
|
|
$year = $month = $day = 0; |
|
1030
|
|
|
$hour = $min = $sec = 0; |
|
1031
|
|
|
|
|
1032
|
|
|
$date_time = $date_input; |
|
1033
|
|
|
if (preg_match("/(\d{4})\-(\d{2})\-(\d{2})/", $date_time, $matches)) { |
|
1034
|
|
|
list($junk, $year, $month, $day) = $matches; |
|
|
|
|
|
|
1035
|
|
|
} |
|
1036
|
|
|
if (preg_match("/(\d+):(\d{2}):(\d{2})/", $date_time, $matches)) { |
|
1037
|
|
|
list($junk, $hour, $min, $sec) = $matches; |
|
|
|
|
|
|
1038
|
|
|
$seconds = ($hour * 60 * 60 + $min * 60 + $sec) / (24 * 60 * 60); |
|
1039
|
|
|
} |
|
1040
|
|
|
|
|
1041
|
|
|
//using 1900 as epoch, not 1904, ignoring 1904 special case |
|
1042
|
|
|
|
|
1043
|
|
|
# Special cases for Excel. |
|
1044
|
|
|
if ("$year-$month-$day" == '1899-12-31') { |
|
1045
|
|
|
return $seconds; |
|
1046
|
|
|
} # Excel 1900 epoch |
|
1047
|
|
|
if ("$year-$month-$day" == '1900-01-00') { |
|
1048
|
|
|
return $seconds; |
|
1049
|
|
|
} # Excel 1900 epoch |
|
1050
|
|
|
if ("$year-$month-$day" == '1900-02-29') { |
|
1051
|
|
|
return 60 + $seconds; |
|
1052
|
|
|
} # Excel false leapday |
|
1053
|
|
|
|
|
1054
|
|
|
# We calculate the date by calculating the number of days since the epoch |
|
1055
|
|
|
# and adjust for the number of leap days. We calculate the number of leap |
|
1056
|
|
|
# days by normalising the year in relation to the epoch. Thus the year 2000 |
|
1057
|
|
|
# becomes 100 for 4 and 100 year leapdays and 400 for 400 year leapdays. |
|
1058
|
|
|
$epoch = 1900; |
|
1059
|
|
|
$offset = 0; |
|
1060
|
|
|
$norm = 300; |
|
1061
|
|
|
$range = $year - $epoch; |
|
1062
|
|
|
|
|
1063
|
|
|
# Set month days and check for leap year. |
|
1064
|
|
|
$leap = (($year % 400 == 0) || (($year % 4 == 0) && ($year % 100))) ? 1 : 0; |
|
1065
|
|
|
$mdays = array(31, ($leap ? 29 : 28), 31, 30, 31, 30, 31, 31, 30, 31, 30, 31); |
|
1066
|
|
|
|
|
1067
|
|
|
# Some boundary checks |
|
1068
|
|
|
if ($year < $epoch || $year > 9999) { |
|
1069
|
|
|
return 0; |
|
1070
|
|
|
} |
|
1071
|
|
|
if ($month < 1 || $month > 12) { |
|
1072
|
|
|
return 0; |
|
1073
|
|
|
} |
|
1074
|
|
|
if ($day < 1 || $day > $mdays[$month - 1]) { |
|
1075
|
|
|
return 0; |
|
1076
|
|
|
} |
|
1077
|
|
|
|
|
1078
|
|
|
# Accumulate the number of days since the epoch. |
|
1079
|
|
|
$days = $day; # Add days for current month |
|
1080
|
|
|
$days += array_sum(array_slice($mdays, 0, $month - 1)); # Add days for past months |
|
1081
|
|
|
$days += $range * 365; # Add days for past years |
|
1082
|
|
|
$days += intval(($range) / 4); # Add leapdays |
|
1083
|
|
|
$days -= intval(($range + $offset) / 100); # Subtract 100 year leapdays |
|
1084
|
|
|
$days += intval(($range + $offset + $norm) / 400); # Add 400 year leapdays |
|
1085
|
|
|
$days -= $leap; # Already counted above |
|
1086
|
|
|
|
|
1087
|
|
|
# Adjust for Excel erroneously treating 1900 as a leap year. |
|
1088
|
|
|
if ($days > 59) { |
|
1089
|
|
|
$days++; |
|
1090
|
|
|
} |
|
1091
|
|
|
|
|
1092
|
|
|
return $days + $seconds; |
|
1093
|
|
|
} |
|
1094
|
|
|
//------------------------------------------------------------------ |
|
1095
|
|
|
} |
|
1096
|
|
|
|
|
1097
|
|
|
class XLSXWriter_BuffererWriter |
|
|
|
|
|
|
1098
|
|
|
{ |
|
1099
|
|
|
protected $fd = null; |
|
1100
|
|
|
protected $buffer = ''; |
|
1101
|
|
|
protected $check_utf8 = false; |
|
1102
|
|
|
|
|
1103
|
|
|
public function __construct($filename, $fd_fopen_flags = 'w', $check_utf8 = false) |
|
1104
|
|
|
{ |
|
1105
|
|
|
$this->check_utf8 = $check_utf8; |
|
1106
|
|
|
$this->fd = fopen($filename, $fd_fopen_flags); |
|
1107
|
|
|
if ($this->fd === false) { |
|
1108
|
|
|
XLSXWriter::log("Unable to open $filename for writing."); |
|
1109
|
|
|
} |
|
1110
|
|
|
} |
|
1111
|
|
|
|
|
1112
|
|
|
public function write($string) |
|
1113
|
|
|
{ |
|
1114
|
|
|
$this->buffer .= $string; |
|
1115
|
|
|
if (isset($this->buffer[8191])) { |
|
1116
|
|
|
$this->purge(); |
|
1117
|
|
|
} |
|
1118
|
|
|
} |
|
1119
|
|
|
|
|
1120
|
|
|
protected function purge() |
|
1121
|
|
|
{ |
|
1122
|
|
|
if ($this->fd) { |
|
1123
|
|
|
if ($this->check_utf8 && !self::isValidUTF8($this->buffer)) { |
|
1124
|
|
|
XLSXWriter::log("Error, invalid UTF8 encoding detected."); |
|
1125
|
|
|
$this->check_utf8 = false; |
|
1126
|
|
|
} |
|
1127
|
|
|
fwrite($this->fd, $this->buffer); |
|
1128
|
|
|
$this->buffer = ''; |
|
1129
|
|
|
} |
|
1130
|
|
|
} |
|
1131
|
|
|
|
|
1132
|
|
|
public function close() |
|
1133
|
|
|
{ |
|
1134
|
|
|
$this->purge(); |
|
1135
|
|
|
if ($this->fd) { |
|
1136
|
|
|
fclose($this->fd); |
|
1137
|
|
|
$this->fd = null; |
|
1138
|
|
|
} |
|
1139
|
|
|
} |
|
1140
|
|
|
|
|
1141
|
|
|
public function __destruct() |
|
1142
|
|
|
{ |
|
1143
|
|
|
$this->close(); |
|
1144
|
|
|
} |
|
1145
|
|
|
|
|
1146
|
|
|
public function ftell() |
|
1147
|
|
|
{ |
|
1148
|
|
|
if ($this->fd) { |
|
1149
|
|
|
$this->purge(); |
|
1150
|
|
|
return ftell($this->fd); |
|
1151
|
|
|
} |
|
1152
|
|
|
return -1; |
|
1153
|
|
|
} |
|
1154
|
|
|
|
|
1155
|
|
|
public function fseek($pos) |
|
1156
|
|
|
{ |
|
1157
|
|
|
if ($this->fd) { |
|
1158
|
|
|
$this->purge(); |
|
1159
|
|
|
return fseek($this->fd, $pos); |
|
1160
|
|
|
} |
|
1161
|
|
|
return -1; |
|
1162
|
|
|
} |
|
1163
|
|
|
|
|
1164
|
|
|
protected static function isValidUTF8($string) |
|
1165
|
|
|
{ |
|
1166
|
|
|
if (function_exists('mb_check_encoding')) { |
|
1167
|
|
|
return mb_check_encoding($string, 'UTF-8') ? true : false; |
|
1168
|
|
|
} |
|
1169
|
|
|
return preg_match("//u", $string) ? true : false; |
|
1170
|
|
|
} |
|
1171
|
|
|
} |
|
1172
|
|
|
|
luyadev /
luya
Loading history...
Our type inference engine has found an assignment to a property that is incompatible with the declared type of that property.
Either this assignment is in error or the assigned type should be added to the documentation/type hint for that property..