免费注册 查看新帖 |

Chinaunix

  平台 论坛 博客 文库
最近访问板块 发新帖
查看: 1341 | 回复: 0

php读取excel文件内容 [复制链接]

论坛徽章:
0
发表于 2011-12-19 14:02 |显示全部楼层

1,reader.php文件内容

  1. <?php
  2. /* vim: set expandtab tabstop=4 shiftwidth=4 softtabstop=4: */

  3. /**
  4. * A class for reading Microsoft Excel Spreadsheets.
  5. *
  6. * Originally developed by Vadim Tkachenko under the name PHPExcelReader.
  7. * (http://sourceforge.net/projects/phpexcelreader)
  8. * Based on the Java version by Andy Khan (http://www.andykhan.com). Now
  9. * maintained by David Sanders. Reads only Biff 7 and Biff 8 formats.
  10. *
  11. * PHP versions 4 and 5
  12. *
  13. * LICENSE: This source file is subject to version 3.0 of the PHP license
  14. * that is available through the world-wide-web at the following URI:
  15. * http://www.php.net/license/3_0.txt. If you did not receive a copy of
  16. * the PHP License and are unable to obtain it through the web, please
  17. * send a note to license@php.net so we can mail you a copy immediately.
  18. *
  19. * @category Spreadsheet
  20. * @package Spreadsheet_Excel_Reader
  21. * @author Vadim Tkachenko <vt@apachephp.com>
  22. * @license http://www.php.net/license/3_0.txt PHP License 3.0
  23. * @version CVS: $Id: reader.php 19 2007-03-13 12:42:41Z shangxiao $
  24. * @link http://pear.php.net/package/Spreadsheet_Excel_Reader
  25. * @see OLE, Spreadsheet_Excel_Writer
  26. */


  27. //require_once 'PEAR.php';

  28. require_once 'OLERead.php';
  29. //require_once 'OLE.php';


  30. define('SPREADSHEET_EXCEL_READER_BIFF8', 0x600);
  31. define('SPREADSHEET_EXCEL_READER_BIFF7', 0x500);
  32. define('SPREADSHEET_EXCEL_READER_WORKBOOKGLOBALS', 0x5);
  33. define('SPREADSHEET_EXCEL_READER_WORKSHEET', 0x10);

  34. define('SPREADSHEET_EXCEL_READER_TYPE_BOF', 0x809);
  35. define('SPREADSHEET_EXCEL_READER_TYPE_EOF', 0x0a);
  36. define('SPREADSHEET_EXCEL_READER_TYPE_BOUNDSHEET', 0x85);
  37. define('SPREADSHEET_EXCEL_READER_TYPE_DIMENSION', 0x200);
  38. define('SPREADSHEET_EXCEL_READER_TYPE_ROW', 0x208);
  39. define('SPREADSHEET_EXCEL_READER_TYPE_DBCELL', 0xd7);
  40. define('SPREADSHEET_EXCEL_READER_TYPE_FILEPASS', 0x2f);
  41. define('SPREADSHEET_EXCEL_READER_TYPE_NOTE', 0x1c);
  42. define('SPREADSHEET_EXCEL_READER_TYPE_TXO', 0x1b6);
  43. define('SPREADSHEET_EXCEL_READER_TYPE_RK', 0x7e);
  44. define('SPREADSHEET_EXCEL_READER_TYPE_RK2', 0x27e);
  45. define('SPREADSHEET_EXCEL_READER_TYPE_MULRK', 0xbd);
  46. define('SPREADSHEET_EXCEL_READER_TYPE_MULBLANK', 0xbe);
  47. define('SPREADSHEET_EXCEL_READER_TYPE_INDEX', 0x20b);
  48. define('SPREADSHEET_EXCEL_READER_TYPE_SST', 0xfc);
  49. define('SPREADSHEET_EXCEL_READER_TYPE_EXTSST', 0xff);
  50. define('SPREADSHEET_EXCEL_READER_TYPE_CONTINUE', 0x3c);
  51. define('SPREADSHEET_EXCEL_READER_TYPE_LABEL', 0x204);
  52. define('SPREADSHEET_EXCEL_READER_TYPE_LABELSST', 0xfd);
  53. define('SPREADSHEET_EXCEL_READER_TYPE_NUMBER', 0x203);
  54. define('SPREADSHEET_EXCEL_READER_TYPE_NAME', 0x18);
  55. define('SPREADSHEET_EXCEL_READER_TYPE_ARRAY', 0x221);
  56. define('SPREADSHEET_EXCEL_READER_TYPE_STRING', 0x207);
  57. define('SPREADSHEET_EXCEL_READER_TYPE_FORMULA', 0x406);
  58. define('SPREADSHEET_EXCEL_READER_TYPE_FORMULA2', 0x6);
  59. define('SPREADSHEET_EXCEL_READER_TYPE_FORMAT', 0x41e);
  60. define('SPREADSHEET_EXCEL_READER_TYPE_XF', 0xe0);
  61. define('SPREADSHEET_EXCEL_READER_TYPE_BOOLERR', 0x205);
  62. define('SPREADSHEET_EXCEL_READER_TYPE_UNKNOWN', 0xffff);
  63. define('SPREADSHEET_EXCEL_READER_TYPE_NINETEENFOUR', 0x22);
  64. define('SPREADSHEET_EXCEL_READER_TYPE_MERGEDCELLS', 0xE5);

  65. define('SPREADSHEET_EXCEL_READER_UTCOFFSETDAYS' , 25569);
  66. define('SPREADSHEET_EXCEL_READER_UTCOFFSETDAYS1904', 24107);
  67. define('SPREADSHEET_EXCEL_READER_MSINADAY', 86400);
  68. //define('SPREADSHEET_EXCEL_READER_MSINADAY', 24 * 60 * 60);


  69. //define('SPREADSHEET_EXCEL_READER_DEF_NUM_FORMAT', "%.2f");

  70. define('SPREADSHEET_EXCEL_READER_DEF_NUM_FORMAT', "%s");


  71. /*
  72. * Place includes, constant defines and $_GLOBAL settings here.
  73. * Make sure they have appropriate docblocks to avoid phpDocumentor
  74. * construing they are documented by the page-level docblock.
  75. */

  76. /**
  77. * A class for reading Microsoft Excel Spreadsheets.
  78. *
  79. * Originally developed by Vadim Tkachenko under the name PHPExcelReader.
  80. * (http://sourceforge.net/projects/phpexcelreader)
  81. * Based on the Java version by Andy Khan (http://www.andykhan.com). Now
  82. * maintained by David Sanders. Reads only Biff 7 and Biff 8 formats.
  83. *
  84. * @category Spreadsheet
  85. * @package Spreadsheet_Excel_Reader
  86. * @author Vadim Tkachenko <vt@phpapache.com>
  87. * @copyright 1997-2005 The PHP Group
  88. * @license http://www.php.net/license/3_0.txt PHP License 3.0
  89. * @version Release: @package_version@
  90. * @link http://pear.php.net/package/PackageName
  91. * @see OLE, Spreadsheet_Excel_Writer
  92. */
  93. class Spreadsheet_Excel_Reader
  94. {
  95.     /**
  96.      * Array of worksheets found
  97.      *
  98.      * @var array
  99.      * @access public
  100.      */
  101.     var $boundsheets = array();

  102.     /**
  103.      * Array of format records found
  104.      *
  105.      * @var array
  106.      * @access public
  107.      */
  108.     var $formatRecords = array();

  109.     /**
  110.      * todo
  111.      *
  112.      * @var array
  113.      * @access public
  114.      */
  115.     var $sst = array();

  116.     /**
  117.      * Array of worksheets
  118.      *
  119.      * The data is stored in 'cells' and the meta-data is stored in an array
  120.      * called 'cellsInfo'
  121.      *
  122.      * Example:
  123.      *
  124.      * $sheets --> 'cells' --> row --> column --> Interpreted value
  125.      * --> 'cellsInfo' --> row --> column --> 'type' - Can be 'date', 'number', or 'unknown'
  126.      * --> 'raw' - The raw data that Excel stores for that data cell
  127.      *
  128.      * @var array
  129.      * @access public
  130.      */
  131.     var $sheets = array();

  132.     /**
  133.      * The data returned by OLE
  134.      *
  135.      * @var string
  136.      * @access public
  137.      */
  138.     var $data;

  139.     /**
  140.      * OLE object for reading the file
  141.      *
  142.      * @var OLE object
  143.      * @access private
  144.      */
  145.     var $_ole;

  146.     /**
  147.      * Default encoding
  148.      *
  149.      * @var string
  150.      * @access private
  151.      */
  152.     var $_defaultEncoding;

  153.     /**
  154.      * Default number format
  155.      *
  156.      * @var integer
  157.      * @access private
  158.      */
  159.     var $_defaultFormat = SPREADSHEET_EXCEL_READER_DEF_NUM_FORMAT;

  160.     /**
  161.      * todo
  162.      * List of formats to use for each column
  163.      *
  164.      * @var array
  165.      * @access private
  166.      */
  167.     var $_columnsFormat = array();

  168.     /**
  169.      * todo
  170.      *
  171.      * @var integer
  172.      * @access private
  173.      */
  174.     var $_rowoffset = 1;

  175.     /**
  176.      * todo
  177.      *
  178.      * @var integer
  179.      * @access private
  180.      */
  181.     var $_coloffset = 1;

  182.     /**
  183.      * List of default date formats used by Excel
  184.      *
  185.      * @var array
  186.      * @access public
  187.      */
  188.     var $dateFormats = array (
  189.         0xe => "d/m/Y",
  190.         0xf => "d-M-Y",
  191.         0x10 => "d-M",
  192.         0x11 => "M-Y",
  193.         0x12 => "h:i a",
  194.         0x13 => "h:i:s a",
  195.         0x14 => "H:i",
  196.         0x15 => "H:i:s",
  197.         0x16 => "d/m/Y H:i",
  198.         0x2d => "i:s",
  199.         0x2e => "H:i:s",
  200.         0x2f => "i:s.S");

  201.     /**
  202.      * Default number formats used by Excel
  203.      *
  204.      * @var array
  205.      * @access public
  206.      */
  207.     var $numberFormats = array(
  208.         0x1 => "%1.0f", // "0"

  209.         0x2 => "%1.2f", // "0.00",

  210.         0x3 => "%1.0f", //"#,##0",

  211.         0x4 => "%1.2f", //"#,##0.00",

  212.         0x5 => "%1.0f", /*"$#,##0;($#,##0)",*/
  213.         0x6 => '$%1.0f', /*"$#,##0;($#,##0)",*/
  214.         0x7 => '$%1.2f', //"$#,##0.00;($#,##0.00)",

  215.         0x8 => '$%1.2f', //"$#,##0.00;($#,##0.00)",

  216.         0x9 => '%1.0f%%', // "0%"

  217.         0xa => '%1.2f%%', // "0.00%"

  218.         0xb => '%1.2f', // 0.00E00",

  219.         0x25 => '%1.0f', // "#,##0;(#,##0)",

  220.         0x26 => '%1.0f', //"#,##0;(#,##0)",

  221.         0x27 => '%1.2f', //"#,##0.00;(#,##0.00)",

  222.         0x28 => '%1.2f', //"#,##0.00;(#,##0.00)",

  223.         0x29 => '%1.0f', //"#,##0;(#,##0)",

  224.         0x2a => '$%1.0f', //"$#,##0;($#,##0)",

  225.         0x2b => '%1.2f', //"#,##0.00;(#,##0.00)",

  226.         0x2c => '$%1.2f', //"$#,##0.00;($#,##0.00)",

  227.         0x30 => '%1.0f'); //"##0.0E0";


  228.     // }}}

  229.     // {{{ Spreadsheet_Excel_Reader()


  230.     /**
  231.      * Constructor
  232.      *
  233.      * Some basic initialisation
  234.      */
  235.     function Spreadsheet_Excel_Reader()
  236.     {
  237.         $this->_ole =& new OLERead();
  238.         $this->setUTFEncoder('iconv');
  239.     }

  240.     // }}}

  241.     // {{{ setOutputEncoding()


  242.     /**
  243.      * Set the encoding method
  244.      *
  245.      * @param string Encoding to use
  246.      * @access public
  247.      */
  248.     function setOutputEncoding($encoding)
  249.     {
  250.         $this->_defaultEncoding = $encoding;
  251.     }

  252.     // }}}

  253.     // {{{ setUTFEncoder()


  254.     /**
  255.      * $encoder = 'iconv' or 'mb'
  256.      * set iconv if you would like use 'iconv' for encode UTF-16LE to your encoding
  257.      * set mb if you would like use 'mb_convert_encoding' for encode UTF-16LE to your encoding
  258.      *
  259.      * @access public
  260.      * @param string Encoding type to use. Either 'iconv' or 'mb'
  261.      */
  262.     function setUTFEncoder($encoder = 'iconv')
  263.     {
  264.         $this->_encoderFunction = '';

  265.         if ($encoder == 'iconv') {
  266.             $this->_encoderFunction = function_exists('iconv') ? 'iconv' : '';
  267.         } elseif ($encoder == 'mb') {
  268.             $this->_encoderFunction = function_exists('mb_convert_encoding') ?
  269.                                       'mb_convert_encoding' :
  270.                                       '';
  271.         }
  272.     }

  273.     // }}}

  274.     // {{{ setRowColOffset()


  275.     /**
  276.      * todo
  277.      *
  278.      * @access public
  279.      * @param offset
  280.      */
  281.     function setRowColOffset($iOffset)
  282.     {
  283.         $this->_rowoffset = $iOffset;
  284.         $this->_coloffset = $iOffset;
  285.     }

  286.     // }}}

  287.     // {{{ setDefaultFormat()


  288.     /**
  289.      * Set the default number format
  290.      *
  291.      * @access public
  292.      * @param Default format
  293.      */
  294.     function setDefaultFormat($sFormat)
  295.     {
  296.         $this->_defaultFormat = $sFormat;
  297.     }

  298.     // }}}

  299.     // {{{ setColumnFormat()


  300.     /**
  301.      * Force a column to use a certain format
  302.      *
  303.      * @access public
  304.      * @param integer Column number
  305.      * @param string Format
  306.      */
  307.     function setColumnFormat($column, $sFormat)
  308.     {
  309.         $this->_columnsFormat[$column] = $sFormat;
  310.     }


  311.     // }}}

  312.     // {{{ read()


  313.     /**
  314.      * Read the spreadsheet file using OLE, then parse
  315.      *
  316.      * @access public
  317.      * @param filename
  318.      * @todo return a valid value
  319.      */
  320.     function read($sFileName)
  321.     {
  322.     /*
  323.         require_once 'OLE.php';
  324.         $ole = new OLE();
  325.         $ole->read($sFileName);

  326.         foreach ($ole->_list as $i => $pps) {
  327.             if (($pps->Name == 'Workbook' || $pps->Name == 'Book') &&
  328.                 $pps->Size >= SMALL_BLOCK_THRESHOLD) {

  329.                 $this->data = $ole->getData($i, 0, $ole->getDataLength($i));
  330.             } elseif ($pps->Name == 'Root Entry') {
  331.                 $this->data = $ole->getData($i, 0, $ole->getDataLength($i));
  332.             }
  333.             //var_dump(strlen($ole->getData($i, 0, $ole->getDataLength($i))), $pps->Name, md5($this->data), $ole->getDataLength($i));
  334.         }
  335. //exit;
  336.         $this->_parse();

  337.         return sizeof($this->sheets) > 0;
  338.     */

  339.         $res = $this->_ole->read($sFileName);

  340.         // oops, something goes wrong (Darko Miljanovic)

  341.         if($res === false) {
  342.             // check error code

  343.             if($this->_ole->error == 1) {
  344.             // bad file

  345.                 die('The filename ' . $sFileName . ' is not readable');
  346.             }
  347.             // check other error codes here (eg bad fileformat, etc...)

  348.         }

  349.         $this->data = $this->_ole->getWorkBook();


  350.         /*
  351.         $res = $this->_ole->read($sFileName);

  352.         if ($this->isError($res)) {
  353. // var_dump($res);
  354.             return $this->raiseError($res);
  355.         }

  356.         $total = $this->_ole->ppsTotal();
  357.         for ($i = 0; $i < $total; $i++) {
  358.             if ($this->_ole->isFile($i)) {
  359.                 $type = unpack("v", $this->_ole->getData($i, 0, 2));
  360.                 if ($type[''] == 0x0809) { // check if it's a BIFF stream
  361.                     $this->_index = $i;
  362.                     $this->data = $this->_ole->getData($i, 0, $this->_ole->getDataLength($i));
  363.                     break;
  364.                 }
  365.             }
  366.         }

  367.         if ($this->_index === null) {
  368.             return $this->raiseError("$file doesn't seem to be an Excel file");
  369.         }

  370.         */

  371.     //echo "data =".$this->data;

  372.         //$this->readRecords();

  373.         $this->_parse();
  374.     }


  375.     // }}}

  376.     // {{{ _parse()


  377.     /**
  378.      * Parse a workbook
  379.      *
  380.      * @access private
  381.      * @return bool
  382.      */
  383.     function _parse()
  384.     {
  385.         $pos = 0;

  386.         $code = ord($this->data[$pos]) | ord($this->data[$pos+1])<<8;
  387.         $length = ord($this->data[$pos+2]) | ord($this->data[$pos+3])<<8;

  388.         $version = ord($this->data[$pos + 4]) | ord($this->data[$pos + 5])<<8;
  389.         $substreamType = ord($this->data[$pos + 6]) | ord($this->data[$pos + 7])<<8;
  390.         //echo "Start parse code=".base_convert($code,10,16)." version=".base_convert($version,10,16)." substreamType=".base_convert($substreamType,10,16).""."\n";


  391.         if (($version != SPREADSHEET_EXCEL_READER_BIFF8) &&
  392.             ($version != SPREADSHEET_EXCEL_READER_BIFF7)) {
  393.             return false;
  394.         }

  395.         if ($substreamType != SPREADSHEET_EXCEL_READER_WORKBOOKGLOBALS){
  396.             return false;
  397.         }

  398.         //print_r($rec);

  399.         $pos += $length + 4;

  400.         $code = ord($this->data[$pos]) | ord($this->data[$pos+1])<<8;
  401.         $length = ord($this->data[$pos+2]) | ord($this->data[$pos+3])<<8;

  402.         while ($code != SPREADSHEET_EXCEL_READER_TYPE_EOF) {
  403.             switch ($code) {
  404.                 case SPREADSHEET_EXCEL_READER_TYPE_SST:
  405.                     //echo "Type_SST\n";

  406.                      $spos = $pos + 4;
  407.                      $limitpos = $spos + $length;
  408.                      $uniqueStrings = $this->_GetInt4d($this->data, $spos+4);
  409.                                                 $spos += 8;
  410.                                        for ($i = 0; $i < $uniqueStrings; $i++) {
  411.         // Read in the number of characters

  412.                                                 if ($spos == $limitpos) {
  413.                                                 $opcode = ord($this->data[$spos]) | ord($this->data[$spos+1])<<8;
  414.                                                 $conlength = ord($this->data[$spos+2]) | ord($this->data[$spos+3])<<8;
  415.                                                         if ($opcode != 0x3c) {
  416.                                                                 return -1;
  417.                                                         }
  418.                                                 $spos += 4;
  419.                                                 $limitpos = $spos + $conlength;
  420.                                                 }
  421.                                                 $numChars = ord($this->data[$spos]) | (ord($this->data[$spos+1]) << 8);
  422.                                                 //echo "i = $i pos = $pos numChars = $numChars ";

  423.                                                 $spos += 2;
  424.                                                 $optionFlags = ord($this->data[$spos]);
  425.                                                 $spos++;
  426.                                         $asciiEncoding = (($optionFlags & 0x01) == 0) ;
  427.                                                 $extendedString = ( ($optionFlags & 0x04) != 0);

  428.                                                 // See if string contains formatting information

  429.                                                 $richString = ( ($optionFlags & 0x08) != 0);

  430.                                                 if ($richString) {
  431.                                         // Read in the crun

  432.                                                         $formattingRuns = ord($this->data[$spos]) | (ord($this->data[$spos+1]) << 8);
  433.                                                         $spos += 2;
  434.                                                 }

  435.                                                 if ($extendedString) {
  436.                                                   // Read in cchExtRst

  437.                                                   $extendedRunLength = $this->_GetInt4d($this->data, $spos);
  438.                                                   $spos += 4;
  439.                                                 }

  440.                                                 $len = ($asciiEncoding)? $numChars : $numChars*2;
  441.                                                 if ($spos + $len < $limitpos) {
  442.                                                                 $retstr = substr($this->data, $spos, $len);
  443.                                                                 $spos += $len;
  444.                                                 }else{
  445.                                                         // found countinue

  446.                                                         $retstr = substr($this->data, $spos, $limitpos - $spos);
  447.                                                         $bytesRead = $limitpos - $spos;
  448.                                                         $charsLeft = $numChars - (($asciiEncoding) ? $bytesRead : ($bytesRead / 2));
  449.                                                         $spos = $limitpos;

  450.                                                          while ($charsLeft > 0){
  451.                                                                 $opcode = ord($this->data[$spos]) | ord($this->data[$spos+1])<<8;
  452.                                                                 $conlength = ord($this->data[$spos+2]) | ord($this->data[$spos+3])<<8;
  453.                                                                         if ($opcode != 0x3c) {
  454.                                                                                 return -1;
  455.                                                                         }
  456.                                                                 $spos += 4;
  457.                                                                 $limitpos = $spos + $conlength;
  458.                                                                 $option = ord($this->data[$spos]);
  459.                                                                 $spos += 1;
  460.                                                                   if ($asciiEncoding && ($option == 0)) {
  461.                                                                                 $len = min($charsLeft, $limitpos - $spos); // min($charsLeft, $conlength);

  462.                                                                     $retstr .= substr($this->data, $spos, $len);
  463.                                                                     $charsLeft -= $len;
  464.                                                                     $asciiEncoding = true;
  465.                                                                   }elseif (!$asciiEncoding && ($option != 0)){
  466.                                                                                 $len = min($charsLeft * 2, $limitpos - $spos); // min($charsLeft, $conlength);

  467.                                                                     $retstr .= substr($this->data, $spos, $len);
  468.                                                                     $charsLeft -= $len/2;
  469.                                                                     $asciiEncoding = false;
  470.                                                                   }elseif (!$asciiEncoding && ($option == 0)) {
  471.                                                                 // Bummer - the string starts off as Unicode, but after the

  472.                                                                 // continuation it is in straightforward ASCII encoding

  473.                                                                                 $len = min($charsLeft, $limitpos - $spos); // min($charsLeft, $conlength);

  474.                                                                         for ($j = 0; $j < $len; $j++) {
  475.                                                                  $retstr .= $this->data[$spos + $j].chr(0);
  476.                                                                 }
  477.                                                             $charsLeft -= $len;
  478.                                                                 $asciiEncoding = false;
  479.                                                                   }else{
  480.                                                             $newstr = '';
  481.                                                                     for ($j = 0; $j < strlen($retstr); $j++) {
  482.                                                                       $newstr = $retstr[$j].chr(0);
  483.                                                                     }
  484.                                                                     $retstr = $newstr;
  485.                                                                                 $len = min($charsLeft * 2, $limitpos - $spos); // min($charsLeft, $conlength);

  486.                                                                     $retstr .= substr($this->data, $spos, $len);
  487.                                                                     $charsLeft -= $len/2;
  488.                                                                     $asciiEncoding = false;
  489.                                                                         //echo "Izavrat\n";

  490.                                                                   }
  491.                                                           $spos += $len;

  492.                                                          }
  493.                                                 }
  494.                                                 $retstr = ($asciiEncoding) ? $retstr : $this->_encodeUTF16($retstr);
  495. // echo "Str $i = $retstr\n";

  496.                                         if ($richString){
  497.                                                   $spos += 4 * $formattingRuns;
  498.                                                 }

  499.                                                 // For extended strings, skip over the extended string data

  500.                                                 if ($extendedString) {
  501.                                                   $spos += $extendedRunLength;
  502.                                                 }
  503.                                                         //if ($retstr == 'Derby'){

  504.                                                         // echo "bb\n";

  505.                                                         //}

  506.                                                 $this->sst[]=$retstr;
  507.                                        }
  508.                     /*$continueRecords = array();
  509.                     while ($this->getNextCode() == Type_CONTINUE) {
  510.                         $continueRecords[] = &$this->nextRecord();
  511.                     }
  512.                     //echo " 1 Type_SST\n";
  513.                     $this->shareStrings = new SSTRecord($r, $continueRecords);
  514.                     //print_r($this->shareStrings->strings);
  515.                      */
  516.                      // echo 'SST read: '.($time_end-$time_start)."\n";

  517.                     break;

  518.                 case SPREADSHEET_EXCEL_READER_TYPE_FILEPASS:
  519.                     return false;
  520.                     break;
  521.                 case SPREADSHEET_EXCEL_READER_TYPE_NAME:
  522.                     //echo "Type_NAME\n";

  523.                     break;
  524.                 case SPREADSHEET_EXCEL_READER_TYPE_FORMAT:
  525.                         $indexCode = ord($this->data[$pos+4]) | ord($this->data[$pos+5]) << 8;

  526.                         if ($version == SPREADSHEET_EXCEL_READER_BIFF8) {
  527.                             $numchars = ord($this->data[$pos+6]) | ord($this->data[$pos+7]) << 8;
  528.                             if (ord($this->data[$pos+8]) == 0){
  529.                                 $formatString = substr($this->data, $pos+9, $numchars);
  530.                             } else {
  531.                                 $formatString = substr($this->data, $pos+9, $numchars*2);
  532.                             }
  533.                         } else {
  534.                             $numchars = ord($this->data[$pos+6]);
  535.                             $formatString = substr($this->data, $pos+7, $numchars*2);
  536.                         }

  537.                     $this->formatRecords[$indexCode] = $formatString;
  538.                    // echo "Type.FORMAT\n";

  539.                     break;
  540.                 case SPREADSHEET_EXCEL_READER_TYPE_XF:
  541.                         //global $dateFormats, $numberFormats;

  542.                         $indexCode = ord($this->data[$pos+6]) | ord($this->data[$pos+7]) << 8;
  543.                         //echo "\nType.XF ".count($this->formatRecords['xfrecords'])." $indexCode ";

  544.                         if (array_key_exists($indexCode, $this->dateFormats)) {
  545.                             //echo "isdate ".$dateFormats[$indexCode];

  546.                             $this->formatRecords['xfrecords'][] = array(
  547.                                     'type' => 'date',
  548.                                     'format' => $this->dateFormats[$indexCode]
  549.                                     );
  550.                         }elseif (array_key_exists($indexCode, $this->numberFormats)) {
  551.                         //echo "isnumber ".$this->numberFormats[$indexCode];

  552.                             $this->formatRecords['xfrecords'][] = array(
  553.                                     'type' => 'number',
  554.                                     'format' => $this->numberFormats[$indexCode]
  555.                                     );
  556.                         }else{
  557.                             $isdate = FALSE;
  558.                             if ($indexCode > 0){
  559.                                 if (isset($this->formatRecords[$indexCode]))
  560.                                     $formatstr = $this->formatRecords[$indexCode];
  561.                                 //echo '.other.';

  562.                                 //echo "\ndate-time=$formatstr=\n";

  563.                                 if ($formatstr)
  564.                                 if (preg_match("/[^hmsday\/\-:\s]/i", $formatstr) == 0) { // found day and time format

  565.                                     $isdate = TRUE;
  566.                                     $formatstr = str_replace('mm', 'i', $formatstr);
  567.                                     $formatstr = str_replace('h', 'H', $formatstr);
  568.                                     //echo "\ndate-time $formatstr \n";

  569.                                 }
  570.                             }

  571.                             if ($isdate){
  572.                                 $this->formatRecords['xfrecords'][] = array(
  573.                                         'type' => 'date',
  574.                                         'format' => $formatstr,
  575.                                         );
  576.                             }else{
  577.                                 $this->formatRecords['xfrecords'][] = array(
  578.                                         'type' => 'other',
  579.                                         'format' => '',
  580.                                         'code' => $indexCode
  581.                                         );
  582.                             }
  583.                         }
  584.                         //echo "\n";

  585.                     break;
  586.                 case SPREADSHEET_EXCEL_READER_TYPE_NINETEENFOUR:
  587.                     //echo "Type.NINETEENFOUR\n";

  588.                     $this->nineteenFour = (ord($this->data[$pos+4]) == 1);
  589.                     break;
  590.                 case SPREADSHEET_EXCEL_READER_TYPE_BOUNDSHEET:
  591.                     //echo "Type.BOUNDSHEET\n";

  592.                         $rec_offset = $this->_GetInt4d($this->data, $pos+4);
  593.                         $rec_typeFlag = ord($this->data[$pos+8]);
  594.                         $rec_visibilityFlag = ord($this->data[$pos+9]);
  595.                         $rec_length = ord($this->data[$pos+10]);

  596.                         if ($version == SPREADSHEET_EXCEL_READER_BIFF8){
  597.                             $chartype = ord($this->data[$pos+11]);
  598.                             if ($chartype == 0){
  599.                                 $rec_name = substr($this->data, $pos+12, $rec_length);
  600.                             } else {
  601.                                 $rec_name = $this->_encodeUTF16(substr($this->data, $pos+12, $rec_length*2));
  602.                             }
  603.                         }elseif ($version == SPREADSHEET_EXCEL_READER_BIFF7){
  604.                                 $rec_name = substr($this->data, $pos+11, $rec_length);
  605.                         }
  606.                     $this->boundsheets[] = array('name'=>$rec_name,
  607.                                                  'offset'=>$rec_offset);

  608.                     break;

  609.             }

  610.             //echo "Code = ".base_convert($r['code'],10,16)."\n";

  611.             $pos += $length + 4;
  612.             $code = ord($this->data[$pos]) | ord($this->data[$pos+1])<<8;
  613.             $length = ord($this->data[$pos+2]) | ord($this->data[$pos+3])<<8;

  614.             //$r = &$this->nextRecord();

  615.             //echo "1 Code = ".base_convert($r['code'],10,16)."\n";

  616.         }

  617.         foreach ($this->boundsheets as $key=>$val){
  618.             $this->sn = $key;
  619.             $this->_parsesheet($val['offset']);
  620.         }
  621.         return true;

  622.     }

  623.     /**
  624.      * Parse a worksheet
  625.      *
  626.      * @access private
  627.      * @param todo
  628.      * @todo fix return codes
  629.      */
  630.     function _parsesheet($spos)
  631.     {
  632.         $cont = true;
  633.         // read BOF

  634.         $code = ord($this->data[$spos]) | ord($this->data[$spos+1])<<8;
  635.         $length = ord($this->data[$spos+2]) | ord($this->data[$spos+3])<<8;

  636.         $version = ord($this->data[$spos + 4]) | ord($this->data[$spos + 5])<<8;
  637.         $substreamType = ord($this->data[$spos + 6]) | ord($this->data[$spos + 7])<<8;

  638.         if (($version != SPREADSHEET_EXCEL_READER_BIFF8) && ($version != SPREADSHEET_EXCEL_READER_BIFF7)) {
  639.             return -1;
  640.         }

  641.         if ($substreamType != SPREADSHEET_EXCEL_READER_WORKSHEET){
  642.             return -2;
  643.         }
  644.         //echo "Start parse code=".base_convert($code,10,16)." version=".base_convert($version,10,16)." substreamType=".base_convert($substreamType,10,16).""."\n";

  645.         $spos += $length + 4;
  646.         //var_dump($this->formatRecords);

  647.     //echo "code $code $length";

  648.         while($cont) {
  649.             //echo "mem= ".memory_get_usage()."\n";

  650. // $r = &$this->file->nextRecord();

  651.             $lowcode = ord($this->data[$spos]);
  652.             if ($lowcode == SPREADSHEET_EXCEL_READER_TYPE_EOF) break;
  653.             $code = $lowcode | ord($this->data[$spos+1])<<8;
  654.             $length = ord($this->data[$spos+2]) | ord($this->data[$spos+3])<<8;
  655.             $spos += 4;
  656.             $this->sheets[$this->sn]['maxrow'] = $this->_rowoffset - 1;
  657.             $this->sheets[$this->sn]['maxcol'] = $this->_coloffset - 1;
  658.             //echo "Code=".base_convert($code,10,16)." $code\n";

  659.             unset($this->rectype);
  660.             $this->multiplier = 1; // need for format with %

  661.             switch ($code) {
  662.                 case SPREADSHEET_EXCEL_READER_TYPE_DIMENSION:
  663.                     //echo 'Type_DIMENSION ';

  664.                     if (!isset($this->numRows)) {
  665.                         if (($length == 10) || ($version == SPREADSHEET_EXCEL_READER_BIFF7)){
  666.                             $this->sheets[$this->sn]['numRows'] = ord($this->data[$spos+2]) | ord($this->data[$spos+3]) << 8;
  667.                             $this->sheets[$this->sn]['numCols'] = ord($this->data[$spos+6]) | ord($this->data[$spos+7]) << 8;
  668.                         } else {
  669.                             $this->sheets[$this->sn]['numRows'] = ord($this->data[$spos+4]) | ord($this->data[$spos+5]) << 8;
  670.                             $this->sheets[$this->sn]['numCols'] = ord($this->data[$spos+10]) | ord($this->data[$spos+11]) << 8;
  671.                         }
  672.                     }
  673.                     //echo 'numRows '.$this->numRows.' '.$this->numCols."\n";

  674.                     break;
  675.                 case SPREADSHEET_EXCEL_READER_TYPE_MERGEDCELLS:
  676.                     $cellRanges = ord($this->data[$spos]) | ord($this->data[$spos+1])<<8;
  677.                     for ($i = 0; $i < $cellRanges; $i++) {
  678.                         $fr = ord($this->data[$spos + 8*$i + 2]) | ord($this->data[$spos + 8*$i + 3])<<8;
  679.                         $lr = ord($this->data[$spos + 8*$i + 4]) | ord($this->data[$spos + 8*$i + 5])<<8;
  680.                         $fc = ord($this->data[$spos + 8*$i + 6]) | ord($this->data[$spos + 8*$i + 7])<<8;
  681.                         $lc = ord($this->data[$spos + 8*$i + 8]) | ord($this->data[$spos + 8*$i + 9])<<8;
  682.                         //$this->sheets[$this->sn]['mergedCells'][] = array($fr + 1, $fc + 1, $lr + 1, $lc + 1);

  683.                         if ($lr - $fr > 0) {
  684.                             $this->sheets[$this->sn]['cellsInfo'][$fr+1][$fc+1]['rowspan'] = $lr - $fr + 1;
  685.                         }
  686.                         if ($lc - $fc > 0) {
  687.                             $this->sheets[$this->sn]['cellsInfo'][$fr+1][$fc+1]['colspan'] = $lc - $fc + 1;
  688.                         }
  689.                     }
  690.                     //echo "Merged Cells $cellRanges $lr $fr $lc $fc\n";

  691.                     break;
  692.                 case SPREADSHEET_EXCEL_READER_TYPE_RK:
  693.                 case SPREADSHEET_EXCEL_READER_TYPE_RK2:
  694.                     //echo 'SPREADSHEET_EXCEL_READER_TYPE_RK'."\n";

  695.                     $row = ord($this->data[$spos]) | ord($this->data[$spos+1])<<8;
  696.                     $column = ord($this->data[$spos+2]) | ord($this->data[$spos+3])<<8;
  697.                     $rknum = $this->_GetInt4d($this->data, $spos + 6);
  698.                     $numValue = $this->_GetIEEE754($rknum);
  699.                     //echo $numValue." ";

  700.                     if ($this->isDate($spos)) {
  701.                         list($string, $raw) = $this->createDate($numValue);
  702.                     }else{
  703.                         $raw = $numValue;
  704.                         if (isset($this->_columnsFormat[$column + 1])){
  705.                                 $this->curformat = $this->_columnsFormat[$column + 1];
  706.                         }
  707.                         $string = sprintf($this->curformat, $numValue * $this->multiplier);
  708.                         //$this->addcell(RKRecord($r));

  709.                     }
  710.                     $this->addcell($row, $column, $string, $raw);
  711.                     //echo "Type_RK $row $column $string $raw {$this->curformat}\n";

  712.                     break;
  713.                 case SPREADSHEET_EXCEL_READER_TYPE_LABELSST:
  714.                         $row = ord($this->data[$spos]) | ord($this->data[$spos+1])<<8;
  715.                         $column = ord($this->data[$spos+2]) | ord($this->data[$spos+3])<<8;
  716.                         $xfindex = ord($this->data[$spos+4]) | ord($this->data[$spos+5])<<8;
  717.                         $index = $this->_GetInt4d($this->data, $spos + 6);
  718.             //var_dump($this->sst);

  719.                         $this->addcell($row, $column, $this->sst[$index]);
  720.                         //echo "LabelSST $row $column $string\n";

  721.                     break;
  722.                 case SPREADSHEET_EXCEL_READER_TYPE_MULRK:
  723.                     $row = ord($this->data[$spos]) | ord($this->data[$spos+1])<<8;
  724.                     $colFirst = ord($this->data[$spos+2]) | ord($this->data[$spos+3])<<8;
  725.                     $colLast = ord($this->data[$spos + $length - 2]) | ord($this->data[$spos + $length - 1])<<8;
  726.                     $columns = $colLast - $colFirst + 1;
  727.                     $tmppos = $spos+4;
  728.                     for ($i = 0; $i < $columns; $i++) {
  729.                         $numValue = $this->_GetIEEE754($this->_GetInt4d($this->data, $tmppos + 2));
  730.                         if ($this->isDate($tmppos-4)) {
  731.                             list($string, $raw) = $this->createDate($numValue);
  732.                         }else{
  733.                             $raw = $numValue;
  734.                             if (isset($this->_columnsFormat[$colFirst + $i + 1])){
  735.                                         $this->curformat = $this->_columnsFormat[$colFirst + $i + 1];
  736.                                 }
  737.                             $string = sprintf($this->curformat, $numValue * $this->multiplier);
  738.                         }
  739.                       //$rec['rknumbers'][$i]['xfindex'] = ord($rec['data'][$pos]) | ord($rec['data'][$pos+1]) << 8;

  740.                       $tmppos += 6;
  741.                       $this->addcell($row, $colFirst + $i, $string, $raw);
  742.                       //echo "MULRK $row ".($colFirst + $i)." $string\n";

  743.                     }
  744.                      //MulRKRecord($r);

  745.                     // Get the individual cell records from the multiple record

  746.                      //$num = ;


  747.                     break;
  748.                 case SPREADSHEET_EXCEL_READER_TYPE_NUMBER:
  749.                     $row = ord($this->data[$spos]) | ord($this->data[$spos+1])<<8;
  750.                     $column = ord($this->data[$spos+2]) | ord($this->data[$spos+3])<<8;
  751.                     $tmp = unpack("ddouble", substr($this->data, $spos + 6, 8)); // It machine machine dependent

  752.                     if ($this->isDate($spos)) {
  753.                         list($string, $raw) = $this->createDate($tmp['double']);
  754.                      // $this->addcell(DateRecord($r, 1));

  755.                     }else{
  756.                         //$raw = $tmp[''];

  757.                         if (isset($this->_columnsFormat[$column + 1])){
  758.                                 $this->curformat = $this->_columnsFormat[$column + 1];
  759.                         }
  760.                         $raw = $this->createNumber($spos);
  761.                         $string = sprintf($this->curformat, $raw * $this->multiplier);

  762.                      // $this->addcell(NumberRecord($r));

  763.                     }
  764.                     $this->addcell($row, $column, $string, $raw);
  765.                     //echo "Number $row $column $string\n";

  766.                     break;
  767.                 case SPREADSHEET_EXCEL_READER_TYPE_FORMULA:
  768.                 case SPREADSHEET_EXCEL_READER_TYPE_FORMULA2:
  769.                     $row = ord($this->data[$spos]) | ord($this->data[$spos+1])<<8;
  770.                     $column = ord($this->data[$spos+2]) | ord($this->data[$spos+3])<<8;
  771.                     if ((ord($this->data[$spos+6])==0) && (ord($this->data[$spos+12])==255) && (ord($this->data[$spos+13])==255)) {
  772.                         //String formula. Result follows in a STRING record

  773.                         //echo "FORMULA $row $column Formula with a string<br>\n";

  774.                     } elseif ((ord($this->data[$spos+6])==1) && (ord($this->data[$spos+12])==255) && (ord($this->data[$spos+13])==255)) {
  775.                         //Boolean formula. Result is in +2; 0=false,1=true

  776.                     } elseif ((ord($this->data[$spos+6])==2) && (ord($this->data[$spos+12])==255) && (ord($this->data[$spos+13])==255)) {
  777.                         //Error formula. Error code is in +2;

  778.                     } elseif ((ord($this->data[$spos+6])==3) && (ord($this->data[$spos+12])==255) && (ord($this->data[$spos+13])==255)) {
  779.                         //Formula result is a null string.

  780.                     } else {
  781.                         // result is a number, so first 14 bytes are just like a _NUMBER record

  782.                         $tmp = unpack("ddouble", substr($this->data, $spos + 6, 8)); // It machine machine dependent

  783.                         if ($this->isDate($spos)) {
  784.                             list($string, $raw) = $this->createDate($tmp['double']);
  785.                          // $this->addcell(DateRecord($r, 1));

  786.                         }else{
  787.                             //$raw = $tmp[''];

  788.                             if (isset($this->_columnsFormat[$column + 1])){
  789.                                     $this->curformat = $this->_columnsFormat[$column + 1];
  790.                             }
  791.                             $raw = $this->createNumber($spos);
  792.                             $string = sprintf($this->curformat, $raw * $this->multiplier);

  793.                          // $this->addcell(NumberRecord($r));

  794.                         }
  795.                         $this->addcell($row, $column, $string, $raw);
  796.                         //echo "Number $row $column $string\n";

  797.                     }
  798.                     break;
  799.                 case SPREADSHEET_EXCEL_READER_TYPE_BOOLERR:
  800.                     $row = ord($this->data[$spos]) | ord($this->data[$spos+1])<<8;
  801.                     $column = ord($this->data[$spos+2]) | ord($this->data[$spos+3])<<8;
  802.                     $string = ord($this->data[$spos+6]);
  803.                     $this->addcell($row, $column, $string);
  804.                     //echo 'Type_BOOLERR '."\n";

  805.                     break;
  806.                 case SPREADSHEET_EXCEL_READER_TYPE_ROW:
  807.                 case SPREADSHEET_EXCEL_READER_TYPE_DBCELL:
  808.                 case SPREADSHEET_EXCEL_READER_TYPE_MULBLANK:
  809.                     break;
  810.                 case SPREADSHEET_EXCEL_READER_TYPE_LABEL:
  811.                     $row = ord($this->data[$spos]) | ord($this->data[$spos+1])<<8;
  812.                     $column = ord($this->data[$spos+2]) | ord($this->data[$spos+3])<<8;
  813.                     $this->addcell($row, $column, substr($this->data, $spos + 8, ord($this->data[$spos + 6]) | ord($this->data[$spos + 7])<<8));

  814.                    // $this->addcell(LabelRecord($r));

  815.                     break;

  816.                 case SPREADSHEET_EXCEL_READER_TYPE_EOF:
  817.                     $cont = false;
  818.                     break;
  819.                 default:
  820.                     //echo ' unknown :'.base_convert($r['code'],10,16)."\n";

  821.                     break;

  822.             }
  823.             $spos += $length;
  824.         }

  825.         if (!isset($this->sheets[$this->sn]['numRows']))
  826.              $this->sheets[$this->sn]['numRows'] = $this->sheets[$this->sn]['maxrow'];
  827.         if (!isset($this->sheets[$this->sn]['numCols']))
  828.              $this->sheets[$this->sn]['numCols'] = $this->sheets[$this->sn]['maxcol'];

  829.     }

  830.     /**
  831.      * Check whether the current record read is a date
  832.      *
  833.      * @param todo
  834.      * @return boolean True if date, false otherwise
  835.      */
  836.     function isDate($spos)
  837.     {
  838.         //$xfindex = GetInt2d(, 4);

  839.         $xfindex = ord($this->data[$spos+4]) | ord($this->data[$spos+5]) << 8;
  840.         //echo 'check is date '.$xfindex.' '.$this->formatRecords['xfrecords'][$xfindex]['type']."\n";

  841.         //var_dump($this->formatRecords['xfrecords'][$xfindex]);

  842.         if ($this->formatRecords['xfrecords'][$xfindex]['type'] == 'date') {
  843.             $this->curformat = $this->formatRecords['xfrecords'][$xfindex]['format'];
  844.             $this->rectype = 'date';
  845.             return true;
  846.         } else {
  847.             if ($this->formatRecords['xfrecords'][$xfindex]['type'] == 'number') {
  848.                 $this->curformat = $this->formatRecords['xfrecords'][$xfindex]['format'];
  849.                 $this->rectype = 'number';
  850.                 if (($xfindex == 0x9) || ($xfindex == 0xa)){
  851.                     $this->multiplier = 100;
  852.                 }
  853.             }else{
  854.                 $this->curformat = $this->_defaultFormat;
  855.                 $this->rectype = 'unknown';
  856.             }
  857.             return false;
  858.         }
  859.     }

  860.     //}}}

  861.     //{{{ createDate()


  862.     /**
  863.      * Convert the raw Excel date into a human readable format
  864.      *
  865.      * Dates in Excel are stored as number of seconds from an epoch. On
  866.      * Windows, the epoch is 30/12/1899 and on Mac it's 01/01/1904
  867.      *
  868.      * @access private
  869.      * @param integer The raw Excel value to convert
  870.      * @return array First element is the converted date, the second element is number a unix timestamp
  871.      */
  872.     function createDate($numValue)
  873.     {
  874.         if ($numValue > 1) {
  875.             $utcDays = $numValue - ($this->nineteenFour ? SPREADSHEET_EXCEL_READER_UTCOFFSETDAYS1904 : SPREADSHEET_EXCEL_READER_UTCOFFSETDAYS);
  876.             $utcValue = round(($utcDays+1) * SPREADSHEET_EXCEL_READER_MSINADAY);
  877.             $string = date ($this->curformat, $utcValue);
  878.             $raw = $utcValue;
  879.         } else {
  880.             $raw = $numValue;
  881.             $hours = floor($numValue * 24);
  882.             $mins = floor($numValue * 24 * 60) - $hours * 60;
  883.             $secs = floor($numValue * SPREADSHEET_EXCEL_READER_MSINADAY) - $hours * 60 * 60 - $mins * 60;
  884.             $string = date ($this->curformat, mktime($hours, $mins, $secs));
  885.         }

  886.         return array($string, $raw);
  887.     }

  888.     function createNumber($spos)
  889.     {
  890.         $rknumhigh = $this->_GetInt4d($this->data, $spos + 10);
  891.         $rknumlow = $this->_GetInt4d($this->data, $spos + 6);
  892.         //for ($i=0; $i<8; $i++) { echo ord($this->data[$i+$spos+6]) . " "; } echo "<br>";

  893.         $sign = ($rknumhigh & 0x80000000) >> 31;
  894.         $exp = ($rknumhigh & 0x7ff00000) >> 20;
  895.         $mantissa = (0x100000 | ($rknumhigh & 0x000fffff));
  896.         $mantissalow1 = ($rknumlow & 0x80000000) >> 31;
  897.         $mantissalow2 = ($rknumlow & 0x7fffffff);
  898.         $value = $mantissa / pow( 2 , (20- ($exp - 1023)));
  899.         if ($mantissalow1 != 0) $value += 1 / pow (2 , (21 - ($exp - 1023)));
  900.         $value += $mantissalow2 / pow (2 , (52 - ($exp - 1023)));
  901.         //echo "Sign = $sign, Exp = $exp, mantissahighx = $mantissa, mantissalow1 = $mantissalow1, mantissalow2 = $mantissalow2<br>\n";

  902.         if ($sign) {$value = -1 * $value;}
  903.         return $value;
  904.     }

  905.     function addcell($row, $col, $string, $raw = '')
  906.     {
  907.         //echo "ADD cel $row-$col $string\n";

  908.         $this->sheets[$this->sn]['maxrow'] = max($this->sheets[$this->sn]['maxrow'], $row + $this->_rowoffset);
  909.         $this->sheets[$this->sn]['maxcol'] = max($this->sheets[$this->sn]['maxcol'], $col + $this->_coloffset);
  910.         $this->sheets[$this->sn]['cells'][$row + $this->_rowoffset][$col + $this->_coloffset] = $string;
  911.         if ($raw)
  912.             $this->sheets[$this->sn]['cellsInfo'][$row + $this->_rowoffset][$col + $this->_coloffset]['raw'] = $raw;
  913.         if (isset($this->rectype))
  914.             $this->sheets[$this->sn]['cellsInfo'][$row + $this->_rowoffset][$col + $this->_coloffset]['type'] = $this->rectype;

  915.     }


  916.     function _GetIEEE754($rknum)
  917.     {
  918.         if (($rknum & 0x02) != 0) {
  919.                 $value = $rknum >> 2;
  920.         } else {
  921. //mmp

  922. // first comment out the previously existing 7 lines of code here

  923. // $tmp = unpack("d", pack("VV", 0, ($rknum & 0xfffffffc)));

  924. // //$value = $tmp[''];

  925. // if (array_key_exists(1, $tmp)) {

  926. // $value = $tmp[1];

  927. // } else {

  928. // $value = $tmp[''];

  929. // }

  930. // I got my info on IEEE754 encoding from

  931. // http://research.microsoft.com/~hollasch/cgindex/coding/ieeefloat.html

  932. // The RK format calls for using only the most significant 30 bits of the

  933. // 64 bit floating point value. The other 34 bits are assumed to be 0

  934. // So, we use the upper 30 bits of $rknum as follows...

  935.          $sign = ($rknum & 0x80000000) >> 31;
  936.         $exp = ($rknum & 0x7ff00000) >> 20;
  937.         $mantissa = (0x100000 | ($rknum & 0x000ffffc));
  938.         $value = $mantissa / pow( 2 , (20- ($exp - 1023)));
  939.         if ($sign) {$value = -1 * $value;}
  940. //end of changes by mmp


  941.         }

  942.         if (($rknum & 0x01) != 0) {
  943.             $value /= 100;
  944.         }
  945.         return $value;
  946.     }

  947.     function _encodeUTF16($string)
  948.     {
  949.         $result = $string;
  950.         if ($this->_defaultEncoding){
  951.             switch ($this->_encoderFunction){
  952.                 case 'iconv' : $result = iconv('UTF-16LE', $this->_defaultEncoding, $string);
  953.                                 break;
  954.                 case 'mb_convert_encoding' : $result = mb_convert_encoding($string, $this->_defaultEncoding, 'UTF-16LE' );
  955.                                 break;
  956.             }
  957.         }
  958.         return $result;
  959.     }

  960.     function _GetInt4d($data, $pos)
  961.     {
  962.         $value = ord($data[$pos]) | (ord($data[$pos+1]) << 8) | (ord($data[$pos+2]) << 16) | (ord($data[$pos+3]) << 24);
  963.         if ($value>=4294967294)
  964.         {
  965.             $value=-2;
  966.         }
  967.         return $value;
  968.     }

  969. }

  970. /*
  971.  * Local variables:
  972.  * tab-width: 4
  973.  * c-basic-offset: 4
  974.  * c-hanging-comment-ender-p: nil
  975.  * End:
  976.  */

  977. ?>
2,OLERead.php文件内容
  1. <?php
  2. define('NUM_BIG_BLOCK_DEPOT_BLOCKS_POS', 0x2c);
  3. define('SMALL_BLOCK_DEPOT_BLOCK_POS', 0x3c);
  4. define('ROOT_START_BLOCK_POS', 0x30);
  5. define('BIG_BLOCK_SIZE', 0x200);
  6. define('SMALL_BLOCK_SIZE', 0x40);
  7. define('EXTENSION_BLOCK_POS', 0x44);
  8. define('NUM_EXTENSION_BLOCK_POS', 0x48);
  9. define('PROPERTY_STORAGE_BLOCK_SIZE', 0x80);
  10. define('BIG_BLOCK_DEPOT_BLOCKS_POS', 0x4c);
  11. define('SMALL_BLOCK_THRESHOLD', 0x1000);
  12. // property storage offsets

  13. define('SIZE_OF_NAME_POS', 0x40);
  14. define('TYPE_POS', 0x42);
  15. define('START_BLOCK_POS', 0x74);
  16. define('SIZE_POS', 0x78);
  17. define('IDENTIFIER_OLE', pack("CCCCCCCC",0xd0,0xcf,0x11,0xe0,0xa1,0xb1,0x1a,0xe1));

  18. //echo 'ROOT_START_BLOCK_POS = '.ROOT_START_BLOCK_POS."\n";


  19. //echo bin2hex($data[ROOT_START_BLOCK_POS])."\n";

  20. //echo "a=";

  21. //echo $data[ROOT_START_BLOCK_POS];

  22. //function log


  23. function GetInt4d($data, $pos)
  24. {
  25.     $value = ord($data[$pos]) | (ord($data[$pos+1])    << 8) | (ord($data[$pos+2]) << 16) | (ord($data[$pos+3]) << 24);
  26.     if ($value>=4294967294)
  27.     {
  28.         $value=-2;
  29.     }
  30.     return $value;
  31. }


  32. class OLERead {
  33.     var $data = '';
  34.     
  35.     
  36.     function OLERead(){
  37.         
  38.         
  39.     }
  40.     
  41.     function read($sFileName){
  42.         
  43.         // check if file exist and is readable (Darko Miljanovic)

  44.         if(!is_readable($sFileName)) {
  45.             $this->error = 1;
  46.             return false;
  47.         }
  48.         
  49.         $this->data = @file_get_contents($sFileName);
  50.         if (!$this->data) {
  51.             $this->error = 1;
  52.             return false;
  53.            }
  54.            //echo IDENTIFIER_OLE;

  55.            //echo 'start';

  56.            if (substr($this->data, 0, 8) != IDENTIFIER_OLE) {
  57.             $this->error = 1;
  58.             return false;
  59.            }
  60.         $this->numBigBlockDepotBlocks = GetInt4d($this->data, NUM_BIG_BLOCK_DEPOT_BLOCKS_POS);
  61.         $this->sbdStartBlock = GetInt4d($this->data, SMALL_BLOCK_DEPOT_BLOCK_POS);
  62.         $this->rootStartBlock = GetInt4d($this->data, ROOT_START_BLOCK_POS);
  63.         $this->extensionBlock = GetInt4d($this->data, EXTENSION_BLOCK_POS);
  64.         $this->numExtensionBlocks = GetInt4d($this->data, NUM_EXTENSION_BLOCK_POS);
  65.         
  66.     /*
  67.         echo $this->numBigBlockDepotBlocks." ";
  68.         echo $this->sbdStartBlock." ";
  69.         echo $this->rootStartBlock." ";
  70.         echo $this->extensionBlock." ";
  71.         echo $this->numExtensionBlocks." ";
  72.         */
  73.         //echo "sbdStartBlock = $this->sbdStartBlock\n";

  74.         $bigBlockDepotBlocks = array();
  75.         $pos = BIG_BLOCK_DEPOT_BLOCKS_POS;
  76.        // echo "pos = $pos";

  77.     $bbdBlocks = $this->numBigBlockDepotBlocks;
  78.         
  79.             if ($this->numExtensionBlocks != 0) {
  80.                 $bbdBlocks = (BIG_BLOCK_SIZE - BIG_BLOCK_DEPOT_BLOCKS_POS)/4;
  81.             }
  82.         
  83.         for ($i = 0; $i < $bbdBlocks; $i++) {
  84.               $bigBlockDepotBlocks[$i] = GetInt4d($this->data, $pos);
  85.               $pos += 4;
  86.         }
  87.         
  88.         
  89.         for ($j = 0; $j < $this->numExtensionBlocks; $j++) {
  90.             $pos = ($this->extensionBlock + 1) * BIG_BLOCK_SIZE;
  91.             $blocksToRead = min($this->numBigBlockDepotBlocks - $bbdBlocks, BIG_BLOCK_SIZE / 4 - 1);

  92.             for ($i = $bbdBlocks; $i < $bbdBlocks + $blocksToRead; $i++) {
  93.                 $bigBlockDepotBlocks[$i] = GetInt4d($this->data, $pos);
  94.                 $pos += 4;
  95.             }

  96.             $bbdBlocks += $blocksToRead;
  97.             if ($bbdBlocks < $this->numBigBlockDepotBlocks) {
  98.                 $this->extensionBlock = GetInt4d($this->data, $pos);
  99.             }
  100.         }

  101.        // var_dump($bigBlockDepotBlocks);

  102.         
  103.         // readBigBlockDepot

  104.         $pos = 0;
  105.         $index = 0;
  106.         $this->bigBlockChain = array();
  107.         
  108.         for ($i = 0; $i < $this->numBigBlockDepotBlocks; $i++) {
  109.             $pos = ($bigBlockDepotBlocks[$i] + 1) * BIG_BLOCK_SIZE;
  110.             //echo "pos = $pos";    

  111.             for ($j = 0 ; $j < BIG_BLOCK_SIZE / 4; $j++) {
  112.                 $this->bigBlockChain[$index] = GetInt4d($this->data, $pos);
  113.                 $pos += 4 ;
  114.                 $index++;
  115.             }
  116.         }

  117.     //var_dump($this->bigBlockChain);

  118.         //echo '=====2';

  119.         // readSmallBlockDepot();

  120.         $pos = 0;
  121.      $index = 0;
  122.      $sbdBlock = $this->sbdStartBlock;
  123.      $this->smallBlockChain = array();
  124.     
  125.      while ($sbdBlock != -2) {
  126.     
  127.      $pos = ($sbdBlock + 1) * BIG_BLOCK_SIZE;
  128.     
  129.      for ($j = 0; $j < BIG_BLOCK_SIZE / 4; $j++) {
  130.      $this->smallBlockChain[$index] = GetInt4d($this->data, $pos);
  131.      $pos += 4;
  132.      $index++;
  133.      }
  134.     
  135.      $sbdBlock = $this->bigBlockChain[$sbdBlock];
  136.      }

  137.         
  138.         // readData(rootStartBlock)

  139.         $block = $this->rootStartBlock;
  140.         $pos = 0;
  141.         $this->entry = $this->__readData($block);
  142.         
  143.         /*
  144.         while ($block != -2) {
  145.             $pos = ($block + 1) * BIG_BLOCK_SIZE;
  146.             $this->entry = $this->entry.substr($this->data, $pos, BIG_BLOCK_SIZE);
  147.             $block = $this->bigBlockChain[$block];
  148.         }
  149.         */
  150.         //echo '==='.$this->entry."===";

  151.         $this->__readPropertySets();

  152.     }
  153.     
  154.      function __readData($bl) {
  155.         $block = $bl;
  156.         $pos = 0;
  157.         $data = '';
  158.         
  159.         while ($block != -2) {
  160.             $pos = ($block + 1) * BIG_BLOCK_SIZE;
  161.             $data = $data.substr($this->data, $pos, BIG_BLOCK_SIZE);
  162.             //echo "pos = $pos data=$data\n";    

  163.      $block = $this->bigBlockChain[$block];
  164.         }
  165.         return $data;
  166.      }
  167.         
  168.     function __readPropertySets(){
  169.         $offset = 0;
  170.         //var_dump($this->entry);

  171.         while ($offset < strlen($this->entry)) {
  172.               $d = substr($this->entry, $offset, PROPERTY_STORAGE_BLOCK_SIZE);
  173.             
  174.               $nameSize = ord($d[SIZE_OF_NAME_POS]) | (ord($d[SIZE_OF_NAME_POS+1]) << 8);
  175.               
  176.               $type = ord($d[TYPE_POS]);
  177.               //$maxBlock = strlen($d) / BIG_BLOCK_SIZE - 1;

  178.         
  179.               $startBlock = GetInt4d($d, START_BLOCK_POS);
  180.               $size = GetInt4d($d, SIZE_POS);
  181.         
  182.             $name = '';
  183.             for ($i = 0; $i < $nameSize ; $i++) {
  184.               $name .= $d[$i];
  185.             }
  186.             
  187.             $name = str_replace("\x00", "", $name);
  188.             
  189.             $this->props[] = array (
  190.                 'name' => $name,
  191.                 'type' => $type,
  192.                 'startBlock' => $startBlock,
  193.                 'size' => $size);

  194.             if (($name == "Workbook") || ($name == "Book")) {
  195.                 $this->wrkbook = count($this->props) - 1;
  196.             }

  197.             if ($name == "Root Entry") {
  198.                 $this->rootentry = count($this->props) - 1;
  199.             }
  200.             
  201.             //echo "name ==$name=\n";


  202.             
  203.             $offset += PROPERTY_STORAGE_BLOCK_SIZE;
  204.         }
  205.         
  206.     }
  207.     
  208.     
  209.     function getWorkBook(){
  210.         if ($this->props[$this->wrkbook]['size'] < SMALL_BLOCK_THRESHOLD){
  211. //      getSmallBlockStream(PropertyStorage ps)


  212.             $rootdata = $this->__readData($this->props[$this->rootentry]['startBlock']);
  213.     
  214.             $streamData = '';
  215.      $block = $this->props[$this->wrkbook]['startBlock'];
  216.      //$count = 0;

  217.      $pos = 0;
  218.          while ($block != -2) {
  219.            $pos = $block * SMALL_BLOCK_SIZE;
  220.          $streamData .= substr($rootdata, $pos, SMALL_BLOCK_SIZE);

  221.              $block = $this->smallBlockChain[$block];
  222.          }
  223.             
  224.          return $streamData;
  225.             

  226.         }else{
  227.         
  228.      $numBlocks = $this->props[$this->wrkbook]['size'] / BIG_BLOCK_SIZE;
  229.      if ($this->props[$this->wrkbook]['size'] % BIG_BLOCK_SIZE != 0) {
  230.      $numBlocks++;
  231.      }
  232.     
  233.      if ($numBlocks == 0) return '';
  234.     
  235.      //echo "numBlocks = $numBlocks\n";

  236.      //byte[] streamData = new byte[numBlocks * BIG_BLOCK_SIZE];

  237.      //print_r($this->wrkbook);

  238.      $streamData = '';
  239.      $block = $this->props[$this->wrkbook]['startBlock'];
  240.      //$count = 0;

  241.      $pos = 0;
  242.      //echo "block = $block";

  243.      while ($block != -2) {
  244.      $pos = ($block + 1) * BIG_BLOCK_SIZE;
  245.      $streamData .= substr($this->data, $pos, BIG_BLOCK_SIZE);
  246.      $block = $this->bigBlockChain[$block];
  247.      }
  248.      //echo 'stream'.$streamData;

  249.      return $streamData;
  250.         }
  251.     }
  252.     
  253. }
  254. ?>

 

3,使用示例:

注意:这里的编码要根据你的excel文件编码来定,这个示例只是取第一行数据,其它的可以例推。

  1. error_reporting(E_ALL);
  2. /** Include path **/
  3. set_include_path('../../lib/PHPExcel/');

  4. /** PHPExcel_IOFactory */
  5. include 'PHPExcel/Reader/reader.php';

  6. $data = new Spreadsheet_Excel_Reader();
  7. //设置文本输出编码
  8. $data->setOutputEncoding('utf-8');
  9. //读取Excel文件
  10. $data->read("2.xls");
  11. //$data->sheets[0]['numRows']为Excel行数
  12. $key=array();
  13. for ($i = 1; $i <= $data->sheets[0]['numRows']; $i++) {
  14.     for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++) {
  15.         //显示每个单元格内容
  16.         if(isset($data->sheets[0]['cells'][$i][$j])) {
  17.             $key[]=$data->sheets[0]['cells'][$i][$j];
  18.         }
  19.     }
  20.     break;
  21. }
您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

DTCC2020中国数据库技术大会

【架构革新 高效可控】2020年12月21日-23日第十一届中国数据库技术大会将在北京隆重召开。

大会设置2大主会场,20+技术专场,将邀请超百位行业专家,重点围绕数据架构、AI与大数据、传统企业数据库实践和国产开源数据库等内容展开分享和探讨,为广大数据领域从业人士提供一场年度盛会和交流平台。

http://dtcc.it168.com


大会官网>>
  

北京盛拓优讯信息技术有限公司. 版权所有 16024965号-6 北京市公安局海淀分局网监中心备案编号:11010802020122
中国互联网协会会员  联系我们:huangweiwei@it168.com
感谢所有关心和支持过ChinaUnix的朋友们 转载本站内容请注明原作者名及出处

清除 Cookies - ChinaUnix - Archiver - WAP - TOP