Documentation

Worksheet extends BIFFwriter
in package

Application

Constants

MAX_XLS_COLUMN = 256
MAX_XLS_COLUMN_STRING = 'IV'
MAX_XLS_ROW = 65536
WRITE_FORMULA_ERRORS = -1
WRITE_FORMULA_EXCEPTION = -3
WRITE_FORMULA_NORMAL = 0
WRITE_FORMULA_RANGE = -2

Properties

$_data : string|null: 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).
$fontHashIndex : array<string|int, mixed>: Array of font hashes associated to FONT records index.
$phpSheet : Worksheet: Sheet object.
$activePane : int: The active pane for the worksheet.
$allowThrow : bool
$always0 : int
$always1 : int
$byteOrder : int|null: The byte order of this architecture. 0 => little endian, 1 => big endian.
$colors : array<string|int, mixed>: Color cache.
$columnInfo : array<string|int, mixed>: Array containing format information for columns.
$escher : Escher|null: Escher object corresponding to MSODRAWING.
$firstColumnIndex : int: Index of first used column (at least 0).
$firstRowIndex : int: Index of first used row (at least 0).
$lastColumnIndex : int: Index of last used column (no used columns means -1).
$lastRowIndex : int: Index of last used row. (no used rows means -1).
$limit : int: The maximum length for a BIFF record (excluding record header and length field). See addContinue().
$outlineBelow : bool: Whether to have outline summary below.
$outlineOn : bool: Whether to use outline.
$outlineRight : bool: Whether to have outline summary at the right.
$outlineStyle : bool: Auto outline styles.
$parser : Parser: Formula parser.
$preCalculateFormulas : bool
$printHeaders : int
$stringTable : array<string|int, mixed>: Reference to the array containing all the unique strings in the workbook.
$stringTotal : int: Reference to the total number of strings in the workbook.
$stringUnique : int: Reference to the number of unique strings in the workbook.
$writerWorkbook : Workbook|null

Methods

__construct() : mixed: Constructor.
close() : void: Add data to the beginning of the workbook (note the reverse order) and to the end of the workbook.
getAllowThrow() : bool
getByteOrder() : int: Determine the byte order and store it as class data to avoid recalculating it for each call to new().
getData() : string: Retrieves data from memory in one chunk, or from disk sized chunks.
getEscher() : Escher|null: Get Escher object.
insertBitmap() : void: Insert a 24bit bitmap image in a worksheet.
positionImage() : void: Calculate the vertices that define the position of the image as required by the OBJ record.
printRowColHeaders() : void: Set the option to print the row and column headers on the printed page.
processBitmap() : array<string|int, mixed>: Convert a 24 bit bitmap into the modified internal format used by Windows.
processBitmapGd() : array{0: float, 1: float, 2: int, 3: string}: Convert a GD-image into the internal format.
setAllowThrow() : void
setEscher() : void: Set Escher object.
setOutline() : void: This method sets the properties for outlining and grouping. The defaults correspond to Excel's defaults.
writeBlank() : int: Write a blank cell to the specified row and column (zero indexed).
writeData() : string: General storage function like append, but returns string instead of modifying $this->_data.
writeEof() : string: Writes Excel EOF record to indicate the end of a BIFF stream.
writeUrlWeb() : void: Used to write http, ftp and mailto hyperlinks.
append() : void: General storage function.
storeBof() : void: Writes Excel BOF record to indicate the beginning of a stream or sub-stream in the BIFF file.
storeEof() : void: Writes Excel EOF record to indicate the end of a BIFF stream.
addContinue() : 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.
limitRange() : string
protectionBitsDefaultFalse() : int
protectionBitsDefaultTrue() : int
workbookColorIndex() : int
writeAutoFilterInfo() : void: Write the AUTOFILTERINFO BIFF record. This is used to configure the number of autofilter select used in the sheet.
writeBIFF8CellRangeAddressFixed() : 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.
writeBoolErr() : int: Write a boolean or an error type to the specified row and column (zero indexed).
writeBreaks() : void: Write the HORIZONTALPAGEBREAKS and VERTICALPAGEBREAKS BIFF records.
writeCFHeader() : bool: Write CFHeader record.
writeCFRule() : void: Write CFRule Record.
writeColinfo() : void: Write BIFF record COLINFO to define column widths.
writeConditionalFormatting() : void
writeDataValidity() : void: Store the DATAVALIDATIONS and DATAVALIDATION records.
writeDefaultRowHeight() : void: Write BIFF record DEFAULTROWHEIGHT.
writeDefcol() : void: Write BIFF record DEFCOLWIDTH if COLINFO records are in use.
writeDimensions() : void: Writes Excel DIMENSIONS to define the area in which there is data.
writeFooter() : void: Store the footer caption BIFF record.
writeFormula() : int: Write a formula to the specified row and column (zero indexed).
writeGridset() : void: Write the GRIDSET BIFF record. Must be used in conjunction with the PRINTGRIDLINES record.
writeGuts() : void: Write the GUTS BIFF record. This is used to configure the gutter margins where Excel outline symbols are displayed. The visibility of the gutters is controlled by a flag in WSBOOL.
writeHcenter() : void: Store the horizontal centering HCENTER BIFF record.
writeHeader() : void: Store the header caption BIFF record.
writeLabelSst() : void: Write a string to the specified row and column (zero indexed).
writeMarginBottom() : void: Store the BOTTOMMARGIN BIFF record.
writeMarginLeft() : void: Store the LEFTMARGIN BIFF record.
writeMarginRight() : void: Store the RIGHTMARGIN BIFF record.
writeMarginTop() : void: Store the TOPMARGIN BIFF record.
writeMergedCells() : void: Store the MERGEDCELLS records for all ranges of merged cells.
writeMsoDrawing() : void: Write MSODRAWING record.
writeNumber() : int: Write a double to the specified row and column (zero indexed).
writeObjectProtect() : void: Write OBJECTPROTECT.
writeObjPicture() : void: Store the OBJ record that precedes an IMDATA record. This could be generalise to support other Excel objects.
writePageLayoutView() : void: Write PLV Record.
writePanes() : void: Writes the Excel BIFF PANE record.
writePassword() : void: Write the worksheet PASSWORD record.
writePrintGridlines() : void: Write the PRINTGRIDLINES BIFF record. Must be used in conjunction with the GRIDSET record.
writePrintHeaders() : void: Write the PRINTHEADERS BIFF record.
writeProtect() : void: Set the Biff PROTECT record to indicate that the worksheet is protected.
writeRangeProtection() : void: Write BIFF record RANGEPROTECTION.
writeRichTextString() : 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.
writeRow() : void: This method is used to set the height and format for a row.
writeScenProtect() : void: Write SCENPROTECT.
writeSelection() : void: Write BIFF record SELECTION.
writeSetup() : void: Store the page setup SETUP BIFF record.
writeSheetLayout() : void: Write SHEETLAYOUT record.
writeSheetProtection() : void: Write SHEETPROTECTION.
writeString() : void: Write a LABELSST record or a LABEL record. Which one depends on BIFF version.
writeStringRecord() : void: Write a STRING record. This.
writeUrl() : void: Write a hyperlink.
writeUrlExternal() : void: Write links to external directory names such as 'c:\foo.xls', c:\foo.xls#Sheet1!A1', '../../foo.xls'. and '../../foo.xls#Sheet1!A1'.
writeUrlInternal() : void: Used to write internal reference hyperlinks such as "Sheet1!A1".
writeUrlRange() : void: 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').
writeVcenter() : void: Store the vertical centering VCENTER BIFF record.
writeWindow2() : void: Write BIFF record Window2.
writeWsbool() : void: Write the WSBOOL BIFF record, mainly for fit-to-page. Used in conjunction with the SETUP record.
writeZoom() : void: Store the window zoom factor. This should be a reduced fraction but for simplicity we will store all fractions with a numerator of 100.

MAX_XLS_COLUMN


    public
        mixed
    MAX_XLS_COLUMN
    = 256

MAX_XLS_COLUMN_STRING


    public
        mixed
    MAX_XLS_COLUMN_STRING
    = 'IV'

MAX_XLS_ROW


    public
        mixed
    MAX_XLS_ROW
    = 65536

WRITE_FORMULA_ERRORS


    public
        mixed
    WRITE_FORMULA_ERRORS
    = -1

WRITE_FORMULA_EXCEPTION


    public
        mixed
    WRITE_FORMULA_EXCEPTION
    = -3

WRITE_FORMULA_NORMAL


    public
        mixed
    WRITE_FORMULA_NORMAL
    = 0

WRITE_FORMULA_RANGE


    public
        mixed
    WRITE_FORMULA_RANGE
    = -2

$_data

The string containing the data of the BIFF stream.


    public
        string|null
    $_data

$_datasize

The size of the data in bytes. Should be the same as strlen($this->_data).


    public
        int
    $_datasize

$fontHashIndex

Array of font hashes associated to FONT records index.


    public
        array<string|int, mixed>
    $fontHashIndex

$phpSheet

Sheet object.


    public
        Worksheet
    $phpSheet

$activePane

The active pane for the worksheet.


    private
        int
    $activePane

$allowThrow


    private
    static    bool
    $allowThrow
     = false

$always0


    private
    static    int
    $always0
     = 0

$always1


    private
    static    int
    $always1
     = 1

$byteOrder

The byte order of this architecture. 0 => little endian, 1 => big endian.


    private
    static    int|null
    $byteOrder

$colors

Color cache.


    private
        array<string|int, mixed>
    $colors

$columnInfo

Array containing format information for columns.


    private
        array<string|int, mixed>
    $columnInfo

$escher

Escher object corresponding to MSODRAWING.


    private
        Escher|null
    $escher
     = null

$firstColumnIndex

Index of first used column (at least 0).


    private
        int
    $firstColumnIndex

$firstRowIndex

Index of first used row (at least 0).


    private
        int
    $firstRowIndex

$lastColumnIndex

Index of last used column (no used columns means -1).


    private
        int
    $lastColumnIndex

$lastRowIndex

Index of last used row. (no used rows means -1).


    private
        int
    $lastRowIndex

$limit

The maximum length for a BIFF record (excluding record header and length field). See addContinue().


    private
        int
    $limit
     = 8224

$outlineBelow

Whether to have outline summary below.


    private
        bool
    $outlineBelow

Not currently used.

$outlineOn

Whether to use outline.


    private
        bool
    $outlineOn

$outlineRight

Whether to have outline summary at the right.


    private
        bool
    $outlineRight

Not currently used.

$outlineStyle

Auto outline styles.


    private
        bool
    $outlineStyle

$parser

Formula parser.


    private
        Parser
    $parser

$preCalculateFormulas


    private
        bool
    $preCalculateFormulas

$printHeaders


    private
        int
    $printHeaders

$stringTable

Reference to the array containing all the unique strings in the workbook.


    private
        array<string|int, mixed>
    $stringTable

$stringTotal

Reference to the total number of strings in the workbook.


    private
        int
    $stringTotal

$stringUnique

Reference to the number of unique strings in the workbook.


    private
        int
    $stringUnique

$writerWorkbook


    private
        Workbook|null
    $writerWorkbook

__construct()

Constructor.


    public
                    __construct(int &$str_total, int &$str_unique, array<string|int, mixed> &$str_table, array<string|int, mixed> &$colors, Parser $parser, bool $preCalculateFormulas, Worksheet $phpSheet[, Workbook|null $writerWorkbook = null ]) : mixed

Parameters

$str_total : int: Total number of strings
$str_unique : int: Total number of unique strings
$str_table : array<string|int, mixed>: String Table
$colors : array<string|int, mixed>: Colour Table
$parser : Parser: The formula parser created for the Workbook
$preCalculateFormulas : bool: Flag indicating whether formulas should be calculated or just written
$phpSheet : Worksheet: The worksheet to write
$writerWorkbook : Workbook|null = null

close()

Add data to the beginning of the workbook (note the reverse order) and to the end of the workbook.


    public
                    close() : void

getAllowThrow()


    public
            static        getAllowThrow() : bool

Return values

bool

getByteOrder()

Determine the byte order and store it as class data to avoid recalculating it for each call to new().


    public
            static        getByteOrder() : int

Return values

int

getData()

Retrieves data from memory in one chunk, or from disk sized chunks.


    public
                    getData() : string

Return values

string —

The data

getEscher()

Get Escher object.


    public
                    getEscher() : Escher|null

Return values

Escher|null

insertBitmap()

Insert a 24bit bitmap image in a worksheet.


    public
                    insertBitmap(int $row, int $col, GdImage|string $bitmap[, int $x = 0 ][, int $y = 0 ][, float $scale_x = 1 ][, float $scale_y = 1 ]) : void

Parameters

$row : int: The row we are going to insert the bitmap into
$col : int: The column we are going to insert the bitmap into
$bitmap : GdImage|string: The bitmap filename or GD-image resource
$x : int = 0: the horizontal position (offset) of the image inside the cell
$y : int = 0: the vertical position (offset) of the image inside the cell
$scale_x : float = 1: The horizontal scale
$scale_y : float = 1: The vertical scale

positionImage()

Calculate the vertices that define the position of the image as required by the OBJ record.


    public
                    positionImage(int $col_start, int $row_start, int $x1, int $y1, int $width, int $height) : void

+------------+------------+ | 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.

Parameters

$col_start : int: Col containing upper left corner of object
$row_start : int: Row containing top left corner of object
$x1 : int: Distance to left side of object
$y1 : int: Distance to top of object
$width : int: Width of image frame
$height : int: Height of image frame

printRowColHeaders()

Set the option to print the row and column headers on the printed page.


    public
                    printRowColHeaders([int $print = 1 ]) : void

Parameters

$print : int = 1: Whether to print the headers or not. Defaults to 1 (print).

processBitmap()

Convert a 24 bit bitmap into the modified internal format used by Windows.


    public
                    processBitmap(string $bitmap) : array<string|int, mixed>

This is described in BITMAPCOREHEADER and BITMAPCOREINFO structures in the MSDN library.

Parameters

$bitmap : string: The bitmap to process

Return values

array<string|int, mixed> —

Array with data and properties of the bitmap

processBitmapGd()

Convert a GD-image into the internal format.


    public
                    processBitmapGd(GdImage $image) : array{0: float, 1: float, 2: int, 3: string}

Parameters

$image : GdImage: The image to process

Return values

array{0: float, 1: float, 2: int, 3: string} —

Data and properties of the bitmap

setAllowThrow()


    public
            static        setAllowThrow(bool $allowThrow) : void

Parameters

$allowThrow : bool

setEscher()

Set Escher object.


    public
                    setEscher(Escher|null $escher) : void

Parameters

$escher : Escher|null

setOutline()

This method sets the properties for outlining and grouping. The defaults correspond to Excel's defaults.


    public
                    setOutline([bool $visible = true ][, bool $symbols_below = true ][, bool $symbols_right = true ][, bool $auto_style = false ]) : void

Parameters

$visible : bool = true
$symbols_below : bool = true
$symbols_right : bool = true
$auto_style : bool = false

writeBlank()

Write a blank cell to the specified row and column (zero indexed).


    public
                    writeBlank(int $row, int $col, int $xfIndex) : int

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

Parameters

$row : int: Zero indexed row
$col : int: Zero indexed column
$xfIndex : int: The XF format index

Return values

int

writeData()

General storage function like append, but returns string instead of modifying $this->_data.


    public
                    writeData(string $data) : string

Parameters

$data : string: binary data to write

Return values

string

writeEof()

Writes Excel EOF record to indicate the end of a BIFF stream.


    public
                    writeEof() : string

Return values

string

writeUrlWeb()

Used to write http, ftp and mailto hyperlinks.


    public
                    writeUrlWeb(int $row1, int $col1, int $row2, int $col2, string $url) : void

The link type ($options) is 0x03 is the same as absolute dir ref without sheet. However it is differentiated by the $unknown2 data stream.

Parameters

$row1 : int: Start row
$col1 : int: Start column
$row2 : int: End row
$col2 : int: End column
$url : string: URL string

append()

General storage function.


    protected
                    append(string $data) : void

Parameters

$data : string: binary data to append

storeBof()

Writes Excel BOF record to indicate the beginning of a stream or sub-stream in the BIFF file.


    protected
                    storeBof(int $type) : void

Parameters

$type : int: type of BIFF file to write: 0x0005 Workbook, 0x0010 Worksheet

storeEof()

Writes Excel EOF record to indicate the end of a BIFF stream.


    protected
                    storeEof() : void

addContinue()

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.


    private
                    addContinue(string $data) : string

This function takes a long BIFF record and inserts CONTINUE records as necessary.

Parameters

$data : string: The original binary data to be written

Return values

string —

A very convenient string of continue blocks

limitRange()


    private
            static        limitRange(string $exploded) : string

Parameters

$exploded : string

Return values

string

protectionBitsDefaultFalse()


    private
            static        protectionBitsDefaultFalse(bool|null $value, int $shift) : int

Parameters

$value : bool|null
$shift : int

Return values

int

protectionBitsDefaultTrue()


    private
            static        protectionBitsDefaultTrue(bool|null $value, int $shift) : int

Parameters

$value : bool|null
$shift : int

Return values

int

workbookColorIndex()


    private
                    workbookColorIndex(string|null $rgb, int $default) : int

Parameters

$rgb : string|null
$default : int

Return values

int

writeAutoFilterInfo()

Write the AUTOFILTERINFO BIFF record. This is used to configure the number of autofilter select used in the sheet.


    private
                    writeAutoFilterInfo() : void

writeBIFF8CellRangeAddressFixed()

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.


    private
                    writeBIFF8CellRangeAddressFixed(string $range) : string

Parameters

$range : string: E.g. 'A1' or 'A1:B6'

Return values

string —

Binary data

writeBoolErr()

Write a boolean or an error type to the specified row and column (zero indexed).


    private
                    writeBoolErr(int $row, int $col, int $value, int $isError, int $xfIndex) : int

Parameters

$row : int: Row index (0-based)
$col : int: Column index (0-based)
$value : int
$isError : int: Error or Boolean?
$xfIndex : int

Return values

int

writeBreaks()

Write the HORIZONTALPAGEBREAKS and VERTICALPAGEBREAKS BIFF records.


    private
                    writeBreaks() : void

writeCFHeader()

Write CFHeader record.


    private
                    writeCFHeader(string $cellCoordinate, array<string|int, Conditional> $conditionalStyles) : bool

Parameters

$cellCoordinate : string
$conditionalStyles : array<string|int, Conditional>

Return values

bool

writeCFRule()

Write CFRule Record.


    private
                    writeCFRule(ConditionalHelper $conditionalFormulaHelper, Conditional $conditional, string $cellRange) : void

Parameters

$conditionalFormulaHelper : ConditionalHelper
$conditional : Conditional
$cellRange : string

writeColinfo()

Write BIFF record COLINFO to define column widths.


    private
                    writeColinfo(array<string|int, mixed> $col_array) : void

Note: The SDK says the record length is 0x0B but Excel writes a 0x0C length record.

Parameters

$col_array : array<string|int, mixed>: 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

writeConditionalFormatting()


    private
                    writeConditionalFormatting() : void

writeDataValidity()

Store the DATAVALIDATIONS and DATAVALIDATION records.


    private
                    writeDataValidity() : void

writeDefaultRowHeight()

Write BIFF record DEFAULTROWHEIGHT.


    private
                    writeDefaultRowHeight() : void

writeDefcol()

Write BIFF record DEFCOLWIDTH if COLINFO records are in use.


    private
                    writeDefcol() : void

writeDimensions()

Writes Excel DIMENSIONS to define the area in which there is data.


    private
                    writeDimensions() : void

writeFooter()

Store the footer caption BIFF record.


    private
                    writeFooter() : void

writeFormula()

Write a formula to the specified row and column (zero indexed).


    private
                    writeFormula(int $row, int $col, string $formula, int $xfIndex, mixed $calculatedValue) : int

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

Parameters

$row : int: Zero indexed row
$col : int: Zero indexed column
$formula : string: The formula text string
$xfIndex : int: The XF format index
$calculatedValue : mixed: Calculated value

Return values

int

writeGridset()

Write the GRIDSET BIFF record. Must be used in conjunction with the PRINTGRIDLINES record.


    private
                    writeGridset() : void

writeGuts()

Write the GUTS BIFF record. This is used to configure the gutter margins where Excel outline symbols are displayed. The visibility of the gutters is controlled by a flag in WSBOOL.


    private
                    writeGuts() : void

writeHcenter()

Store the horizontal centering HCENTER BIFF record.


    private
                    writeHcenter() : void

writeHeader()

Store the header caption BIFF record.


    private
                    writeHeader() : void

writeLabelSst()

Write a string to the specified row and column (zero indexed).


    private
                    writeLabelSst(int $row, int $col, string $str, int $xfIndex) : void

This is the BIFF8 version (no 255 chars limit). $format is optional.

Parameters

$row : int: Zero indexed row
$col : int: Zero indexed column
$str : string: The string to write
$xfIndex : int: The XF format index for the cell

writeMarginBottom()

Store the BOTTOMMARGIN BIFF record.


    private
                    writeMarginBottom() : void

writeMarginLeft()

Store the LEFTMARGIN BIFF record.


    private
                    writeMarginLeft() : void

writeMarginRight()

Store the RIGHTMARGIN BIFF record.


    private
                    writeMarginRight() : void

writeMarginTop()

Store the TOPMARGIN BIFF record.


    private
                    writeMarginTop() : void

writeMergedCells()

Store the MERGEDCELLS records for all ranges of merged cells.


    private
                    writeMergedCells() : void

writeMsoDrawing()

Write MSODRAWING record.


    private
                    writeMsoDrawing() : void

writeNumber()

Write a double to the specified row and column (zero indexed).


    private
                    writeNumber(int $row, int $col, float $num, int $xfIndex) : int

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

Parameters

$row : int: Zero indexed row
$col : int: Zero indexed column
$num : float: The number to write
$xfIndex : int: The optional XF format

Return values

int

writeObjectProtect()

Write OBJECTPROTECT.


    private
                    writeObjectProtect() : void

writeObjPicture()

Store the OBJ record that precedes an IMDATA record. This could be generalise to support other Excel objects.


    private
                    writeObjPicture(int $colL, int $dxL, int $rwT, float|int $dyT, int $colR, int $dxR, int $rwB, int $dyB) : void

Parameters

$colL : int: Column containing upper left corner of object
$dxL : int: Distance from left side of cell
$rwT : int: Row containing top left corner of object
$dyT : float|int: Distance from top of cell
$colR : int: Column containing lower right corner of object
$dxR : int: Distance from right of cell
$rwB : int: Row containing bottom right corner of object
$dyB : int: Distance from bottom of cell

writePageLayoutView()

Write PLV Record.


    private
                    writePageLayoutView() : void

writePanes()

Writes the Excel BIFF PANE record.


    private
                    writePanes() : void

The panes can either be frozen or thawed (unfrozen). Frozen panes are specified in terms of an integer number of rows and columns. Thawed panes are specified in terms of Excel's units for rows and columns.

writePassword()

Write the worksheet PASSWORD record.


    private
                    writePassword() : void

writePrintGridlines()

Write the PRINTGRIDLINES BIFF record. Must be used in conjunction with the GRIDSET record.


    private
                    writePrintGridlines() : void

writePrintHeaders()

Write the PRINTHEADERS BIFF record.


    private
                    writePrintHeaders() : void

writeProtect()

Set the Biff PROTECT record to indicate that the worksheet is protected.


    private
                    writeProtect() : void

writeRangeProtection()

Write BIFF record RANGEPROTECTION.


    private
                    writeRangeProtection() : void

Openoffice.org's Documentation of the Microsoft Excel File Format uses term RANGEPROTECTION for these records Microsoft Office Excel 97-2007 Binary File Format Specification uses term FEAT for these records

writeRichTextString()

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.


    private
                    writeRichTextString(int $row, int $col, string $str, int $xfIndex, array<string|int, mixed> $arrcRun) : void

Parameters

$row : int: Row index (0-based)
$col : int: Column index (0-based)
$str : string: The string
$xfIndex : int: The XF format index for the cell
$arrcRun : array<string|int, mixed>: Index to Font record and characters beginning

writeRow()

This method is used to set the height and format for a row.


    private
                    writeRow(int $row, int $height, int $xfIndex[, bool $hidden = false ][, int $level = 0 ]) : void

Parameters

$row : int: The row to set
$height : int: Height we are giving to the row. Use null to set XF without setting height
$xfIndex : int: The optional cell style Xf index to apply to the columns
$hidden : bool = false: The optional hidden attribute
$level : int = 0: The optional outline level for row, in range [0,7]

writeScenProtect()

Write SCENPROTECT.


    private
                    writeScenProtect() : void

writeSelection()

Write BIFF record SELECTION.


    private
                    writeSelection() : void

writeSetup()

Store the page setup SETUP BIFF record.


    private
                    writeSetup() : void

writeSheetLayout()

Write SHEETLAYOUT record.


    private
                    writeSheetLayout() : void

writeSheetProtection()

Write SHEETPROTECTION.


    private
                    writeSheetProtection() : void

writeString()

Write a LABELSST record or a LABEL record. Which one depends on BIFF version.


    private
                    writeString(int $row, int $col, string $str, int $xfIndex) : void

Parameters

$row : int: Row index (0-based)
$col : int: Column index (0-based)
$str : string: The string
$xfIndex : int: Index to XF record

writeStringRecord()

Write a STRING record. This.


    private
                    writeStringRecord(string $stringValue) : void

Parameters

$stringValue : string

writeUrl()

Write a hyperlink.


    private
                    writeUrl(int $row, int $col, string $url) : void

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.

Parameters

$row : int: Row
$col : int: Column
$url : string: URL string

writeUrlExternal()

Write links to external directory names such as 'c:\foo.xls', c:\foo.xls#Sheet1!A1', '../../foo.xls'. and '../../foo.xls#Sheet1!A1'.


    private
                    writeUrlExternal(int $row1, int $col1, int $row2, int $col2, string $url) : void

Note: Excel writes some relative links with the $dir_long string. We ignore these cases for the sake of simpler code.

Parameters

$row1 : int: Start row
$col1 : int: Start column
$row2 : int: End row
$col2 : int: End column
$url : string: URL string

writeUrlInternal()

Used to write internal reference hyperlinks such as "Sheet1!A1".


    private
                    writeUrlInternal(int $row1, int $col1, int $row2, int $col2, string $url) : void

Parameters

$row1 : int: Start row
$col1 : int: Start column
$row2 : int: End row
$col2 : int: End column
$url : string: URL string

writeUrlRange()

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').


    private
                    writeUrlRange(int $row1, int $col1, int $row2, int $col2, string $url) : void

Parameters

$row1 : int: Start row
$col1 : int: Start column
$row2 : int: End row
$col2 : int: End column
$url : string: URL string

writeVcenter()

Store the vertical centering VCENTER BIFF record.


    private
                    writeVcenter() : void

writeWindow2()

Write BIFF record Window2.


    private
                    writeWindow2() : void

writeWsbool()

Write the WSBOOL BIFF record, mainly for fit-to-page. Used in conjunction with the SETUP record.


    private
                    writeWsbool() : void

writeZoom()

Store the window zoom factor. This should be a reduced fraction but for simplicity we will store all fractions with a numerator of 100.


    private
                    writeZoom() : void

Worksheet extends BIFFwriter in package Application

Table of Contents

Constants

Properties

Methods

Constants

MAX_XLS_COLUMN

MAX_XLS_COLUMN_STRING

MAX_XLS_ROW

WRITE_FORMULA_ERRORS

WRITE_FORMULA_EXCEPTION

WRITE_FORMULA_NORMAL

WRITE_FORMULA_RANGE

Properties

$_data

$_datasize

$fontHashIndex

$phpSheet

$activePane

$allowThrow

$always0

$always1

$byteOrder

$colors

$columnInfo

$escher

$firstColumnIndex

$firstRowIndex

$lastColumnIndex

$lastRowIndex

$limit

Tags

$outlineBelow

$outlineOn

$outlineRight

$outlineStyle

$parser

$preCalculateFormulas

$printHeaders

$stringTable

$stringTotal

$stringUnique

$writerWorkbook

Methods

__construct()

Parameters

close()

Tags

getAllowThrow()

Return values

getByteOrder()

Return values

getData()

Return values

getEscher()

Return values

insertBitmap()

Parameters

positionImage()

Parameters

printRowColHeaders()

Parameters

processBitmap()

Parameters

Return values

processBitmapGd()

Parameters

Return values

setAllowThrow()

Parameters

setEscher()

Parameters

setOutline()

Parameters

writeBlank()

Parameters

Return values

writeData()

Parameters

Return values

Worksheet extends BIFFwriter
in package

Application