Добавлен: 25.04.2023
Просмотров: 477
Скачиваний: 2
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);
if (colFirst colLast) list(colFirst, colLast) array(colLast, colFirst);
header pack(, record, length);
data pack(, pnn, rwAct, colAct, irefAct, cref, rwFirst, rwLast, colFirst, colLast);
this_append(header.data);
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 function _store_externcount(count) record 0x0016; Record identifier length 0x0002; Number of bytes to follow header pack(, record, length);
data pack(, count);
this_prepend(header.data);
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 function _store_externsheet(sheetname) record 0x0017; Record identifier References to the current sheet are encoded differently to references to external sheets.
if (thisname sheetname) { sheetname '';
length 0x02; The following 2 bytes cch 1; The following byte rgch 0x02; Self reference else { length 0x02 + strlen(sheetname);
cch strlen(sheetname);
rgch 0x03; Reference to a sheet in the current workbook header pack(, record, length);
data pack(, cch, rgch);
this_prepend(header.data.sheetname);
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:
0 Vertical split position, 1 Horizontal split position 2 Top row visible 3 Leftmost column visible 4 Active pane function _store_panes(panes) y panes;
x panes;
rwTop panes;
colLeft panes;
if(count(panes) 4) { if Active pane was received pnnAct panes;
else { pnnAct NULL;
record 0x0041; Record identifier length 0x000A; Number of bytes to follow Code specific to frozen or thawed panes.
if (this_frozen) { Set default values for rwTop and colLeft if(!isset(rwTop)) { rwTop y;
if(!isset(colLeft)) { colLeft x;
else { Set default values for rwTop and colLeft if(!isset(rwTop)) { rwTop 0;
if(!isset(colLeft)) { colLeft 0;
Convert Excel's row and column units to the internal units.
The default row height is 12.75
The default column width is 8.43
The following slope and intersection values were interpolated.
y 20y + 255;
x 113.879x + 390;
Determine which pane should be active. There is also the undocumented option to override this should it be necessary: may be removed later.
if (!isset(pnnAct)) if (x ! 0 and y ! 0)
pnnAct 0; Bottom right if (x ! 0 and y 0) pnnAct 1; Top right if (x 0 and y ! 0)
pnnAct 2; Bottom left if (x 0 and y 0) pnnAct 3; Top left this_active_pane pnnAct; Used in _store_selection header pack(, record, length);
data pack(, x, y, rwTop, colLeft, pnnAct);
this_append(header.data);
Store the page setup SETUP BIFF record.
function _store_setup() record 0x00A1; Record identifier length 0x0022; Number of bytes to follow iPaperSize this_paper_size; Paper size iScale this_print_scale; Print scaling factor iPageStart 0x01; Starting page number iFitWidth this_fit_width; Fit to number of pages wide iFitHeight this_fit_height; Fit to number of pages high grbit 0x00; Option flags iRes 0x0258; Print resolution iVRes 0x0258; Vertical print resolution numHdr this_margin_head; Header Margin numFtr this_margin_foot; Footer Margin iCopies 0x01; Number of copies fLeftToRight 0x0; Print over then down fLandscape this_orientation; Page orientation fNoPls 0x0; Setup not read from printer fNoColor 0x0; Print black and white fDraft 0x0; Print draft quality fNotes 0x0; Print notes fNoOrient 0x0; Orientation not set fUsePage 0x0; Use custom starting page grbit fLeftToRight;
grbit | fLandscape 1;
grbit | fNoPls 2;
grbit | fNoColor 3;
grbit | fDraft 4;
grbit | fNotes 5;
grbit | fNoOrient 6;
grbit | fUsePage 7;
numHdr pack(, numHdr);
numFtr pack(, numFtr);
if (this_byte_order) if it's Big Endian numHdr strrev(numHdr);
numFtr strrev(numFtr);
header pack(, record, length);
data1 pack(, iPaperSize, iScale, iPageStart, iFitWidth, iFitHeight, grbit, iRes, iVRes);
data2 numHdr.numFtr;
data3 pack(, iCopies);
this_prepend(header.data1.data2.data3);
Store the header caption BIFF record.
function store_header() record 0x0014; Record identifier str this_header; header string cch strlen(str); Length of header string length 1 + cch; Bytes to follow header pack(, record, length);
data pack(, cch);
this_append(header.data.str);
Store the footer caption BIFF record.
function store_footer() record 0x0015; Record identifier str this_footer; Footer string cch strlen(str); Length of footer string length 1 + cch; Bytes to follow header pack(, record, length);
data pack(, cch);
this_append(header.data.str);
Store the horizontal centering HCENTER BIFF record.
function store_hcenter() record 0x0083; Record identifier length 0x0002; Bytes to follow fHCenter this_hcenter; Horizontal centering header pack(, record, length);
data pack(, fHCenter);
this_append(header.data);
Store the vertical centering VCENTER BIFF record.
function store_vcenter() record 0x0084; Record identifier length 0x0002; Bytes to follow fVCenter this_vcenter; Horizontal centering header pack(, record, length);
data pack(, fVCenter);
this_append(header.data);
Store the LEFTMARGIN BIFF record.
function _store_margin_left() record 0x0026; Record identifier length 0x0008; Bytes to follow margin this_margin_left; Margin in inches header pack(, record, length);
data pack(, margin);
if (this_byte_order) if it's Big Endian { data strrev(data);
this_append(header.data);
Store the RIGHTMARGIN BIFF record.
function _store_margin_right() record 0x0027; Record identifier length 0x0008; Bytes to follow margin this_margin_right; Margin in inches header pack(, record, length);
data pack(, margin);
if (this_byte_order) if it's Big Endian { data strrev(data);
this_append(header.data);
Store the TOPMARGIN BIFF record.
function _store_margin_top() record 0x0028; Record identifier length 0x0008; Bytes to follow margin this_margin_top; Margin in inches header pack(, record, length);
data pack(, margin);
if (this_byte_order) if it's Big Endian { data strrev(data);
this_append(header.data);
Store the BOTTOMMARGIN BIFF record.
function _store_margin_bottom() record 0x0029; Record identifier length 0x0008; Bytes to follow margin this_margin_bottom; Margin in inches header pack(, record, length);
data pack(, margin);
if (this_byte_order) if it's Big Endian { data strrev(data);
this_append(header.data);
This is an Excel972000 method. It is required to perform more complicated merging than the normal set_align('merge'). It merges the area given by its arguments.
access public 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 function merge_cells(first_row, first_col, last_row, last_col) record 0x00E5; Record identifier length 0x000A; Bytes to follow cref 1; Number of refs Swap last rowcol for first rowcol as necessary if (first_row last_row) { list(first_row, last_row) array(last_row, first_row);
if (first_col last_col) { list(first_col, last_col) array(last_col, first_col);
header pack(, record, length);
data pack(, cref, first_row, last_row, first_col, last_col);
this_append(header.data);
Write the PRINTHEADERS BIFF record.
function _store_print_headers() record 0x002a; Record identifier length 0x0002; Bytes to follow fPrintRwCol this_print_headers; Boolean flag header pack(, record, length);
data pack(, fPrintRwCol);
this_prepend(header.data);
Write the PRINTGRIDLINES BIFF record. Must be used in conjunction with the GRIDSET record.
function _store_print_gridlines() record 0x002b; Record identifier length 0x0002; Bytes to follow fPrintGrid this_print_gridlines; Boolean flag header pack(, record, length);