class Worksheet extends BIFFwriter (View source)

Properties

string $_data The string containing the data of the BIFF stream. from BIFFwriter
int $_datasize The size of the data in bytes. Should be the same as strlen($this->_data). from BIFFwriter
Worksheet $phpSheet Sheet object.
array $fontHashIndex Array of font hashes associated to FONT records index.

Methods

__construct(int $str_total, int $str_unique, array $str_table, array $colors, Parser $parser, bool $preCalculateFormulas, Worksheet $phpSheet)

Constructor.

static int
getByteOrder()

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

append(string $data)

General storage function.

string
writeData(string $data)

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

storeBof(int $type)

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

storeEof()

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

writeEof()

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

close()

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

string
getData()

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

printRowColHeaders(int $print = 1)

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

setOutline(bool $visible = true, bool $symbols_below = true, bool $symbols_right = true, bool $auto_style = false)

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

int
writeBlank(int $row, int $col, mixed $xfIndex)

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

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

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
writeUrlWeb(int $row1, int $col1, int $row2, int $col2, string $url)

Used to write http, ftp and mailto hyperlinks.

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

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

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

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

insertBitmap(int $row, int $col, mixed $bitmap, int $x = 0, int $y = 0, float $scale_x = 1, float $scale_y = 1)

Insert a 24bit bitmap image in a worksheet.

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

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

array
processBitmapGd(resource $image)

Convert a GD-image into the internal format.

array
processBitmap(string $bitmap)

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

getEscher()

Get Escher object.

setEscher(Escher $pValue = null)

Set Escher object.

Details

__construct(int $str_total, int $str_unique, array $str_table, array $colors, Parser $parser, bool $preCalculateFormulas, Worksheet $phpSheet)

Constructor.

Parameters

int $str_total Total number of strings
int $str_unique Total number of unique strings
array $str_table &$str_table String Table
array $colors &$colors Colour Table
Parser $parser The formula parser created for the Workbook
bool $preCalculateFormulas Flag indicating whether formulas should be calculated or just written
Worksheet $phpSheet The worksheet to write

static int getByteOrder()

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

Return Value

int

protected append(string $data)

General storage function.

Parameters

string $data binary data to append

string writeData(string $data)

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

Parameters

string $data binary data to write

Return Value

string

protected storeBof(int $type)

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

Parameters

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

protected storeEof()

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

writeEof()

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

close()

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

See also

\PhpOffice\PhpSpreadsheet\Writer\Xls\Workbook::storeWorkbook()

string getData()

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

Return Value

string The data

printRowColHeaders(int $print = 1)

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

Parameters

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

setOutline(bool $visible = true, bool $symbols_below = true, bool $symbols_right = true, bool $auto_style = false)

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

Parameters

bool $visible
bool $symbols_below
bool $symbols_right
bool $auto_style

int writeBlank(int $row, int $col, mixed $xfIndex)

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

Parameters

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

Return Value

int

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

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

Parameters

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

Return Value

int

See also

writeUrl()

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

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.

Parameters

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

Return Value

int

See also

writeUrl()

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

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

Parameters

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

Return Value

int

See also

writeUrl()

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

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.

Parameters

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

Return Value

int

See also

writeUrl()

insertBitmap(int $row, int $col, mixed $bitmap, int $x = 0, int $y = 0, float $scale_x = 1, float $scale_y = 1)

Insert a 24bit bitmap image in a worksheet.

Parameters

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)

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.

Parameters

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

array processBitmapGd(resource $image)

Convert a GD-image into the internal format.

Parameters

resource $image The image to process

Return Value

array Array with data and properties of the bitmap

array processBitmap(string $bitmap)

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.

Parameters

string $bitmap The bitmap to process

Return Value

array Array with data and properties of the bitmap

Escher getEscher()

Get Escher object.

Return Value

Escher

setEscher(Escher $pValue = null)

Set Escher object.

Parameters

Escher $pValue