PHPXRef 0.7 : phpwcms V1.4.3 _r380 (23.11.09) : Detail view of Worksheet.php

Class: Spreadsheet_Excel_Writer_Worksheet - X-Ref

Class for generating Excel Spreadsheets

Spreadsheet_Excel_Writer_Worksheet($BIFF_version, $name,$index, &$activesheet,&$firstsheet, &$str_total,&$str_unique, &$str_table,&$url_format, &$parser) X-Ref

Constructor

param: string  $name         The name of the new worksheet
param: integer $index        The index of the new worksheet
param: mixed   &$activesheet The current activesheet of the workbook we belong to
param: mixed   &$firstsheet  The first worksheet in the workbook we belong to
param: mixed   &$url_format  The default format for hyperlinks
param: mixed   &$parser      The formula parser created for the Workbook

_initialize() X-Ref

Open a tmp file to store the majority of the Worksheet data. If this fails,
for example due to write permissions, store the data in memory. This can be
slow for large files.

close($sheetnames) X-Ref

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

param: array $sheetnames The array of sheetnames from the Workbook this

getName() X-Ref

Retrieve the worksheet name.
This is usefull when creating worksheets without a name.

return: string The worksheet's name

getData() X-Ref

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

return: string The data

setMerge($first_row, $first_col, $last_row, $last_col) X-Ref

Sets a merged cell range

param: integer $first_row First row of the area to merge
param: integer $first_col First column of the area to merge
param: integer $last_row Last row of the area to merge
param: integer $last_col Last column of the area to merge

select() X-Ref

Set this worksheet as a selected worksheet,
i.e. the worksheet has its tab highlighted.

activate() X-Ref

Set this worksheet as the active worksheet,
i.e. the worksheet that is displayed when the workbook is opened.
Also set it as selected.

setFirstSheet() X-Ref

Set this worksheet as the first visible sheet.
This is necessary when there are a large number of worksheets and the
activated worksheet is not visible on the screen.

protect($password) X-Ref

Set the worksheet protection flag
to prevent accidental modification and to
hide formulas if the locked and hidden format properties have been set.

param: string $password The password to use for protecting the sheet.

setColumn($firstcol, $lastcol, $width, $format = null, $hidden = 0, $level = 0) X-Ref

Set the width of a single column or a range of columns.

param: integer $firstcol first column on the range
param: integer $lastcol  last column on the range
param: integer $width    width to set
param: mixed   $format   The optional XF format to apply to the columns
param: integer $hidden   The optional hidden atribute
param: integer $level    The optional outline level

setSelection($first_row,$first_column,$last_row,$last_column) X-Ref

Set which cell or cells are selected in a worksheet

param: integer $first_row    first row in the selected quadrant
param: integer $first_column first column in the selected quadrant
param: integer $last_row     last row in the selected quadrant
param: integer $last_column  last column in the selected quadrant

freezePanes($panes) X-Ref

Set panes and mark them as frozen.

param: array $panes This is the only parameter received and is composed of the following:

thawPanes($panes) X-Ref

Set panes and mark them as unfrozen.

param: array $panes This is the only parameter received and is composed of the following:

setPortrait() X-Ref

Set the page orientation as portrait.

setLandscape() X-Ref

Set the page orientation as landscape.

setPaper($size = 0) X-Ref

Set the paper type. Ex. 1 = US Letter, 9 = A4

param: integer $size The type of paper size to use

setHeader($string,$margin = 0.50) X-Ref

Set the page header caption and optional margin.

param: string $string The header text
param: float $margin optional head margin in inches.

setFooter($string,$margin = 0.50) X-Ref

Set the page footer caption and optional margin.

param: string $string The footer text
param: float $margin optional foot margin in inches.

centerHorizontally($center = 1) X-Ref

Center the page horinzontally.

param: integer $center the optional value for centering. Defaults to 1 (center).

centerVertically($center = 1) X-Ref

Center the page vertically.

param: integer $center the optional value for centering. Defaults to 1 (center).

setMargins($margin) X-Ref

Set all the page margins to the same value in inches.

param: float $margin The margin to set in inches

setMargins_LR($margin) X-Ref

Set the left and right margins to the same value in inches.

param: float $margin The margin to set in inches

setMargins_TB($margin) X-Ref

Set the top and bottom margins to the same value in inches.

param: float $margin The margin to set in inches

setMarginLeft($margin = 0.75) X-Ref

Set the left margin in inches.

param: float $margin The margin to set in inches

setMarginRight($margin = 0.75) X-Ref

Set the right margin in inches.

param: float $margin The margin to set in inches

setMarginTop($margin = 1.00) X-Ref

Set the top margin in inches.

param: float $margin The margin to set in inches

setMarginBottom($margin = 1.00) X-Ref

Set the bottom margin in inches.

param: float $margin The margin to set in inches

repeatRows($first_row, $last_row = null) X-Ref

Set the rows to repeat at the top of each printed page.

param: integer $first_row First row to repeat
param: integer $last_row Last row to repeat. Optional.

repeatColumns($first_col, $last_col = null) X-Ref

Set the columns to repeat at the left hand side of each printed page.

param: integer $first_col First column to repeat
param: integer $last_col Last column to repeat. Optional.

printArea($first_row, $first_col, $last_row, $last_col) X-Ref

Set the area of each worksheet that will be printed.

param: integer $first_row First row of the area to print
param: integer $first_col First column of the area to print
param: integer $last_row Last row of the area to print
param: integer $last_col Last column of the area to print

hideGridlines() X-Ref

Set the option to hide gridlines on the printed page.

hideScreenGridlines() X-Ref

Set the option to hide gridlines on the worksheet (as seen on the screen).

printRowColHeaders($print = 1) X-Ref

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

param: integer $print Whether to print the headers or not. Defaults to 1 (print).

fitToPages($width, $height) X-Ref

Set the vertical and horizontal number of pages that will define the maximum area printed.
It doesn't seem to work with OpenOffice.

param: integer $width Maximun width of printed area in pages
param: integer $height Maximun heigth of printed area in pages

setHPagebreaks($breaks) X-Ref

Store the horizontal page breaks on a worksheet (for printing).
The breaks represent the row after which the break is inserted.

param: array $breaks Array containing the horizontal page breaks

setVPagebreaks($breaks) X-Ref

Store the vertical page breaks on a worksheet (for printing).
The breaks represent the column after which the break is inserted.

param: array $breaks Array containing the vertical page breaks

setZoom($scale = 100) X-Ref

Set the worksheet zoom factor.

param: integer $scale The zoom factor

setPrintScale($scale = 100) X-Ref

Set the scale factor for the printed page.
It turns off the "fit to page" option

param: integer $scale The optional scale factor. Defaults to 100

write($row, $col, $token, $format = null) X-Ref

Map to the appropriate write method acording to the token recieved.

param: integer $row    The row of the cell we are writing to
param: integer $col    The column of the cell we are writing to
param: mixed   $token  What we are writing
param: mixed   $format The optional format to apply to the cell

writeRow($row, $col, $val, $format = null) X-Ref

Write an array of values as a row

param: integer $row    The row we are writing to
param: integer $col    The first col (leftmost col) we are writing to
param: array   $val    The array of values to write
param: mixed   $format The optional format to apply to the cell
return: mixed PEAR_Error on failure

writeCol($row, $col, $val, $format = null) X-Ref

Write an array of values as a column

param: integer $row    The first row (uppermost row) we are writing to
param: integer $col    The col we are writing to
param: array   $val    The array of values to write
param: mixed   $format The optional format to apply to the cell
return: mixed PEAR_Error on failure

_XF(&$format) X-Ref

Returns an index to the XF record in the workbook

param: mixed &$format The optional XF format
return: integer The XF record index

_append($data) X-Ref

Store Worksheet data in memory using the parent's class append() or to a
temporary file, the default.

param: string $data The binary data to append

_substituteCellref($cell) X-Ref

Substitute an Excel cell reference in A1 notation for zero based row and
column values in an argument list.

Ex: ("A4", "Hello") is converted to (3, 0, "Hello").

param: string $cell The cell reference. Or range of cells.
return: array

_cellToRowcol($cell) X-Ref

Convert an Excel cell reference in A1 notation to a zero based row and column
reference; converts C1 to (0, 2).

param: string $cell The cell reference.
return: array containing (row, column)

_encodePassword($plaintext) X-Ref

Based on the algorithm provided by Daniel Rentz of OpenOffice.

param: string $plaintext The password to be encoded in plaintext.
return: string The encoded password

setOutline($visible = true, $symbols_below = true, $symbols_right = true, $auto_style = false) X-Ref

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

param: bool $visible
param: bool $symbols_below
param: bool $symbols_right
param: bool $auto_style

writeNumber($row, $col, $num, $format = null) X-Ref

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

param: integer $row    Zero indexed row
param: integer $col    Zero indexed column
param: float   $num    The number to write
param: mixed   $format The optional XF format
return: integer

writeString($row, $col, $str, $format = null) X-Ref

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

param: integer $row    Zero indexed row
param: integer $col    Zero indexed column
param: string  $str    The string to write
param: mixed   $format The XF format for the cell
return: integer

setInputEncoding($encoding) X-Ref

Sets Input Encoding for writing strings

param: string $encoding The encoding. Ex: 'UTF-16LE', 'utf-8', 'ISO-859-7'

writeStringBIFF8($row, $col, $str, $format = null) X-Ref

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

param: integer $row    Zero indexed row
param: integer $col    Zero indexed column
param: string  $str    The string to write
param: mixed   $format The XF format for the cell
return: integer

_checkRowCol($row, $col) X-Ref

Check row and col before writing to a cell, and update the sheet's
dimensions accordingly

param: integer $row Zero indexed row
param: integer $col Zero indexed column
return: boolean true for success, false if row and/or col are grester

writeNote($row, $col, $note) X-Ref

Writes a note associated with the cell given by the row and column.
NOTE records don't have a length limit.

param: integer $row    Zero indexed row
param: integer $col    Zero indexed column
param: string  $note   The note to write

writeBlank($row, $col, $format) X-Ref

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

param: integer $row    Zero indexed row
param: integer $col    Zero indexed column
param: mixed   $format The XF format

writeFormula($row, $col, $formula, $format = null) X-Ref

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

param: integer $row     Zero indexed row
param: integer $col     Zero indexed column
param: string  $formula The formula text string
param: mixed   $format  The optional XF format
return: integer

writeUrl($row, $col, $url, $string = '', $format = null) X-Ref

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

param: integer $row    Row
param: integer $col    Column
param: string  $url    URL string
param: string  $string Alternative label
param: mixed   $format The cell format
return: integer

_writeUrlRange($row1, $col1, $row2, $col2, $url, $string = '', $format = null) X-Ref

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

param: integer $row1   Start row
param: integer $col1   Start column
param: integer $row2   End row
param: integer $col2   End column
param: string  $url    URL string
param: string  $string Alternative label
param: mixed   $format The cell format
return: integer

_writeUrlWeb($row1, $col1, $row2, $col2, $url, $str, $format = null) X-Ref

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.

param: integer $row1   Start row
param: integer $col1   Start column
param: integer $row2   End row
param: integer $col2   End column
param: string  $url    URL string
param: string  $str    Alternative label
param: mixed   $format The cell format
return: integer

_writeUrlInternal($row1, $col1, $row2, $col2, $url, $str, $format = null) X-Ref

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

param: integer $row1   Start row
param: integer $col1   Start column
param: integer $row2   End row
param: integer $col2   End column
param: string  $url    URL string
param: string  $str    Alternative label
param: mixed   $format The cell format
return: integer

_writeUrlExternal($row1, $col1, $row2, $col2, $url, $str, $format = null) X-Ref

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.

param: integer $row1   Start row
param: integer $col1   Start column
param: integer $row2   End row
param: integer $col2   End column
param: string  $url    URL string
param: string  $str    Alternative label
param: mixed   $format The cell format
return: integer

setRow($row, $height, $format = null, $hidden = false, $level = 0) X-Ref

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

param: integer $row    The row to set
param: integer $height Height we are giving to the row.
param: mixed   $format XF format we are giving to the row
param: bool    $hidden The optional hidden attribute
param: integer $level  The optional outline level for row, in range [0,7]

_storeDimensions() X-Ref

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

_storeWindow2() X-Ref

Write BIFF record Window2.

_storeDefcol() X-Ref

Write BIFF record DEFCOLWIDTH if COLINFO records are in use.

_storeColinfo($col_array) X-Ref

Write BIFF record COLINFO to define column widths

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

param: array $col_array This is the only parameter received and is composed of the following:

_storeSelection($array) X-Ref

Write BIFF record SELECTION.

param: array $array array containing ($rwFirst,$colFirst,$rwLast,$colLast)

_storeMergedCells() X-Ref

Store the MERGEDCELLS record for all ranges of merged cells

_storeExterncount($count) X-Ref

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.

param: integer $count The number of external sheet references in this worksheet

_storeExternsheet($sheetname) X-Ref

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.

param: string $sheetname The name of a external worksheet

_storePanes($panes) X-Ref

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.

param: array $panes This is the only parameter received and is composed of the following:

_storeSetup() X-Ref

Store the page setup SETUP BIFF record.

_storeHeader() X-Ref

Store the header caption BIFF record.

_storeFooter() X-Ref

Store the footer caption BIFF record.

_storeHcenter() X-Ref

Store the horizontal centering HCENTER BIFF record.

_storeVcenter() X-Ref

Store the vertical centering VCENTER BIFF record.

_storeMarginLeft() X-Ref

Store the LEFTMARGIN BIFF record.

_storeMarginRight() X-Ref

Store the RIGHTMARGIN BIFF record.

_storeMarginTop() X-Ref

Store the TOPMARGIN BIFF record.

_storeMarginBottom() X-Ref

Store the BOTTOMMARGIN BIFF record.

mergeCells($first_row, $first_col, $last_row, $last_col) X-Ref

Merges the area given by its arguments.
This is an Excel97/2000 method. It is required to perform more complicated
merging than the normal setAlign('merge').

param: integer $first_row First row of the area to merge
param: integer $first_col First column of the area to merge
param: integer $last_row Last row of the area to merge
param: integer $last_col Last column of the area to merge

_storePrintHeaders() X-Ref

Write the PRINTHEADERS BIFF record.

_storePrintGridlines() X-Ref

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

_storeGridset() X-Ref

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

_storeGuts() X-Ref

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.

_storeWsbool() X-Ref

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

_storeHbreak() X-Ref

Write the HORIZONTALPAGEBREAKS BIFF record.

_storeVbreak() X-Ref

Write the VERTICALPAGEBREAKS BIFF record.

_storeProtect() X-Ref

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

_storePassword() X-Ref

Write the worksheet PASSWORD record.

insertBitmap($row, $col, $bitmap, $x = 0, $y = 0, $scale_x = 1, $scale_y = 1) X-Ref

Insert a 24bit bitmap image in a worksheet.

param: integer $row     The row we are going to insert the bitmap into
param: integer $col     The column we are going to insert the bitmap into
param: string  $bitmap  The bitmap filename
param: integer $x       The horizontal position (offset) of the image inside the cell.
param: integer $y       The vertical position (offset) of the image inside the cell.
param: integer $scale_x The horizontal scale
param: integer $scale_y The vertical scale

_positionImage($col_start, $row_start, $x1, $y1, $width, $height) X-Ref

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

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

_sizeCol($col) X-Ref

Convert the width of a cell from user's units to pixels. By interpolation
the relationship is: y = 7x +5. If the width hasn't been set by the user we
use the default value. If the col is hidden we use a value of zero.

param: integer $col The column
return: integer The width in pixels

_sizeRow($row) X-Ref

Convert the height of a cell from user's units to pixels. By interpolation
the relationship is: y = 4/3x. If the height hasn't been set by the user we
use the default value. If the row is hidden we use a value of zero. (Not
possible to hide row yet).

param: integer $row The row
return: integer The width in pixels

_storeObjPicture($colL,$dxL,$rwT,$dyT,$colR,$dxR,$rwB,$dyB) X-Ref

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

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

_processBitmap($bitmap) X-Ref

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.

param: string $bitmap The bitmap to process
return: array Array with data and properties of the bitmap

_storeZoom() X-Ref

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

setValidation($row1, $col1, $row2, $col2, &$validator) X-Ref

FIXME: add comments

_storeDataValidity() X-Ref

Store the DVAL and DV records.

File Size:	3502 lines (112 kb)
Included or required:	0 times
Referenced:	0 times
Includes or requires:	0 files

PHP Cross Reference of phpwcms V1.4.3 _r380 (23.11.09)

/include/inc_ext/PEAR/Spreadsheet/Excel/Writer/ -> Worksheet.php (summary)

Defines 1 class