| 1 |
<?php |
| 2 |
/** |
| 3 |
* PHPExcel |
| 4 |
* |
| 5 |
* Copyright (c) 2006 - 2014 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 - 2014 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 - 2014 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 |
0 |
| 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 |
} |
| 1451 |
|
