Archive for 03月, 2008

You are currently browsing the archives of 乱七八糟 .

php 读取excel

这不是我写的，我志修改了一下，使调用更方便

  1. <?php
  2. require_once 'ExcelReader.class.php';   //调用下面的代码
  3.  
  4. // ExcelFile($filename, $encoding);
  5. $data = new Spreadsheet_Excel_Reader();
  6. $data->setOutputEncoding('GB2312');
  7. $data->setUTFEncoder('mb');         // iconv  or mb
  8. $data->setRowColOffset(1);
  9. $data->read('book2.xls');
  10. $array = $data->outPutData(); //返回2维数组
  11. print_r($array);
  12. ?>
  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.  
  19. //echo 'ROOT_START_BLOCK_POS = '.ROOT_START_BLOCK_POS."\n";
  20.  
  21. //echo bin2hex($data[ROOT_START_BLOCK_POS])."\n";
  22. //echo "a=";
  23. //echo $data[ROOT_START_BLOCK_POS];
  24. //function log
  25.  
  26. function GetInt4d($data, $pos) {
  27.         return ord($data[$pos]) | (ord($data[$pos+1]) << 8) | (ord($data[$pos+2]) << 16) | (ord($data[$pos+3]) << 24);
  28. }
  29.  
  30.  
  31. class OLERead {
  32.     var $data = '';
  33.    
  34.    
  35.     function OLERead(){
  36.        
  37.        
  38.     }
  39.    
  40.     function read($sFileName){
  41.        
  42.         // check if file exist and is readable (Darko Miljanovic)
  43.         if(!is_readable($sFileName)) {
  44.             $this->error = 1;
  45.             return false;
  46.         }
  47.        
  48.         $this->data = @file_get_contents($sFileName);
  49.         if (!$this->data) { 
  50.             $this->error = 1;
  51.             return false;
  52.            }
  53.            //echo IDENTIFIER_OLE;
  54.            //echo 'start';
  55.            if (substr($this->data, 0, 8) != IDENTIFIER_OLE) {
  56.             $this->error = 1;
  57.             return false;
  58.            }
  59.         $this->numBigBlockDepotBlocks = GetInt4d($this->data, NUM_BIG_BLOCK_DEPOT_BLOCKS_POS);
  60.         $this->sbdStartBlock = GetInt4d($this->data, SMALL_BLOCK_DEPOT_BLOCK_POS);
  61.         $this->rootStartBlock = GetInt4d($this->data, ROOT_START_BLOCK_POS);
  62.         $this->extensionBlock = GetInt4d($this->data, EXTENSION_BLOCK_POS);
  63.         $this->numExtensionBlocks = GetInt4d($this->data, NUM_EXTENSION_BLOCK_POS);
  64.        
  65.     /*
  66.         echo $this->numBigBlockDepotBlocks." ";
  67.         echo $this->sbdStartBlock." ";
  68.         echo $this->rootStartBlock." ";
  69.         echo $this->extensionBlock." ";
  70.         echo $this->numExtensionBlocks." ";
  71.         */
  72.         //echo "sbdStartBlock = $this->sbdStartBlock\n";
  73.         $bigBlockDepotBlocks = array();
  74.         $pos = BIG_BLOCK_DEPOT_BLOCKS_POS;
  75.        // echo "pos = $pos";
  76.     $bbdBlocks = $this->numBigBlockDepotBlocks;
  77.        
  78.             if ($this->numExtensionBlocks != 0) {
  79.                 $bbdBlocks = (BIG_BLOCK_SIZE - BIG_BLOCK_DEPOT_BLOCKS_POS)/4;
  80.             }
  81.        
  82.         for ($i = 0; $i < $bbdBlocks; $i++) {
  83.               $bigBlockDepotBlocks[$i] = GetInt4d($this->data, $pos);
  84.               $pos += 4;
  85.         }
  86.        
  87.        
  88.         for ($j = 0; $j < $this->numExtensionBlocks; $j++) {
  89.             $pos = ($this->extensionBlock + 1) * BIG_BLOCK_SIZE;
  90.             $blocksToRead = min($this->numBigBlockDepotBlocks - $bbdBlocks, BIG_BLOCK_SIZE / 4 - 1);
  91.  
  92.             for ($i = $bbdBlocks; $i < $bbdBlocks + $blocksToRead; $i++) {
  93.                 $bigBlockDepotBlocks[$i] = GetInt4d($this->data, $pos);
  94.                 $pos += 4;
  95.             }  
  96.  
  97.             $bbdBlocks += $blocksToRead;
  98.             if ($bbdBlocks < $this->numBigBlockDepotBlocks) {
  99.                 $this->extensionBlock = GetInt4d($this->data, $pos);
  100.             }
  101.         }
  102.  
  103.        // var_dump($bigBlockDepotBlocks);
  104.        
  105.         // readBigBlockDepot
  106.         $pos = 0;
  107.         $index = 0;
  108.         $this->bigBlockChain = array();
  109.        
  110.         for ($i = 0; $i < $this->numBigBlockDepotBlocks; $i++) {
  111.             $pos = ($bigBlockDepotBlocks[$i] + 1) * BIG_BLOCK_SIZE;
  112.             //echo "pos = $pos";   
  113.             for ($j = 0 ; $j < BIG_BLOCK_SIZE / 4; $j++) {
  114.                 $this->bigBlockChain[$index] = GetInt4d($this->data, $pos);
  115.                 $pos += 4 ;
  116.                 $index++;
  117.             }
  118.         }
  119.  
  120.     //var_dump($this->bigBlockChain);
  121.         //echo '=====2';
  122.         // readSmallBlockDepot();
  123.         $pos = 0;
  124.         $index = 0;
  125.         $sbdBlock = $this->sbdStartBlock;
  126.         $this->smallBlockChain = array();
  127.    
  128.         while ($sbdBlock != -2) {
  129.    
  130.           $pos = ($sbdBlock + 1) * BIG_BLOCK_SIZE;
  131.    
  132.           for ($j = 0; $j < BIG_BLOCK_SIZE / 4; $j++) {
  133.             $this->smallBlockChain[$index] = GetInt4d($this->data, $pos);
  134.             $pos += 4;
  135.             $index++;
  136.           }
  137.    
  138.           $sbdBlock = $this->bigBlockChain[$sbdBlock];
  139.         }
  140.  
  141.        
  142.         // readData(rootStartBlock)
  143.         $block = $this->rootStartBlock;
  144.         $pos = 0;
  145.         $this->entry = $this->__readData($block);
  146.        
  147.         /*
  148.         while ($block != -2)  {
  149.             $pos = ($block + 1) * BIG_BLOCK_SIZE;
  150.             $this->entry = $this->entry.substr($this->data, $pos, BIG_BLOCK_SIZE);
  151.             $block = $this->bigBlockChain[$block];
  152.         }
  153.         */
  154.         //echo '==='.$this->entry."===";
  155.         $this->__readPropertySets();
  156.  
  157.     }
  158.    
  159.      function __readData($bl) {
  160.         $block = $bl;
  161.         $pos = 0;
  162.         $data = '';
  163.        
  164.         while ($block != -2)  {
  165.             $pos = ($block + 1) * BIG_BLOCK_SIZE;
  166.             $data = $data.substr($this->data, $pos, BIG_BLOCK_SIZE);
  167.             //echo "pos = $pos data=$data\n";   
  168.         $block = $this->bigBlockChain[$block];
  169.         }
  170.         return $data;
  171.      }
  172.        
  173.     function __readPropertySets(){
  174.         $offset = 0;
  175.         //var_dump($this->entry);
  176.         while ($offset < strlen($this->entry)) {
  177.               $d = substr($this->entry, $offset, PROPERTY_STORAGE_BLOCK_SIZE);
  178.            
  179.               $nameSize = ord($d[SIZE_OF_NAME_POS]) | (ord($d[SIZE_OF_NAME_POS+1]) << 8);
  180.              
  181.               $type = ord($d[TYPE_POS]);
  182.               //$maxBlock = strlen($d) / BIG_BLOCK_SIZE - 1;
  183.        
  184.               $startBlock = GetInt4d($d, START_BLOCK_POS);
  185.               $size = GetInt4d($d, SIZE_POS);
  186.        
  187.             $name = '';
  188.             for ($i = 0; $i < $nameSize ; $i++) {
  189.               $name .= $d[$i];
  190.             }
  191.            
  192.             $name = str_replace("\x00", "", $name);
  193.            
  194.             $this->props[] = array (
  195.                 'name' => $name,
  196.                 'type' => $type,
  197.                 'startBlock' => $startBlock,
  198.                 'size' => $size);
  199.  
  200.             if (($name == "Workbook") || ($name == "Book")) {
  201.                 $this->wrkbook = count($this->props) - 1;
  202.             }
  203.  
  204.             if ($name == "Root Entry") {
  205.                 $this->rootentry = count($this->props) - 1;
  206.             }
  207.            
  208.             //echo "name ==$name=\n";
  209.  
  210.            
  211.             $offset += PROPERTY_STORAGE_BLOCK_SIZE;
  212.         }  
  213.        
  214.     }
  215.    
  216.    
  217.     function getWorkBook(){
  218.         if ($this->props[$this->wrkbook]['size'] < SMALL_BLOCK_THRESHOLD){
  219. //          getSmallBlockStream(PropertyStorage ps)
  220.  
  221.             $rootdata = $this->__readData($this->props[$this->rootentry]['startBlock']);
  222.            
  223.             $streamData = '';
  224.             $block = $this->props[$this->wrkbook]['startBlock'];
  225.             //$count = 0;
  226.             $pos = 0;
  227.             while ($block != -2) {
  228.                     $pos = $block * SMALL_BLOCK_SIZE;
  229.                   $streamData .= substr($rootdata, $pos, SMALL_BLOCK_SIZE);
  230.  
  231.                   $block = $this->smallBlockChain[$block];
  232.             }
  233.            
  234.             return $streamData;
  235.            
  236.  
  237.         }else{
  238.        
  239.             $numBlocks = $this->props[$this->wrkbook]['size'] / BIG_BLOCK_SIZE;
  240.             if ($this->props[$this->wrkbook]['size'] % BIG_BLOCK_SIZE != 0) {
  241.                 $numBlocks++;
  242.             }
  243.            
  244.             if ($numBlocks == 0) return '';
  245.            
  246.             //echo "numBlocks = $numBlocks\n";
  247.         //byte[] streamData = new byte[numBlocks * BIG_BLOCK_SIZE];
  248.             //print_r($this->wrkbook);
  249.             $streamData = '';
  250.             $block = $this->props[$this->wrkbook]['startBlock'];
  251.             //$count = 0;
  252.             $pos = 0;
  253.             //echo "block = $block";
  254.             while ($block != -2) {
  255.               $pos = ($block + 1) * BIG_BLOCK_SIZE;
  256.               $streamData .= substr($this->data, $pos, BIG_BLOCK_SIZE);
  257.               $block = $this->bigBlockChain[$block];
  258.             }  
  259.             //echo 'stream'.$streamData;
  260.             return $streamData;
  261.         }
  262.     }
  263.    
  264. }
  265.  
  266. //define('Spreadsheet_Excel_Reader_HAVE_ICONV', function_exists('iconv'));
  267. //define('Spreadsheet_Excel_Reader_HAVE_MB', function_exists('mb_convert_encoding'));
  268.  
  269. define('Spreadsheet_Excel_Reader_BIFF8', 0x600);
  270. define('Spreadsheet_Excel_Reader_BIFF7', 0x500);
  271. define('Spreadsheet_Excel_Reader_WorkbookGlobals', 0x5);
  272. define('Spreadsheet_Excel_Reader_Worksheet', 0x10);
  273.  
  274. define('Spreadsheet_Excel_Reader_Type_BOF', 0x809);
  275. define('Spreadsheet_Excel_Reader_Type_EOF', 0x0a);
  276. define('Spreadsheet_Excel_Reader_Type_BOUNDSHEET', 0x85);
  277. define('Spreadsheet_Excel_Reader_Type_DIMENSION', 0x200);
  278. define('Spreadsheet_Excel_Reader_Type_ROW', 0x208);
  279. define('Spreadsheet_Excel_Reader_Type_DBCELL', 0xd7);
  280. define('Spreadsheet_Excel_Reader_Type_FILEPASS', 0x2f);
  281. define('Spreadsheet_Excel_Reader_Type_NOTE', 0x1c);
  282. define('Spreadsheet_Excel_Reader_Type_TXO', 0x1b6);
  283. define('Spreadsheet_Excel_Reader_Type_RK', 0x7e);
  284. define('Spreadsheet_Excel_Reader_Type_RK2', 0x27e);
  285. define('Spreadsheet_Excel_Reader_Type_MULRK', 0xbd);
  286. define('Spreadsheet_Excel_Reader_Type_MULBLANK', 0xbe);
  287. define('Spreadsheet_Excel_Reader_Type_INDEX', 0x20b);
  288. define('Spreadsheet_Excel_Reader_Type_SST', 0xfc);
  289. define('Spreadsheet_Excel_Reader_Type_EXTSST', 0xff);
  290. define('Spreadsheet_Excel_Reader_Type_CONTINUE', 0x3c);
  291. define('Spreadsheet_Excel_Reader_Type_LABEL', 0x204);
  292. define('Spreadsheet_Excel_Reader_Type_LABELSST', 0xfd);
  293. define('Spreadsheet_Excel_Reader_Type_NUMBER', 0x203);
  294. define('Spreadsheet_Excel_Reader_Type_NAME', 0x18);
  295. define('Spreadsheet_Excel_Reader_Type_ARRAY', 0x221);
  296. define('Spreadsheet_Excel_Reader_Type_STRING', 0x207);
  297. define('Spreadsheet_Excel_Reader_Type_FORMULA', 0x406);
  298. define('Spreadsheet_Excel_Reader_Type_FORMULA2', 0x6);
  299. define('Spreadsheet_Excel_Reader_Type_FORMAT', 0x41e);
  300. define('Spreadsheet_Excel_Reader_Type_XF', 0xe0);
  301. define('Spreadsheet_Excel_Reader_Type_BOOLERR', 0x205);
  302. define('Spreadsheet_Excel_Reader_Type_UNKNOWN', 0xffff);
  303. define('Spreadsheet_Excel_Reader_Type_NINETEENFOUR', 0x22);
  304. define('Spreadsheet_Excel_Reader_Type_MERGEDCELLS', 0xE5);
  305.  
  306. define('Spreadsheet_Excel_Reader_utcOffsetDays' , 25569);
  307. define('Spreadsheet_Excel_Reader_utcOffsetDays1904', 24107);
  308. define('Spreadsheet_Excel_Reader_msInADay', 24 * 60 * 60);
  309.  
  310. //define('Spreadsheet_Excel_Reader_DEF_NUM_FORMAT', "%.2f");
  311. define('Spreadsheet_Excel_Reader_DEF_NUM_FORMAT', "%s");
  312.  
  313. // function file_get_contents for PHP < 4.3.0
  314. // Thanks Marian Steinbach for this function
  315. if (!function_exists('file_get_contents')) {
  316.     function file_get_contents($filename, $use_include_path = 0) {
  317.         $data = '';
  318.         $file = @fopen($filename, "rb", $use_include_path);
  319.         if ($file) {
  320.             while (!feof($file)) $data .= fread($file, 1024);
  321.             fclose($file);
  322.         } else {
  323.             // There was a problem opening the file
  324.             $data = FALSE;
  325.         }
  326.         return $data;
  327.     }
  328. }
  329.  
  330.  
  331. //class Spreadsheet_Excel_Reader extends PEAR {
  332. class Spreadsheet_Excel_Reader {
  333.  
  334.     var $boundsheets = array();
  335.     var $formatRecords = array();
  336.     var $sst = array();
  337.     var $sheets = array();
  338.     var $data;
  339.     var $pos;
  340.     var $_ole;
  341.     var $_defaultEncoding;
  342.     var $_defaultFormat = Spreadsheet_Excel_Reader_DEF_NUM_FORMAT;
  343.     var $_columnsFormat = array();
  344.     var $_rowoffset = 1;
  345.     var $_coloffset = 1;
  346.    
  347.     var $dateFormats = array (
  348.         0xe => "d/m/Y",
  349.         0xf => "d-M-Y",
  350.         0x10 => "d-M",
  351.         0x11 => "M-Y",
  352.         0x12 => "h:i a",
  353.         0x13 => "h:i:s a",
  354.         0x14 => "H:i",
  355.         0x15 => "H:i:s",
  356.         0x16 => "d/m/Y H:i",
  357.         0x2d => "i:s",
  358.         0x2e => "H:i:s",
  359.         0x2f => "i:s.S");
  360.  
  361.     var $numberFormats = array(
  362.         0x1 => "%1.0f", // "0"
  363.         0x2 => "%1.2f", // "0.00",
  364.         0x3 => "%1.0f", //"#,##0",
  365.         0x4 => "%1.2f", //"#,##0.00",
  366.         0x5 => "%1.0f", /*"$#,##0;($#,##0)",*/
  367.         0x6 => '$%1.0f', /*"$#,##0;($#,##0)",*/
  368.         0x7 => '$%1.2f', //"$#,##0.00;($#,##0.00)",
  369.         0x8 => '$%1.2f', //"$#,##0.00;($#,##0.00)",
  370.         0x9 => '%1.0f%%', // "0%"
  371.         0xa => '%1.2f%%', // "0.00%"
  372.         0xb => '%1.2f', // 0.00E00",
  373.         0x25 => '%1.0f', // "#,##0;(#,##0)",
  374.         0x26 => '%1.0f', //"#,##0;(#,##0)",
  375.         0x27 => '%1.2f', //"#,##0.00;(#,##0.00)",
  376.         0x28 => '%1.2f', //"#,##0.00;(#,##0.00)",
  377.         0x29 => '%1.0f', //"#,##0;(#,##0)",
  378.         0x2a => '$%1.0f', //"$#,##0;($#,##0)",
  379.         0x2b => '%1.2f', //"#,##0.00;(#,##0.00)",
  380.         0x2c => '$%1.2f', //"$#,##0.00;($#,##0.00)",
  381.         0x30 => '%1.0f'); //"##0.0E0";
  382.  
  383.     function Spreadsheet_Excel_Reader(){
  384.         $this->_ole =& new OLERead();
  385.         $this->setUTFEncoder('iconv');
  386.  
  387.     }
  388.  
  389.     function setOutputEncoding($Encoding){
  390.         $this->_defaultEncoding = $Encoding;
  391.     }
  392.  
  393.     /**
  394.     *  $encoder = 'iconv' or 'mb'
  395.     *  set iconv if you would like use 'iconv' for encode UTF-16LE to your encoding
  396.     *  set mb if you would like use 'mb_convert_encoding' for encode UTF-16LE to your encoding
  397.     */
  398.     function setUTFEncoder($encoder = 'iconv'){
  399.         $this->_encoderFunction = '';
  400.         if ($encoder == 'iconv'){
  401.             $this->_encoderFunction = function_exists('iconv') ? 'iconv' : '';
  402.         }elseif ($encoder == 'mb') {
  403.             $this->_encoderFunction = function_exists('mb_convert_encoding') ? 'mb_convert_encoding' : '';
  404.         }
  405.     }
  406.  
  407.     function setRowColOffset($iOffset){
  408.         $this->_rowoffset = $iOffset;
  409.         $this->_coloffset = $iOffset;
  410.     }
  411.  
  412.     function setDefaultFormat($sFormat){
  413.         $this->_defaultFormat = $sFormat;
  414.     }
  415.  
  416.     function setColumnFormat($column, $sFormat){
  417.         $this->_columnsFormat[$column] = $sFormat;
  418.     }
  419.  
  420.  
  421.     function read($sFileName) {
  422.         $errlevel = error_reporting();
  423.         error_reporting($errlevel ^ E_NOTICE);
  424.         $res = $this->_ole->read($sFileName);
  425.        
  426.         // oops, something goes wrong (Darko Miljanovic)
  427.         if($res === false) {
  428.             // check error code
  429.             if($this->_ole->error == 1) {
  430.             // bad file
  431.                 die('The filename ' . $sFileName . ' is not readable');   
  432.             }
  433.             // check other error codes here (eg bad fileformat, etc...)
  434.         }
  435.  
  436.         $this->data = $this->_ole->getWorkBook();
  437.  
  438.        
  439.         /*
  440.         $res = $this->_ole->read($sFileName);
  441.  
  442.         if ($this->isError($res)) {
  443. //        var_dump($res);       
  444.             return $this->raiseError($res);
  445.         }
  446.  
  447.         $total = $this->_ole->ppsTotal();
  448.         for ($i = 0; $i < $total; $i++) {
  449.             if ($this->_ole->isFile($i)) {
  450.                 $type = unpack("v", $this->_ole->getData($i, 0, 2));
  451.                 if ($type[''] == 0x0809)  { // check if it's a BIFF stream
  452.                     $this->_index = $i;
  453.                     $this->data = $this->_ole->getData($i, 0, $this->_ole->getDataLength($i));
  454.