\PHPExcel_Writer_Excel5_Worksheet

PHPExcel_Writer_Excel5_Worksheet

Summary

Methods
Properties
Constants
__construct()
getByteOrder()
_append()
writeData()
_storeBof()
_storeEof()
writeEof()
_addContinue()
close()
getData()
printRowColHeaders()
setOutline()
_writeBlank()
_writeUrlRange()
_writeUrlWeb()
_writeUrlInternal()
_writeUrlExternal()
insertBitmap()
_positionImage()
_processBitmapGd()
_processBitmap()
getEscher()
setEscher()
$_data
$_datasize
$_limit
$_xls_strmax
$_colinfo
$_selection
$_active_pane
$_outline_on
$_outline_style
$_outline_below
$_outline_right
$_str_total
$_str_unique
$_str_table
$_phpSheet
$_fntHashIndex
No constants found
No protected methods found
No protected properties found
N/A
_writeBIFF8CellRangeAddressFixed()
_writeNumber()
_writeString()
_writeRichTextString()
_writeLabel()
_writeLabelSst()
_writeNote()
_writeBoolErr()
_writeFormula()
_writeStringRecord()
_writeUrl()
_writeRow()
_writeDimensions()
_writeWindow2()
_writeDefaultRowHeight()
_writeDefcol()
_writeColinfo()
_writeSelection()
_writeMergedCells()
_writeSheetLayout()
_writeSheetProtection()
_writeRangeProtection()
_writeExterncount()
_writeExternsheet()
_writePanes()
_writeSetup()
_writeHeader()
_writeFooter()
_writeHcenter()
_writeVcenter()
_writeMarginLeft()
_writeMarginRight()
_writeMarginTop()
_writeMarginBottom()
_writePrintHeaders()
_writePrintGridlines()
_writeGridset()
_writeAutoFilterInfo()
_writeGuts()
_writeWsbool()
_writeBreaks()
_writeProtect()
_writeScenProtect()
_writeObjectProtect()
_writePassword()
_writeObjPicture()
_writeZoom()
_writeMsoDrawing()
_writeDataValidity()
_mapErrorCode()
_writePageLayoutView()
_writeCFRule()
_writeCFHeader()
$_byte_order
$_parser
$_colors
$_firstRowIndex
$_lastRowIndex
$_firstColumnIndex
$_lastColumnIndex
$_countCellStyleXfs
$_escher
N/A

Properties

$_data

$_data : string

The string containing the data of the BIFF stream

Type

string

$_datasize

$_datasize : integer

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

Type

integer

$_limit

$_limit : integer

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

Type

integer

$_xls_strmax

$_xls_strmax : integer

Maximum number of characters for a string (LABEL record in BIFF5)

Type

integer

$_colinfo

$_colinfo : array

Array containing format information for columns

Type

array

$_selection

$_selection : array

Array containing the selected area for the worksheet

Type

array

$_active_pane

$_active_pane : integer

The active pane for the worksheet

Type

integer

$_outline_on

$_outline_on : integer

Whether to use outline.

Type

integer

$_outline_style

$_outline_style : boolean

Auto outline styles.

Type

boolean

$_outline_below

$_outline_below : boolean

Whether to have outline summary below.

Type

boolean

$_outline_right

$_outline_right : boolean

Whether to have outline summary at the right.

Type

boolean

$_str_total

$_str_total : integer

Reference to the total number of strings in the workbook

Type

integer

$_str_unique

$_str_unique : integer

Reference to the number of unique strings in the workbook

Type

integer

$_str_table

$_str_table : array

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

Type

array

$_fntHashIndex

$_fntHashIndex : array

Array of font hashes associated to FONT records index

Type

array

$_byte_order

$_byte_order : integer

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

Type

integer

$_colors

$_colors : 

Color cache

Type

$_firstRowIndex

$_firstRowIndex : integer

Index of first used row (at least 0)

Type

integer

$_lastRowIndex

$_lastRowIndex : integer

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

Type

integer

$_firstColumnIndex

$_firstColumnIndex : integer

Index of first used column (at least 0)

Type

integer

$_lastColumnIndex

$_lastColumnIndex : integer

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

Type

integer

$_countCellStyleXfs

$_countCellStyleXfs : integer

Count cell style Xfs

Type

integer

$_escher

$_escher : \PHPExcel_Shared_Escher

Escher object corresponding to MSODRAWING

Type

\PHPExcel_Shared_Escher

Methods

__construct()

__construct(  $str_total,   $str_unique,   $str_table,   $colors, mixed  $parser, boolean  $preCalculateFormulas, \PHPExcel_Worksheet  $phpSheet) 

Constructor

Parameters

$str_total
$str_unique
$str_table
$colors
mixed $parser

The formula parser created for the Workbook

boolean $preCalculateFormulas

Flag indicating whether formulas should be calculated or just written

\PHPExcel_Worksheet $phpSheet

getByteOrder()

getByteOrder() : integer

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

Returns

integer

_append()

_append(string  $data) 

General storage function

Parameters

string $data

binary data to append

writeData()

writeData(string  $data) : string

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

Parameters

string $data

binary data to write

Returns

string

_storeBof()

_storeBof(integer  $type) 

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

Parameters

integer $type

Type of BIFF file to write: 0x0005 Workbook, 0x0010 Worksheet.

_storeEof()

_storeEof() 

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

writeEof()

writeEof() 

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

_addContinue()

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

Parameters

string $data

The original binary data to be written

Returns

string —

A very convenient string of continue blocks

close()

close() 

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

getData()

getData() : string

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

Returns

string —

The data

printRowColHeaders()

printRowColHeaders(integer  $print = 1) 

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

Parameters

integer $print

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

setOutline()

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

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

Parameters

boolean $visible
boolean $symbols_below
boolean $symbols_right
boolean $auto_style

_writeBlank()

_writeBlank(integer  $row, integer  $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

integer $row

Zero indexed row

integer $col

Zero indexed column

mixed $xfIndex

The XF format index

_writeUrlRange()

_writeUrlRange(integer  $row1, integer  $col1, integer  $row2, integer  $col2, string  $url) : integer

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

integer $row1

Start row

integer $col1

Start column

integer $row2

End row

integer $col2

End column

string $url

URL string

Returns

integer

_writeUrlWeb()

_writeUrlWeb(integer  $row1, integer  $col1, integer  $row2, integer  $col2, string  $url) : integer

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

integer $row1

Start row

integer $col1

Start column

integer $row2

End row

integer $col2

End column

string $url

URL string

Returns

integer

_writeUrlInternal()

_writeUrlInternal(integer  $row1, integer  $col1, integer  $row2, integer  $col2, string  $url) : integer

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

Parameters

integer $row1

Start row

integer $col1

Start column

integer $row2

End row

integer $col2

End column

string $url

URL string

Returns

integer

_writeUrlExternal()

_writeUrlExternal(integer  $row1, integer  $col1, integer  $row2, integer  $col2, string  $url) : integer

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

integer $row1

Start row

integer $col1

Start column

integer $row2

End row

integer $col2

End column

string $url

URL string

Returns

integer

insertBitmap()

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

Insert a 24bit bitmap image in a worksheet.

Parameters

integer $row

The row we are going to insert the bitmap into

integer $col

The column we are going to insert the bitmap into

mixed $bitmap

The bitmap filename or GD-image resource

integer $x

The horizontal position (offset) of the image inside the cell.

integer $y

The vertical position (offset) of the image inside the cell.

float $scale_x

The horizontal scale

float $scale_y

The vertical scale

_positionImage()

_positionImage(integer  $col_start, integer  $row_start, integer  $x1, integer  $y1, integer  $width, integer  $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

integer $col_start

Col containing upper left corner of object

integer $row_start

Row containing top left corner of object

integer $x1

Distance to left side of object

integer $y1

Distance to top of object

integer $width

Width of image frame

integer $height

Height of image frame

_processBitmapGd()

_processBitmapGd(resource  $image) : array

Convert a GD-image into the internal format.

Parameters

resource $image

The image to process

Returns

array —

Array with data and properties of the bitmap

_processBitmap()

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

Parameters

string $bitmap

The bitmap to process

Returns

array —

Array with data and properties of the bitmap

setEscher()

setEscher(\PHPExcel_Shared_Escher  $pValue = null) 

Set Escher object

Parameters

\PHPExcel_Shared_Escher $pValue

_writeBIFF8CellRangeAddressFixed()

_writeBIFF8CellRangeAddressFixed(string  $range = 'A1') : 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

Parameters

string $range

E.g. 'A1' or 'A1:B6'

Returns

string —

Binary data

_writeNumber()

_writeNumber(integer  $row, integer  $col, float  $num, mixed  $xfIndex) : integer

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

Parameters

integer $row

Zero indexed row

integer $col

Zero indexed column

float $num

The number to write

mixed $xfIndex

The optional XF format

Returns

integer

_writeString()

_writeString(integer  $row, integer  $col, string  $str, integer  $xfIndex) 

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

Parameters

integer $row

Row index (0-based)

integer $col

Column index (0-based)

string $str

The string

integer $xfIndex

Index to XF record

_writeRichTextString()

_writeRichTextString(integer  $row, integer  $col, string  $str, mixed  $xfIndex, array  $arrcRun) 

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.

Parameters

integer $row

Row index (0-based)

integer $col

Column index (0-based)

string $str

The string

mixed $xfIndex

The XF format index for the cell

array $arrcRun

Index to Font record and characters beginning

_writeLabel()

_writeLabel(integer  $row, integer  $col, string  $str, mixed  $xfIndex) : integer

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

NOTE: there is an Excel 5 defined limit of 255 characters. $format is optional. Returns 0 : normal termination -2 : row or column out of range -3 : long string truncated to 255 chars

Parameters

integer $row

Zero indexed row

integer $col

Zero indexed column

string $str

The string to write

mixed $xfIndex

The XF format index for the cell

Returns

integer

_writeLabelSst()

_writeLabelSst(integer  $row, integer  $col, string  $str, mixed  $xfIndex) : integer

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

This is the BIFF8 version (no 255 chars limit). $format is optional. Returns 0 : normal termination -2 : row or column out of range -3 : long string truncated to 255 chars

Parameters

integer $row

Zero indexed row

integer $col

Zero indexed column

string $str

The string to write

mixed $xfIndex

The XF format index for the cell

Returns

integer

_writeNote()

_writeNote(integer  $row, integer  $col, string  $note) 

Writes a note associated with the cell given by the row and column.

NOTE records don't have a length limit.

Parameters

integer $row

Zero indexed row

integer $col

Zero indexed column

string $note

The note to write

_writeBoolErr()

_writeBoolErr(integer  $row, integer  $col, integer  $value, boolean  $isError, integer  $xfIndex) 

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

Parameters

integer $row

Row index (0-based)

integer $col

Column index (0-based)

integer $value
boolean $isError

Error or Boolean?

integer $xfIndex

_writeFormula()

_writeFormula(integer  $row, integer  $col, string  $formula, mixed  $xfIndex, mixed  $calculatedValue) : integer

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 : normal termination -1 : formula errors (bad formula) -2 : row or column out of range

Parameters

integer $row

Zero indexed row

integer $col

Zero indexed column

string $formula

The formula text string

mixed $xfIndex

The XF format index

mixed $calculatedValue

Calculated value

Returns

integer

_writeStringRecord()

_writeStringRecord(string  $stringValue) 

Write a STRING record. This

Parameters

string $stringValue

_writeUrl()

_writeUrl(integer  $row, integer  $col, string  $url) : integer

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

Parameters

integer $row

Row

integer $col

Column

string $url

URL string

Returns

integer

_writeRow()

_writeRow(integer  $row, integer  $height, integer  $xfIndex, boolean  $hidden = false, integer  $level) 

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

Parameters

integer $row

The row to set

integer $height

Height we are giving to the row. Use null to set XF without setting height

integer $xfIndex

The optional cell style Xf index to apply to the columns

boolean $hidden

The optional hidden attribute

integer $level

The optional outline level for row, in range [0,7]

_writeDimensions()

_writeDimensions() 

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

_writeWindow2()

_writeWindow2() 

Write BIFF record Window2.

_writeDefaultRowHeight()

_writeDefaultRowHeight() 

Write BIFF record DEFAULTROWHEIGHT.

_writeDefcol()

_writeDefcol() 

Write BIFF record DEFCOLWIDTH if COLINFO records are in use.

_writeColinfo()

_writeColinfo(array  $col_array) 

Write BIFF record COLINFO to define column widths

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

Parameters

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

_writeSelection()

_writeSelection() 

Write BIFF record SELECTION.

_writeMergedCells()

_writeMergedCells() 

Store the MERGEDCELLS records for all ranges of merged cells

_writeSheetLayout()

_writeSheetLayout() 

Write SHEETLAYOUT record

_writeSheetProtection()

_writeSheetProtection() 

Write SHEETPROTECTION

_writeRangeProtection()

_writeRangeProtection() 

Write BIFF record RANGEPROTECTION

Openoffice.org's Documentaion 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

_writeExterncount()

_writeExterncount(integer  $count) 

Write BIFF record EXTERNCOUNT to indicate the number of external sheet references in a worksheet.

Excel only stores references to external sheets that are used in formulas. For simplicity we store references to all the sheets in the workbook regardless of whether they are used or not. This reduces the overall complexity and eliminates the need for a two way dialogue between the formula parser the worksheet objects.

Parameters

integer $count

The number of external sheet references in this worksheet

_writeExternsheet()

_writeExternsheet(string  $sheetname) 

Writes the Excel BIFF EXTERNSHEET record. These references are used by formulas. A formula references a sheet name via an index. Since we store a reference to all of the external worksheets the EXTERNSHEET index is the same as the worksheet index.

Parameters

string $sheetname

The name of a external worksheet

_writePanes()

_writePanes() 

Writes the Excel BIFF PANE record.

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.

_writeSetup()

_writeSetup() 

Store the page setup SETUP BIFF record.

_writeHeader()

_writeHeader() 

Store the header caption BIFF record.

_writeFooter()

_writeFooter() 

Store the footer caption BIFF record.

_writeHcenter()

_writeHcenter() 

Store the horizontal centering HCENTER BIFF record.

_writeVcenter()

_writeVcenter() 

Store the vertical centering VCENTER BIFF record.

_writeMarginLeft()

_writeMarginLeft() 

Store the LEFTMARGIN BIFF record.

_writeMarginRight()

_writeMarginRight() 

Store the RIGHTMARGIN BIFF record.

_writeMarginTop()

_writeMarginTop() 

Store the TOPMARGIN BIFF record.

_writeMarginBottom()

_writeMarginBottom() 

Store the BOTTOMMARGIN BIFF record.

_writePrintHeaders()

_writePrintHeaders() 

Write the PRINTHEADERS BIFF record.

_writePrintGridlines()

_writePrintGridlines() 

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

_writeGridset()

_writeGridset() 

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

_writeAutoFilterInfo()

_writeAutoFilterInfo() 

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

_writeGuts()

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

_writeWsbool()

_writeWsbool() 

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

_writeBreaks()

_writeBreaks() 

Write the HORIZONTALPAGEBREAKS and VERTICALPAGEBREAKS BIFF records.

_writeProtect()

_writeProtect() 

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

_writeScenProtect()

_writeScenProtect() 

Write SCENPROTECT

_writeObjectProtect()

_writeObjectProtect() 

Write OBJECTPROTECT

_writePassword()

_writePassword() 

Write the worksheet PASSWORD record.

_writeObjPicture()

_writeObjPicture(integer  $colL, integer  $dxL, integer  $rwT, integer  $dyT, integer  $colR, integer  $dxR, integer  $rwB, integer  $dyB) 

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

Parameters

integer $colL

Column containing upper left corner of object

integer $dxL

Distance from left side of cell

integer $rwT

Row containing top left corner of object

integer $dyT

Distance from top of cell

integer $colR

Column containing lower right corner of object

integer $dxR

Distance from right of cell

integer $rwB

Row containing bottom right corner of object

integer $dyB

Distance from bottom of cell

_writeZoom()

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

_writeMsoDrawing()

_writeMsoDrawing() 

Write MSODRAWING record

_writeDataValidity()

_writeDataValidity() 

Store the DATAVALIDATIONS and DATAVALIDATION records.

_mapErrorCode()

_mapErrorCode(string  $errorCode) : integer

Map Error code

Parameters

string $errorCode

Returns

integer

_writePageLayoutView()

_writePageLayoutView() 

Write PLV Record

_writeCFRule()

_writeCFRule(\PHPExcel_Style_Conditional  $conditional) 

Write CFRule Record

Parameters

\PHPExcel_Style_Conditional $conditional

_writeCFHeader()

_writeCFHeader() 

Write CFHeader record