PageRenderTime 70ms CodeModel.GetById 28ms RepoModel.GetById 0ms app.codeStats 1ms

/include/php_writeexcel/class.writeexcel_worksheet.inc.php

https://bitbucket.org/yousef_fadila/vtiger
PHP | 2954 lines | 1615 code | 510 blank | 829 comment | 223 complexity | 73aa872ec571c908ea15f910fb28d549 MD5 | raw file
Possible License(s): LGPL-2.1, GPL-2.0

Large files files are truncated, but you can click here to view the full file

  1. <?php
  2. /*
  3. * Copyleft 2002 Johann Hanne
  4. *
  5. * This is free software; you can redistribute it and/or
  6. * modify it under the terms of the GNU Lesser General Public
  7. * License as published by the Free Software Foundation; either
  8. * version 2.1 of the License, or (at your option) any later version.
  9. *
  10. * This software is distributed in the hope that it will be useful,
  11. * but WITHOUT ANY WARRANTY; without even the implied warranty of
  12. * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
  13. * Lesser General Public License for more details.
  14. *
  15. * You should have received a copy of the GNU Lesser General Public
  16. * License along with this software; if not, write to the
  17. * Free Software Foundation, Inc., 59 Temple Place,
  18. * Suite 330, Boston, MA 02111-1307 USA
  19. */
  20. /*
  21. * This is the Spreadsheet::WriteExcel Perl package ported to PHP
  22. * Spreadsheet::WriteExcel was written by John McNamara, jmcnamara@cpan.org
  23. */
  24. require_once "class.writeexcel_biffwriter.inc.php";
  25. class writeexcel_worksheet extends writeexcel_biffwriter {
  26. var $_name;
  27. var $_index;
  28. var $_activesheet;
  29. var $_firstsheet;
  30. var $_url_format;
  31. var $_parser;
  32. var $_tempdir;
  33. var $_ext_sheets;
  34. var $_using_tmpfile;
  35. var $_filehandle;
  36. var $_fileclosed;
  37. var $_offset;
  38. var $_xls_rowmax;
  39. var $_xls_colmax;
  40. var $_xls_strmax;
  41. var $_dim_rowmin;
  42. var $_dim_rowmax;
  43. var $_dim_colmin;
  44. var $_dim_colmax;
  45. var $_colinfo;
  46. var $_selection;
  47. var $_panes;
  48. var $_active_pane;
  49. var $_frozen;
  50. var $_selected;
  51. var $_paper_size;
  52. var $_orientation;
  53. var $_header;
  54. var $_footer;
  55. var $_hcenter;
  56. var $_vcenter;
  57. var $_margin_head;
  58. var $_margin_foot;
  59. var $_margin_left;
  60. var $_margin_right;
  61. var $_margin_top;
  62. var $_margin_bottom;
  63. var $_title_rowmin;
  64. var $_title_rowmax;
  65. var $_title_colmin;
  66. var $_title_colmax;
  67. var $_print_rowmin;
  68. var $_print_rowmax;
  69. var $_print_colmin;
  70. var $_print_colmax;
  71. var $_print_gridlines;
  72. var $_screen_gridlines;
  73. var $_print_headers;
  74. var $_fit_page;
  75. var $_fit_width;
  76. var $_fit_height;
  77. var $_hbreaks;
  78. var $_vbreaks;
  79. var $_protect;
  80. var $_password;
  81. var $_col_sizes;
  82. var $_row_sizes;
  83. var $_col_formats;
  84. var $_row_formats;
  85. var $_zoom;
  86. var $_print_scale;
  87. //added to get the tmp file name
  88. var $_tmp_file_name;
  89. /*
  90. * Constructor. Creates a new Worksheet object from a BIFFwriter object
  91. */
  92. function writeexcel_worksheet($name, $index, &$activesheet, &$firstsheet,
  93. &$url_format, &$parser, $tempdir) {
  94. $this->writeexcel_biffwriter();
  95. $rowmax = 65536; // 16384 in Excel 5
  96. $colmax = 256;
  97. $strmax = 20000;
  98. $this->_name = $name;
  99. $this->_index = $index;
  100. $this->_activesheet = &$activesheet;
  101. $this->_firstsheet = &$firstsheet;
  102. $this->_url_format = &$url_format;
  103. $this->_parser = &$parser;
  104. $this->_tempdir = $tempdir;
  105. $this->_ext_sheets = array();
  106. $this->_using_tmpfile = 1;
  107. $this->_filehandle = false;
  108. $this->_fileclosed = 0;
  109. $this->_offset = 0;
  110. $this->_xls_rowmax = $rowmax;
  111. $this->_xls_colmax = $colmax;
  112. $this->_xls_strmax = $strmax;
  113. $this->_dim_rowmin = $rowmax +1;
  114. $this->_dim_rowmax = 0;
  115. $this->_dim_colmin = $colmax +1;
  116. $this->_dim_colmax = 0;
  117. $this->_colinfo = array();
  118. $this->_selection = array(0, 0);
  119. $this->_panes = array();
  120. $this->_active_pane = 3;
  121. $this->_frozen = 0;
  122. $this->_selected = 0;
  123. $this->_paper_size = 0x0;
  124. $this->_orientation = 0x1;
  125. $this->_header = '';
  126. $this->_footer = '';
  127. $this->_hcenter = 0;
  128. $this->_vcenter = 0;
  129. $this->_margin_head = 0.50;
  130. $this->_margin_foot = 0.50;
  131. $this->_margin_left = 0.75;
  132. $this->_margin_right = 0.75;
  133. $this->_margin_top = 1.00;
  134. $this->_margin_bottom = 1.00;
  135. $this->_title_rowmin = false;
  136. $this->_title_rowmax = false;
  137. $this->_title_colmin = false;
  138. $this->_title_colmax = false;
  139. $this->_print_rowmin = false;
  140. $this->_print_rowmax = false;
  141. $this->_print_colmin = false;
  142. $this->_print_colmax = false;
  143. $this->_print_gridlines = 1;
  144. $this->_screen_gridlines = 1;
  145. $this->_print_headers = 0;
  146. $this->_fit_page = 0;
  147. $this->_fit_width = 0;
  148. $this->_fit_height = 0;
  149. $this->_hbreaks = array();
  150. $this->_vbreaks = array();
  151. $this->_protect = 0;
  152. $this->_password = false;
  153. $this->_col_sizes = array();
  154. $this->_row_sizes = array();
  155. $this->_col_formats = array();
  156. $this->_row_formats = array();
  157. $this->_zoom = 100;
  158. $this->_print_scale = 100;
  159. $this->_initialize();
  160. }
  161. ###############################################################################
  162. #
  163. # _initialize()
  164. #
  165. # Open a tmp file to store the majority of the Worksheet data. If this fails,
  166. # for example due to write permissions, store the data in memory. This can be
  167. # slow for large files.
  168. #
  169. function _initialize() {
  170. # Open tmp file for storing Worksheet data.
  171. $this->_tmp_file_name = tempnam($this->_tempdir, "php_writeexcel");
  172. $fh=fopen($this->_tmp_file_name, "w+b");
  173. if ($fh) {
  174. # Store filehandle
  175. $this->_filehandle = $fh;
  176. } else {
  177. # If tempfile() failed store data in memory
  178. $this->_using_tmpfile = 0;
  179. if ($this->_index == 0) {
  180. $dir = $this->_tempdir;
  181. //todo warn "Unable to create temp files in $dir. Refer to set_tempdir()".
  182. // " in the Spreadsheet::WriteExcel documentation.\n" ;
  183. }
  184. }
  185. }
  186. /*
  187. * Add data to the beginning of the workbook (note the reverse order)
  188. * and to the end of the workbook.
  189. */
  190. function _close($sheetnames) {
  191. ///////////////////////////////
  192. // Prepend in reverse order!!
  193. //
  194. $this->_store_dimensions(); // Prepend the sheet dimensions
  195. $this->_store_password(); // Prepend the sheet password
  196. $this->_store_protect(); // Prepend the sheet protection
  197. $this->_store_setup(); // Prepend the page setup
  198. $this->_store_margin_bottom(); // Prepend the bottom margin
  199. $this->_store_margin_top(); // Prepend the top margin
  200. $this->_store_margin_right(); // Prepend the right margin
  201. $this->_store_margin_left(); // Prepend the left margin
  202. $this->_store_vcenter(); // Prepend the page vertical
  203. // centering
  204. $this->_store_hcenter(); // Prepend the page horizontal
  205. // centering
  206. $this->_store_footer(); // Prepend the page footer
  207. $this->_store_header(); // Prepend the page header
  208. $this->_store_vbreak(); // Prepend the vertical page breaks
  209. $this->_store_hbreak(); // Prepend the horizontal
  210. // page breaks
  211. $this->_store_wsbool(); // Prepend WSBOOL
  212. $this->_store_gridset(); // Prepend GRIDSET
  213. $this->_store_print_gridlines(); // Prepend PRINTGRIDLINES
  214. $this->_store_print_headers(); // Prepend PRINTHEADERS
  215. // Prepend EXTERNSHEET references
  216. $num_sheets = sizeof($sheetnames);
  217. for ($i = $num_sheets; $i > 0; $i--) {
  218. $sheetname = $sheetnames[$i-1];
  219. $this->_store_externsheet($sheetname);
  220. }
  221. $this->_store_externcount($num_sheets); // Prepend the EXTERNCOUNT
  222. // of external references.
  223. // Prepend the COLINFO records if they exist
  224. if (sizeof($this->_colinfo)>0){
  225. while (sizeof($this->_colinfo)>0) {
  226. $arrayref = array_pop ($this->_colinfo);
  227. $this->_store_colinfo($arrayref);
  228. }
  229. $this->_store_defcol();
  230. }
  231. $this->_store_bof(0x0010); // Prepend the BOF record
  232. //
  233. // End of prepend. Read upwards from here.
  234. ////////////////////////////////////////////
  235. // Append
  236. $this->_store_window2();
  237. $this->_store_zoom();
  238. if (sizeof($this->_panes)>0) {
  239. $this->_store_panes($this->_panes);
  240. }
  241. $this->_store_selection($this->_selection);
  242. $this->_store_eof();
  243. }
  244. /*
  245. * Retrieve the worksheet name.
  246. */
  247. function get_name() {
  248. return $this->_name;
  249. }
  250. ###############################################################################
  251. #
  252. # get_data().
  253. #
  254. # Retrieves data from memory in one chunk, or from disk in $buffer
  255. # sized chunks.
  256. #
  257. function get_data() {
  258. $buffer = 4096;
  259. # Return data stored in memory
  260. if ($this->_data!==false) {
  261. $tmp = $this->_data;
  262. $this->_data=false;
  263. $fh = $this->_filehandle;
  264. if ($this->_using_tmpfile) {
  265. fseek($fh, 0, SEEK_SET);
  266. }
  267. if ($this->_debug) {
  268. print "*** worksheet::get_data() called (1):";
  269. for ($c=0;$c<strlen($tmp);$c++) {
  270. if ($c%16==0) {
  271. print "\n";
  272. }
  273. printf("%02X ", ord($tmp[$c]));
  274. }
  275. print "\n";
  276. }
  277. return $tmp;
  278. }
  279. # Return data stored on disk
  280. if ($this->_using_tmpfile) {
  281. if ($tmp=fread($this->_filehandle, $buffer)) {
  282. if ($this->_debug) {
  283. print "*** worksheet::get_data() called (2):";
  284. for ($c=0;$c<strlen($tmp);$c++) {
  285. if ($c%16==0) {
  286. print "\n";
  287. }
  288. printf("%02X ", ord($tmp[$c]));
  289. }
  290. print "\n";
  291. }
  292. return $tmp;
  293. }
  294. }
  295. # No data to return
  296. return false;
  297. }
  298. /*
  299. * Set this worksheet as a selected worksheet, i.e. the worksheet has
  300. * its tab highlighted.
  301. */
  302. function select() {
  303. $this->_selected = 1;
  304. }
  305. /*
  306. * Set this worksheet as the active worksheet, i.e. the worksheet
  307. * that is displayed when the workbook is opened. Also set it as
  308. * selected.
  309. */
  310. function activate() {
  311. $this->_selected = 1;
  312. $this->_activesheet = $this->_index;
  313. }
  314. /*
  315. * Set this worksheet as the first visible sheet. This is necessary
  316. * when there are a large number of worksheets and the activated
  317. * worksheet is not visible on the screen.
  318. */
  319. function set_first_sheet() {
  320. $this->_firstsheet = $this->_index;
  321. }
  322. /*
  323. * Set the worksheet protection flag to prevent accidental modification
  324. * and to hide formulas if the locked and hidden format properties have
  325. * been set.
  326. */
  327. function protect($password) {
  328. $this->_protect = 1;
  329. $this->_password = $this->_encode_password($password);
  330. }
  331. ###############################################################################
  332. #
  333. # set_column($firstcol, $lastcol, $width, $format, $hidden)
  334. #
  335. # Set the width of a single column or a range of column.
  336. # See also: _store_colinfo
  337. #
  338. function set_column() {
  339. $_=func_get_args();
  340. $cell = $_[0];
  341. # Check for a cell reference in A1 notation and substitute row and column
  342. if (preg_match('/^\D/', $cell)) {
  343. $_ = $this->_substitute_cellref($_);
  344. }
  345. array_push($this->_colinfo, $_);
  346. # Store the col sizes for use when calculating image vertices taking
  347. # hidden columns into account. Also store the column formats.
  348. #
  349. if (sizeof($_)<3) {
  350. # Ensure at least $firstcol, $lastcol and $width
  351. return;
  352. }
  353. $width = $_[4] ? 0 : $_[2]; # Set width to zero if column is hidden
  354. $format = $_[3];
  355. list($firstcol, $lastcol) = $_;
  356. for ($col=$firstcol;$col<=$lastcol;$col++) {
  357. $this->_col_sizes[$col] = $width;
  358. if ($format) {
  359. $this->_col_formats[$col] = $format;
  360. }
  361. }
  362. }
  363. ###############################################################################
  364. #
  365. # set_selection()
  366. #
  367. # Set which cell or cells are selected in a worksheet: see also the
  368. # function _store_selection
  369. #
  370. function set_selection() {
  371. $_=func_get_args();
  372. # Check for a cell reference in A1 notation and substitute row and column
  373. if (preg_match('/^\D/', $_[0])) {
  374. $_ = $this->_substitute_cellref($_);
  375. }
  376. $this->_selection = $_;
  377. }
  378. ###############################################################################
  379. #
  380. # freeze_panes()
  381. #
  382. # Set panes and mark them as frozen. See also _store_panes().
  383. #
  384. function freeze_panes() {
  385. $_=func_get_args();
  386. # Check for a cell reference in A1 notation and substitute row and column
  387. if (preg_match('/^\D/', $_[0])) {
  388. $_ = $this->_substitute_cellref($_);
  389. }
  390. $this->_frozen = 1;
  391. $this->_panes = $_;
  392. }
  393. ###############################################################################
  394. #
  395. # thaw_panes()
  396. #
  397. # Set panes and mark them as unfrozen. See also _store_panes().
  398. #
  399. function thaw_panes() {
  400. $_=func_get_args();
  401. $this->_frozen = 0;
  402. $this->_panes = $_;
  403. }
  404. /*
  405. * Set the page orientation as portrait.
  406. */
  407. function set_portrait() {
  408. $this->_orientation = 1;
  409. }
  410. /*
  411. * Set the page orientation as landscape.
  412. */
  413. function set_landscape() {
  414. $this->_orientation = 0;
  415. }
  416. /*
  417. * Set the paper type. Ex. 1 = US Letter, 9 = A4
  418. */
  419. function set_paper($type) {
  420. $this->_paper_size = $type;
  421. }
  422. /*
  423. * Set the page header caption and optional margin.
  424. */
  425. function set_header($string, $margin) {
  426. if (strlen($string) >= 255) {
  427. trigger_error("Header string must be less than 255 characters",
  428. E_USER_WARNING);
  429. return;
  430. }
  431. $this->_header = $string;
  432. $this->_margin_head = $margin;
  433. }
  434. /*
  435. * Set the page footer caption and optional margin.
  436. */
  437. function set_footer($string, $margin) {
  438. if (strlen($string) >= 255) {
  439. trigger_error("Footer string must be less than 255 characters",
  440. E_USER_WARNING);
  441. return;
  442. }
  443. $this->_footer = $string;
  444. $this->_margin_foot = $margin;
  445. }
  446. /*
  447. * Center the page horizontally.
  448. */
  449. function center_horizontally($hcenter=1) {
  450. $this->_hcenter = $hcenter;
  451. }
  452. /*
  453. * Center the page horizontally.
  454. */
  455. function center_vertically($vcenter=1) {
  456. $this->_vcenter = $vcenter;
  457. }
  458. /*
  459. * Set all the page margins to the same value in inches.
  460. */
  461. function set_margins($margin) {
  462. $this->set_margin_left($margin);
  463. $this->set_margin_right($margin);
  464. $this->set_margin_top($margin);
  465. $this->set_margin_bottom($margin);
  466. }
  467. /*
  468. * Set the left and right margins to the same value in inches.
  469. */
  470. function set_margins_LR($margin) {
  471. $this->set_margin_left($margin);
  472. $this->set_margin_right($margin);
  473. }
  474. /*
  475. * Set the top and bottom margins to the same value in inches.
  476. */
  477. function set_margins_TB($margin) {
  478. $this->set_margin_top($margin);
  479. $this->set_margin_bottom($margin);
  480. }
  481. /*
  482. * Set the left margin in inches.
  483. */
  484. function set_margin_left($margin=0.75) {
  485. $this->_margin_left = $margin;
  486. }
  487. /*
  488. * Set the right margin in inches.
  489. */
  490. function set_margin_right($margin=0.75) {
  491. $this->_margin_right = $margin;
  492. }
  493. /*
  494. * Set the top margin in inches.
  495. */
  496. function set_margin_top($margin=1.00) {
  497. $this->_margin_top = $margin;
  498. }
  499. /*
  500. * Set the bottom margin in inches.
  501. */
  502. function set_margin_bottom($margin=1.00) {
  503. $this->_margin_bottom = $margin;
  504. }
  505. ###############################################################################
  506. #
  507. # repeat_rows($first_row, $last_row)
  508. #
  509. # Set the rows to repeat at the top of each printed page. See also the
  510. # _store_name_xxxx() methods in Workbook.pm.
  511. #
  512. function repeat_rows() {
  513. $_=func_get_args();
  514. $this->_title_rowmin = $_[0];
  515. $this->_title_rowmax = isset($_[1]) ? $_[1] : $_[0]; # Second row is optional
  516. }
  517. ###############################################################################
  518. #
  519. # repeat_columns($first_col, $last_col)
  520. #
  521. # Set the columns to repeat at the left hand side of each printed page.
  522. # See also the _store_names() methods in Workbook.pm.
  523. #
  524. function repeat_columns() {
  525. $_=func_get_args();
  526. # Check for a cell reference in A1 notation and substitute row and column
  527. if (preg_match('/^\D/', $_[0])) {
  528. $_ = $this->_substitute_cellref($_);
  529. }
  530. $this->_title_colmin = $_[0];
  531. $this->_title_colmax = isset($_[1]) ? $_[1] : $_[0]; # Second col is optional
  532. }
  533. ###############################################################################
  534. #
  535. # print_area($first_row, $first_col, $last_row, $last_col)
  536. #
  537. # Set the area of each worksheet that will be printed. See also the
  538. # _store_names() methods in Workbook.pm.
  539. #
  540. function print_area() {
  541. $_=func_get_args();
  542. # Check for a cell reference in A1 notation and substitute row and column
  543. if (preg_match('/^\D/', $_[0])) {
  544. $_ = $this->_substitute_cellref($_);
  545. }
  546. if (sizeof($_) != 4) {
  547. # Require 4 parameters
  548. return;
  549. }
  550. $this->_print_rowmin = $_[0];
  551. $this->_print_colmin = $_[1];
  552. $this->_print_rowmax = $_[2];
  553. $this->_print_colmax = $_[3];
  554. }
  555. /*
  556. * Set the option to hide gridlines on the screen and the printed page.
  557. * There are two ways of doing this in the Excel BIFF format: The first
  558. * is by setting the DspGrid field of the WINDOW2 record, this turns off
  559. * the screen and subsequently the print gridline. The second method is
  560. * to via the PRINTGRIDLINES and GRIDSET records, this turns off the
  561. * printed gridlines only. The first method is probably sufficient for
  562. * most cases. The second method is supported for backwards compatibility.
  563. */
  564. function hide_gridlines($option=1) {
  565. if ($option == 0) {
  566. $this->_print_gridlines = 1; # 1 = display, 0 = hide
  567. $this->_screen_gridlines = 1;
  568. } elseif ($option == 1) {
  569. $this->_print_gridlines = 0;
  570. $this->_screen_gridlines = 1;
  571. } else {
  572. $this->_print_gridlines = 0;
  573. $this->_screen_gridlines = 0;
  574. }
  575. }
  576. /*
  577. * Set the option to print the row and column headers on the printed page.
  578. * See also the _store_print_headers() method below.
  579. */
  580. function print_row_col_headers($headers=1) {
  581. $this->_print_headers = $headers;
  582. }
  583. /*
  584. * Store the vertical and horizontal number of pages that will define
  585. * the maximum area printed. See also _store_setup() and _store_wsbool()
  586. * below.
  587. */
  588. function fit_to_pages($width, $height) {
  589. $this->_fit_page = 1;
  590. $this->_fit_width = $width;
  591. $this->_fit_height = $height;
  592. }
  593. /*
  594. * Store the horizontal page breaks on a worksheet.
  595. */
  596. function set_h_pagebreaks($breaks) {
  597. $this->_hbreaks=array_merge($this->_hbreaks, $breaks);
  598. }
  599. /*
  600. * Store the vertical page breaks on a worksheet.
  601. */
  602. function set_v_pagebreaks($breaks) {
  603. $this->_vbreaks=array_merge($this->_vbreaks, $breaks);
  604. }
  605. /*
  606. * Set the worksheet zoom factor.
  607. */
  608. function set_zoom($scale=100) {
  609. // Confine the scale to Excel's range
  610. if ($scale < 10 || $scale > 400) {
  611. trigger_error("Zoom factor $scale outside range: ".
  612. "10 <= zoom <= 400", E_USER_WARNING);
  613. $scale = 100;
  614. }
  615. $this->_zoom = $scale;
  616. }
  617. /*
  618. * Set the scale factor for the printed page.
  619. */
  620. function set_print_scale($scale=100) {
  621. // Confine the scale to Excel's range
  622. if ($scale < 10 || $scale > 400) {
  623. trigger_error("Print scale $scale outside range: ".
  624. "10 <= zoom <= 400", E_USER_WARNING);
  625. $scale = 100;
  626. }
  627. // Turn off "fit to page" option
  628. $this->_fit_page = 0;
  629. $this->_print_scale = $scale;
  630. }
  631. ###############################################################################
  632. #
  633. # write($row, $col, $token, $format)
  634. #
  635. # Parse $token call appropriate write method. $row and $column are zero
  636. # indexed. $format is optional.
  637. #
  638. # Returns: return value of called subroutine
  639. #
  640. function write() {
  641. $_=func_get_args();
  642. # Check for a cell reference in A1 notation and substitute row and column
  643. if (preg_match('/^\D/', $_[0])) {
  644. $_ = $this->_substitute_cellref($_);
  645. }
  646. $token = $_[2];
  647. # Match an array ref.
  648. if (is_array($token)) {
  649. return call_user_method_array('write_row', $this, $_);
  650. }
  651. # Match number
  652. if (preg_match('/^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?$/', $token)) {
  653. return call_user_method_array('write_number', $this, $_);
  654. }
  655. # Match http, https or ftp URL
  656. elseif (preg_match('|^[fh]tt?ps?://|', $token)) {
  657. return call_user_method_array('write_url', $this, $_);
  658. }
  659. # Match mailto:
  660. elseif (preg_match('/^mailto:/', $token)) {
  661. return call_user_method_array('write_url', $this, $_);
  662. }
  663. # Match internal or external sheet link
  664. elseif (preg_match('[^(?:in|ex)ternal:]', $token)) {
  665. return call_user_method_array('write_url', $this, $_);
  666. }
  667. # Match formula
  668. elseif (preg_match('/^=/', $token)) {
  669. return call_user_method_array('write_formula', $this, $_);
  670. }
  671. # Match blank
  672. elseif ($token == '') {
  673. array_splice($_, 2, 1); # remove the empty string from the parameter list
  674. return call_user_method_array('write_blank', $this, $_);
  675. }
  676. # Default: match string
  677. else {
  678. return call_user_method_array('write_string', $this, $_);
  679. }
  680. }
  681. ###############################################################################
  682. #
  683. # write_row($row, $col, $array_ref, $format)
  684. #
  685. # Write a row of data starting from ($row, $col). Call write_col() if any of
  686. # the elements of the array ref are in turn array refs. This allows the writing
  687. # of 1D or 2D arrays of data in one go.
  688. #
  689. # Returns: the first encountered error value or zero for no errors
  690. #
  691. function write_row() {
  692. $_=func_get_args();
  693. # Check for a cell reference in A1 notation and substitute row and column
  694. if (preg_match('/^\D/', $_[0])) {
  695. $_ = $this->_substitute_cellref($_);
  696. }
  697. # Catch non array refs passed by user.
  698. if (!is_array($_[2])) {
  699. trigger_error("Not an array ref in call to write_row()!", E_USER_ERROR);
  700. }
  701. list($row, $col, $tokens)=array_splice($_, 0, 3);
  702. $options = $_[0];
  703. $error = 0;
  704. foreach ($tokens as $token) {
  705. # Check for nested arrays
  706. if (is_array($token)) {
  707. $ret = $this->write_col($row, $col, $token, $options);
  708. } else {
  709. $ret = $this->write ($row, $col, $token, $options);
  710. }
  711. # Return only the first error encountered, if any.
  712. $error = $error || $ret;
  713. $col++;
  714. }
  715. return $error;
  716. }
  717. ###############################################################################
  718. #
  719. # _XF()
  720. #
  721. # Returns an index to the XF record in the workbook.
  722. # TODO
  723. #
  724. # Note: this is a function, not a method.
  725. #
  726. function _XF($row=false, $col=false, $format=false) {
  727. if ($format) {
  728. return $format->get_xf_index();
  729. } elseif (isset($this->_row_formats[$row])) {
  730. return $this->_row_formats[$row]->get_xf_index();
  731. } elseif (isset($this->_col_formats[$col])) {
  732. return $this->_col_formats[$col]->get_xf_index();
  733. } else {
  734. return 0x0F;
  735. }
  736. }
  737. ###############################################################################
  738. #
  739. # write_col($row, $col, $array_ref, $format)
  740. #
  741. # Write a column of data starting from ($row, $col). Call write_row() if any of
  742. # the elements of the array ref are in turn array refs. This allows the writing
  743. # of 1D or 2D arrays of data in one go.
  744. #
  745. # Returns: the first encountered error value or zero for no errors
  746. #
  747. function write_col() {
  748. $_=func_get_args();
  749. # Check for a cell reference in A1 notation and substitute row and column
  750. if (preg_match('/^\D/', $_[0])) {
  751. $_ = $this->_substitute_cellref($_);
  752. }
  753. # Catch non array refs passed by user.
  754. if (!is_array($_[2])) {
  755. trigger_error("Not an array ref in call to write_row()!", E_USER_ERROR);
  756. }
  757. $row = array_shift($_);
  758. $col = array_shift($_);
  759. $tokens = array_shift($_);
  760. $options = $_;
  761. $error = 0;
  762. foreach ($tokens as $token) {
  763. # write() will deal with any nested arrays
  764. $ret = $this->write($row, $col, $token, $options);
  765. # Return only the first error encountered, if any.
  766. $error = $error || $ret;
  767. $row++;
  768. }
  769. return $error;
  770. }
  771. ###############################################################################
  772. ###############################################################################
  773. #
  774. # Internal methods
  775. #
  776. ###############################################################################
  777. #
  778. # _append(), overloaded.
  779. #
  780. # Store Worksheet data in memory using the base class _append() or to a
  781. # temporary file, the default.
  782. #
  783. function _append($data) {
  784. if (func_num_args()>1) {
  785. trigger_error("writeexcel_worksheet::_append() ".
  786. "called with more than one argument", E_USER_ERROR);
  787. }
  788. if ($this->_using_tmpfile) {
  789. if ($this->_debug) {
  790. print "worksheet::_append() called:";
  791. for ($c=0;$c<strlen($data);$c++) {
  792. if ($c%16==0) {
  793. print "\n";
  794. }
  795. printf("%02X ", ord($data[$c]));
  796. }
  797. print "\n";
  798. }
  799. # Add CONTINUE records if necessary
  800. if (strlen($data) > $this->_limit) {
  801. $data = $this->_add_continue($data);
  802. }
  803. fputs($this->_filehandle, $data);
  804. $this->_datasize += strlen($data);
  805. } else {
  806. parent::_append($data);
  807. }
  808. }
  809. ###############################################################################
  810. #
  811. # _substitute_cellref()
  812. #
  813. # Substitute an Excel cell reference in A1 notation for zero based row and
  814. # column values in an argument list.
  815. #
  816. # Ex: ("A4", "Hello") is converted to (3, 0, "Hello").
  817. #
  818. // Exactly one array must be passed!
  819. function _substitute_cellref($_) {
  820. $cell = strtoupper(array_shift($_));
  821. # Convert a column range: 'A:A' or 'B:G'
  822. if (preg_match('/([A-I]?[A-Z]):([A-I]?[A-Z])/', $cell, $reg)) {
  823. list($dummy, $col1) = $this->_cell_to_rowcol($reg[1] .'1'); # Add a dummy row
  824. list($dummy, $col2) = $this->_cell_to_rowcol($reg[2] .'1'); # Add a dummy row
  825. return array_merge(array($col1, $col2), $_);
  826. }
  827. # Convert a cell range: 'A1:B7'
  828. if (preg_match('/\$?([A-I]?[A-Z]\$?\d+):\$?([A-I]?[A-Z]\$?\d+)/', $cell, $reg)) {
  829. list($row1, $col1) = $this->_cell_to_rowcol($reg[1]);
  830. list($row2, $col2) = $this->_cell_to_rowcol($reg[2]);
  831. return array_merge(array($row1, $col1, $row2, $col2), $_);
  832. }
  833. # Convert a cell reference: 'A1' or 'AD2000'
  834. if (preg_match('/\$?([A-I]?[A-Z]\$?\d+)/', $cell, $reg)) {
  835. list($row1, $col1) = $this->_cell_to_rowcol($reg[1]);
  836. return array_merge(array($row1, $col1), $_);
  837. }
  838. trigger_error("Unknown cell reference $cell", E_USER_ERROR);
  839. }
  840. ###############################################################################
  841. #
  842. # _cell_to_rowcol($cell_ref)
  843. #
  844. # Convert an Excel cell reference in A1 notation to a zero based row and column
  845. # reference; converts C1 to (0, 2).
  846. #
  847. # Returns: row, column
  848. #
  849. # TODO use functions in Utility.pm
  850. #
  851. function _cell_to_rowcol($cell) {
  852. preg_match('/\$?([A-I]?[A-Z])\$?(\d+)/', $cell, $reg);
  853. $col = $reg[1];
  854. $row = $reg[2];
  855. # Convert base26 column string to number
  856. # All your Base are belong to us.
  857. $chars = preg_split('//', $col, -1, PREG_SPLIT_NO_EMPTY);
  858. $expn = 0;
  859. $col = 0;
  860. while (sizeof($chars)) {
  861. $char = array_pop($chars); # LS char first
  862. $col += (ord($char) -ord('A') +1) * pow(26, $expn);
  863. $expn++;
  864. }
  865. # Convert 1-index to zero-index
  866. $row--;
  867. $col--;
  868. return array($row, $col);
  869. }
  870. /*
  871. * This is an internal method that is used to filter elements of the
  872. * array of pagebreaks used in the _store_hbreak() and _store_vbreak()
  873. * methods. It:
  874. * 1. Removes duplicate entries from the list.
  875. * 2. Sorts the list.
  876. * 3. Removes 0 from the list if present.
  877. */
  878. function _sort_pagebreaks($breaks) {
  879. // Hash slice to remove duplicates
  880. foreach ($breaks as $break) {
  881. $hash["$break"]=1;
  882. }
  883. // Numerical sort
  884. $breaks=array_keys($hash);
  885. sort($breaks, SORT_NUMERIC);
  886. // Remove zero
  887. if ($breaks[0] == 0) {
  888. array_shift($breaks);
  889. }
  890. // 1000 vertical pagebreaks appears to be an internal Excel 5 limit.
  891. // It is slightly higher in Excel 97/200, approx. 1026
  892. if (sizeof($breaks) > 1000) {
  893. array_splice($breaks, 1000);
  894. }
  895. return $breaks;
  896. }
  897. /*
  898. * Based on the algorithm provided by Daniel Rentz of OpenOffice.
  899. */
  900. function _encode_password($plaintext) {
  901. $chars=preg_split('//', $plaintext, -1, PREG_SPLIT_NO_EMPTY);
  902. $count=sizeof($chars);
  903. for ($c=0;$c<sizeof($chars);$c++) {
  904. $char=&$chars[$c];
  905. $char = ord($char) << ++$i;
  906. $low_15 = $char & 0x7fff;
  907. $high_15 = $char & 0x7fff << 15;
  908. $high_15 = $high_15 >> 15;
  909. $char = $low_15 | $high_15;
  910. }
  911. $password = 0x0000;
  912. foreach ($chars as $char) {
  913. $password ^= $char;
  914. }
  915. $password ^= $count;
  916. $password ^= 0xCE4B;
  917. return $password;
  918. }
  919. ###############################################################################
  920. ###############################################################################
  921. #
  922. # BIFF RECORDS
  923. #
  924. ###############################################################################
  925. #
  926. # write_number($row, $col, $num, $format)
  927. #
  928. # Write a double to the specified row and column (zero indexed).
  929. # An integer can be written as a double. Excel will display an
  930. # integer. $format is optional.
  931. #
  932. # Returns 0 : normal termination
  933. # -1 : insufficient number of arguments
  934. # -2 : row or column out of range
  935. #
  936. function write_number() {
  937. $_=func_get_args();
  938. # Check for a cell reference in A1 notation and substitute row and column
  939. if (preg_match('/^\D/', $_[0])) {
  940. $_ = $this->_substitute_cellref($_);
  941. }
  942. # Check the number of args
  943. if (sizeof($_) < 3) {
  944. return -1;
  945. }
  946. $record = 0x0203; # Record identifier
  947. $length = 0x000E; # Number of bytes to follow
  948. $row = $_[0]; # Zero indexed row
  949. $col = $_[1]; # Zero indexed column
  950. $num = $_[2];
  951. //!!!
  952. $xf = $this->_XF($row, $col, $_[3]); # The cell format
  953. # Check that row and col are valid and store max and min values
  954. if ($row >= $this->_xls_rowmax) { return -2; }
  955. if ($col >= $this->_xls_colmax) { return -2; }
  956. if ($row < $this->_dim_rowmin) { $this->_dim_rowmin = $row; }
  957. if ($row > $this->_dim_rowmax) { $this->_dim_rowmax = $row; }
  958. if ($col < $this->_dim_colmin) { $this->_dim_colmin = $col; }
  959. if ($col > $this->_dim_colmax) { $this->_dim_colmax = $col; }
  960. $header = pack("vv", $record, $length);
  961. $data = pack("vvv", $row, $col, $xf);
  962. $xl_double = pack("d", $num);
  963. if ($this->_byte_order) {
  964. //TODO
  965. $xl_double = strrev($xl_double);
  966. }
  967. $this->_append($header . $data . $xl_double);
  968. return 0;
  969. }
  970. ###############################################################################
  971. #
  972. # write_string ($row, $col, $string, $format)
  973. #
  974. # Write a string to the specified row and column (zero indexed).
  975. # NOTE: there is an Excel 5 defined limit of 255 characters.
  976. # $format is optional.
  977. # Returns 0 : normal termination
  978. # -1 : insufficient number of arguments
  979. # -2 : row or column out of range
  980. # -3 : long string truncated to 255 chars
  981. #
  982. function write_string() {
  983. $_=func_get_args();
  984. # Check for a cell reference in A1 notation and substitute row and column
  985. if (preg_match('/^\D/', $_[0])) {
  986. $_ = $this->_substitute_cellref($_);
  987. }
  988. # Check the number of args
  989. if (sizeof($_) < 3) {
  990. return -1;
  991. }
  992. $record = 0x0204; # Record identifier
  993. $length = 0x0008 + strlen($_[2]); # Bytes to follow
  994. $row = $_[0]; # Zero indexed row
  995. $col = $_[1]; # Zero indexed column
  996. $strlen = strlen($_[2]);
  997. $str = $_[2];
  998. $xf = $this->_XF($row, $col, $_[3]); # The cell format
  999. $str_error = 0;
  1000. # Check that row and col are valid and store max and min values
  1001. if ($row >= $this->_xls_rowmax) { return -2; }
  1002. if ($col >= $this->_xls_colmax) { return -2; }
  1003. if ($row < $this->_dim_rowmin) { $this->_dim_rowmin = $row; }
  1004. if ($row > $this->_dim_rowmax) { $this->_dim_rowmax = $row; }
  1005. if ($col < $this->_dim_colmin) { $this->_dim_colmin = $col; }
  1006. if ($col > $this->_dim_colmax) { $this->_dim_colmax = $col; }
  1007. if ($strlen > $this->_xls_strmax) { # LABEL must be < 255 chars
  1008. $str = substr($str, 0, $this->_xls_strmax);
  1009. $length = 0x0008 + $this->_xls_strmax;
  1010. $strlen = $this->_xls_strmax;
  1011. $str_error = -3;
  1012. }
  1013. $header = pack("vv", $record, $length);
  1014. $data = pack("vvvv", $row, $col, $xf, $strlen);
  1015. $this->_append($header . $data . $str);
  1016. return $str_error;
  1017. }
  1018. ###############################################################################
  1019. #
  1020. # write_blank($row, $col, $format)
  1021. #
  1022. # Write a blank cell to the specified row and column (zero indexed).
  1023. # A blank cell is used to specify formatting without adding a string
  1024. # or a number.
  1025. #
  1026. # A blank cell without a format serves no purpose. Therefore, we don't write
  1027. # a BLANK record unless a format is specified. This is mainly an optimisation
  1028. # for the write_row() and write_col() methods.
  1029. #
  1030. # Returns 0 : normal termination (including no format)
  1031. # -1 : insufficient number of arguments
  1032. # -2 : row or column out of range
  1033. #
  1034. function write_blank() {
  1035. $_=func_get_args();
  1036. # Check for a cell reference in A1 notation and substitute row and column
  1037. if (preg_match('/^\D/', $_[0])) {
  1038. $_ = $this->_substitute_cellref($_);
  1039. }
  1040. # Check the number of args
  1041. if (sizeof($_) < 2) {
  1042. return -1;
  1043. }
  1044. # Don't write a blank cell unless it has a format
  1045. if (!isset($_[2])) {
  1046. return 0;
  1047. }
  1048. $record = 0x0201; # Record identifier
  1049. $length = 0x0006; # Number of bytes to follow
  1050. $row = $_[0]; # Zero indexed row
  1051. $col = $_[1]; # Zero indexed column
  1052. $xf = $this->_XF($row, $col, $_[2]); # The cell format
  1053. # Check that row and col are valid and store max and min values
  1054. if ($row >= $this->_xls_rowmax) { return -2; }
  1055. if ($col >= $this->_xls_colmax) { return -2; }
  1056. if ($row < $this->_dim_rowmin) { $this->_dim_rowmin = $row; }
  1057. if ($row > $this->_dim_rowmax) { $this->_dim_rowmax = $row; }
  1058. if ($col < $this->_dim_colmin) { $this->_dim_colmin = $col; }
  1059. if ($col > $this->_dim_colmax) { $this->_dim_colmax = $col; }
  1060. $header = pack("vv", $record, $length);
  1061. $data = pack("vvv", $row, $col, $xf);
  1062. $this->_append($header . $data);
  1063. return 0;
  1064. }
  1065. ###############################################################################
  1066. #
  1067. # write_formula($row, $col, $formula, $format)
  1068. #
  1069. # Write a formula to the specified row and column (zero indexed).
  1070. # The textual representation of the formula is passed to the parser in
  1071. # Formula.pm which returns a packed binary string.
  1072. #
  1073. # $format is optional.
  1074. #
  1075. # Returns 0 : normal termination
  1076. # -1 : insufficient number of arguments
  1077. # -2 : row or column out of range
  1078. #
  1079. function write_formula() {
  1080. $_=func_get_args();
  1081. # Check for a cell reference in A1 notation and substitute row and column
  1082. if (preg_match('/^\D/', $_[0])) {
  1083. $_ = $this->_substitute_cellref($_);
  1084. }
  1085. # Check the number of args
  1086. if (sizeof($_) < 3) {
  1087. return -1;
  1088. }
  1089. $record = 0x0006; # Record identifier
  1090. $length=0; # Bytes to follow
  1091. $row = $_[0]; # Zero indexed row
  1092. $col = $_[1]; # Zero indexed column
  1093. $formula = $_[2]; # The formula text string
  1094. # Excel normally stores the last calculated value of the formula in $num.
  1095. # Clearly we are not in a position to calculate this a priori. Instead
  1096. # we set $num to zero and set the option flags in $grbit to ensure
  1097. # automatic calculation of the formula when the file is opened.
  1098. #
  1099. $xf = $this->_XF($row, $col, $_[3]); # The cell format
  1100. $num = 0x00; # Current value of formula
  1101. $grbit = 0x03; # Option flags
  1102. $chn = 0x0000; # Must be zero
  1103. # Check that row and col are valid and store max and min values
  1104. if ($row >= $this->_xls_rowmax) { return -2; }
  1105. if ($col >= $this->_xls_colmax) { return -2; }
  1106. if ($row < $this->_dim_rowmin) { $this->_dim_rowmin = $row; }
  1107. if ($row > $this->_dim_rowmax) { $this->_dim_rowmax = $row; }
  1108. if ($col < $this->_dim_colmin) { $this->_dim_colmin = $col; }
  1109. if ($col > $this->_dim_colmax) { $this->_dim_colmax = $col; }
  1110. # Strip the = sign at the beginning of the formula string
  1111. $formula = preg_replace('/^=/', "", $formula);
  1112. # Parse the formula using the parser in Formula.pm
  1113. $parser =& $this->_parser;
  1114. $formula = $parser->parse_formula($formula);
  1115. $formlen = strlen($formula); # Length of the binary string
  1116. $length = 0x16 + $formlen; # Length of the record data
  1117. $header = pack("vv", $record, $length);
  1118. $data = pack("vvvdvVv", $row, $col, $xf, $num,
  1119. $grbit, $chn, $formlen);
  1120. $this->_append($header . $data . $formula);
  1121. return 0;
  1122. }
  1123. ###############################################################################
  1124. #
  1125. # write_url($row, $col, $url, $string, $format)
  1126. #
  1127. # Write a hyperlink. This is comprised of two elements: the visible label and
  1128. # the invisible link. The visible label is the same as the link unless an
  1129. # alternative string is specified. The label is written using the
  1130. # write_string() method. Therefore the 255 characters string limit applies.
  1131. # $string and $format are optional and their order is interchangeable.
  1132. #
  1133. # The hyperlink can be to a http, ftp, mail, internal sheet, or external
  1134. # directory url.
  1135. #
  1136. # Returns 0 : normal termination
  1137. # -1 : insufficient number of arguments
  1138. # -2 : row or column out of range
  1139. # -3 : long string truncated to 255 chars
  1140. #
  1141. function write_url() {
  1142. $_=func_get_args();
  1143. # Check for a cell reference in A1 notation and substitute row and column
  1144. if (preg_match('/^\D/', $_[0])) {
  1145. $_ = $this->_substitute_cellref($_);
  1146. }
  1147. # Check the number of args
  1148. if (sizeof($_) < 3) {
  1149. return -1;
  1150. }
  1151. # Add start row and col to arg list
  1152. return call_user_method_array('write_url_range', $this,
  1153. array_merge(array($_[0], $_[1]), $_));
  1154. }
  1155. ###############################################################################
  1156. #
  1157. # write_url_range($row1, $col1, $row2, $col2, $url, $string, $format)
  1158. #
  1159. # This is the more general form of write_url(). It allows a hyperlink to be
  1160. # written to a range of cells. This function also decides the type of hyperlink
  1161. # to be written. These are either, Web (http, ftp, mailto), Internal
  1162. # (Sheet1!A1) or external ('c:\temp\foo.xls#Sheet1!A1').
  1163. #
  1164. # See also write_url() above for a general description and return values.
  1165. #
  1166. function write_url_range() {
  1167. $_=func_get_args();
  1168. # Check for a cell reference in A1 notation and substitute row and column
  1169. if (preg_match('/^\D/', $_[0])) {
  1170. $_ = $this->_substitute_cellref($_);
  1171. }
  1172. # Check the number of args
  1173. if (sizeof($_) < 5) {
  1174. return -1;
  1175. }
  1176. # Reverse the order of $string and $format if necessary.
  1177. //TODO ($_[5], $_[6]) = ($_[6], $_[5]) if (ref $_[5]);
  1178. $url = $_[4];
  1179. # Check for internal/external sheet links or default to web link
  1180. if (preg_match('[^internal:]', $url)) {
  1181. return call_user_method_array('_write_url_internal', $this, $_);
  1182. }
  1183. if (preg_match('[^external:]', $url)) {
  1184. return call_user_method_array('_write_url_external', $this, $_);
  1185. }
  1186. return call_user_method_array('_write_url_web', $this, $_);
  1187. }
  1188. ###############################################################################
  1189. #
  1190. # _write_url_web($row1, $col1, $row2, $col2, $url, $string, $format)
  1191. #
  1192. # Used to write http, ftp and mailto hyperlinks.
  1193. # The link type ($options) is 0x03 is the same as absolute dir ref without
  1194. # sheet. However it is differentiated by the $unknown2 data stream.
  1195. #
  1196. # See also write_url() above for a general description and return values.
  1197. #
  1198. function _write_url_web() {
  1199. $_=func_get_args();
  1200. $record = 0x01B8; # Record identifier
  1201. $length = 0x00000; # Bytes to follow
  1202. $row1 = $_[0]; # Start row
  1203. $col1 = $_[1]; # Start column
  1204. $row2 = $_[2]; # End row
  1205. $col2 = $_[3]; # End column
  1206. $url = $_[4]; # URL string
  1207. if (isset($_[5])) {
  1208. $str = $_[5]; # Alternative label
  1209. }
  1210. $xf = $_[6] ? $_[6] : $this->_url_format; # The cell format
  1211. # Write the visible label using the write_string() method.
  1212. if(!isset($str)) {
  1213. $str = $url;
  1214. }
  1215. $str_error = $this->write_string($row1, $col1, $str, $xf);
  1216. if ($str_error == -2) {
  1217. return $str_error;
  1218. }
  1219. # Pack the undocumented parts of the hyperlink stream
  1220. $unknown1 = pack("H*", "D0C9EA79F9BACE118C8200AA004BA90B02000000");
  1221. $unknown2 = pack("H*", "E0C9EA79F9BACE118C8200AA004BA90B");
  1222. # Pack the option flags
  1223. $options = pack("V", 0x03);
  1224. # Convert URL to a null terminated wchar string
  1225. $url = join("\0", preg_split("''", $url, -1, PREG_SPLIT_NO_EMPTY));
  1226. $url = $url . "\0\0\0";
  1227. # Pack the length of the URL
  1228. $url_len = pack("V", strlen($url));
  1229. # Calculate the data length
  1230. $length = 0x34 + strlen($url);
  1231. # Pack the header data
  1232. $header = pack("vv", $record, $length);
  1233. $data = pack("vvvv", $row1, $row2, $col1, $col2);
  1234. # Write the packed data
  1235. $this->_append($header.
  1236. $data.
  1237. $unknown1.
  1238. $options.
  1239. $unknown2.
  1240. $url_len.
  1241. $url);
  1242. return $str_error;
  1243. }
  1244. ###############################################################################
  1245. #
  1246. # _write_url_internal($row1, $col1, $row2, $col2, $url, $string, $format)
  1247. #
  1248. # Used to write internal reference hyperlinks such as "Sheet1!A1".
  1249. #
  1250. # See also write_url() above for a general description and return values.
  1251. #
  1252. function _write_url_internal() {
  1253. $_=func_get_args();
  1254. $record = 0x01B8; # Record identifier
  1255. $length = 0x00000; # Bytes to follow
  1256. $row1 = $_[0]; # Start row
  1257. $col1 = $_[1]; # Start column
  1258. $row2 = $_[2]; # End row
  1259. $col2 = $_[3]; # End column
  1260. $url = $_[4]; # URL string
  1261. if (isset($_[5])) {
  1262. $str = $_[5]; # Alternative label
  1263. }
  1264. $xf = $_[6] ? $_[6] : $this->_url_format; # The cell format
  1265. # Strip URL type
  1266. $url = preg_replace('s[^internal:]', '', $url);
  1267. # Write the visible label
  1268. if (!isset($str)) {
  1269. $str = $url;
  1270. }
  1271. $str_error = $this->write_string($row1, $col1, $str, $xf);
  1272. if ($str_error == -2) {
  1273. return $str_error;
  1274. }
  1275. # Pack the undocumented parts of the hyperlink stream
  1276. $unknown1 = pack("H*", "D0C9EA79F9BACE118C8200AA004BA90B02000000");
  1277. # Pack the option flags
  1278. $options = pack("V", 0x08);
  1279. # Convert the URL type and to a null terminated wchar string
  1280. $url = join("\0", preg_split("''", $url, -1, PREG_SPLIT_NO_EMPTY));
  1281. $url = $url . "\0\0\0";
  1282. # Pack the length of the URL as chars (not wchars)
  1283. $url_len = pack("V", int(strlen($url)/2));
  1284. # Calculate the data length
  1285. $length = 0x24 + strlen($url);
  1286. # Pack the header data
  1287. $header = pack("vv", $record, $length);
  1288. $data = pack("vvvv", $row1, $row2, $col1, $col2);
  1289. # Write the packed data
  1290. $this->_append($header.
  1291. $data.
  1292. $unknown1.
  1293. $options.
  1294. $url_len.
  1295. $url);
  1296. return $str_error;
  1297. }
  1298. ###############################################################################
  1299. #
  1300. # _write_url_external($row1, $col1, $row2, $col2, $url, $string, $format)
  1301. #
  1302. # Write links to external directory names such as 'c:\foo.xls',
  1303. # c:\foo.xls#Sheet1!A1', '../../foo.xls'. and '../../foo.xls#Sheet1!A1'.
  1304. #
  1305. # Note: Excel writes some relative links with the $dir_long string. We ignore
  1306. # these cases for the sake of simpler code.
  1307. #
  1308. # See also write_url() above for a general description and return values.
  1309. #
  1310. function _write_url_external() {
  1311. $_=func_get_args();
  1312. # Network drives are different. We will handle them separately
  1313. # MS/Novell network drives and shares start with \\
  1314. if (preg_match('[^external:\\\\]', $_[4])) {
  1315. return call_user_method_array('_write_url_external_net', $this, $_);
  1316. }
  1317. $record = 0x01B8; # Record identifier
  1318. $length = 0x00000; # Bytes to follow
  1319. $row1 = $_[0]; # Start row
  1320. $col1 = $_[1]; # Start column
  1321. $row2 = $_[2]; # End row
  1322. $col2 = $_[3]; # End column
  1323. $url = $_[4]; # URL string
  1324. if (isset($_[5])) {
  1325. $str = $_[5]; # Alternative label
  1326. }
  1327. $xf = $_[6] ? $_[6] : $this->_url_format; # The cell format
  1328. # Strip URL type and change Unix dir separator to Dos style (if needed)
  1329. #
  1330. $url = preg_replace('[^external:]', '', $url);
  1331. $url = preg_replace('[/]', "\\", $url);
  1332. # Write the visible label
  1333. if (!isset($str)) {
  1334. $str = preg_replace('[\#]', ' - ', $url);
  1335. }
  1336. $str_error = $this->write_string($row1, $col1, $str, $xf);
  1337. if ($str_error == -2) {
  1338. return $str_error;
  1339. }
  1340. # Determine if the link is relative or absolute:
  1341. # relative if link contains no dir separator, "somefile.xls"
  1342. # relative if link starts with up-dir, "..\..\somefile.xls"
  1343. # otherwise, absolute
  1344. #
  1345. $absolute = 0x02; # Bit mask
  1346. if (!preg_match('[\\]', $url)) {
  1347. $absolute = 0x00;
  1348. }
  1349. if (preg_match('[^\.\.\\]', $url)) {
  1350. $absolute = 0x00;
  1351. }
  1352. # Determine if the link contains a sheet reference and change some of the
  1353. # parameters accordingly.
  1354. # Split the dir name and sheet name (if it exists)
  1355. #
  1356. list($dir_long, $sheet) = preg_split('/\#/', $url);
  1357. $link_type = 0x01 | $absolute;
  1358. //!!!
  1359. if (isset($sheet)) {
  1360. $link_type |= 0x08;
  1361. $sheet_len = pack("V", length($sheet) + 0x01);
  1362. $sheet = join("\0", split('', $sheet));
  1363. $sheet .= "\0\0\0";
  1364. } else {
  1365. $sheet_len = '';
  1366. $sheet = '';
  1367. }
  1368. # Pack the link type
  1369. $link_type = pack("V", $link_type);
  1370. # Calculate the up-level dir count e.g.. (..\..\..\ == 3)
  1371. /* TODO
  1372. $up_count = 0;
  1373. $up_count++ while $dir_long =~ s[^\.\.\\][];
  1374. $up_count = pack("v", $up_count);
  1375. */
  1376. # S

Large files files are truncated, but you can click here to view the full file