| 2388 |
jpm |
1 |
<?php
|
|
|
2 |
/**
|
|
|
3 |
* PHPExcel
|
|
|
4 |
*
|
|
|
5 |
* Copyright (c) 2006 - 2013 PHPExcel
|
|
|
6 |
*
|
|
|
7 |
* This library is free software; you can redistribute it and/or
|
|
|
8 |
* modify it under the terms of the GNU Lesser General Public
|
|
|
9 |
* License as published by the Free Software Foundation; either
|
|
|
10 |
* version 2.1 of the License, or (at your option) any later version.
|
|
|
11 |
*
|
|
|
12 |
* This library is distributed in the hope that it will be useful,
|
|
|
13 |
* but WITHOUT ANY WARRANTY; without even the implied warranty of
|
|
|
14 |
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
|
|
|
15 |
* Lesser General Public License for more details.
|
|
|
16 |
*
|
|
|
17 |
* You should have received a copy of the GNU Lesser General Public
|
|
|
18 |
* License along with this library; if not, write to the Free Software
|
|
|
19 |
* Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
|
|
|
20 |
*
|
|
|
21 |
* @category PHPExcel
|
|
|
22 |
* @package PHPExcel_Writer_Excel5
|
|
|
23 |
* @copyright Copyright (c) 2006 - 2013 PHPExcel (http://www.codeplex.com/PHPExcel)
|
|
|
24 |
* @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
|
|
|
25 |
* @version ##VERSION##, ##DATE##
|
|
|
26 |
*/
|
|
|
27 |
|
|
|
28 |
// Original file header of PEAR::Spreadsheet_Excel_Writer_Workbook (used as the base for this class):
|
|
|
29 |
// -----------------------------------------------------------------------------------------
|
|
|
30 |
// /*
|
|
|
31 |
// * Module written/ported by Xavier Noguer <xnoguer@rezebra.com>
|
|
|
32 |
// *
|
|
|
33 |
// * The majority of this is _NOT_ my code. I simply ported it from the
|
|
|
34 |
// * PERL Spreadsheet::WriteExcel module.
|
|
|
35 |
// *
|
|
|
36 |
// * The author of the Spreadsheet::WriteExcel module is John McNamara
|
|
|
37 |
// * <jmcnamara@cpan.org>
|
|
|
38 |
// *
|
|
|
39 |
// * I _DO_ maintain this code, and John McNamara has nothing to do with the
|
|
|
40 |
// * porting of this code to PHP. Any questions directly related to this
|
|
|
41 |
// * class library should be directed to me.
|
|
|
42 |
// *
|
|
|
43 |
// * License Information:
|
|
|
44 |
// *
|
|
|
45 |
// * Spreadsheet_Excel_Writer: A library for generating Excel Spreadsheets
|
|
|
46 |
// * Copyright (c) 2002-2003 Xavier Noguer xnoguer@rezebra.com
|
|
|
47 |
// *
|
|
|
48 |
// * This library is free software; you can redistribute it and/or
|
|
|
49 |
// * modify it under the terms of the GNU Lesser General Public
|
|
|
50 |
// * License as published by the Free Software Foundation; either
|
|
|
51 |
// * version 2.1 of the License, or (at your option) any later version.
|
|
|
52 |
// *
|
|
|
53 |
// * This library is distributed in the hope that it will be useful,
|
|
|
54 |
// * but WITHOUT ANY WARRANTY; without even the implied warranty of
|
|
|
55 |
// * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
|
|
|
56 |
// * Lesser General Public License for more details.
|
|
|
57 |
// *
|
|
|
58 |
// * You should have received a copy of the GNU Lesser General Public
|
|
|
59 |
// * License along with this library; if not, write to the Free Software
|
|
|
60 |
// * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
|
|
|
61 |
// */
|
|
|
62 |
|
|
|
63 |
|
|
|
64 |
/**
|
|
|
65 |
* PHPExcel_Writer_Excel5_Workbook
|
|
|
66 |
*
|
|
|
67 |
* @category PHPExcel
|
|
|
68 |
* @package PHPExcel_Writer_Excel5
|
|
|
69 |
* @copyright Copyright (c) 2006 - 2013 PHPExcel (http://www.codeplex.com/PHPExcel)
|
|
|
70 |
*/
|
|
|
71 |
class PHPExcel_Writer_Excel5_Workbook extends PHPExcel_Writer_Excel5_BIFFwriter
|
|
|
72 |
{
|
|
|
73 |
/**
|
|
|
74 |
* Formula parser
|
|
|
75 |
*
|
|
|
76 |
* @var PHPExcel_Writer_Excel5_Parser
|
|
|
77 |
*/
|
|
|
78 |
private $_parser;
|
|
|
79 |
|
|
|
80 |
/**
|
|
|
81 |
* The BIFF file size for the workbook.
|
|
|
82 |
* @var integer
|
|
|
83 |
* @see _calcSheetOffsets()
|
|
|
84 |
*/
|
|
|
85 |
public $_biffsize;
|
|
|
86 |
|
|
|
87 |
/**
|
|
|
88 |
* XF Writers
|
|
|
89 |
* @var PHPExcel_Writer_Excel5_Xf[]
|
|
|
90 |
*/
|
|
|
91 |
private $_xfWriters = array();
|
|
|
92 |
|
|
|
93 |
/**
|
|
|
94 |
* Array containing the colour palette
|
|
|
95 |
* @var array
|
|
|
96 |
*/
|
|
|
97 |
public $_palette;
|
|
|
98 |
|
|
|
99 |
/**
|
|
|
100 |
* The codepage indicates the text encoding used for strings
|
|
|
101 |
* @var integer
|
|
|
102 |
*/
|
|
|
103 |
public $_codepage;
|
|
|
104 |
|
|
|
105 |
/**
|
|
|
106 |
* The country code used for localization
|
|
|
107 |
* @var integer
|
|
|
108 |
*/
|
|
|
109 |
public $_country_code;
|
|
|
110 |
|
|
|
111 |
/**
|
|
|
112 |
* Workbook
|
|
|
113 |
* @var PHPExcel
|
|
|
114 |
*/
|
|
|
115 |
private $_phpExcel;
|
|
|
116 |
|
|
|
117 |
/**
|
|
|
118 |
* Fonts writers
|
|
|
119 |
*
|
|
|
120 |
* @var PHPExcel_Writer_Excel5_Font[]
|
|
|
121 |
*/
|
|
|
122 |
private $_fontWriters = array();
|
|
|
123 |
|
|
|
124 |
/**
|
|
|
125 |
* Added fonts. Maps from font's hash => index in workbook
|
|
|
126 |
*
|
|
|
127 |
* @var array
|
|
|
128 |
*/
|
|
|
129 |
private $_addedFonts = array();
|
|
|
130 |
|
|
|
131 |
/**
|
|
|
132 |
* Shared number formats
|
|
|
133 |
*
|
|
|
134 |
* @var array
|
|
|
135 |
*/
|
|
|
136 |
private $_numberFormats = array();
|
|
|
137 |
|
|
|
138 |
/**
|
|
|
139 |
* Added number formats. Maps from numberFormat's hash => index in workbook
|
|
|
140 |
*
|
|
|
141 |
* @var array
|
|
|
142 |
*/
|
|
|
143 |
private $_addedNumberFormats = array();
|
|
|
144 |
|
|
|
145 |
/**
|
|
|
146 |
* Sizes of the binary worksheet streams
|
|
|
147 |
*
|
|
|
148 |
* @var array
|
|
|
149 |
*/
|
|
|
150 |
private $_worksheetSizes = array();
|
|
|
151 |
|
|
|
152 |
/**
|
|
|
153 |
* Offsets of the binary worksheet streams relative to the start of the global workbook stream
|
|
|
154 |
*
|
|
|
155 |
* @var array
|
|
|
156 |
*/
|
|
|
157 |
private $_worksheetOffsets = array();
|
|
|
158 |
|
|
|
159 |
/**
|
|
|
160 |
* Total number of shared strings in workbook
|
|
|
161 |
*
|
|
|
162 |
* @var int
|
|
|
163 |
*/
|
|
|
164 |
private $_str_total;
|
|
|
165 |
|
|
|
166 |
/**
|
|
|
167 |
* Number of unique shared strings in workbook
|
|
|
168 |
*
|
|
|
169 |
* @var int
|
|
|
170 |
*/
|
|
|
171 |
private $_str_unique;
|
|
|
172 |
|
|
|
173 |
/**
|
|
|
174 |
* Array of unique shared strings in workbook
|
|
|
175 |
*
|
|
|
176 |
* @var array
|
|
|
177 |
*/
|
|
|
178 |
private $_str_table;
|
|
|
179 |
|
|
|
180 |
/**
|
|
|
181 |
* Color cache
|
|
|
182 |
*/
|
|
|
183 |
private $_colors;
|
|
|
184 |
|
|
|
185 |
/**
|
|
|
186 |
* Escher object corresponding to MSODRAWINGGROUP
|
|
|
187 |
*
|
|
|
188 |
* @var PHPExcel_Shared_Escher
|
|
|
189 |
*/
|
|
|
190 |
private $_escher;
|
|
|
191 |
|
|
|
192 |
|
|
|
193 |
/**
|
|
|
194 |
* Class constructor
|
|
|
195 |
*
|
|
|
196 |
* @param PHPExcel $phpExcel The Workbook
|
|
|
197 |
* @param int &$str_total Total number of strings
|
|
|
198 |
* @param int &$str_unique Total number of unique strings
|
|
|
199 |
* @param array &$str_table String Table
|
|
|
200 |
* @param array &$colors Colour Table
|
|
|
201 |
* @param mixed $parser The formula parser created for the Workbook
|
|
|
202 |
*/
|
|
|
203 |
public function __construct(PHPExcel $phpExcel = null,
|
|
|
204 |
&$str_total, &$str_unique, &$str_table, &$colors,
|
|
|
205 |
$parser )
|
|
|
206 |
{
|
|
|
207 |
// It needs to call its parent's constructor explicitly
|
|
|
208 |
parent::__construct();
|
|
|
209 |
|
|
|
210 |
$this->_parser = $parser;
|
|
|
211 |
$this->_biffsize = 0;
|
|
|
212 |
$this->_palette = array();
|
|
|
213 |
$this->_country_code = -1;
|
|
|
214 |
|
|
|
215 |
$this->_str_total = &$str_total;
|
|
|
216 |
$this->_str_unique = &$str_unique;
|
|
|
217 |
$this->_str_table = &$str_table;
|
|
|
218 |
$this->_colors = &$colors;
|
|
|
219 |
$this->_setPaletteXl97();
|
|
|
220 |
|
|
|
221 |
$this->_phpExcel = $phpExcel;
|
|
|
222 |
|
|
|
223 |
// set BIFFwriter limit for CONTINUE records
|
|
|
224 |
// $this->_limit = 8224;
|
|
|
225 |
$this->_codepage = 0x04B0;
|
|
|
226 |
|
|
|
227 |
// Add empty sheets and Build color cache
|
|
|
228 |
$countSheets = $phpExcel->getSheetCount();
|
|
|
229 |
for ($i = 0; $i < $countSheets; ++$i) {
|
|
|
230 |
$phpSheet = $phpExcel->getSheet($i);
|
|
|
231 |
|
|
|
232 |
$this->_parser->setExtSheet($phpSheet->getTitle(), $i); // Register worksheet name with parser
|
|
|
233 |
|
|
|
234 |
$supbook_index = 0x00;
|
|
|
235 |
$ref = pack('vvv', $supbook_index, $i, $i);
|
|
|
236 |
$this->_parser->_references[] = $ref; // Register reference with parser
|
|
|
237 |
|
|
|
238 |
// Sheet tab colors?
|
|
|
239 |
if ($phpSheet->isTabColorSet()) {
|
|
|
240 |
$this->_addColor($phpSheet->getTabColor()->getRGB());
|
|
|
241 |
}
|
|
|
242 |
}
|
|
|
243 |
|
|
|
244 |
}
|
|
|
245 |
|
|
|
246 |
/**
|
|
|
247 |
* Add a new XF writer
|
|
|
248 |
*
|
|
|
249 |
* @param PHPExcel_Style
|
|
|
250 |
* @param boolean Is it a style XF?
|
|
|
251 |
* @return int Index to XF record
|
|
|
252 |
*/
|
|
|
253 |
public function addXfWriter($style, $isStyleXf = false)
|
|
|
254 |
{
|
|
|
255 |
$xfWriter = new PHPExcel_Writer_Excel5_Xf($style);
|
|
|
256 |
$xfWriter->setIsStyleXf($isStyleXf);
|
|
|
257 |
|
|
|
258 |
// Add the font if not already added
|
|
|
259 |
$fontIndex = $this->_addFont($style->getFont());
|
|
|
260 |
|
|
|
261 |
// Assign the font index to the xf record
|
|
|
262 |
$xfWriter->setFontIndex($fontIndex);
|
|
|
263 |
|
|
|
264 |
// Background colors, best to treat these after the font so black will come after white in custom palette
|
|
|
265 |
$xfWriter->setFgColor($this->_addColor($style->getFill()->getStartColor()->getRGB()));
|
|
|
266 |
$xfWriter->setBgColor($this->_addColor($style->getFill()->getEndColor()->getRGB()));
|
|
|
267 |
$xfWriter->setBottomColor($this->_addColor($style->getBorders()->getBottom()->getColor()->getRGB()));
|
|
|
268 |
$xfWriter->setTopColor($this->_addColor($style->getBorders()->getTop()->getColor()->getRGB()));
|
|
|
269 |
$xfWriter->setRightColor($this->_addColor($style->getBorders()->getRight()->getColor()->getRGB()));
|
|
|
270 |
$xfWriter->setLeftColor($this->_addColor($style->getBorders()->getLeft()->getColor()->getRGB()));
|
|
|
271 |
$xfWriter->setDiagColor($this->_addColor($style->getBorders()->getDiagonal()->getColor()->getRGB()));
|
|
|
272 |
|
|
|
273 |
// Add the number format if it is not a built-in one and not already added
|
|
|
274 |
if ($style->getNumberFormat()->getBuiltInFormatCode() === false) {
|
|
|
275 |
$numberFormatHashCode = $style->getNumberFormat()->getHashCode();
|
|
|
276 |
|
|
|
277 |
if (isset($this->_addedNumberFormats[$numberFormatHashCode])) {
|
|
|
278 |
$numberFormatIndex = $this->_addedNumberFormats[$numberFormatHashCode];
|
|
|
279 |
} else {
|
|
|
280 |
$numberFormatIndex = 164 + count($this->_numberFormats);
|
|
|
281 |
$this->_numberFormats[$numberFormatIndex] = $style->getNumberFormat();
|
|
|
282 |
$this->_addedNumberFormats[$numberFormatHashCode] = $numberFormatIndex;
|
|
|
283 |
}
|
|
|
284 |
} else {
|
|
|
285 |
$numberFormatIndex = (int) $style->getNumberFormat()->getBuiltInFormatCode();
|
|
|
286 |
}
|
|
|
287 |
|
|
|
288 |
// Assign the number format index to xf record
|
|
|
289 |
$xfWriter->setNumberFormatIndex($numberFormatIndex);
|
|
|
290 |
|
|
|
291 |
$this->_xfWriters[] = $xfWriter;
|
|
|
292 |
|
|
|
293 |
$xfIndex = count($this->_xfWriters) - 1;
|
|
|
294 |
return $xfIndex;
|
|
|
295 |
}
|
|
|
296 |
|
|
|
297 |
/**
|
|
|
298 |
* Add a font to added fonts
|
|
|
299 |
*
|
|
|
300 |
* @param PHPExcel_Style_Font $font
|
|
|
301 |
* @return int Index to FONT record
|
|
|
302 |
*/
|
|
|
303 |
public function _addFont(PHPExcel_Style_Font $font)
|
|
|
304 |
{
|
|
|
305 |
$fontHashCode = $font->getHashCode();
|
|
|
306 |
if(isset($this->_addedFonts[$fontHashCode])){
|
|
|
307 |
$fontIndex = $this->_addedFonts[$fontHashCode];
|
|
|
308 |
} else {
|
|
|
309 |
$countFonts = count($this->_fontWriters);
|
|
|
310 |
$fontIndex = ($countFonts < 4) ? $countFonts : $countFonts + 1;
|
|
|
311 |
|
|
|
312 |
$fontWriter = new PHPExcel_Writer_Excel5_Font($font);
|
|
|
313 |
$fontWriter->setColorIndex($this->_addColor($font->getColor()->getRGB()));
|
|
|
314 |
$this->_fontWriters[] = $fontWriter;
|
|
|
315 |
|
|
|
316 |
$this->_addedFonts[$fontHashCode] = $fontIndex;
|
|
|
317 |
}
|
|
|
318 |
return $fontIndex;
|
|
|
319 |
}
|
|
|
320 |
|
|
|
321 |
/**
|
|
|
322 |
* Alter color palette adding a custom color
|
|
|
323 |
*
|
|
|
324 |
* @param string $rgb E.g. 'FF00AA'
|
|
|
325 |
* @return int Color index
|
|
|
326 |
*/
|
|
|
327 |
private function _addColor($rgb) {
|
|
|
328 |
if (!isset($this->_colors[$rgb])) {
|
|
|
329 |
if (count($this->_colors) < 57) {
|
|
|
330 |
// then we add a custom color altering the palette
|
|
|
331 |
$colorIndex = 8 + count($this->_colors);
|
|
|
332 |
$this->_palette[$colorIndex] =
|
|
|
333 |
array(
|
|
|
334 |
hexdec(substr($rgb, 0, 2)),
|
|
|
335 |
hexdec(substr($rgb, 2, 2)),
|
|
|
336 |
hexdec(substr($rgb, 4)),
|
|
|
337 |
|
|
|
338 |
);
|
|
|
339 |
$this->_colors[$rgb] = $colorIndex;
|
|
|
340 |
} else {
|
|
|
341 |
// no room for more custom colors, just map to black
|
|
|
342 |
$colorIndex = 0;
|
|
|
343 |
}
|
|
|
344 |
} else {
|
|
|
345 |
// fetch already added custom color
|
|
|
346 |
$colorIndex = $this->_colors[$rgb];
|
|
|
347 |
}
|
|
|
348 |
|
|
|
349 |
return $colorIndex;
|
|
|
350 |
}
|
|
|
351 |
|
|
|
352 |
/**
|
|
|
353 |
* Sets the colour palette to the Excel 97+ default.
|
|
|
354 |
*
|
|
|
355 |
* @access private
|
|
|
356 |
*/
|
|
|
357 |
function _setPaletteXl97()
|
|
|
358 |
{
|
|
|
359 |
$this->_palette = array(
|
|
|
360 |
0x08 => array(0x00, 0x00, 0x00, 0x00),
|
|
|
361 |
0x09 => array(0xff, 0xff, 0xff, 0x00),
|
|
|
362 |
0x0A => array(0xff, 0x00, 0x00, 0x00),
|
|
|
363 |
0x0B => array(0x00, 0xff, 0x00, 0x00),
|
|
|
364 |
0x0C => array(0x00, 0x00, 0xff, 0x00),
|
|
|
365 |
0x0D => array(0xff, 0xff, 0x00, 0x00),
|
|
|
366 |
0x0E => array(0xff, 0x00, 0xff, 0x00),
|
|
|
367 |
0x0F => array(0x00, 0xff, 0xff, 0x00),
|
|
|
368 |
0x10 => array(0x80, 0x00, 0x00, 0x00),
|
|
|
369 |
0x11 => array(0x00, 0x80, 0x00, 0x00),
|
|
|
370 |
0x12 => array(0x00, 0x00, 0x80, 0x00),
|
|
|
371 |
0x13 => array(0x80, 0x80, 0x00, 0x00),
|
|
|
372 |
0x14 => array(0x80, 0x00, 0x80, 0x00),
|
|
|
373 |
0x15 => array(0x00, 0x80, 0x80, 0x00),
|
|
|
374 |
0x16 => array(0xc0, 0xc0, 0xc0, 0x00),
|
|
|
375 |
0x17 => array(0x80, 0x80, 0x80, 0x00),
|
|
|
376 |
0x18 => array(0x99, 0x99, 0xff, 0x00),
|
|
|
377 |
0x19 => array(0x99, 0x33, 0x66, 0x00),
|
|
|
378 |
0x1A => array(0xff, 0xff, 0xcc, 0x00),
|
|
|
379 |
0x1B => array(0xcc, 0xff, 0xff, 0x00),
|
|
|
380 |
0x1C => array(0x66, 0x00, 0x66, 0x00),
|
|
|
381 |
0x1D => array(0xff, 0x80, 0x80, 0x00),
|
|
|
382 |
0x1E => array(0x00, 0x66, 0xcc, 0x00),
|
|
|
383 |
0x1F => array(0xcc, 0xcc, 0xff, 0x00),
|
|
|
384 |
0x20 => array(0x00, 0x00, 0x80, 0x00),
|
|
|
385 |
0x21 => array(0xff, 0x00, 0xff, 0x00),
|
|
|
386 |
0x22 => array(0xff, 0xff, 0x00, 0x00),
|
|
|
387 |
0x23 => array(0x00, 0xff, 0xff, 0x00),
|
|
|
388 |
0x24 => array(0x80, 0x00, 0x80, 0x00),
|
|
|
389 |
0x25 => array(0x80, 0x00, 0x00, 0x00),
|
|
|
390 |
0x26 => array(0x00, 0x80, 0x80, 0x00),
|
|
|
391 |
0x27 => array(0x00, 0x00, 0xff, 0x00),
|
|
|
392 |
0x28 => array(0x00, 0xcc, 0xff, 0x00),
|
|
|
393 |
0x29 => array(0xcc, 0xff, 0xff, 0x00),
|
|
|
394 |
0x2A => array(0xcc, 0xff, 0xcc, 0x00),
|
|
|
395 |
0x2B => array(0xff, 0xff, 0x99, 0x00),
|
|
|
396 |
0x2C => array(0x99, 0xcc, 0xff, 0x00),
|
|
|
397 |
0x2D => array(0xff, 0x99, 0xcc, 0x00),
|
|
|
398 |
0x2E => array(0xcc, 0x99, 0xff, 0x00),
|
|
|
399 |
0x2F => array(0xff, 0xcc, 0x99, 0x00),
|
|
|
400 |
0x30 => array(0x33, 0x66, 0xff, 0x00),
|
|
|
401 |
0x31 => array(0x33, 0xcc, 0xcc, 0x00),
|
|
|
402 |
0x32 => array(0x99, 0xcc, 0x00, 0x00),
|
|
|
403 |
0x33 => array(0xff, 0xcc, 0x00, 0x00),
|
|
|
404 |
0x34 => array(0xff, 0x99, 0x00, 0x00),
|
|
|
405 |
0x35 => array(0xff, 0x66, 0x00, 0x00),
|
|
|
406 |
0x36 => array(0x66, 0x66, 0x99, 0x00),
|
|
|
407 |
0x37 => array(0x96, 0x96, 0x96, 0x00),
|
|
|
408 |
0x38 => array(0x00, 0x33, 0x66, 0x00),
|
|
|
409 |
0x39 => array(0x33, 0x99, 0x66, 0x00),
|
|
|
410 |
0x3A => array(0x00, 0x33, 0x00, 0x00),
|
|
|
411 |
0x3B => array(0x33, 0x33, 0x00, 0x00),
|
|
|
412 |
0x3C => array(0x99, 0x33, 0x00, 0x00),
|
|
|
413 |
0x3D => array(0x99, 0x33, 0x66, 0x00),
|
|
|
414 |
0x3E => array(0x33, 0x33, 0x99, 0x00),
|
|
|
415 |
0x3F => array(0x33, 0x33, 0x33, 0x00),
|
|
|
416 |
);
|
|
|
417 |
}
|
|
|
418 |
|
|
|
419 |
/**
|
|
|
420 |
* Assemble worksheets into a workbook and send the BIFF data to an OLE
|
|
|
421 |
* storage.
|
|
|
422 |
*
|
|
|
423 |
* @param array $pWorksheetSizes The sizes in bytes of the binary worksheet streams
|
|
|
424 |
* @return string Binary data for workbook stream
|
|
|
425 |
*/
|
|
|
426 |
public function writeWorkbook($pWorksheetSizes = null)
|
|
|
427 |
{
|
|
|
428 |
$this->_worksheetSizes = $pWorksheetSizes;
|
|
|
429 |
|
|
|
430 |
// Calculate the number of selected worksheet tabs and call the finalization
|
|
|
431 |
// methods for each worksheet
|
|
|
432 |
$total_worksheets = $this->_phpExcel->getSheetCount();
|
|
|
433 |
|
|
|
434 |
// Add part 1 of the Workbook globals, what goes before the SHEET records
|
|
|
435 |
$this->_storeBof(0x0005);
|
|
|
436 |
$this->_writeCodepage();
|
|
|
437 |
$this->_writeWindow1();
|
|
|
438 |
|
|
|
439 |
$this->_writeDatemode();
|
|
|
440 |
$this->_writeAllFonts();
|
|
|
441 |
$this->_writeAllNumFormats();
|
|
|
442 |
$this->_writeAllXfs();
|
|
|
443 |
$this->_writeAllStyles();
|
|
|
444 |
$this->_writePalette();
|
|
|
445 |
|
|
|
446 |
// Prepare part 3 of the workbook global stream, what goes after the SHEET records
|
|
|
447 |
$part3 = '';
|
|
|
448 |
if ($this->_country_code != -1) {
|
|
|
449 |
$part3 .= $this->_writeCountry();
|
|
|
450 |
}
|
|
|
451 |
$part3 .= $this->_writeRecalcId();
|
|
|
452 |
|
|
|
453 |
$part3 .= $this->_writeSupbookInternal();
|
|
|
454 |
/* TODO: store external SUPBOOK records and XCT and CRN records
|
|
|
455 |
in case of external references for BIFF8 */
|
|
|
456 |
$part3 .= $this->_writeExternsheetBiff8();
|
|
|
457 |
$part3 .= $this->_writeAllDefinedNamesBiff8();
|
|
|
458 |
$part3 .= $this->_writeMsoDrawingGroup();
|
|
|
459 |
$part3 .= $this->_writeSharedStringsTable();
|
|
|
460 |
|
|
|
461 |
$part3 .= $this->writeEof();
|
|
|
462 |
|
|
|
463 |
// Add part 2 of the Workbook globals, the SHEET records
|
|
|
464 |
$this->_calcSheetOffsets();
|
|
|
465 |
for ($i = 0; $i < $total_worksheets; ++$i) {
|
|
|
466 |
$this->_writeBoundsheet($this->_phpExcel->getSheet($i), $this->_worksheetOffsets[$i]);
|
|
|
467 |
}
|
|
|
468 |
|
|
|
469 |
// Add part 3 of the Workbook globals
|
|
|
470 |
$this->_data .= $part3;
|
|
|
471 |
|
|
|
472 |
return $this->_data;
|
|
|
473 |
}
|
|
|
474 |
|
|
|
475 |
/**
|
|
|
476 |
* Calculate offsets for Worksheet BOF records.
|
|
|
477 |
*
|
|
|
478 |
* @access private
|
|
|
479 |
*/
|
|
|
480 |
function _calcSheetOffsets()
|
|
|
481 |
{
|
|
|
482 |
$boundsheet_length = 10; // fixed length for a BOUNDSHEET record
|
|
|
483 |
|
|
|
484 |
// size of Workbook globals part 1 + 3
|
|
|
485 |
$offset = $this->_datasize;
|
|
|
486 |
|
|
|
487 |
// add size of Workbook globals part 2, the length of the SHEET records
|
|
|
488 |
$total_worksheets = count($this->_phpExcel->getAllSheets());
|
|
|
489 |
foreach ($this->_phpExcel->getWorksheetIterator() as $sheet) {
|
|
|
490 |
$offset += $boundsheet_length + strlen(PHPExcel_Shared_String::UTF8toBIFF8UnicodeShort($sheet->getTitle()));
|
|
|
491 |
}
|
|
|
492 |
|
|
|
493 |
// add the sizes of each of the Sheet substreams, respectively
|
|
|
494 |
for ($i = 0; $i < $total_worksheets; ++$i) {
|
|
|
495 |
$this->_worksheetOffsets[$i] = $offset;
|
|
|
496 |
$offset += $this->_worksheetSizes[$i];
|
|
|
497 |
}
|
|
|
498 |
$this->_biffsize = $offset;
|
|
|
499 |
}
|
|
|
500 |
|
|
|
501 |
/**
|
|
|
502 |
* Store the Excel FONT records.
|
|
|
503 |
*/
|
|
|
504 |
private function _writeAllFonts()
|
|
|
505 |
{
|
|
|
506 |
foreach ($this->_fontWriters as $fontWriter) {
|
|
|
507 |
$this->_append($fontWriter->writeFont());
|
|
|
508 |
}
|
|
|
509 |
}
|
|
|
510 |
|
|
|
511 |
/**
|
|
|
512 |
* Store user defined numerical formats i.e. FORMAT records
|
|
|
513 |
*/
|
|
|
514 |
private function _writeAllNumFormats()
|
|
|
515 |
{
|
|
|
516 |
foreach ($this->_numberFormats as $numberFormatIndex => $numberFormat) {
|
|
|
517 |
$this->_writeNumFormat($numberFormat->getFormatCode(), $numberFormatIndex);
|
|
|
518 |
}
|
|
|
519 |
}
|
|
|
520 |
|
|
|
521 |
/**
|
|
|
522 |
* Write all XF records.
|
|
|
523 |
*/
|
|
|
524 |
private function _writeAllXfs()
|
|
|
525 |
{
|
|
|
526 |
foreach ($this->_xfWriters as $xfWriter) {
|
|
|
527 |
$this->_append($xfWriter->writeXf());
|
|
|
528 |
}
|
|
|
529 |
}
|
|
|
530 |
|
|
|
531 |
/**
|
|
|
532 |
* Write all STYLE records.
|
|
|
533 |
*/
|
|
|
534 |
private function _writeAllStyles()
|
|
|
535 |
{
|
|
|
536 |
$this->_writeStyle();
|
|
|
537 |
}
|
|
|
538 |
|
|
|
539 |
/**
|
|
|
540 |
* Write the EXTERNCOUNT and EXTERNSHEET records. These are used as indexes for
|
|
|
541 |
* the NAME records.
|
|
|
542 |
*/
|
|
|
543 |
private function _writeExterns()
|
|
|
544 |
{
|
|
|
545 |
$countSheets = $this->_phpExcel->getSheetCount();
|
|
|
546 |
// Create EXTERNCOUNT with number of worksheets
|
|
|
547 |
$this->_writeExterncount($countSheets);
|
|
|
548 |
|
|
|
549 |
// Create EXTERNSHEET for each worksheet
|
|
|
550 |
for ($i = 0; $i < $countSheets; ++$i) {
|
|
|
551 |
$this->_writeExternsheet($this->_phpExcel->getSheet($i)->getTitle());
|
|
|
552 |
}
|
|
|
553 |
}
|
|
|
554 |
|
|
|
555 |
/**
|
|
|
556 |
* Write the NAME record to define the print area and the repeat rows and cols.
|
|
|
557 |
*/
|
|
|
558 |
private function _writeNames()
|
|
|
559 |
{
|
|
|
560 |
// total number of sheets
|
|
|
561 |
$total_worksheets = $this->_phpExcel->getSheetCount();
|
|
|
562 |
|
|
|
563 |
// Create the print area NAME records
|
|
|
564 |
for ($i = 0; $i < $total_worksheets; ++$i) {
|
|
|
565 |
$sheetSetup = $this->_phpExcel->getSheet($i)->getPageSetup();
|
|
|
566 |
// Write a Name record if the print area has been defined
|
|
|
567 |
if ($sheetSetup->isPrintAreaSet()) {
|
|
|
568 |
// Print area
|
|
|
569 |
$printArea = PHPExcel_Cell::splitRange($sheetSetup->getPrintArea());
|
|
|
570 |
$printArea = $printArea[0];
|
|
|
571 |
$printArea[0] = PHPExcel_Cell::coordinateFromString($printArea[0]);
|
|
|
572 |
$printArea[1] = PHPExcel_Cell::coordinateFromString($printArea[1]);
|
|
|
573 |
|
|
|
574 |
$print_rowmin = $printArea[0][1] - 1;
|
|
|
575 |
$print_rowmax = $printArea[1][1] - 1;
|
|
|
576 |
$print_colmin = PHPExcel_Cell::columnIndexFromString($printArea[0][0]) - 1;
|
|
|
577 |
$print_colmax = PHPExcel_Cell::columnIndexFromString($printArea[1][0]) - 1;
|
|
|
578 |
|
|
|
579 |
$this->_writeNameShort(
|
|
|
580 |
$i, // sheet index
|
|
|
581 |
0x06, // NAME type
|
|
|
582 |
$print_rowmin,
|
|
|
583 |
$print_rowmax,
|
|
|
584 |
$print_colmin,
|
|
|
585 |
$print_colmax
|
|
|
586 |
);
|
|
|
587 |
}
|
|
|
588 |
}
|
|
|
589 |
|
|
|
590 |
// Create the print title NAME records
|
|
|
591 |
for ($i = 0; $i < $total_worksheets; ++$i) {
|
|
|
592 |
$sheetSetup = $this->_phpExcel->getSheet($i)->getPageSetup();
|
|
|
593 |
|
|
|
594 |
// simultaneous repeatColumns repeatRows
|
|
|
595 |
if ($sheetSetup->isColumnsToRepeatAtLeftSet() && $sheetSetup->isRowsToRepeatAtTopSet()) {
|
|
|
596 |
$repeat = $sheetSetup->getColumnsToRepeatAtLeft();
|
|
|
597 |
$colmin = PHPExcel_Cell::columnIndexFromString($repeat[0]) - 1;
|
|
|
598 |
$colmax = PHPExcel_Cell::columnIndexFromString($repeat[1]) - 1;
|
|
|
599 |
|
|
|
600 |
$repeat = $sheetSetup->getRowsToRepeatAtTop();
|
|
|
601 |
$rowmin = $repeat[0] - 1;
|
|
|
602 |
$rowmax = $repeat[1] - 1;
|
|
|
603 |
|
|
|
604 |
$this->_writeNameLong(
|
|
|
605 |
$i, // sheet index
|
|
|
606 |
0x07, // NAME type
|
|
|
607 |
$rowmin,
|
|
|
608 |
$rowmax,
|
|
|
609 |
$colmin,
|
|
|
610 |
$colmax
|
|
|
611 |
);
|
|
|
612 |
|
|
|
613 |
// (exclusive) either repeatColumns or repeatRows
|
|
|
614 |
} else if ($sheetSetup->isColumnsToRepeatAtLeftSet() || $sheetSetup->isRowsToRepeatAtTopSet()) {
|
|
|
615 |
|
|
|
616 |
// Columns to repeat
|
|
|
617 |
if ($sheetSetup->isColumnsToRepeatAtLeftSet()) {
|
|
|
618 |
$repeat = $sheetSetup->getColumnsToRepeatAtLeft();
|
|
|
619 |
$colmin = PHPExcel_Cell::columnIndexFromString($repeat[0]) - 1;
|
|
|
620 |
$colmax = PHPExcel_Cell::columnIndexFromString($repeat[1]) - 1;
|
|
|
621 |
} else {
|
|
|
622 |
$colmin = 0;
|
|
|
623 |
$colmax = 255;
|
|
|
624 |
}
|
|
|
625 |
|
|
|
626 |
// Rows to repeat
|
|
|
627 |
if ($sheetSetup->isRowsToRepeatAtTopSet()) {
|
|
|
628 |
$repeat = $sheetSetup->getRowsToRepeatAtTop();
|
|
|
629 |
$rowmin = $repeat[0] - 1;
|
|
|
630 |
$rowmax = $repeat[1] - 1;
|
|
|
631 |
} else {
|
|
|
632 |
$rowmin = 0;
|
|
|
633 |
$rowmax = 65535;
|
|
|
634 |
}
|
|
|
635 |
|
|
|
636 |
$this->_writeNameShort(
|
|
|
637 |
$i, // sheet index
|
|
|
638 |
0x07, // NAME type
|
|
|
639 |
$rowmin,
|
|
|
640 |
$rowmax,
|
|
|
641 |
$colmin,
|
|
|
642 |
$colmax
|
|
|
643 |
);
|
|
|
644 |
}
|
|
|
645 |
}
|
|
|
646 |
}
|
|
|
647 |
|
|
|
648 |
/**
|
|
|
649 |
* Writes all the DEFINEDNAME records (BIFF8).
|
|
|
650 |
* So far this is only used for repeating rows/columns (print titles) and print areas
|
|
|
651 |
*/
|
|
|
652 |
private function _writeAllDefinedNamesBiff8()
|
|
|
653 |
{
|
|
|
654 |
$chunk = '';
|
|
|
655 |
|
|
|
656 |
// Named ranges
|
|
|
657 |
if (count($this->_phpExcel->getNamedRanges()) > 0) {
|
|
|
658 |
// Loop named ranges
|
|
|
659 |
$namedRanges = $this->_phpExcel->getNamedRanges();
|
|
|
660 |
foreach ($namedRanges as $namedRange) {
|
|
|
661 |
|
|
|
662 |
// Create absolute coordinate
|
|
|
663 |
$range = PHPExcel_Cell::splitRange($namedRange->getRange());
|
|
|
664 |
for ($i = 0; $i < count($range); $i++) {
|
|
|
665 |
$range[$i][0] = '\'' . str_replace("'", "''", $namedRange->getWorksheet()->getTitle()) . '\'!' . PHPExcel_Cell::absoluteCoordinate($range[$i][0]);
|
|
|
666 |
if (isset($range[$i][1])) {
|
|
|
667 |
$range[$i][1] = PHPExcel_Cell::absoluteCoordinate($range[$i][1]);
|
|
|
668 |
}
|
|
|
669 |
}
|
|
|
670 |
$range = PHPExcel_Cell::buildRange($range); // e.g. Sheet1!$A$1:$B$2
|
|
|
671 |
|
|
|
672 |
// parse formula
|
|
|
673 |
try {
|
|
|
674 |
$error = $this->_parser->parse($range);
|
|
|
675 |
$formulaData = $this->_parser->toReversePolish();
|
|
|
676 |
|
|
|
677 |
// make sure tRef3d is of type tRef3dR (0x3A)
|
|
|
678 |
if (isset($formulaData{0}) and ($formulaData{0} == "\x7A" or $formulaData{0} == "\x5A")) {
|
|
|
679 |
$formulaData = "\x3A" . substr($formulaData, 1);
|
|
|
680 |
}
|
|
|
681 |
|
|
|
682 |
if ($namedRange->getLocalOnly()) {
|
|
|
683 |
// local scope
|
|
|
684 |
$scope = $this->_phpExcel->getIndex($namedRange->getScope()) + 1;
|
|
|
685 |
} else {
|
|
|
686 |
// global scope
|
|
|
687 |
$scope = 0;
|
|
|
688 |
}
|
|
|
689 |
$chunk .= $this->writeData($this->_writeDefinedNameBiff8($namedRange->getName(), $formulaData, $scope, false));
|
|
|
690 |
|
|
|
691 |
} catch(PHPExcel_Exception $e) {
|
|
|
692 |
// do nothing
|
|
|
693 |
}
|
|
|
694 |
}
|
|
|
695 |
}
|
|
|
696 |
|
|
|
697 |
// total number of sheets
|
|
|
698 |
$total_worksheets = $this->_phpExcel->getSheetCount();
|
|
|
699 |
|
|
|
700 |
// write the print titles (repeating rows, columns), if any
|
|
|
701 |
for ($i = 0; $i < $total_worksheets; ++$i) {
|
|
|
702 |
$sheetSetup = $this->_phpExcel->getSheet($i)->getPageSetup();
|
|
|
703 |
// simultaneous repeatColumns repeatRows
|
|
|
704 |
if ($sheetSetup->isColumnsToRepeatAtLeftSet() && $sheetSetup->isRowsToRepeatAtTopSet()) {
|
|
|
705 |
$repeat = $sheetSetup->getColumnsToRepeatAtLeft();
|
|
|
706 |
$colmin = PHPExcel_Cell::columnIndexFromString($repeat[0]) - 1;
|
|
|
707 |
$colmax = PHPExcel_Cell::columnIndexFromString($repeat[1]) - 1;
|
|
|
708 |
|
|
|
709 |
$repeat = $sheetSetup->getRowsToRepeatAtTop();
|
|
|
710 |
$rowmin = $repeat[0] - 1;
|
|
|
711 |
$rowmax = $repeat[1] - 1;
|
|
|
712 |
|
|
|
713 |
// construct formula data manually
|
|
|
714 |
$formulaData = pack('Cv', 0x29, 0x17); // tMemFunc
|
|
|
715 |
$formulaData .= pack('Cvvvvv', 0x3B, $i, 0, 65535, $colmin, $colmax); // tArea3d
|
|
|
716 |
$formulaData .= pack('Cvvvvv', 0x3B, $i, $rowmin, $rowmax, 0, 255); // tArea3d
|
|
|
717 |
$formulaData .= pack('C', 0x10); // tList
|
|
|
718 |
|
|
|
719 |
// store the DEFINEDNAME record
|
|
|
720 |
$chunk .= $this->writeData($this->_writeDefinedNameBiff8(pack('C', 0x07), $formulaData, $i + 1, true));
|
|
|
721 |
|
|
|
722 |
// (exclusive) either repeatColumns or repeatRows
|
|
|
723 |
} else if ($sheetSetup->isColumnsToRepeatAtLeftSet() || $sheetSetup->isRowsToRepeatAtTopSet()) {
|
|
|
724 |
|
|
|
725 |
// Columns to repeat
|
|
|
726 |
if ($sheetSetup->isColumnsToRepeatAtLeftSet()) {
|
|
|
727 |
$repeat = $sheetSetup->getColumnsToRepeatAtLeft();
|
|
|
728 |
$colmin = PHPExcel_Cell::columnIndexFromString($repeat[0]) - 1;
|
|
|
729 |
$colmax = PHPExcel_Cell::columnIndexFromString($repeat[1]) - 1;
|
|
|
730 |
} else {
|
|
|
731 |
$colmin = 0;
|
|
|
732 |
$colmax = 255;
|
|
|
733 |
}
|
|
|
734 |
// Rows to repeat
|
|
|
735 |
if ($sheetSetup->isRowsToRepeatAtTopSet()) {
|
|
|
736 |
$repeat = $sheetSetup->getRowsToRepeatAtTop();
|
|
|
737 |
$rowmin = $repeat[0] - 1;
|
|
|
738 |
$rowmax = $repeat[1] - 1;
|
|
|
739 |
} else {
|
|
|
740 |
$rowmin = 0;
|
|
|
741 |
$rowmax = 65535;
|
|
|
742 |
}
|
|
|
743 |
|
|
|
744 |
// construct formula data manually because parser does not recognize absolute 3d cell references
|
|
|
745 |
$formulaData = pack('Cvvvvv', 0x3B, $i, $rowmin, $rowmax, $colmin, $colmax);
|
|
|
746 |
|
|
|
747 |
// store the DEFINEDNAME record
|
|
|
748 |
$chunk .= $this->writeData($this->_writeDefinedNameBiff8(pack('C', 0x07), $formulaData, $i + 1, true));
|
|
|
749 |
}
|
|
|
750 |
}
|
|
|
751 |
|
|
|
752 |
// write the print areas, if any
|
|
|
753 |
for ($i = 0; $i < $total_worksheets; ++$i) {
|
|
|
754 |
$sheetSetup = $this->_phpExcel->getSheet($i)->getPageSetup();
|
|
|
755 |
if ($sheetSetup->isPrintAreaSet()) {
|
|
|
756 |
// Print area, e.g. A3:J6,H1:X20
|
|
|
757 |
$printArea = PHPExcel_Cell::splitRange($sheetSetup->getPrintArea());
|
|
|
758 |
$countPrintArea = count($printArea);
|
|
|
759 |
|
|
|
760 |
$formulaData = '';
|
|
|
761 |
for ($j = 0; $j < $countPrintArea; ++$j) {
|
|
|
762 |
$printAreaRect = $printArea[$j]; // e.g. A3:J6
|
|
|
763 |
$printAreaRect[0] = PHPExcel_Cell::coordinateFromString($printAreaRect[0]);
|
|
|
764 |
$printAreaRect[1] = PHPExcel_Cell::coordinateFromString($printAreaRect[1]);
|
|
|
765 |
|
|
|
766 |
$print_rowmin = $printAreaRect[0][1] - 1;
|
|
|
767 |
$print_rowmax = $printAreaRect[1][1] - 1;
|
|
|
768 |
$print_colmin = PHPExcel_Cell::columnIndexFromString($printAreaRect[0][0]) - 1;
|
|
|
769 |
$print_colmax = PHPExcel_Cell::columnIndexFromString($printAreaRect[1][0]) - 1;
|
|
|
770 |
|
|
|
771 |
// construct formula data manually because parser does not recognize absolute 3d cell references
|
|
|
772 |
$formulaData .= pack('Cvvvvv', 0x3B, $i, $print_rowmin, $print_rowmax, $print_colmin, $print_colmax);
|
|
|
773 |
|
|
|
774 |
if ($j > 0) {
|
|
|
775 |
$formulaData .= pack('C', 0x10); // list operator token ','
|
|
|
776 |
}
|
|
|
777 |
}
|
|
|
778 |
|
|
|
779 |
// store the DEFINEDNAME record
|
|
|
780 |
$chunk .= $this->writeData($this->_writeDefinedNameBiff8(pack('C', 0x06), $formulaData, $i + 1, true));
|
|
|
781 |
}
|
|
|
782 |
}
|
|
|
783 |
|
|
|
784 |
// write autofilters, if any
|
|
|
785 |
for ($i = 0; $i < $total_worksheets; ++$i) {
|
|
|
786 |
$sheetAutoFilter = $this->_phpExcel->getSheet($i)->getAutoFilter();
|
|
|
787 |
$autoFilterRange = $sheetAutoFilter->getRange();
|
|
|
788 |
if(!empty($autoFilterRange)) {
|
|
|
789 |
$rangeBounds = PHPExcel_Cell::rangeBoundaries($autoFilterRange);
|
|
|
790 |
|
|
|
791 |
//Autofilter built in name
|
|
|
792 |
$name = pack('C', 0x0D);
|
|
|
793 |
|
|
|
794 |
$chunk .= $this->writeData($this->_writeShortNameBiff8($name, $i + 1, $rangeBounds, true));
|
|
|
795 |
}
|
|
|
796 |
}
|
|
|
797 |
|
|
|
798 |
return $chunk;
|
|
|
799 |
}
|
|
|
800 |
|
|
|
801 |
/**
|
|
|
802 |
* Write a DEFINEDNAME record for BIFF8 using explicit binary formula data
|
|
|
803 |
*
|
|
|
804 |
* @param string $name The name in UTF-8
|
|
|
805 |
* @param string $formulaData The binary formula data
|
|
|
806 |
* @param string $sheetIndex 1-based sheet index the defined name applies to. 0 = global
|
|
|
807 |
* @param boolean $isBuiltIn Built-in name?
|
|
|
808 |
* @return string Complete binary record data
|
|
|
809 |
*/
|
|
|
810 |
private function _writeDefinedNameBiff8($name, $formulaData, $sheetIndex = 0, $isBuiltIn = false)
|
|
|
811 |
{
|
|
|
812 |
$record = 0x0018;
|
|
|
813 |
|
|
|
814 |
// option flags
|
|
|
815 |
$options = $isBuiltIn ? 0x20 : 0x00;
|
|
|
816 |
|
|
|
817 |
// length of the name, character count
|
|
|
818 |
$nlen = PHPExcel_Shared_String::CountCharacters($name);
|
|
|
819 |
|
|
|
820 |
// name with stripped length field
|
|
|
821 |
$name = substr(PHPExcel_Shared_String::UTF8toBIFF8UnicodeLong($name), 2);
|
|
|
822 |
|
|
|
823 |
// size of the formula (in bytes)
|
|
|
824 |
$sz = strlen($formulaData);
|
|
|
825 |
|
|
|
826 |
// combine the parts
|
|
|
827 |
$data = pack('vCCvvvCCCC', $options, 0, $nlen, $sz, 0, $sheetIndex, 0, 0, 0, 0)
|
|
|
828 |
. $name . $formulaData;
|
|
|
829 |
$length = strlen($data);
|
|
|
830 |
|
|
|
831 |
$header = pack('vv', $record, $length);
|
|
|
832 |
|
|
|
833 |
return $header . $data;
|
|
|
834 |
}
|
|
|
835 |
|
|
|
836 |
/**
|
|
|
837 |
* Write a short NAME record
|
|
|
838 |
*
|
|
|
839 |
* @param string $name
|
|
|
840 |
* @param string $sheetIndex 1-based sheet index the defined name applies to. 0 = global
|
|
|
841 |
* @param integer[][] $rangeBounds range boundaries
|
|
|
842 |
* @param boolean $isHidden
|
|
|
843 |
* @return string Complete binary record data
|
|
|
844 |
* */
|
|
|
845 |
private function _writeShortNameBiff8($name, $sheetIndex = 0, $rangeBounds, $isHidden = false){
|
|
|
846 |
$record = 0x0018;
|
|
|
847 |
|
|
|
848 |
// option flags
|
|
|
849 |
$options = ($isHidden ? 0x21 : 0x00);
|
|
|
850 |
|
|
|
851 |
$extra = pack('Cvvvvv',
|
|
|
852 |
0x3B,
|
|
|
853 |
$sheetIndex - 1,
|
|
|
854 |
$rangeBounds[0][1] - 1,
|
|
|
855 |
$rangeBounds[1][1] - 1,
|
|
|
856 |
$rangeBounds[0][0] - 1,
|
|
|
857 |
$rangeBounds[1][0] - 1);
|
|
|
858 |
|
|
|
859 |
// size of the formula (in bytes)
|
|
|
860 |
$sz = strlen($extra);
|
|
|
861 |
|
|
|
862 |
// combine the parts
|
|
|
863 |
$data = pack('vCCvvvCCCCC', $options, 0, 1, $sz, 0, $sheetIndex, 0, 0, 0, 0, 0)
|
|
|
864 |
. $name . $extra;
|
|
|
865 |
$length = strlen($data);
|
|
|
866 |
|
|
|
867 |
$header = pack('vv', $record, $length);
|
|
|
868 |
|
|
|
869 |
return $header . $data;
|
|
|
870 |
}
|
|
|
871 |
|
|
|
872 |
/**
|
|
|
873 |
* Stores the CODEPAGE biff record.
|
|
|
874 |
*/
|
|
|
875 |
private function _writeCodepage()
|
|
|
876 |
{
|
|
|
877 |
$record = 0x0042; // Record identifier
|
|
|
878 |
$length = 0x0002; // Number of bytes to follow
|
|
|
879 |
$cv = $this->_codepage; // The code page
|
|
|
880 |
|
|
|
881 |
$header = pack('vv', $record, $length);
|
|
|
882 |
$data = pack('v', $cv);
|
|
|
883 |
|
|
|
884 |
$this->_append($header . $data);
|
|
|
885 |
}
|
|
|
886 |
|
|
|
887 |
/**
|
|
|
888 |
* Write Excel BIFF WINDOW1 record.
|
|
|
889 |
*/
|
|
|
890 |
private function _writeWindow1()
|
|
|
891 |
{
|
|
|
892 |
$record = 0x003D; // Record identifier
|
|
|
893 |
$length = 0x0012; // Number of bytes to follow
|
|
|
894 |
|
|
|
895 |
$xWn = 0x0000; // Horizontal position of window
|
|
|
896 |
$yWn = 0x0000; // Vertical position of window
|
|
|
897 |
$dxWn = 0x25BC; // Width of window
|
|
|
898 |
$dyWn = 0x1572; // Height of window
|
|
|
899 |
|
|
|
900 |
$grbit = 0x0038; // Option flags
|
|
|
901 |
|
|
|
902 |
// not supported by PHPExcel, so there is only one selected sheet, the active
|
|
|
903 |
$ctabsel = 1; // Number of workbook tabs selected
|
|
|
904 |
|
|
|
905 |
$wTabRatio = 0x0258; // Tab to scrollbar ratio
|
|
|
906 |
|
|
|
907 |
// not supported by PHPExcel, set to 0
|
|
|
908 |
$itabFirst = 0; // 1st displayed worksheet
|
|
|
909 |
$itabCur = $this->_phpExcel->getActiveSheetIndex(); // Active worksheet
|
|
|
910 |
|
|
|
911 |
$header = pack("vv", $record, $length);
|
|
|
912 |
$data = pack("vvvvvvvvv", $xWn, $yWn, $dxWn, $dyWn,
|
|
|
913 |
$grbit,
|
|
|
914 |
$itabCur, $itabFirst,
|
|
|
915 |
$ctabsel, $wTabRatio);
|
|
|
916 |
$this->_append($header . $data);
|
|
|
917 |
}
|
|
|
918 |
|
|
|
919 |
/**
|
|
|
920 |
* Writes Excel BIFF BOUNDSHEET record.
|
|
|
921 |
*
|
|
|
922 |
* @param PHPExcel_Worksheet $sheet Worksheet name
|
|
|
923 |
* @param integer $offset Location of worksheet BOF
|
|
|
924 |
*/
|
|
|
925 |
private function _writeBoundsheet($sheet, $offset)
|
|
|
926 |
{
|
|
|
927 |
$sheetname = $sheet->getTitle();
|
|
|
928 |
$record = 0x0085; // Record identifier
|
|
|
929 |
|
|
|
930 |
// sheet state
|
|
|
931 |
switch ($sheet->getSheetState()) {
|
|
|
932 |
case PHPExcel_Worksheet::SHEETSTATE_VISIBLE: $ss = 0x00; break;
|
|
|
933 |
case PHPExcel_Worksheet::SHEETSTATE_HIDDEN: $ss = 0x01; break;
|
|
|
934 |
case PHPExcel_Worksheet::SHEETSTATE_VERYHIDDEN: $ss = 0x02; break;
|
|
|
935 |
default: $ss = 0x00; break;
|
|
|
936 |
}
|
|
|
937 |
|
|
|
938 |
// sheet type
|
|
|
939 |
$st = 0x00;
|
|
|
940 |
|
|
|
941 |
$grbit = 0x0000; // Visibility and sheet type
|
|
|
942 |
|
|
|
943 |
$data = pack("VCC", $offset, $ss, $st);
|
|
|
944 |
$data .= PHPExcel_Shared_String::UTF8toBIFF8UnicodeShort($sheetname);
|
|
|
945 |
|
|
|
946 |
$length = strlen($data);
|
|
|
947 |
$header = pack("vv", $record, $length);
|
|
|
948 |
$this->_append($header . $data);
|
|
|
949 |
}
|
|
|
950 |
|
|
|
951 |
/**
|
|
|
952 |
* Write Internal SUPBOOK record
|
|
|
953 |
*/
|
|
|
954 |
private function _writeSupbookInternal()
|
|
|
955 |
{
|
|
|
956 |
$record = 0x01AE; // Record identifier
|
|
|
957 |
$length = 0x0004; // Bytes to follow
|
|
|
958 |
|
|
|
959 |
$header = pack("vv", $record, $length);
|
|
|
960 |
$data = pack("vv", $this->_phpExcel->getSheetCount(), 0x0401);
|
|
|
961 |
return $this->writeData($header . $data);
|
|
|
962 |
}
|
|
|
963 |
|
|
|
964 |
/**
|
|
|
965 |
* Writes the Excel BIFF EXTERNSHEET record. These references are used by
|
|
|
966 |
* formulas.
|
|
|
967 |
*
|
|
|
968 |
*/
|
|
|
969 |
private function _writeExternsheetBiff8()
|
|
|
970 |
{
|
|
|
971 |
$total_references = count($this->_parser->_references);
|
|
|
972 |
$record = 0x0017; // Record identifier
|
|
|
973 |
$length = 2 + 6 * $total_references; // Number of bytes to follow
|
|
|
974 |
|
|
|
975 |
$supbook_index = 0; // FIXME: only using internal SUPBOOK record
|
|
|
976 |
$header = pack("vv", $record, $length);
|
|
|
977 |
$data = pack('v', $total_references);
|
|
|
978 |
for ($i = 0; $i < $total_references; ++$i) {
|
|
|
979 |
$data .= $this->_parser->_references[$i];
|
|
|
980 |
}
|
|
|
981 |
return $this->writeData($header . $data);
|
|
|
982 |
}
|
|
|
983 |
|
|
|
984 |
/**
|
|
|
985 |
* Write Excel BIFF STYLE records.
|
|
|
986 |
*/
|
|
|
987 |
private function _writeStyle()
|
|
|
988 |
{
|
|
|
989 |
$record = 0x0293; // Record identifier
|
|
|
990 |
$length = 0x0004; // Bytes to follow
|
|
|
991 |
|
|
|
992 |
$ixfe = 0x8000; // Index to cell style XF
|
|
|
993 |
$BuiltIn = 0x00; // Built-in style
|
|
|
994 |
$iLevel = 0xff; // Outline style level
|
|
|
995 |
|
|
|
996 |
$header = pack("vv", $record, $length);
|
|
|
997 |
$data = pack("vCC", $ixfe, $BuiltIn, $iLevel);
|
|
|
998 |
$this->_append($header . $data);
|
|
|
999 |
}
|
|
|
1000 |
|
|
|
1001 |
/**
|
|
|
1002 |
* Writes Excel FORMAT record for non "built-in" numerical formats.
|
|
|
1003 |
*
|
|
|
1004 |
* @param string $format Custom format string
|
|
|
1005 |
* @param integer $ifmt Format index code
|
|
|
1006 |
*/
|
|
|
1007 |
private function _writeNumFormat($format, $ifmt)
|
|
|
1008 |
{
|
|
|
1009 |
$record = 0x041E; // Record identifier
|
|
|
1010 |
|
|
|
1011 |
$numberFormatString = PHPExcel_Shared_String::UTF8toBIFF8UnicodeLong($format);
|
|
|
1012 |
$length = 2 + strlen($numberFormatString); // Number of bytes to follow
|
|
|
1013 |
|
|
|
1014 |
|
|
|
1015 |
$header = pack("vv", $record, $length);
|
|
|
1016 |
$data = pack("v", $ifmt) . $numberFormatString;
|
|
|
1017 |
$this->_append($header . $data);
|
|
|
1018 |
}
|
|
|
1019 |
|
|
|
1020 |
/**
|
|
|
1021 |
* Write DATEMODE record to indicate the date system in use (1904 or 1900).
|
|
|
1022 |
*/
|
|
|
1023 |
private function _writeDatemode()
|
|
|
1024 |
{
|
|
|
1025 |
$record = 0x0022; // Record identifier
|
|
|
1026 |
$length = 0x0002; // Bytes to follow
|
|
|
1027 |
|
|
|
1028 |
$f1904 = (PHPExcel_Shared_Date::getExcelCalendar() == PHPExcel_Shared_Date::CALENDAR_MAC_1904) ?
|
|
|
1029 |
1 : 0; // Flag for 1904 date system
|
|
|
1030 |
|
|
|
1031 |
$header = pack("vv", $record, $length);
|
|
|
1032 |
$data = pack("v", $f1904);
|
|
|
1033 |
$this->_append($header . $data);
|
|
|
1034 |
}
|
|
|
1035 |
|
|
|
1036 |
/**
|
|
|
1037 |
* Write BIFF record EXTERNCOUNT to indicate the number of external sheet
|
|
|
1038 |
* references in the workbook.
|
|
|
1039 |
*
|
|
|
1040 |
* Excel only stores references to external sheets that are used in NAME.
|
|
|
1041 |
* The workbook NAME record is required to define the print area and the repeat
|
|
|
1042 |
* rows and columns.
|
|
|
1043 |
*
|
|
|
1044 |
* A similar method is used in Worksheet.php for a slightly different purpose.
|
|
|
1045 |
*
|
|
|
1046 |
* @param integer $cxals Number of external references
|
|
|
1047 |
*/
|
|
|
1048 |
private function _writeExterncount($cxals)
|
|
|
1049 |
{
|
|
|
1050 |
$record = 0x0016; // Record identifier
|
|
|
1051 |
$length = 0x0002; // Number of bytes to follow
|
|
|
1052 |
|
|
|
1053 |
$header = pack("vv", $record, $length);
|
|
|
1054 |
$data = pack("v", $cxals);
|
|
|
1055 |
$this->_append($header . $data);
|
|
|
1056 |
}
|
|
|
1057 |
|
|
|
1058 |
/**
|
|
|
1059 |
* Writes the Excel BIFF EXTERNSHEET record. These references are used by
|
|
|
1060 |
* formulas. NAME record is required to define the print area and the repeat
|
|
|
1061 |
* rows and columns.
|
|
|
1062 |
*
|
|
|
1063 |
* A similar method is used in Worksheet.php for a slightly different purpose.
|
|
|
1064 |
*
|
|
|
1065 |
* @param string $sheetname Worksheet name
|
|
|
1066 |
*/
|
|
|
1067 |
private function _writeExternsheet($sheetname)
|
|
|
1068 |
{
|
|
|
1069 |
$record = 0x0017; // Record identifier
|
|
|
1070 |
$length = 0x02 + strlen($sheetname); // Number of bytes to follow
|
|
|
1071 |
|
|
|
1072 |
$cch = strlen($sheetname); // Length of sheet name
|
|
|
1073 |
$rgch = 0x03; // Filename encoding
|
|
|
1074 |
|
|
|
1075 |
$header = pack("vv", $record, $length);
|
|
|
1076 |
$data = pack("CC", $cch, $rgch);
|
|
|
1077 |
$this->_append($header . $data . $sheetname);
|
|
|
1078 |
}
|
|
|
1079 |
|
|
|
1080 |
/**
|
|
|
1081 |
* Store the NAME record in the short format that is used for storing the print
|
|
|
1082 |
* area, repeat rows only and repeat columns only.
|
|
|
1083 |
*
|
|
|
1084 |
* @param integer $index Sheet index
|
|
|
1085 |
* @param integer $type Built-in name type
|
|
|
1086 |
* @param integer $rowmin Start row
|
|
|
1087 |
* @param integer $rowmax End row
|
|
|
1088 |
* @param integer $colmin Start colum
|
|
|
1089 |
* @param integer $colmax End column
|
|
|
1090 |
*/
|
|
|
1091 |
private function _writeNameShort($index, $type, $rowmin, $rowmax, $colmin, $colmax)
|
|
|
1092 |
{
|
|
|
1093 |
$record = 0x0018; // Record identifier
|
|
|
1094 |
$length = 0x0024; // Number of bytes to follow
|
|
|
1095 |
|
|
|
1096 |
$grbit = 0x0020; // Option flags
|
|
|
1097 |
$chKey = 0x00; // Keyboard shortcut
|
|
|
1098 |
$cch = 0x01; // Length of text name
|
|
|
1099 |
$cce = 0x0015; // Length of text definition
|
|
|
1100 |
$ixals = $index + 1; // Sheet index
|
|
|
1101 |
$itab = $ixals; // Equal to ixals
|
|
|
1102 |
$cchCustMenu = 0x00; // Length of cust menu text
|
|
|
1103 |
$cchDescription = 0x00; // Length of description text
|
|
|
1104 |
$cchHelptopic = 0x00; // Length of help topic text
|
|
|
1105 |
$cchStatustext = 0x00; // Length of status bar text
|
|
|
1106 |
$rgch = $type; // Built-in name type
|
|
|
1107 |
|
|
|
1108 |
$unknown03 = 0x3b;
|
|
|
1109 |
$unknown04 = 0xffff-$index;
|
|
|
1110 |
$unknown05 = 0x0000;
|
|
|
1111 |
$unknown06 = 0x0000;
|
|
|
1112 |
$unknown07 = 0x1087;
|
|
|
1113 |
$unknown08 = 0x8005;
|
|
|
1114 |
|
|
|
1115 |
$header = pack("vv", $record, $length);
|
|
|
1116 |
$data = pack("v", $grbit);
|
|
|
1117 |
$data .= pack("C", $chKey);
|
|
|
1118 |
$data .= pack("C", $cch);
|
|
|
1119 |
$data .= pack("v", $cce);
|
|
|
1120 |
$data .= pack("v", $ixals);
|
|
|
1121 |
$data .= pack("v", $itab);
|
|
|
1122 |
$data .= pack("C", $cchCustMenu);
|
|
|
1123 |
$data .= pack("C", $cchDescription);
|
|
|
1124 |
$data .= pack("C", $cchHelptopic);
|
|
|
1125 |
$data .= pack("C", $cchStatustext);
|
|
|
1126 |
$data .= pack("C", $rgch);
|
|
|
1127 |
$data .= pack("C", $unknown03);
|
|
|
1128 |
$data .= pack("v", $unknown04);
|
|
|
1129 |
$data .= pack("v", $unknown05);
|
|
|
1130 |
$data .= pack("v", $unknown06);
|
|
|
1131 |
$data .= pack("v", $unknown07);
|
|
|
1132 |
$data .= pack("v", $unknown08);
|
|
|
1133 |
$data .= pack("v", $index);
|
|
|
1134 |
$data .= pack("v", $index);
|
|
|
1135 |
$data .= pack("v", $rowmin);
|
|
|
1136 |
$data .= pack("v", $rowmax);
|
|
|
1137 |
$data .= pack("C", $colmin);
|
|
|
1138 |
$data .= pack("C", $colmax);
|
|
|
1139 |
$this->_append($header . $data);
|
|
|
1140 |
}
|
|
|
1141 |
|
|
|
1142 |
/**
|
|
|
1143 |
* Store the NAME record in the long format that is used for storing the repeat
|
|
|
1144 |
* rows and columns when both are specified. This shares a lot of code with
|
|
|
1145 |
* _writeNameShort() but we use a separate method to keep the code clean.
|
|
|
1146 |
* Code abstraction for reuse can be carried too far, and I should know. ;-)
|
|
|
1147 |
*
|
|
|
1148 |
* @param integer $index Sheet index
|
|
|
1149 |
* @param integer $type Built-in name type
|
|
|
1150 |
* @param integer $rowmin Start row
|
|
|
1151 |
* @param integer $rowmax End row
|
|
|
1152 |
* @param integer $colmin Start colum
|
|
|
1153 |
* @param integer $colmax End column
|
|
|
1154 |
*/
|
|
|
1155 |
private function _writeNameLong($index, $type, $rowmin, $rowmax, $colmin, $colmax)
|
|
|
1156 |
{
|
|
|
1157 |
$record = 0x0018; // Record identifier
|
|
|
1158 |
$length = 0x003d; // Number of bytes to follow
|
|
|
1159 |
$grbit = 0x0020; // Option flags
|
|
|
1160 |
$chKey = 0x00; // Keyboard shortcut
|
|
|
1161 |
$cch = 0x01; // Length of text name
|
|
|
1162 |
$cce = 0x002e; // Length of text definition
|
|
|
1163 |
$ixals = $index + 1; // Sheet index
|
|
|
1164 |
$itab = $ixals; // Equal to ixals
|
|
|
1165 |
$cchCustMenu = 0x00; // Length of cust menu text
|
|
|
1166 |
$cchDescription = 0x00; // Length of description text
|
|
|
1167 |
$cchHelptopic = 0x00; // Length of help topic text
|
|
|
1168 |
$cchStatustext = 0x00; // Length of status bar text
|
|
|
1169 |
$rgch = $type; // Built-in name type
|
|
|
1170 |
|
|
|
1171 |
$unknown01 = 0x29;
|
|
|
1172 |
$unknown02 = 0x002b;
|
|
|
1173 |
$unknown03 = 0x3b;
|
|
|
1174 |
$unknown04 = 0xffff-$index;
|
|
|
1175 |
$unknown05 = 0x0000;
|
|
|
1176 |
$unknown06 = 0x0000;
|
|
|
1177 |
$unknown07 = 0x1087;
|
|
|
1178 |
$unknown08 = 0x8008;
|
|
|
1179 |
|
|
|
1180 |
$header = pack("vv", $record, $length);
|
|
|
1181 |
$data = pack("v", $grbit);
|
|
|
1182 |
$data .= pack("C", $chKey);
|
|
|
1183 |
$data .= pack("C", $cch);
|
|
|
1184 |
$data .= pack("v", $cce);
|
|
|
1185 |
$data .= pack("v", $ixals);
|
|
|
1186 |
$data .= pack("v", $itab);
|
|
|
1187 |
$data .= pack("C", $cchCustMenu);
|
|
|
1188 |
$data .= pack("C", $cchDescription);
|
|
|
1189 |
$data .= pack("C", $cchHelptopic);
|
|
|
1190 |
$data .= pack("C", $cchStatustext);
|
|
|
1191 |
$data .= pack("C", $rgch);
|
|
|
1192 |
$data .= pack("C", $unknown01);
|
|
|
1193 |
$data .= pack("v", $unknown02);
|
|
|
1194 |
// Column definition
|
|
|
1195 |
$data .= pack("C", $unknown03);
|
|
|
1196 |
$data .= pack("v", $unknown04);
|
|
|
1197 |
$data .= pack("v", $unknown05);
|
|
|
1198 |
$data .= pack("v", $unknown06);
|
|
|
1199 |
$data .= pack("v", $unknown07);
|
|
|
1200 |
$data .= pack("v", $unknown08);
|
|
|
1201 |
$data .= pack("v", $index);
|
|
|
1202 |
$data .= pack("v", $index);
|
|
|
1203 |
$data .= pack("v", 0x0000);
|
|
|
1204 |
$data .= pack("v", 0x3fff);
|
|
|
1205 |
$data .= pack("C", $colmin);
|
|
|
1206 |
$data .= pack("C", $colmax);
|
|
|
1207 |
// Row definition
|
|
|
1208 |
$data .= pack("C", $unknown03);
|
|
|
1209 |
$data .= pack("v", $unknown04);
|
|
|
1210 |
$data .= pack("v", $unknown05);
|
|
|
1211 |
$data .= pack("v", $unknown06);
|
|
|
1212 |
$data .= pack("v", $unknown07);
|
|
|
1213 |
$data .= pack("v", $unknown08);
|
|
|
1214 |
$data .= pack("v", $index);
|
|
|
1215 |
$data .= pack("v", $index);
|
|
|
1216 |
$data .= pack("v", $rowmin);
|
|
|
1217 |
$data .= pack("v", $rowmax);
|
|
|
1218 |
$data .= pack("C", 0x00);
|
|
|
1219 |
$data .= pack("C", 0xff);
|
|
|
1220 |
// End of data
|
|
|
1221 |
$data .= pack("C", 0x10);
|
|
|
1222 |
$this->_append($header . $data);
|
|
|
1223 |
}
|
|
|
1224 |
|
|
|
1225 |
/**
|
|
|
1226 |
* Stores the COUNTRY record for localization
|
|
|
1227 |
*
|
|
|
1228 |
* @return string
|
|
|
1229 |
*/
|
|
|
1230 |
private function _writeCountry()
|
|
|
1231 |
{
|
|
|
1232 |
$record = 0x008C; // Record identifier
|
|
|
1233 |
$length = 4; // Number of bytes to follow
|
|
|
1234 |
|
|
|
1235 |
$header = pack('vv', $record, $length);
|
|
|
1236 |
/* using the same country code always for simplicity */
|
|
|
1237 |
$data = pack('vv', $this->_country_code, $this->_country_code);
|
|
|
1238 |
//$this->_append($header . $data);
|
|
|
1239 |
return $this->writeData($header . $data);
|
|
|
1240 |
}
|
|
|
1241 |
|
|
|
1242 |
/**
|
|
|
1243 |
* Write the RECALCID record
|
|
|
1244 |
*
|
|
|
1245 |
* @return string
|
|
|
1246 |
*/
|
|
|
1247 |
private function _writeRecalcId()
|
|
|
1248 |
{
|
|
|
1249 |
$record = 0x01C1; // Record identifier
|
|
|
1250 |
$length = 8; // Number of bytes to follow
|
|
|
1251 |
|
|
|
1252 |
$header = pack('vv', $record, $length);
|
|
|
1253 |
|
|
|
1254 |
// by inspection of real Excel files, MS Office Excel 2007 writes this
|
|
|
1255 |
$data = pack('VV', 0x000001C1, 0x00001E667);
|
|
|
1256 |
|
|
|
1257 |
return $this->writeData($header . $data);
|
|
|
1258 |
}
|
|
|
1259 |
|
|
|
1260 |
/**
|
|
|
1261 |
* Stores the PALETTE biff record.
|
|
|
1262 |
*/
|
|
|
1263 |
private function _writePalette()
|
|
|
1264 |
{
|
|
|
1265 |
$aref = $this->_palette;
|
|
|
1266 |
|
|
|
1267 |
$record = 0x0092; // Record identifier
|
|
|
1268 |
$length = 2 + 4 * count($aref); // Number of bytes to follow
|
|
|
1269 |
$ccv = count($aref); // Number of RGB values to follow
|
|
|
1270 |
$data = ''; // The RGB data
|
|
|
1271 |
|
|
|
1272 |
// Pack the RGB data
|
|
|
1273 |
foreach ($aref as $color) {
|
|
|
1274 |
foreach ($color as $byte) {
|
|
|
1275 |
$data .= pack("C",$byte);
|
|
|
1276 |
}
|
|
|
1277 |
}
|
|
|
1278 |
|
|
|
1279 |
$header = pack("vvv", $record, $length, $ccv);
|
|
|
1280 |
$this->_append($header . $data);
|
|
|
1281 |
}
|
|
|
1282 |
|
|
|
1283 |
/**
|
|
|
1284 |
* Handling of the SST continue blocks is complicated by the need to include an
|
|
|
1285 |
* additional continuation byte depending on whether the string is split between
|
|
|
1286 |
* blocks or whether it starts at the beginning of the block. (There are also
|
|
|
1287 |
* additional complications that will arise later when/if Rich Strings are
|
|
|
1288 |
* supported).
|
|
|
1289 |
*
|
|
|
1290 |
* The Excel documentation says that the SST record should be followed by an
|
|
|
1291 |
* EXTSST record. The EXTSST record is a hash table that is used to optimise
|
|
|
1292 |
* access to SST. However, despite the documentation it doesn't seem to be
|
|
|
1293 |
* required so we will ignore it.
|
|
|
1294 |
*
|
|
|
1295 |
* @return string Binary data
|
|
|
1296 |
*/
|
|
|
1297 |
private function _writeSharedStringsTable()
|
|
|
1298 |
{
|
|
|
1299 |
// maximum size of record data (excluding record header)
|
|
|
1300 |
$continue_limit = 8224;
|
|
|
1301 |
|
|
|
1302 |
// initialize array of record data blocks
|
|
|
1303 |
$recordDatas = array();
|
|
|
1304 |
|
|
|
1305 |
// start SST record data block with total number of strings, total number of unique strings
|
|
|
1306 |
$recordData = pack("VV", $this->_str_total, $this->_str_unique);
|
|
|
1307 |
|
|
|
1308 |
// loop through all (unique) strings in shared strings table
|
|
|
1309 |
foreach (array_keys($this->_str_table) as $string) {
|
|
|
1310 |
|
|
|
1311 |
// here $string is a BIFF8 encoded string
|
|
|
1312 |
|
|
|
1313 |
// length = character count
|
|
|
1314 |
$headerinfo = unpack("vlength/Cencoding", $string);
|
|
|
1315 |
|
|
|
1316 |
// currently, this is always 1 = uncompressed
|
|
|
1317 |
$encoding = $headerinfo["encoding"];
|
|
|
1318 |
|
|
|
1319 |
// initialize finished writing current $string
|
|
|
1320 |
$finished = false;
|
|
|
1321 |
|
|
|
1322 |
while ($finished === false) {
|
|
|
1323 |
|
|
|
1324 |
// normally, there will be only one cycle, but if string cannot immediately be written as is
|
|
|
1325 |
// there will be need for more than one cylcle, if string longer than one record data block, there
|
|
|
1326 |
// may be need for even more cycles
|
|
|
1327 |
|
|
|
1328 |
if (strlen($recordData) + strlen($string) <= $continue_limit) {
|
|
|
1329 |
// then we can write the string (or remainder of string) without any problems
|
|
|
1330 |
$recordData .= $string;
|
|
|
1331 |
|
|
|
1332 |
if (strlen($recordData) + strlen($string) == $continue_limit) {
|
|
|
1333 |
// we close the record data block, and initialize a new one
|
|
|
1334 |
$recordDatas[] = $recordData;
|
|
|
1335 |
$recordData = '';
|
|
|
1336 |
}
|
|
|
1337 |
|
|
|
1338 |
// we are finished writing this string
|
|
|
1339 |
$finished = true;
|
|
|
1340 |
} else {
|
|
|
1341 |
// special treatment writing the string (or remainder of the string)
|
|
|
1342 |
// If the string is very long it may need to be written in more than one CONTINUE record.
|
|
|
1343 |
|
|
|
1344 |
// check how many bytes more there is room for in the current record
|
|
|
1345 |
$space_remaining = $continue_limit - strlen($recordData);
|
|
|
1346 |
|
|
|
1347 |
// minimum space needed
|
|
|
1348 |
// uncompressed: 2 byte string length length field + 1 byte option flags + 2 byte character
|
|
|
1349 |
// compressed: 2 byte string length length field + 1 byte option flags + 1 byte character
|
|
|
1350 |
$min_space_needed = ($encoding == 1) ? 5 : 4;
|
|
|
1351 |
|
|
|
1352 |
// We have two cases
|
|
|
1353 |
// 1. space remaining is less than minimum space needed
|
|
|
1354 |
// here we must waste the space remaining and move to next record data block
|
|
|
1355 |
// 2. space remaining is greater than or equal to minimum space needed
|
|
|
1356 |
// here we write as much as we can in the current block, then move to next record data block
|
|
|
1357 |
|
|
|
1358 |
// 1. space remaining is less than minimum space needed
|
|
|
1359 |
if ($space_remaining < $min_space_needed) {
|
|
|
1360 |
// we close the block, store the block data
|
|
|
1361 |
$recordDatas[] = $recordData;
|
|
|
1362 |
|
|
|
1363 |
// and start new record data block where we start writing the string
|
|
|
1364 |
$recordData = '';
|
|
|
1365 |
|
|
|
1366 |
// 2. space remaining is greater than or equal to minimum space needed
|
|
|
1367 |
} else {
|
|
|
1368 |
// initialize effective remaining space, for Unicode strings this may need to be reduced by 1, see below
|
|
|
1369 |
$effective_space_remaining = $space_remaining;
|
|
|
1370 |
|
|
|
1371 |
// for uncompressed strings, sometimes effective space remaining is reduced by 1
|
|
|
1372 |
if ( $encoding == 1 && (strlen($string) - $space_remaining) % 2 == 1 ) {
|
|
|
1373 |
--$effective_space_remaining;
|
|
|
1374 |
}
|
|
|
1375 |
|
|
|
1376 |
// one block fininshed, store the block data
|
|
|
1377 |
$recordData .= substr($string, 0, $effective_space_remaining);
|
|
|
1378 |
|
|
|
1379 |
$string = substr($string, $effective_space_remaining); // for next cycle in while loop
|
|
|
1380 |
$recordDatas[] = $recordData;
|
|
|
1381 |
|
|
|
1382 |
// start new record data block with the repeated option flags
|
|
|
1383 |
$recordData = pack('C', $encoding);
|
|
|
1384 |
}
|
|
|
1385 |
}
|
|
|
1386 |
}
|
|
|
1387 |
}
|
|
|
1388 |
|
|
|
1389 |
// Store the last record data block unless it is empty
|
|
|
1390 |
// if there was no need for any continue records, this will be the for SST record data block itself
|
|
|
1391 |
if (strlen($recordData) > 0) {
|
|
|
1392 |
$recordDatas[] = $recordData;
|
|
|
1393 |
}
|
|
|
1394 |
|
|
|
1395 |
// combine into one chunk with all the blocks SST, CONTINUE,...
|
|
|
1396 |
$chunk = '';
|
|
|
1397 |
foreach ($recordDatas as $i => $recordData) {
|
|
|
1398 |
// first block should have the SST record header, remaing should have CONTINUE header
|
|
|
1399 |
$record = ($i == 0) ? 0x00FC : 0x003C;
|
|
|
1400 |
|
|
|
1401 |
$header = pack("vv", $record, strlen($recordData));
|
|
|
1402 |
$data = $header . $recordData;
|
|
|
1403 |
|
|
|
1404 |
$chunk .= $this->writeData($data);
|
|
|
1405 |
}
|
|
|
1406 |
|
|
|
1407 |
return $chunk;
|
|
|
1408 |
}
|
|
|
1409 |
|
|
|
1410 |
/**
|
|
|
1411 |
* Writes the MSODRAWINGGROUP record if needed. Possibly split using CONTINUE records.
|
|
|
1412 |
*/
|
|
|
1413 |
private function _writeMsoDrawingGroup()
|
|
|
1414 |
{
|
|
|
1415 |
// write the Escher stream if necessary
|
|
|
1416 |
if (isset($this->_escher)) {
|
|
|
1417 |
$writer = new PHPExcel_Writer_Excel5_Escher($this->_escher);
|
|
|
1418 |
$data = $writer->close();
|
|
|
1419 |
|
|
|
1420 |
$record = 0x00EB;
|
|
|
1421 |
$length = strlen($data);
|
|
|
1422 |
$header = pack("vv", $record, $length);
|
|
|
1423 |
|
|
|
1424 |
return $this->writeData($header . $data);
|
|
|
1425 |
|
|
|
1426 |
} else {
|
|
|
1427 |
return '';
|
|
|
1428 |
}
|
|
|
1429 |
}
|
|
|
1430 |
|
|
|
1431 |
/**
|
|
|
1432 |
* Get Escher object
|
|
|
1433 |
*
|
|
|
1434 |
* @return PHPExcel_Shared_Escher
|
|
|
1435 |
*/
|
|
|
1436 |
public function getEscher()
|
|
|
1437 |
{
|
|
|
1438 |
return $this->_escher;
|
|
|
1439 |
}
|
|
|
1440 |
|
|
|
1441 |
/**
|
|
|
1442 |
* Set Escher object
|
|
|
1443 |
*
|
|
|
1444 |
* @param PHPExcel_Shared_Escher $pValue
|
|
|
1445 |
*/
|
|
|
1446 |
public function setEscher(PHPExcel_Shared_Escher $pValue = null)
|
|
|
1447 |
{
|
|
|
1448 |
$this->_escher = $pValue;
|
|
|
1449 |
}
|
|
|
1450 |
}
|