Добавлен: 14.03.2024
Просмотров: 44
Скачиваний: 0
return(array(row1, col1));
die("Unknown cell reference cell ");
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) function _cell_to_rowcol(cell) preg_match("\?(?)\?(\d+)",cell,match);
col match;
row match;
Convert base26 column string to number chars split("", col);
expn 0;
col 0;
while (chars) { char array_pop(chars); LS char first col + (ord(char) ord("A") +1) pow(26,expn);
expn++;
Convert 1index to zeroindex row;
col;
return(array(row, col));
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 function _encode_password(plaintext) password 0x0000;
i 1; char position split the plain text password in its component characters chars preg_split("", plaintext, 1, PREG_SPLIT_NO_EMPTY);
foreach(chars as char) value ord(char) i; shifted ASCII value bit_16 value 0x8000; the bit 16 bit_16 15; 0x0000 or 0x0001 bit_17 value 0x00010000;
bit_17 15;
value 0x7fff; first 15 bits password ^ (value | bit_16);
password ^ (value | bit_16 | bit_17);
i++;
password ^ strlen(plaintext);
password ^ 0xCE4B;
return(password);
BIFF RECORDS 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 access public 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 function write_number(row, col, num, format 0) record 0x0203; Record identifier length 0x000E; Number of bytes to follow xf this_XF(format); The cell format Check that row and col are valid and store max and min values if (row thisxls_rowmax) return(2);
if (col thisxls_colmax) return(2);
if (row thisdim_rowmin) thisdim_rowmin row;
if (row thisdim_rowmax) thisdim_rowmax row;
if (col thisdim_colmin) thisdim_colmin col;
if (col thisdim_colmax) thisdim_colmax col;
header pack(, record, length);
data pack(, row, col, xf);
xl_double pack(, num);
if (this_byte_order) if it"s Big Endian xl_double strrev(xl_double);
this_append(header.data.xl_double);
return(0);
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 1: insufficient number of arguments 2: row or column out of range 3: long string truncated to 255 chars access public 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 function write_string(row, col, str, format 0) strlen strlen(str);
record 0x0204; Record identifier length 0x0008 + strlen; Bytes to follow xf this_XF(format); The cell format str_error 0;
Check that row and col are valid and store max and min values if (row thisxls_rowmax) return(2);
if (col thisxls_colmax) return(2);
if (row thisdim_rowmin) thisdim_rowmin row;
if (row thisdim_rowmax) thisdim_rowmax row;
if (col thisdim_colmin) thisdim_colmin col;
if (col thisdim_colmax) thisdim_colmax col;
if (strlen thisxls_strmax) LABEL must be 255 chars str substr(str, 0, thisxls_strmax);
length 0x0008 + thisxls_strmax;
strlen thisxls_strmax;
str_error 3;
header pack(, record, length);
data pack(, row, col, xf, strlen);
this_append(header.data.str);
return(str_error);
Writes a note associated with the cell given by the row and column.
NOTE records don"t have a length limit.
access public param integer row Zero indexed row param integer col Zero indexed column param string note The note to write function write_note(row, col, note) note_length strlen(note);
record 0x001C; Record identifier max_length 2048; Maximun length for a NOTE record length 0x0006 + note_length; Bytes to follow Check that row and col are valid and store max and min values if (row thisxls_rowmax) return(2);
if (col thisxls_colmax) return(2);
if (row thisdim_rowmin) thisdim_rowmin row;
if (row thisdim_rowmax) thisdim_rowmax row;
if (col thisdim_colmin) thisdim_colmin col;
if (col thisdim_colmax) thisdim_colmax col;
Length for this record is no more than 2048 + 6 length 0x0006 + min(note_length, 2048);
header pack(, record, length);
data pack(, row, col, note_length);
this_append(header.data.substr(note, 0, 2048));
for(i max_length; i note_length; i + max_length) chunk substr(note, i, max_length);
length 0x0006 + strlen(chunk);
header pack(, record, length);
data pack(, 1, 0, strlen(chunk));
this_append(header.data.chunk);
return(0);
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. This is mainly an optimisation for the write_row() and write_col() methods.
Returns 0: normal termination (including no format) 1: insufficient number of arguments 2: row or column out of range access public param integer row Zero indexed row param integer col Zero indexed column param mixed format The XF format function write_blank(row, col, format 0) Don"t write a blank cell unless it has a format if (format 0) return(0);
record 0x0201; Record identifier length 0x0006; Number of bytes to follow xf this_XF(format); The cell format Check that row and col are valid and store max and min values if (row thisxls_rowmax) return(2);
if (col thisxls_colmax) return(2);
if (row thisdim_rowmin) thisdim_rowmin row;
if (row thisdim_rowmax) thisdim_rowmax row;
if (col thisdim_colmin) thisdim_colmin col;
if (col thisdim_colmax) thisdim_colmax col;
header pack(, record, length);
data pack(, row, col, xf);
this_append(header.data);
return 0;
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 2: row or column out of range access public 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 function write_formula(row, col, formula, format 0) record 0x0006; Record identifier Excel normally stores the last calculated value of the formula in num.
Clearly we are not in a position to calculate this a priori. Instead we set num to zero and set the option flags in grbit to ensure automatic calculation of the formula when the file is opened.
xf this_XF(format); The cell format num 0x00; Current value of formula grbit 0x03; Option flags chn 0x0000; Must be zero Check that row and col are valid and store max and min values if (row thisxls_rowmax) return(2);
if (col thisxls_colmax) return(2);
if (row thisdim_rowmin) thisdim_rowmin row;
if (row thisdim_rowmax) thisdim_rowmax row;
if (col thisdim_colmin) thisdim_colmin col;
if (col thisdim_colmax) thisdim_colmax col;
Strip the "" or "" sign at the beginning of the formula string if (ereg(,formula)) { formula preg_replace(,,formula);
elseif(ereg(,formula)) { formula preg_replace(,,formula);
else { die("Unrecognised character for formula");
Parse the formula using the parser in Parser.php tree new Parser(this_byte_order);
this_parserparse(formula);
treeparse(formula);
formula this_parserto_reverse_polish();
formlen strlen(formula); Length of the binary string length 0x16 + formlen; Length of the record data header pack(, record, length);
data pack(, row, col, xf, num, grbit, chn, formlen);
this_append(header.data.formula);
return 0;
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 write_string() method. Therefore the 255 characters string limit applies.
string and format are optional and their order is interchangeable.
The hyperlink can be to a http, ftp, mail, internal sheet, or external directory url.
Returns 0: normal termination 1: insufficient number of arguments 2: row or column out of range 3: long string truncated to 255 chars access public param integer row Row param integer col Column param string url URL string param string string Alternative label param mixed format The cell format function write_url(row, col, url, string "", format 0) Add start row and col to arg list return(this_write_url_range(row, col, row, col, url, string, format));
This is the more general form of write_url(). 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.xlsSheet1!A1").
See also write_url() above for a general description and return values.
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 function _write_url_range(row1, col1, row2, col2, url, string "", format 0) Check for internalexternal sheet links or default to web link if (preg_match("", url)) { return(this_write_url_internal(row1, col1, row2, col2, url, string, format));
if (preg_match("", url)) { return(this_write_url_external(row1, col1, row2, col2, url, string, format));
return(this_write_url_web(row1, col1, row2, col2, url, string, format));
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.
see write_url() 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 function _write_url_web(row1, col1, row2, col2, url, str, format 0) record 0x01B8; Record identifier length 0x00000; Bytes to follow if(format 0) { format this_url_format;
Write the visible label using the write_string() method.
if(str "") { str url;
str_error thiswrite_string(row1, col1, str, format);
if (str_error 2) { return(str_error);
Pack the undocumented parts of the hyperlink stream unknown1 pack(, "D0C9EA79F9BACE118C8200AA004BA90B02000000");
unknown2 pack(, "E0C9EA79F9BACE118C8200AA004BA90B");
Pack the option flags options pack(, 0x03);
Convert URL to a null terminated wchar string url join(, preg_split(, url, 1, PREG_SPLIT_NO_EMPTY));
url url.;
Pack the length of the URL url_len pack(, strlen(url));
Calculate the data length length 0x34 + strlen(url);
Pack the header data header pack(, record, length);
data pack(, row1, row2, col1, col2);
Write the packed data this_append( header. data.
unknown1. options.
unknown2. url_len. url);
return(str_error);
Used to write internal reference hyperlinks such as.
see write_url() 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 function _write_url_internal(row1, col1, row2, col2, url, str, format 0) record 0x01B8; Record identifier length 0x00000; Bytes to follow if (format 0) { format this_url_format;
Strip URL type url preg_replace("s", "", url);
Write the visible label if(str "") { str url;
str_error thiswrite_string(row1, col1, str, format);
if (str_error 2) { return(str_error);
Pack the undocumented parts of the hyperlink stream unknown1 pack(, "D0C9EA79F9BACE118C8200AA004BA90B02000000");
Pack the option flags options pack(, 0x08);
Convert the URL type and to a null terminated wchar string url join(, preg_split(, url, 1, PREG_SPLIT_NO_EMPTY));
url url.;
Pack the length of the URL as chars (not wchars) url_len pack(, floor(strlen(url)2));
Calculate the data length length 0x24 + strlen(url);
Pack the header data header pack(, record, length);
data pack(, row1, row2, col1, col2);
Write the packed data this_append(header. data.
unknown1. options.
url_len. url);
return(str_error);
Write links to external directory names such as "c:\foo.xls", c:\foo.xlsSheet1!A1", "..foo.xls". and "..foo.xlsSheet1!A1".
Note: Excel writes some relative links with the dir_long string. We ignore these cases for the sake of simpler code.
see write_url() 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 function _write_url_external(row1, col1, row2, col2, url, str, format 0) Network drives are different. We will handle them separately MSNovell network drives and shares start with \\ if (preg_match("", url)) { return(this_write_url_external_net(row1, col1, row2, col2, url, str, format));
record 0x01B8; Record identifier length 0x00000; Bytes to follow if (format 0) { format this_url_format;
Strip URL type and change Unix dir separator to Dos style (if needed) url preg_replace("", "", url);
url preg_replace("",, url);
Write the visible label if (str "") { str preg_replace("", " ", url);
str_error thiswrite_string(row1, col1, str, format);
if (str_error 2) { return(str_error);
Determine if the link is relative or absolute:
relative if link contains no dir separator, relative if link starts with updir, otherwise, absolute absolute 0x02; Bit mask if (!preg_match("", url)) { absolute 0x00;
if (preg_match("", url)) { absolute 0x00;
Determine if the link contains a sheet reference and change some of the parameters accordingly.
Split the dir name and sheet name (if it exists) list(dir_long, sheet) split("\", url);
link_type 0x01 | absolute;
if (isset(sheet)) { link_type | 0x08;
sheet_len pack(, strlen(sheet) + 0x01);
sheet join(, split("", sheet));
sheet.;
else { sheet_len "";
sheet "";
Pack the link type link_type pack(, link_type);
Calculate the uplevel dir count e.g. (.\.\.\ 3) up_count preg_match_all(, dir_long, useless);
up_count pack(, up_count);
Store the short dos dir name (null terminated) dir_short preg_replace("\.\.\\", "", dir_long).;
Store the long dir name as a wchar string (nonnull terminated) dir_long join(, split("", dir_long));
dir_long dir_long.;
Pack the lengths of the dir strings dir_short_len pack(, strlen(dir_short) );
dir_long_len pack(, strlen(dir_long) );
stream_len pack(, strlen(dir_long) + 0x06);
Pack the undocumented parts of the hyperlink stream unknown1 pack(,"D0C9EA79F9BACE118C8200AA004BA90B02000000" );
unknown2 pack(,"0303000000000000C000000000000046" );
unknown3 pack(,"FFFFADDE000000000000000000000000000000000000000");
unknown4 pack(, 0x03 );
Pack the main data stream data pack(, row1, row2, col1, col2).
unknown1.
link_type.
unknown2.
up_count.
dir_short_len.
dir_short.
unknown3.
stream_len.
dir_long_len.
unknown4.
dir_long.
sheet_len.
sheet;
Pack the header data length strlen(data);
header pack(, record, length);
Write the packed data this_append(header. data);
return(str_error);
write_url_xxx(row1, col1, row2, col2, url, string, format) Write links to external MSNovell network drives and shares such as "NETWORKsharefoo.xls" and "NETWORKsharefoo.xlsSheet1!A1".
See also write_url() above for a general description and return values.
sub _write_url_external_net { my this shift;
my record 0x01B8; Record identifier my length 0x00000; Bytes to follow my row1 _; Start row my col1 _; Start column my row2 _; End row my col2 _; End column my url _; URL string my str _; Alternative label my xf _ || this; The cell format Strip URL type and change Unix dir separator to Dos style (if needed) url ~ s[];
url ~ s[\\]g;
Write the visible label (str url) ~ s[ ] unless defined str;
my str_error thiswrite_string(row1, col1, str, xf);
return str_error if str_error 2;
Determine if the link contains a sheet reference and change some of the parameters accordingly.
Split the dir name and sheet name (if it exists) my (dir_long, sheet) split \, url;
my link_type 0x0103; Always absolute my sheet_len;
if (defined sheet) { link_type | 0x08;
sheet_len pack(, length(sheet) + 0x01);
sheet join(, split("", sheet));
sheet.;
else { sheet_len "";
sheet "";
Pack the link type link_type pack(, link_type);
Make the string null terminated dir_long dir_long.;
Pack the lengths of the dir string my dir_long_len pack(, length dir_long);
Store the long dir name as a wchar string (nonnull terminated) dir_long join(, split("", dir_long));
dir_long dir_long.;
Pack the undocumented part of the hyperlink stream my unknown1 pack(,"D0C9EA79F9BACE118C8200AA004BA90B02000000");
Pack the main data stream my data pack(, row1, row2, col1, col2).
unknown1.link_type.
dir_long_len.dir_long.sheet_len.
sheet;Pack the header data length length data;
my header pack(, record, length);
Write the packed data this_append( header, data);
return str_error;
} This method is used to set the height and XF format for a row.
Writes the BIFF record ROW.
access public param integer row The row to set param integer height Height we are giving to the row.
Use NULL to set XF without setting height param mixed format XF format we are giving to the row function set_row(row, height, format 0) record 0x0208; Record identifier length 0x0010; Number of bytes to follow colMic 0x0000; First defined column colMac 0x0000; Last defined column irwMac 0x0000; Used by Excel to optimise loading reserved 0x0000; Reserved grbit 0x01C0; Option flags. (monkey) see 1 do ixfe this_XF(format); XF index Use set_row(row, NULL, XF) to set XF without setting height if (height ! NULL) { miyRw height 20; row height else { miyRw 0xff; default row height is 256 header pack(, record, length);
data pack(, row, colMic, colMac, miyRw, irwMac,reserved, grbit, ixfe);
this_append(header.data);
Writes Excel DIMENSIONS to define the area in which there is data.
function _store_dimensions() record 0x0000; Record identifier length 0x000A; Number of bytes to follow row_min thisdim_rowmin; First row row_max thisdim_rowmax; Last row plus 1 col_min thisdim_colmin; First column col_max thisdim_colmax; Last column plus 1 reserved 0x0000; Reserved by Excel header pack(, record, length);
data pack(, row_min, row_max, col_min, col_max, reserved);
this_prepend(header.data);
Write BIFF record Window2.
function _store_window2() record 0x023E; Record identifier length 0x000A; Number of bytes to follow grbit 0x00B6; Option flags rwTop 0x0000; Top row visible in window colLeft 0x0000; Leftmost column visible in window rgbHdr 0x00000000; Rowcolumn heading and gridline color The options flags that comprise grbit fDspFmla 0; 0 bit fDspGrid 1; 1
fDspRwCol 1; 2
fFrozen this_frozen; 3
fDspZeros 1; 4
fDefaultHdr 1; 5
fArabic 0; 6
fDspGuts 1; 7
fFrozenNoSplit 0; 0 bit fSelected thisselected; 1
fPaged 1; 2
grbit fDspFmla;
grbit | fDspGrid 1;
grbit | fDspRwCol 2;
grbit | fFrozen 3;
grbit | fDspZeros 4;
grbit | fDefaultHdr 5;
grbit | fArabic 6;
grbit | fDspGuts 7;
grbit | fFrozenNoSplit 8;
grbit | fSelected 9;
grbit | fPaged 10;
header pack(, record, length);
data pack(, grbit, rwTop, colLeft, rgbHdr);
this_append(header.data);
Write BIFF record DEFCOLWIDTH if COLINFO records are in use.
function _store_defcol() record 0x0055; Record identifier length 0x0002; Number of bytes to follow colwidth 0x0008; Default column width header pack(, record, length);
data pack(, colwidth);
this_prepend(header.data);
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:
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.
function _store_colinfo(col_array) if(isset(col_array)) { colFirst col_array;
if(isset(col_array)) { colLast col_array;
if(isset(col_array)) { coldx col_array;
else { coldx 8.43;
if(isset(col_array)) { format col_array;
else { format 0;
if(isset(col_array)) { grbit col_array;
else { grbit 0;
record 0x007D; Record identifier length 0x000B; Number of bytes to follow coldx + 0.72; Fudge. Excel subtracts 0.72 !?
coldx 256; Convert to units of 1256 of a char ixfe this_XF(format);
reserved 0x00; Reserved header pack(, record, length);
data pack(, colFirst, colLast, coldx, ixfe, grbit, reserved);
this_prepend(header.data);
Write BIFF record SELECTION.
param array array array containing (rwFirst,colFirst,rwLast,colLast) see set_selection() function _store_selection(array) list(rwFirst,colFirst,rwLast,colLast) array;
record 0x001D; Record identifier length 0x000F; Number of bytes to follow pnn this_active_pane; Pane position rwAct rwFirst; Active row colAct colFirst; Active column irefAct 0; Active cell ref cref 1; Number of refs if (!isset(rwLast)) { rwLast rwFirst; Last row in reference if (!isset(colLast)) { colLast colFirst; Last col in reference Swap last rowcol for first rowcol as necessary if (rwFirst rwLast) list(rwFirst, rwLast) array(rwLast, rwFirst);
