PHP PHPExcel::getWorksheetIterator方法代码示例

 /**
  * init worksheets
  * 
  * @access protected
  */
 protected function load_worksheets()
 {
     //empty the worksheets
     $this->_worksheets = array();
     foreach ($this->_spreadsheet->getWorksheetIterator() as $i => $worksheet) {
         //create default worksheet
         $worksheet = new Worksheet($this->_spreadsheet, $worksheet);
         $this->_worksheets[$i] = $worksheet;
     }
     return $this;
 }

 /**
  * Parse file.
  * @param array $columns
  * @return array
  */
 public function parseFile($columns = [])
 {
     $content = [];
     $this->setSelectedColumn($columns);
     if (!$this->isParsed) {
         $iterator = $this->excel->getWorksheetIterator();
         foreach ($iterator as $this->worksheet) {
             if ($this->reader->isSelected($iterator->key())) {
                 $worksheet = $this->parseWorksheet();
                 if (!empty($worksheet)) {
                     $title = $this->worksheet->getTitle();
                     $content[$title] = $worksheet;
                 }
             }
         }
     }
     $this->isParsed = true;
     return $content;
 }

 /**
  *  Parse the file
  * @param array $columns
  * @return SheetCollection
  */
 public function parseFile($columns = array())
 {
     // Init new sheet collection
     $workbook = new SheetCollection();
     // Set the selected columns
     $this->setSelectedColumns($columns);
     // If not parsed yet
     if (!$this->isParsed) {
         // Set worksheet count
         $this->w = 0;
         // Get selected sheets
         $iterator = $this->excel->getWorksheetIterator();
         // Loop through the worksheets
         foreach ($iterator as $this->worksheet) {
             // Check if the sheet might have been selected by it's index
             if ($this->reader->isSelectedByIndex($iterator->key())) {
                 // Parse the worksheet
                 $worksheet = $this->parseWorksheet();
                 // If multiple sheets
                 if ($this->parseAsMultiple()) {
                     // Push every sheet
                     $workbook->push($worksheet);
                     $workbook->setTitle($this->excel->getProperties()->getTitle());
                 } else {
                     // Ignore the sheet collection
                     $workbook = $worksheet;
                     break;
                 }
             }
             $this->w++;
         }
     }
     $this->isParsed = true;
     // Return itself
     return $workbook;
 }

 /**
  * Update named formulas (i.e. containing worksheet references / named ranges)
  *
  * @param PHPExcel $pPhpExcel	Object to update
  * @param string $oldName		Old name (name to replace)
  * @param string $newName		New name
  */
 public function updateNamedFormulas(PHPExcel $pPhpExcel, $oldName = '', $newName = '')
 {
     if ($oldName == '') {
         return;
     }
     foreach ($pPhpExcel->getWorksheetIterator() as $sheet) {
         foreach ($sheet->getCellCollection(false) as $cell) {
             if (!is_null($cell) && $cell->getDataType() == PHPExcel_Cell_DataType::TYPE_FORMULA) {
                 $formula = $cell->getValue();
                 if (strpos($formula, $oldName) !== false) {
                     $formula = str_replace("'" . $oldName . "'!", "'" . $newName . "'!", $formula);
                     $formula = str_replace($oldName . "!", $newName . "!", $formula);
                     $cell->setValueExplicit($formula, PHPExcel_Cell_DataType::TYPE_FORMULA);
                 }
             }
         }
     }
 }

 /**
  * Calculate offsets for Worksheet BOF records.
  *
  * @access private
  */
 private function calcSheetOffsets()
 {
     $boundsheet_length = 10;
     // fixed length for a BOUNDSHEET record
     // size of Workbook globals part 1 + 3
     $offset = $this->_datasize;
     // add size of Workbook globals part 2, the length of the SHEET records
     $total_worksheets = count($this->phpExcel->getAllSheets());
     foreach ($this->phpExcel->getWorksheetIterator() as $sheet) {
         $offset += $boundsheet_length + strlen(PHPExcel_Shared_String::UTF8toBIFF8UnicodeShort($sheet->getTitle()));
     }
     // add the sizes of each of the Sheet substreams, respectively
     for ($i = 0; $i < $total_worksheets; ++$i) {
         $this->worksheetOffsets[$i] = $offset;
         $offset += $this->worksheetSizes[$i];
     }
     $this->biffSize = $offset;
 }

 /**
  * Render $page into html.
  *
  * This method is generally called via double-dispatch, as provided by Visitor\VisitableTrait.
  *
  * @param PageInterface $page
  * @return string
  * @throws Exception if no tables found in page.
  */
 public function visitPage(PageInterface $page)
 {
     /** @var WritableInterface[] $writables */
     $writables = [$page];
     /** @var TableInterface[] $tables */
     $tables = [];
     while ($writables !== []) {
         $writable = array_pop($writables);
         if (method_exists($writable, 'getWritable') === true && $writable->getWritable() !== null) {
             $writables[] = $writable->getWritable();
         }
         if (method_exists($writable, 'getWritables') === true) {
             $writables = array_merge($writables, array_values($writable->getWritables()));
         }
         if ($writable instanceof TableInterface) {
             $tables[] = $writable;
         }
     }
     if ($tables === []) {
         throw new Exception("No tables found in writables.");
     }
     $objPHPExcel = new \PHPExcel();
     foreach ($tables as $table) {
         // Create a sheet
         $objWorkSheet = $objPHPExcel->createSheet();
         if (sizeof($table->getRows()) > 0) {
             // Write header
             /** @var FieldInterface $field */
             foreach (array_values($table->getRows()[0]->getWritableBearer()->getWritables()) as $j => $field) {
                 $cellIndex = static::excelRow($j) . "1";
                 $objWorkSheet->setCellValue($cellIndex, $field->getLabel());
                 $objWorkSheet->getStyle($cellIndex)->getFont()->setBold(true);
             }
             // Write cells
             foreach ($table->getRows() as $i => $row) {
                 foreach (array_values($row->getWritableBearer()->getWritables()) as $j => $field) {
                     if ($field->getInitial() !== "") {
                         $cellIndex = static::excelRow($j) . ($i + 2);
                         $objWorkSheet->setCellValue($cellIndex, $field->getInitial());
                     }
                 }
             }
         } else {
             $objWorkSheet->setCellValue("A1", "No records found");
         }
     }
     // Remove worksheet 0; it was created with the file but we never wrote to it
     $objPHPExcel->removeSheetByIndex(0);
     // Auto size columns for each worksheet
     foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
         $objPHPExcel->setActiveSheetIndex($objPHPExcel->getIndex($worksheet));
         $sheet = $objPHPExcel->getActiveSheet();
         $cellIterator = $sheet->getRowIterator()->current()->getCellIterator();
         $cellIterator->setIterateOnlyExistingCells(true);
         foreach ($cellIterator as $cell) {
             $sheet->getColumnDimension($cell->getColumn())->setAutoSize(true);
         }
     }
     $objPHPExcel->setActiveSheetIndex(0);
     $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
     $tmp = tmpfile();
     $fileLocation = stream_get_meta_data($tmp)['uri'];
     ob_start();
     $objWriter->save('php://output');
     return ob_get_clean();
 }

 /**
  * Write the EXTERNCOUNT and EXTERNSHEET records. These are used as indexes for
  * the NAME records.
  */
 private function _writeExterns()
 {
     // Create EXTERNCOUNT with number of worksheets
     $this->_writeExterncount(count($this->_phpExcel->getAllSheets()));
     // Create EXTERNSHEET for each worksheet
     foreach ($this->_phpExcel->getWorksheetIterator() as $sheet) {
         $this->_writeExternsheet($sheet->getTitle());
     }
 }