| 1 |
<?php |
| 2 |
|
| 3 |
namespace TablePress\PhpOffice\PhpSpreadsheet\Reader\Xls; |
| 4 |
|
| 5 |
use TablePress\PhpOffice\PhpSpreadsheet\Cell\Coordinate; |
| 6 |
use TablePress\PhpOffice\PhpSpreadsheet\Reader\Exception as ReaderException; |
| 7 |
use TablePress\PhpOffice\PhpSpreadsheet\Reader\Xls; |
| 8 |
|
| 9 |
class Biff8 extends Xls |
| 10 |
{ |
| 11 |
/** |
| 12 |
* read BIFF8 constant value array from array data |
| 13 |
* returns e.g. ['value' => '{1,2;3,4}', 'size' => 40] |
| 14 |
* section 2.5.8. |
| 15 |
* |
| 16 |
* @return array{value: string, size: int} |
| 17 |
*/ |
| 18 |
protected static function readBIFF8ConstantArray(string $arrayData): array |
| 19 |
{ |
| 20 |
// offset: 0; size: 1; number of columns decreased by 1 |
| 21 |
$nc = ord($arrayData[0]); |
| 22 |
|
| 23 |
// offset: 1; size: 2; number of rows decreased by 1 |
| 24 |
$nr = self::getUInt2d($arrayData, 1); |
| 25 |
$size = 3; // initialize |
| 26 |
$arrayData = (string) substr($arrayData, 3); |
| 27 |
|
| 28 |
// offset: 3; size: var; list of ($nc + 1) * ($nr + 1) constant values |
| 29 |
$matrixChunks = []; |
| 30 |
for ($r = 1; $r <= $nr + 1; ++$r) { |
| 31 |
$items = []; |
| 32 |
for ($c = 1; $c <= $nc + 1; ++$c) { |
| 33 |
$constant = self::readBIFF8Constant($arrayData); |
| 34 |
$items[] = $constant['value']; |
| 35 |
$arrayData = (string) substr($arrayData, $constant['size']); |
| 36 |
$size += $constant['size']; |
| 37 |
} |
| 38 |
$matrixChunks[] = implode(',', $items); // looks like e.g. '1,"hello"' |
| 39 |
} |
| 40 |
$matrix = '{' . implode(';', $matrixChunks) . '}'; |
| 41 |
|
| 42 |
return [ |
| 43 |
'value' => $matrix, |
| 44 |
'size' => $size, |
| 45 |
]; |
| 46 |
} |
| 47 |
|
| 48 |
/** |
| 49 |
* read BIFF8 constant value which may be 'Empty Value', 'Number', 'String Value', 'Boolean Value', 'Error Value' |
| 50 |
* section 2.5.7 |
| 51 |
* returns e.g. ['value' => '5', 'size' => 9]. |
| 52 |
* |
| 53 |
* @return array{value: bool|float|int|string, size: int} |
| 54 |
*/ |
| 55 |
private static function readBIFF8Constant(string $valueData): array |
| 56 |
{ |
| 57 |
// offset: 0; size: 1; identifier for type of constant |
| 58 |
$identifier = ord($valueData[0]); |
| 59 |
|
| 60 |
switch ($identifier) { |
| 61 |
case 0x00: // empty constant (what is this?) |
| 62 |
$value = ''; |
| 63 |
$size = 9; |
| 64 |
|
| 65 |
break; |
| 66 |
case 0x01: // number |
| 67 |
// offset: 1; size: 8; IEEE 754 floating-point value |
| 68 |
$value = self::extractNumber((string) substr($valueData, 1, 8)); |
| 69 |
$size = 9; |
| 70 |
|
| 71 |
break; |
| 72 |
case 0x02: // string value |
| 73 |
// offset: 1; size: var; Unicode string, 16-bit string length |
| 74 |
$string = self::readUnicodeStringLong((string) substr($valueData, 1)); |
| 75 |
$value = '"' . $string['value'] . '"'; |
| 76 |
$size = 1 + $string['size']; |
| 77 |
|
| 78 |
break; |
| 79 |
case 0x04: // boolean |
| 80 |
// offset: 1; size: 1; 0 = FALSE, 1 = TRUE |
| 81 |
if (ord($valueData[1])) { |
| 82 |
$value = 'TRUE'; |
| 83 |
} else { |
| 84 |
$value = 'FALSE'; |
| 85 |
} |
| 86 |
$size = 9; |
| 87 |
|
| 88 |
break; |
| 89 |
case 0x10: // error code |
| 90 |
// offset: 1; size: 1; error code |
| 91 |
$value = ErrorCode::lookup(ord($valueData[1])); |
| 92 |
$size = 9; |
| 93 |
|
| 94 |
break; |
| 95 |
default: |
| 96 |
throw new ReaderException('Unsupported BIFF8 constant'); |
| 97 |
} |
| 98 |
|
| 99 |
return [ |
| 100 |
'value' => $value, |
| 101 |
'size' => $size, |
| 102 |
]; |
| 103 |
} |
| 104 |
|
| 105 |
/** |
| 106 |
* Read BIFF8 cell range address list |
| 107 |
* section 2.5.15. |
| 108 |
* |
| 109 |
* @return array{size: int, cellRangeAddresses: mixed[]} |
| 110 |
*/ |
| 111 |
public static function readBIFF8CellRangeAddressList(string $subData): array |
| 112 |
{ |
| 113 |
$cellRangeAddresses = []; |
| 114 |
|
| 115 |
// offset: 0; size: 2; number of the following cell range addresses |
| 116 |
$nm = self::getUInt2d($subData, 0); |
| 117 |
|
| 118 |
$offset = 2; |
| 119 |
// offset: 2; size: 8 * $nm; list of $nm (fixed) cell range addresses |
| 120 |
for ($i = 0; $i < $nm; ++$i) { |
| 121 |
$cellRangeAddresses[] = self::readBIFF8CellRangeAddressFixed((string) substr($subData, $offset, 8)); |
| 122 |
$offset += 8; |
| 123 |
} |
| 124 |
|
| 125 |
return [ |
| 126 |
'size' => 2 + 8 * $nm, |
| 127 |
'cellRangeAddresses' => $cellRangeAddresses, |
| 128 |
]; |
| 129 |
} |
| 130 |
|
| 131 |
/** |
| 132 |
* Reads a cell address in BIFF8 e.g. 'A2' or '$A$2' |
| 133 |
* section 3.3.4. |
| 134 |
*/ |
| 135 |
protected static function readBIFF8CellAddress(string $cellAddressStructure): string |
| 136 |
{ |
| 137 |
// offset: 0; size: 2; index to row (0... 65535) (or offset (-32768... 32767)) |
| 138 |
$row = self::getUInt2d($cellAddressStructure, 0) + 1; |
| 139 |
|
| 140 |
// offset: 2; size: 2; index to column or column offset + relative flags |
| 141 |
// bit: 7-0; mask 0x00FF; column index |
| 142 |
$column = Coordinate::stringFromColumnIndex((0x00FF & self::getUInt2d($cellAddressStructure, 2)) + 1); |
| 143 |
|
| 144 |
// bit: 14; mask 0x4000; (1 = relative column index, 0 = absolute column index) |
| 145 |
if (!(0x4000 & self::getUInt2d($cellAddressStructure, 2))) { |
| 146 |
$column = '$' . $column; |
| 147 |
} |
| 148 |
// bit: 15; mask 0x8000; (1 = relative row index, 0 = absolute row index) |
| 149 |
if (!(0x8000 & self::getUInt2d($cellAddressStructure, 2))) { |
| 150 |
$row = '$' . $row; |
| 151 |
} |
| 152 |
|
| 153 |
return $column . $row; |
| 154 |
} |
| 155 |
|
| 156 |
/** |
| 157 |
* Reads a cell address in BIFF8 for shared formulas. Uses positive and negative values for row and column |
| 158 |
* to indicate offsets from a base cell |
| 159 |
* section 3.3.4. |
| 160 |
* |
| 161 |
* @param string $baseCell Base cell, only needed when formula contains tRefN tokens, e.g. with shared formulas |
| 162 |
*/ |
| 163 |
protected static function readBIFF8CellAddressB(string $cellAddressStructure, string $baseCell = 'A1'): string |
| 164 |
{ |
| 165 |
[$baseCol, $baseRow] = Coordinate::coordinateFromString($baseCell); |
| 166 |
$baseCol = Coordinate::columnIndexFromString($baseCol) - 1; |
| 167 |
$baseRow = (int) $baseRow; |
| 168 |
|
| 169 |
// offset: 0; size: 2; index to row (0... 65535) (or offset (-32768... 32767)) |
| 170 |
$rowIndex = self::getUInt2d($cellAddressStructure, 0); |
| 171 |
$row = self::getUInt2d($cellAddressStructure, 0) + 1; |
| 172 |
|
| 173 |
// bit: 14; mask 0x4000; (1 = relative column index, 0 = absolute column index) |
| 174 |
if (!(0x4000 & self::getUInt2d($cellAddressStructure, 2))) { |
| 175 |
// offset: 2; size: 2; index to column or column offset + relative flags |
| 176 |
// bit: 7-0; mask 0x00FF; column index |
| 177 |
$colIndex = 0x00FF & self::getUInt2d($cellAddressStructure, 2); |
| 178 |
|
| 179 |
$column = Coordinate::stringFromColumnIndex($colIndex + 1); |
| 180 |
$column = '$' . $column; |
| 181 |
} else { |
| 182 |
// offset: 2; size: 2; index to column or column offset + relative flags |
| 183 |
// bit: 7-0; mask 0x00FF; column index |
| 184 |
$relativeColIndex = 0x00FF & self::getInt2d($cellAddressStructure, 2); |
| 185 |
$colIndex = $baseCol + $relativeColIndex; |
| 186 |
$colIndex = ($colIndex < 256) ? $colIndex : $colIndex - 256; |
| 187 |
$colIndex = ($colIndex >= 0) ? $colIndex : $colIndex + 256; |
| 188 |
$column = Coordinate::stringFromColumnIndex($colIndex + 1); |
| 189 |
} |
| 190 |
|
| 191 |
// bit: 15; mask 0x8000; (1 = relative row index, 0 = absolute row index) |
| 192 |
if (!(0x8000 & self::getUInt2d($cellAddressStructure, 2))) { |
| 193 |
$row = '$' . $row; |
| 194 |
} else { |
| 195 |
$rowIndex = ($rowIndex <= 32767) ? $rowIndex : $rowIndex - 65536; |
| 196 |
$row = $baseRow + $rowIndex; |
| 197 |
} |
| 198 |
|
| 199 |
return $column . $row; |
| 200 |
} |
| 201 |
|
| 202 |
/** |
| 203 |
* Reads a cell range address in BIFF8 e.g. 'A2:B6' or 'A1' |
| 204 |
* always fixed range |
| 205 |
* section 2.5.14. |
| 206 |
*/ |
| 207 |
protected static function readBIFF8CellRangeAddressFixed(string $subData): string |
| 208 |
{ |
| 209 |
// offset: 0; size: 2; index to first row |
| 210 |
$fr = self::getUInt2d($subData, 0) + 1; |
| 211 |
|
| 212 |
// offset: 2; size: 2; index to last row |
| 213 |
$lr = self::getUInt2d($subData, 2) + 1; |
| 214 |
|
| 215 |
// offset: 4; size: 2; index to first column |
| 216 |
$fc = self::getUInt2d($subData, 4); |
| 217 |
|
| 218 |
// offset: 6; size: 2; index to last column |
| 219 |
$lc = self::getUInt2d($subData, 6); |
| 220 |
|
| 221 |
// check values |
| 222 |
if ($fr > $lr || $fc > $lc) { |
| 223 |
throw new ReaderException('Not a cell range address'); |
| 224 |
} |
| 225 |
|
| 226 |
// column index to letter |
| 227 |
$fc = Coordinate::stringFromColumnIndex($fc + 1); |
| 228 |
$lc = Coordinate::stringFromColumnIndex($lc + 1); |
| 229 |
|
| 230 |
if ($fr == $lr && $fc == $lc) { |
| 231 |
return "$fc$fr"; |
| 232 |
} |
| 233 |
|
| 234 |
return "$fc$fr:$lc$lr"; |
| 235 |
} |
| 236 |
|
| 237 |
/** |
| 238 |
* Reads a cell range address in BIFF8 e.g. 'A2:B6' or '$A$2:$B$6' |
| 239 |
* there are flags indicating whether column/row index is relative |
| 240 |
* section 3.3.4. |
| 241 |
*/ |
| 242 |
protected static function readBIFF8CellRangeAddress(string $subData): string |
| 243 |
{ |
| 244 |
// todo: if cell range is just a single cell, should this function |
| 245 |
// not just return e.g. 'A1' and not 'A1:A1' ? |
| 246 |
|
| 247 |
// offset: 0; size: 2; index to first row (0... 65535) (or offset (-32768... 32767)) |
| 248 |
$fr = self::getUInt2d($subData, 0) + 1; |
| 249 |
|
| 250 |
// offset: 2; size: 2; index to last row (0... 65535) (or offset (-32768... 32767)) |
| 251 |
$lr = self::getUInt2d($subData, 2) + 1; |
| 252 |
|
| 253 |
// offset: 4; size: 2; index to first column or column offset + relative flags |
| 254 |
|
| 255 |
// bit: 7-0; mask 0x00FF; column index |
| 256 |
$fc = Coordinate::stringFromColumnIndex((0x00FF & self::getUInt2d($subData, 4)) + 1); |
| 257 |
|
| 258 |
// bit: 14; mask 0x4000; (1 = relative column index, 0 = absolute column index) |
| 259 |
if (!(0x4000 & self::getUInt2d($subData, 4))) { |
| 260 |
$fc = '$' . $fc; |
| 261 |
} |
| 262 |
|
| 263 |
// bit: 15; mask 0x8000; (1 = relative row index, 0 = absolute row index) |
| 264 |
if (!(0x8000 & self::getUInt2d($subData, 4))) { |
| 265 |
$fr = '$' . $fr; |
| 266 |
} |
| 267 |
|
| 268 |
// offset: 6; size: 2; index to last column or column offset + relative flags |
| 269 |
|
| 270 |
// bit: 7-0; mask 0x00FF; column index |
| 271 |
$lc = Coordinate::stringFromColumnIndex((0x00FF & self::getUInt2d($subData, 6)) + 1); |
| 272 |
|
| 273 |
// bit: 14; mask 0x4000; (1 = relative column index, 0 = absolute column index) |
| 274 |
if (!(0x4000 & self::getUInt2d($subData, 6))) { |
| 275 |
$lc = '$' . $lc; |
| 276 |
} |
| 277 |
|
| 278 |
// bit: 15; mask 0x8000; (1 = relative row index, 0 = absolute row index) |
| 279 |
if (!(0x8000 & self::getUInt2d($subData, 6))) { |
| 280 |
$lr = '$' . $lr; |
| 281 |
} |
| 282 |
|
| 283 |
return "$fc$fr:$lc$lr"; |
| 284 |
} |
| 285 |
|
| 286 |
/** |
| 287 |
* Reads a cell range address in BIFF8 for shared formulas. Uses positive and negative values for row and column |
| 288 |
* to indicate offsets from a base cell |
| 289 |
* section 3.3.4. |
| 290 |
* |
| 291 |
* @param string $baseCell Base cell |
| 292 |
* |
| 293 |
* @return string Cell range address |
| 294 |
*/ |
| 295 |
protected static function readBIFF8CellRangeAddressB(string $subData, string $baseCell = 'A1'): string |
| 296 |
{ |
| 297 |
[$baseCol, $baseRow] = Coordinate::indexesFromString($baseCell); |
| 298 |
$baseCol = $baseCol - 1; |
| 299 |
|
| 300 |
// TODO: if cell range is just a single cell, should this function |
| 301 |
// not just return e.g. 'A1' and not 'A1:A1' ? |
| 302 |
|
| 303 |
// offset: 0; size: 2; first row |
| 304 |
$frIndex = self::getUInt2d($subData, 0); // adjust below |
| 305 |
|
| 306 |
// offset: 2; size: 2; relative index to first row (0... 65535) should be treated as offset (-32768... 32767) |
| 307 |
$lrIndex = self::getUInt2d($subData, 2); // adjust below |
| 308 |
|
| 309 |
// bit: 14; mask 0x4000; (1 = relative column index, 0 = absolute column index) |
| 310 |
if (!(0x4000 & self::getUInt2d($subData, 4))) { |
| 311 |
// absolute column index |
| 312 |
// offset: 4; size: 2; first column with relative/absolute flags |
| 313 |
// bit: 7-0; mask 0x00FF; column index |
| 314 |
$fcIndex = 0x00FF & self::getUInt2d($subData, 4); |
| 315 |
$fc = Coordinate::stringFromColumnIndex($fcIndex + 1); |
| 316 |
$fc = '$' . $fc; |
| 317 |
} else { |
| 318 |
// column offset |
| 319 |
// offset: 4; size: 2; first column with relative/absolute flags |
| 320 |
// bit: 7-0; mask 0x00FF; column index |
| 321 |
$relativeFcIndex = 0x00FF & self::getInt2d($subData, 4); |
| 322 |
$fcIndex = $baseCol + $relativeFcIndex; |
| 323 |
$fcIndex = ($fcIndex < 256) ? $fcIndex : $fcIndex - 256; |
| 324 |
$fcIndex = ($fcIndex >= 0) ? $fcIndex : $fcIndex + 256; |
| 325 |
$fc = Coordinate::stringFromColumnIndex($fcIndex + 1); |
| 326 |
} |
| 327 |
|
| 328 |
// bit: 15; mask 0x8000; (1 = relative row index, 0 = absolute row index) |
| 329 |
if (!(0x8000 & self::getUInt2d($subData, 4))) { |
| 330 |
// absolute row index |
| 331 |
$fr = $frIndex + 1; |
| 332 |
$fr = '$' . $fr; |
| 333 |
} else { |
| 334 |
// row offset |
| 335 |
$frIndex = ($frIndex <= 32767) ? $frIndex : $frIndex - 65536; |
| 336 |
$fr = $baseRow + $frIndex; |
| 337 |
} |
| 338 |
|
| 339 |
// bit: 14; mask 0x4000; (1 = relative column index, 0 = absolute column index) |
| 340 |
if (!(0x4000 & self::getUInt2d($subData, 6))) { |
| 341 |
// absolute column index |
| 342 |
// offset: 6; size: 2; last column with relative/absolute flags |
| 343 |
// bit: 7-0; mask 0x00FF; column index |
| 344 |
$lcIndex = 0x00FF & self::getUInt2d($subData, 6); |
| 345 |
$lc = Coordinate::stringFromColumnIndex($lcIndex + 1); |
| 346 |
$lc = '$' . $lc; |
| 347 |
} else { |
| 348 |
// column offset |
| 349 |
// offset: 6; size: 2; last column with relative/absolute flags |
| 350 |
// bit: 7-0; mask 0x00FF; column index |
| 351 |
$relativeLcIndex = 0x00FF & self::getInt2d($subData, 6); |
| 352 |
$lcIndex = $baseCol + $relativeLcIndex; |
| 353 |
$lcIndex = ($lcIndex < 256) ? $lcIndex : $lcIndex - 256; |
| 354 |
$lcIndex = ($lcIndex >= 0) ? $lcIndex : $lcIndex + 256; |
| 355 |
$lc = Coordinate::stringFromColumnIndex($lcIndex + 1); |
| 356 |
} |
| 357 |
|
| 358 |
// bit: 15; mask 0x8000; (1 = relative row index, 0 = absolute row index) |
| 359 |
if (!(0x8000 & self::getUInt2d($subData, 6))) { |
| 360 |
// absolute row index |
| 361 |
$lr = $lrIndex + 1; |
| 362 |
$lr = '$' . $lr; |
| 363 |
} else { |
| 364 |
// row offset |
| 365 |
$lrIndex = ($lrIndex <= 32767) ? $lrIndex : $lrIndex - 65536; |
| 366 |
$lr = $baseRow + $lrIndex; |
| 367 |
} |
| 368 |
|
| 369 |
return "$fc$fr:$lc$lr"; |
| 370 |
} |
| 371 |
} |
| 372 |
|
