krot

phpExcel пример

Jul 26th, 2016
131
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. <?php
  2.  
  3.  
  4.     error_reporting(E_ALL);
  5.     set_time_limit(0);
  6.     ini_set("memory_limit","-1");
  7.  
  8.  
  9.  
  10.    /**  Set Include path to point at the PHPExcel Classes folder  **/
  11.    set_include_path(get_include_path() . PATH_SEPARATOR . 'xls/Classes/');
  12.  
  13.   /**  Include PHPExcel_IOFactory  **/
  14.   include 'xls/Classes/PHPExcel/IOFactory.php';
  15.  
  16.   $inputFileName = 'FILE.xls';
  17.   $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
  18.  
  19.  /**  Define a Read Filter class implementing PHPExcel_Reader_IReadFilter */
  20.    class chunkReadFilter implements PHPExcel_Reader_IReadFilter
  21.    {
  22.       private $_startRow = 0;
  23.       private $_endRow = 0;
  24.       /**  Set the list of rows that we want to read  */
  25.       public function setRows($startRow, $chunkSize) {
  26.        $this->_startRow = $startRow;
  27.        $this->_endRow = $startRow + $chunkSize;
  28.      }
  29.      public function readCell($column, $row, $worksheetName = '')
  30.     {
  31.           if (($row == 1) || ($row >= $this->_startRow && $row < $this->_endRow))
  32.          {
  33.             return true;
  34.          }
  35.         return false;
  36.     }
  37.   }
  38.   echo 'Loading file ',pathinfo($inputFileName,PATHINFO_BASENAME),' using IOFactory with a defined reader type of ',$inputFileType,'<br />';
  39.    /**  Create a new Reader of the type defined in $inputFileType  **/
  40.     $objReader = PHPExcel_IOFactory::createReader($inputFileType);
  41. //$objReader->setLoadAllSheets();
  42.  
  43.    /*print_r($objReader);
  44.   exit;*/
  45.     /**  Define how many rows we want to read for each "chunk"  **/
  46.     $chunkSize = 100;
  47.    //total rows in excel
  48.    /**  Create a new Instance of our Read Filter  **/
  49.    $chunkFilter = new chunkReadFilter();
  50.    /**  Tell the Reader that we want to use the Read Filter that we've  Instantiated  **/
  51.   $objReader->setReadFilter($chunkFilter);
  52.   $objReader->setReadDataOnly(true);
  53.   /**  Loop to read our worksheet in "chunk size" blocks  **/
  54.   /*
  55.   $cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_to_phpTemp;
  56.      $cacheSettings = array( ' memoryCacheSize '  => '1000MB');
  57.     PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
  58.     $cacheMethod=PHPExcel_CachedObjectStorageFactory::cache_in_memory_serialized;
  59.     PHPExcel_Settings::setCacheStorageMethod($cacheMethod);
  60.     $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip;
  61.     if (!PHPExcel_Settings::setCacheStorageMethod($cacheMethod)) {
  62.         die($cacheMethod . " caching method is not available" . EOL);}//    echo date('H:i:s') , " Enable Cell Caching using " , $cacheMethod , "   method" , PHP_EOL;
  63. */
  64.    $spreadsheetInfo = $objReader->listWorksheetInfo($inputFileName);
  65.   //print_r($spreadsheetInfo);
  66.  
  67.        // exit;
  68.       for($l=0;$l<3;++$l){
  69. $sheetnames = array($spreadsheetInfo[$l]['worksheetName']);
  70.         $objReader->setLoadSheetsOnly($sheetnames);
  71. $totalRows=$spreadsheetInfo[$l]['totalRows'];
  72.  
  73.  for ($startRow = 2; $startRow <= $totalRows; $startRow += $chunkSize) {
  74.     //echo "in for loop<br>";
  75.     //echo 'Loading WorkSheet using configurable filter for headings row 1 and     for rows ',$startRow,' to ',($startRow+$chunkSize-1),'<br />';
  76.  
  77.      $chunkFilter->setRows($startRow,$chunkSize);
  78.      // Load only the rows that match our filter from $inputFileName to a PHPExcel Object  
  79.      $objPHPExcel = $objReader->load($inputFileName);
  80.   //$objPHPExcel->setActiveSheetIndex(1);
  81.      $objWorksheet = $objPHPExcel->getActiveSheet();
  82.      //$highestColumn = $objWorksheet->getHighestColumn();
  83.   //echo 'A'.$startRow.':'.$highestColumn.($startRow + $chunkSize-1);exit;
  84.   //$EndRow=$startRow + $chunkSize-1;
  85.   //if($EndRow>$totalRows)$EndRow=$totalRows;
  86.   //$sheetData = $objWorksheet->rangeToArray('A'.$startRow.':'.$highestColumn.($EndRow),null, false, false, true);
  87.     // print_r($sheetData);
  88.  // $row=$objWorksheet->toArray();
  89.   //print_r($row);
  90.  
  91.   /*
  92. $cell = $objWorksheet->getCellByColumnAndRow(1, 1);
  93. // читаем значение ячейки
  94. $value = $cell->getValue();
  95.     print_r($value);
  96.     exit;*/
  97.     $worksheet=$objWorksheet;
  98.     $highestRow         = $worksheet->getHighestRow(); // e.g. 10
  99.   $highestColumn ='K';//    = $worksheet->getHighestColumn(); // e.g 'F'
  100.     $highestColumnIndex= 11;//PHPExcel_Cell::columnIndexFromString($highestColumn);
  101.     for ($row = 1; $row <= $highestRow; ++ $row) {
  102.         //echo '<tr>';
  103.      $rowArray=array();
  104.         for ($col = 0; $col < $highestColumnIndex; ++ $col) {
  105.             $cell = $worksheet->getCellByColumnAndRow($col, $row);
  106.             $val = $cell->getValue();
  107.          $rowArray[]=$val;
  108.             //$dataType = PHPExcel_Cell_DataType::dataTypeForValue($val);
  109.            // echo '<td>' . $val . '<br>(Typ ' . $dataType . ')</td>';
  110.         }
  111.         print_r($rowArray);
  112.        // echo '</tr>';
  113.        }
  114.    
  115.     break;
  116.    
  117.    
  118.    
  119.    
  120.    
  121.      $objPHPExcel->disconnectWorksheets();
  122.      unset($objPHPExcel);
  123. //exit;
  124.   break;
  125.     }
  126.    }
  127.  
  128.  
  129.   //print_r($spreadsheetInfo);
  130.   $time=time();
  131.       for($l=0;$l<3;++$l){
  132.      $hnd=fopen($l.'.txt','w');
  133. $sheetnames = array($spreadsheetInfo[$l]['worksheetName']);
  134.         $objReader->setLoadSheetsOnly($sheetnames);
  135. $totalRows=$spreadsheetInfo[$l]['totalRows'];
  136.  for ($startRow = 2; $startRow <= $totalRows; $startRow += $chunkSize) {
  137.      $chunkFilter->setRows($startRow,$chunkSize);
  138.      $objPHPExcel = $objReader->load($inputFileName);
  139.      $objWorksheet = $objPHPExcel->getActiveSheet();
  140.   /* $worksheet=$objWorksheet;
  141.     $highestRow         = $worksheet->getHighestRow(); // e.g. 10
  142.   $highestColumn ='K';//    = $worksheet->getHighestColumn(); // e.g 'F'
  143.     $highestColumnIndex= 11;//PHPExcel_Cell::columnIndexFromString($highestColumn);
  144.     for ($row = $startRow; $row <= $highestRow; ++ $row) {
  145.         //echo '<tr>';
  146.      $rowArray=array();
  147.         for ($col = 0; $col < $highestColumnIndex; ++ $col) {
  148.             $cell = $worksheet->getCellByColumnAndRow($col, $row);
  149.             $val = $cell->getValue();
  150.          $rowArray[]=$val;
  151.         }
  152.       //  print_r($rowArray);
  153.         fwrite($hnd,implode(';',$rowArray).PHP_EOL);
  154.        // echo '</tr>';
  155.        }*/
  156.    $EndRow=$startRow + $chunkSize-1;
  157.   if($EndRow>$totalRows)$EndRow=$totalRows;
  158.   $rowArray = $objWorksheet->rangeToArray('A'.$startRow.':K'.($EndRow),null, false, false, false);
  159.  // print_r($rowArray);
  160.  // exit;
  161.    foreach($rowArray as $rowVal){
  162.   foreach($rowVal as $val) fwrite($hnd,$val.';');
  163.  fwrite($hnd,PHP_EOL);
  164.    }
  165.  // print_r( $rowArray);
RAW Paste Data