WRITE_FORMULA_NORMAL
WRITE_FORMULA_NORMAL = 0
$_data : string
The string containing the data of the BIFF stream.
$_datasize : int
The size of the data in bytes. Should be the same as strlen($this->_data).
$phpSheet : \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet
Sheet object.
$fontHashIndex : array
Array of font hashes associated to FONT records index.
$byteOrder : int
The byte order of this architecture. 0 => little endian, 1 => big endian.
$limit : int
The maximum length for a BIFF record (excluding record header and length field). See addContinue().
$parser : \PhpOffice\PhpSpreadsheet\Writer\Xls\Parser
Formula parser.
$xlsStringMaxLength : int
Maximum number of characters for a string (LABEL record in BIFF5).
$columnInfo : array
Array containing format information for columns.
$selection : array
Array containing the selected area for the worksheet.
$activePane : int
The active pane for the worksheet.
$outlineOn : int
Whether to use outline.
$outlineStyle : bool
Auto outline styles.
$outlineBelow : bool
Whether to have outline summary below.
$outlineRight : bool
Whether to have outline summary at the right.
$stringTotal : int
Reference to the total number of strings in the workbook.
$stringUnique : int
Reference to the number of unique strings in the workbook.
$stringTable : array
Reference to the array containing all the unique strings in the workbook.
$firstRowIndex : int
Index of first used row (at least 0).
$lastRowIndex : int
Index of last used row. (no used rows means -1).
$firstColumnIndex : int
Index of first used column (at least 0).
$lastColumnIndex : int
Index of last used column (no used columns means -1).
$countCellStyleXfs : int
Count cell style Xfs.
$escher : \PhpOffice\PhpSpreadsheet\Shared\Escher
Escher object corresponding to MSODRAWING.
$preCalculateFormulas : bool
$printHeaders : int
__construct(int $str_total, int $str_unique, array $str_table, array $colors, \PhpOffice\PhpSpreadsheet\Writer\Xls\Parser $parser, bool $preCalculateFormulas, \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet $phpSheet) : mixed
Constructor.
| int | $str_total | Total number of strings |
| int | $str_unique | Total number of unique strings |
| array | $str_table | String Table |
| array | $colors | Colour Table |
| \PhpOffice\PhpSpreadsheet\Writer\Xls\Parser | $parser | The formula parser created for the Workbook |
| bool | $preCalculateFormulas | Flag indicating whether formulas should be calculated or just written |
| \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet | $phpSheet | The worksheet to write |
setOutline(bool $visible = true, bool $symbols_below = true, bool $symbols_right = true, bool $auto_style = false) : void
This method sets the properties for outlining and grouping. The defaults correspond to Excel's defaults.
| bool | $visible | |
| bool | $symbols_below | |
| bool | $symbols_right | |
| bool | $auto_style |
writeBlank(int $row, int $col, mixed $xfIndex) : int
Write a blank cell to the specified row and column (zero indexed).
A blank cell is used to specify formatting without adding a string or a number.
A blank cell without a format serves no purpose. Therefore, we don't write a BLANK record unless a format is specified.
Returns 0 : normal termination (including no format) -1 : insufficient number of arguments -2 : row or column out of range
| int | $row | Zero indexed row |
| int | $col | Zero indexed column |
| mixed | $xfIndex | The XF format index |
writeUrlRange(int $row1, int $col1, int $row2, int $col2, string $url) : int
This is the more general form of writeUrl(). It allows a hyperlink to be written to a range of cells. This function also decides the type of hyperlink to be written. These are either, Web (http, ftp, mailto), Internal (Sheet1!A1) or external ('c:\temp\foo.xls#Sheet1!A1').
| int | $row1 | Start row |
| int | $col1 | Start column |
| int | $row2 | End row |
| int | $col2 | End column |
| string | $url | URL string |
writeUrlWeb(int $row1, int $col1, int $row2, int $col2, string $url) : int
Used to write http, ftp and mailto hyperlinks.
The link type ($options) is 0x03 is the same as absolute dir ref without sheet. However it is differentiated by the $unknown2 data stream.
| int | $row1 | Start row |
| int | $col1 | Start column |
| int | $row2 | End row |
| int | $col2 | End column |
| string | $url | URL string |
writeUrlExternal(int $row1, int $col1, int $row2, int $col2, string $url) : int
Write links to external directory names such as 'c:\foo.xls', c:\foo.xls#Sheet1!A1', '../../foo.xls'. and '../../foo.xls#Sheet1!A1'.
Note: Excel writes some relative links with the $dir_long string. We ignore these cases for the sake of simpler code.
| int | $row1 | Start row |
| int | $col1 | Start column |
| int | $row2 | End row |
| int | $col2 | End column |
| string | $url | URL string |
insertBitmap(int $row, int $col, mixed $bitmap, int $x, int $y, float $scale_x = 1, float $scale_y = 1) : void
Insert a 24bit bitmap image in a worksheet.
| int | $row | The row we are going to insert the bitmap into |
| int | $col | The column we are going to insert the bitmap into |
| mixed | $bitmap | The bitmap filename or GD-image resource |
| int | $x | the horizontal position (offset) of the image inside the cell |
| int | $y | the vertical position (offset) of the image inside the cell |
| float | $scale_x | The horizontal scale |
| float | $scale_y | The vertical scale |
positionImage(int $col_start, int $row_start, int $x1, int $y1, int $width, int $height) : void
Calculate the vertices that define the position of the image as required by the OBJ record.
+------------+------------+ | A | B | +-----+------------+------------+ | |(x1,y1) | | | 1 |(A1)._| | | | | | | | | | | | +-----+----| BITMAP |-----+ | | | | | | 2 | |______________. | | | | (B2)| | | | (x2,y2)| +---- +------------+------------+
Example of a bitmap that covers some of the area from cell A1 to cell B2.
Based on the width and height of the bitmap we need to calculate 8 vars: $col_start, $row_start, $col_end, $row_end, $x1, $y1, $x2, $y2. The width and height of the cells are also variable and have to be taken into account. The values of $col_start and $row_start are passed in from the calling function. The values of $col_end and $row_end are calculated by subtracting the width and height of the bitmap from the width and height of the underlying cells. The vertices are expressed as a percentage of the underlying cell width as follows (rhs values are in pixels):
x1 = X / W *1024
y1 = Y / H *256
x2 = (X-1) / W *1024
y2 = (Y-1) / H *256
Where: X is distance from the left side of the underlying cell
Y is distance from the top of the underlying cell
W is the width of the cell
H is the height of the cell
The SDK incorrectly states that the height should be expressed as a percentage of 1024.
| int | $col_start | Col containing upper left corner of object |
| int | $row_start | Row containing top left corner of object |
| int | $x1 | Distance to left side of object |
| int | $y1 | Distance to top of object |
| int | $width | Width of image frame |
| int | $height | Height of image frame |
processBitmap(string $bitmap) : array
Convert a 24 bit bitmap into the modified internal format used by Windows.
This is described in BITMAPCOREHEADER and BITMAPCOREINFO structures in the MSDN library.
| string | $bitmap | The bitmap to process |
Array with data and properties of the bitmap
addContinue(string $data) : string
Excel limits the size of BIFF records. In Excel 5 the limit is 2084 bytes. In Excel 97 the limit is 8228 bytes. Records that are longer than these limits must be split up into CONTINUE blocks.
This function takes a long BIFF record and inserts CONTINUE records as necessary.
| string | $data | The original binary data to be written |
A very convenient string of continue blocks
writeBIFF8CellRangeAddressFixed(string $range) : string
Write a cell range address in BIFF8 always fixed range See section 2.5.14 in OpenOffice.org's Documentation of the Microsoft Excel File Format.
| string | $range | E.g. 'A1' or 'A1:B6' |
Binary data
writeNumber(int $row, int $col, float $num, mixed $xfIndex) : int
Write a double to the specified row and column (zero indexed).
An integer can be written as a double. Excel will display an integer. $format is optional.
Returns 0 : normal termination -2 : row or column out of range
| int | $row | Zero indexed row |
| int | $col | Zero indexed column |
| float | $num | The number to write |
| mixed | $xfIndex | The optional XF format |
writeRichTextString(int $row, int $col, string $str, int $xfIndex, array $arrcRun) : void
Write a LABELSST record or a LABEL record. Which one depends on BIFF version It differs from writeString by the writing of rich text strings.
| int | $row | Row index (0-based) |
| int | $col | Column index (0-based) |
| string | $str | The string |
| int | $xfIndex | The XF format index for the cell |
| array | $arrcRun | Index to Font record and characters beginning |
writeLabelSst(int $row, int $col, string $str, mixed $xfIndex) : void
Write a string to the specified row and column (zero indexed).
This is the BIFF8 version (no 255 chars limit). $format is optional.
| int | $row | Zero indexed row |
| int | $col | Zero indexed column |
| string | $str | The string to write |
| mixed | $xfIndex | The XF format index for the cell |
writeBoolErr(int $row, int $col, int $value, bool $isError, int $xfIndex) : int
Write a boolean or an error type to the specified row and column (zero indexed).
| int | $row | Row index (0-based) |
| int | $col | Column index (0-based) |
| int | $value | |
| bool | $isError | Error or Boolean? |
| int | $xfIndex |
writeFormula(int $row, int $col, string $formula, mixed $xfIndex, mixed $calculatedValue) : int
Write a formula to the specified row and column (zero indexed).
The textual representation of the formula is passed to the parser in Parser.php which returns a packed binary string.
Returns 0 : WRITE_FORMULA_NORMAL normal termination -1 : WRITE_FORMULA_ERRORS formula errors (bad formula) -2 : WRITE_FORMULA_RANGE row or column out of range -3 : WRITE_FORMULA_EXCEPTION parse raised exception, probably due to definedname
| int | $row | Zero indexed row |
| int | $col | Zero indexed column |
| string | $formula | The formula text string |
| mixed | $xfIndex | The XF format index |
| mixed | $calculatedValue | Calculated value |
writeUrl(int $row, int $col, string $url) : int
Write a hyperlink.
This is comprised of two elements: the visible label and the invisible link. The visible label is the same as the link unless an alternative string is specified. The label is written using the writeString() method. Therefore the 255 characters string limit applies. $string and $format are optional.
The hyperlink can be to a http, ftp, mail, internal sheet (not yet), or external directory url.
Returns 0 : normal termination -2 : row or column out of range -3 : long string truncated to 255 chars
| int | $row | Row |
| int | $col | Column |
| string | $url | URL string |
writeRow(int $row, int $height, int $xfIndex, bool $hidden = false, int $level) : void
This method is used to set the height and format for a row.
| int | $row | The row to set |
| int | $height | Height we are giving to the row. Use null to set XF without setting height |
| int | $xfIndex | The optional cell style Xf index to apply to the columns |
| bool | $hidden | The optional hidden attribute |
| int | $level | The optional outline level for row, in range [0,7] |
writeColinfo(array $col_array) : void
Write BIFF record COLINFO to define column widths.
Note: The SDK says the record length is 0x0B but Excel writes a 0x0C length record.
| array | $col_array | This is the only parameter received and is composed of the following: 0 => First formatted column, 1 => Last formatted column, 2 => Col width (8.43 is Excel default), 3 => The optional XF format of the column, 4 => Option flags. 5 => Optional outline level |
writeObjPicture(int $colL, int $dxL, int $rwT, int $dyT, int $colR, int $dxR, int $rwB, int $dyB) : void
Store the OBJ record that precedes an IMDATA record. This could be generalise to support other Excel objects.
| int | $colL | Column containing upper left corner of object |
| int | $dxL | Distance from left side of cell |
| int | $rwT | Row containing top left corner of object |
| int | $dyT | Distance from top of cell |
| int | $colR | Column containing lower right corner of object |
| int | $dxR | Distance from right of cell |
| int | $rwB | Row containing bottom right corner of object |
| int | $dyB | Distance from bottom of cell |