PHP PHPExcel_IOFactory::createReaderForFile方法代码示例

 /**
  * Processes the uploaded CSV-file
  */
 private function processCsvImport()
 {
     $uploaded_file_name = $_FILES['csvfile']['tmp_name'];
     try {
         /** try to read excel file **/
         try {
             $objReader = PHPExcel_IOFactory::createReaderForFile($uploaded_file_name);
         } catch (Exception $e) {
             // if format could not be determined, we assume CSV
             $objReader = PHPExcel_IOFactory::createReader('CSV');
         }
         if (method_exists($objReader, "setDelimiter")) {
             // its a csv file
             $separator = urldecode($_POST['separator']);
             if (!$separator) {
                 $separator = ',';
             }
             $objReader->setDelimiter($separator);
         } else {
             // its an excel file
             $objReader->setReadDataOnly(true);
         }
         $objPHPExcel = $objReader->load($uploaded_file_name);
         $objWorksheet = $objPHPExcel->getActiveSheet();
         foreach ($objWorksheet->getRowIterator() as $row) {
             $cellIterator = $row->getCellIterator();
             $cellIterator->setIterateOnlyExistingCells(false);
             $data = array();
             foreach ($cellIterator as $cell) {
                 $data[] = $cell->getValue();
             }
             if (!$this->is_multiArrayEmpty($data)) {
                 $this->numberOfCsvCols = max($this->numberOfCsvCols, count($data));
                 $this->csvDataSets[] = $data;
             }
         }
         if (count($this->csvDataSets) == 0) {
             $this->processError = Messages::getString('No data found in input file.');
         }
     } catch (Exception $e) {
         // some error occured
         $this->processError = Messages::getString('EnterDataPage.CouldNotReadInputFile') . ' ' . $_FILES['csvfile']['error'] . " (" . $e->getMessage() . ")";
     }
     @unlink($uploaded_file_name);
 }

 public function __construct($fileName, $csvDelimiter = null, $csvEnclosure = null, $sheetNumber = 1, $chunkSize = null, $fillMissingColumns = true, $removeCellsNotMatchingHeaderColumns = true)
 {
     $this->filename = $fileName;
     $this->sheetNumber = $sheetNumber;
     $this->fillMissingColumns = $fillMissingColumns;
     $this->removeCellsNotMatchingHeaderColumns = $removeCellsNotMatchingHeaderColumns;
     //@todo: Allow disabling chunked read
     $this->filter = new ChunkReadFilter(0, $chunkSize);
     $this->reader = \PHPExcel_IOFactory::createReaderForFile($this->filename);
     if ($this->reader instanceof \PHPExcel_Reader_CSV) {
         if ($csvDelimiter) {
             $this->reader->setDelimiter($csvDelimiter);
         }
         if ($csvEnclosure) {
             $this->reader->setEnclosure($csvEnclosure);
         }
     }
     $this->reader->setReadFilter($this->filter);
     $worksheet = $this->reloadIterator(1);
     $this->highestDataColumnName = $worksheet->getHighestDataColumn();
     $this->highestRow = $worksheet->getHighestRow();
     $this->rowIterator->rewind();
     $this->header = $this->getFilteredArrayForCurrentRow();
     foreach ($this->header as $headerIndex => &$columnHeader) {
         if (is_null($columnHeader) || $columnHeader === '') {
             $columnHeader = $headerIndex;
         } else {
             $columnHeader = trim($columnHeader);
         }
     }
     $this->rowIterator->next();
 }

 /**
  * get excel contents
  * @return array
  * @link http://fun-program.net/phpexcel-read-excel
  * @link http://stackoverflow.com/questions/4562527/how-to-find-out-how-many-rows-and-columns-to-read-from-an-excel-file-with-phpexc
  * @link http://stackoverflow.com/questions/27708459/getting-cell-as-string-in-phpexcel-by-column-and-row
  */
 public function getContents()
 {
     if ($this->validate()) {
         $result = [];
         $reader = PHPExcel_IOFactory::createReaderForFile($this->file->tempName);
         $reader->setReadDataOnly(false);
         $excel = $reader->load($this->file->tempName);
         $sheet = $excel->getSheetByName($this->activeSheetName);
         if (!isset($sheet)) {
             throw new Exception("取得Excel資料，發生錯誤，請確認[{$this->activeSheetName}]標籤是否存在或合法(標籤名稱必須為英文)");
         }
         $rows = $sheet->getRowIterator();
         $rowIndex = 0;
         foreach ($rows as $row) {
             $cellIndex = 0;
             $cellIterator = $row->getCellIterator();
             $cellIterator->setIterateOnlyExistingCells(false);
             $result[++$rowIndex] = [];
             foreach ($cellIterator as $cell) {
                 $result[$rowIndex][++$cellIndex] = (string) $cell->getCalculatedValue();
             }
         }
         return $result;
         /*
         // 另一種簡潔寫法
         $reader = PHPExcel_IOFactory::load($this->file->tempName);
         return $reader->getActiveSheet()->toArray(null, true, true, true);
         */
     }
     throw new Exception('must be validate excel file.', 1);
 }

 public function import($pathToFile)
 {
     //        $pathToFile = realpath($pathToFile);
     //        var_dump($pathToFile);
     //        $fileType = \PHPExcel_IOFactory::identify($pathToFile);
     $objReader = \PHPExcel_IOFactory::createReaderForFile($pathToFile);
     $objReader->setReadDataOnly(true);
     $objReader->setLoadSheetsOnly('Сотрудники');
     $excel = $objReader->load($pathToFile);
     //        $excel->setActiveSheetIndex(0);
     $sheet = $excel->getSheet(0);
     $rowIterator = $sheet->getRowIterator();
     $break = 0;
     foreach ($rowIterator as $row) {
         //            var_dump($row);
         var_dump('END OF ROW');
         //            var_dump($row);
         //            dd();
         foreach ($row as $cell) {
             var_dump($cell);
         }
         if ($break++ > 10) {
             dd();
         }
     }
 }

 protected function execute(InputInterface $input, OutputInterface $output)
 {
     $this->container = $this->getContainer();
     $this->em = $this->getContainer()->get('doctrine')->getManager();
     //$this->em->getConnection()->getConfiguration()->setSQLLogger(null);
     $this->formCSRF = $this->container->get('form.csrf_provider');
     $this->adminPool = $this->container->get('sonata.admin.pool');
     $this->user = $this->em->getRepository('ApplicationSonataUserBundle:User')->findOneBy(array('id' => $input->getArgument('user_id')));
     $this->translator = \AppKernel::getStaticContainer()->get('translator');
     $token = new UsernamePasswordToken($this->user, $this->user->getPassword(), "public", $this->user->getRoles());
     $this->getContainer()->get("security.context")->setToken($token);
     // Fire the login event
     $event = new InteractiveLoginEvent($this->getContainer()->get('request'), $token);
     $this->getContainer()->get("event_dispatcher")->dispatch("security.interactive_login", $event);
     $this->targetProcess = $input->getArgument('target');
     $this->user_id = $input->getArgument('user_id');
     $this->pid = $input->getArgument('pid');
     $file = $input->getArgument('file');
     $objReader = \PHPExcel_IOFactory::createReaderForFile($file);
     $objReader->setReadDataOnly(true);
     $objPHPExcel = $objReader->load($file);
     $sheet = $objPHPExcel->getSheet(0);
     $rows = $sheet->toArray();
     $this->processClient($rows);
     $this->messages = $this->getCountMessageImports();
     $this->sendNotification();
     echo serialize(array('messages' => $this->messages, 'import_counts' => $this->_import_counts, 'pid' => $this->pid, 'absErrorLogFilename' => $this->absErrorLogFilename));
 }

 /**
  * read
  *
  */
 public function read($filePath)
 {
     $xlsReader = PHPExcel_IOFactory::createReaderForFile($filePath);
     $this->type = preg_replace('/^.+_/', '', get_class($xlsReader));
     $this->xls = $xlsReader->load($filePath);
     return $this;
 }

 public static function read($filename)
 {
     $path = Yii::$app->basePath . "/uploads/" . $filename;
     $objReader = \PHPExcel_IOFactory::createReaderForFile($path);
     //$objReader->setLoadSheetsOnly($sheets);
     $objReader->setReadDataOnly(true);
     $objPHPExcel = $objReader->load($path);
     $reader = new ExcelReader();
     foreach ($objPHPExcel->setActiveSheetIndex(0)->getRowIterator() as $row) {
         $cellIterator = $row->getCellIterator();
         $cellIterator->setIterateOnlyExistingCells(false);
         $rowData = [];
         foreach ($cellIterator as $cell) {
             if (!is_null($cell)) {
                 $rowData[] = $cell->getCalculatedValue();
             }
         }
         $reader->data[] = $rowData;
     }
     $reader->columnCount = count($reader->data[0]);
     $colrange = range(0, $reader->columnCount - 1);
     foreach ($colrange as $column) {
         $reader->columns[] = strval($column);
     }
     return $reader;
 }

 /**
  *
  */
 public function parseResource()
 {
     $configuration = $this->getConfiguration();
     if (!ExtensionManagementUtility::isLoaded('phpexcel_library')) {
         throw new \Exception('phpexcel_library is not loaded', 12367812368);
     }
     $filename = GeneralUtility::getFileAbsFileName($this->filepath);
     GeneralUtility::makeInstanceService('phpexcel');
     $objReader = \PHPExcel_IOFactory::createReaderForFile($filename);
     $objReader->setReadDataOnly(true);
     $objPHPExcel = $objReader->load($filename);
     if ($configuration['sheet'] >= 0) {
         $objWorksheet = $objPHPExcel->getSheet($configuration['sheet']);
     } else {
         $objWorksheet = $objPHPExcel->getActiveSheet();
     }
     $highestRow = $objWorksheet->getHighestRow();
     $highestColumn = $objWorksheet->getHighestColumn();
     $highestColumnIndex = \PHPExcel_Cell::columnIndexFromString($highestColumn);
     for ($row = 1 + $configuration['skipRows']; $row <= $highestRow; ++$row) {
         $rowRecord = [];
         for ($col = 0; $col <= $highestColumnIndex; ++$col) {
             $rowRecord[] = trim($objWorksheet->getCellByColumnAndRow($col, $row)->getValue());
         }
         $this->content[] = $rowRecord;
     }
 }

 public function init()
 {
     $reader = \PHPExcel_IOFactory::createReaderForFile($this->filePath);
     /* @var $reader \PHPExcel_Reader_IReader */
     $reader->setReadDataOnly(true);
     // Load only 1st sheet
     if (method_exists($reader, 'listWorksheetNames')) {
         $sheets = $reader->listWorksheetNames($this->filePath);
         $reader->setLoadSheetsOnly(array($sheets[0]));
     }
     $this->reader = $reader;
     $this->excel = $this->reader->load($this->filePath);
     $this->excel->setActiveSheetIndex(0);
     $this->sheet = $this->excel->getActiveSheet();
     if ($this->useLabels) {
         if ($this->labels === null) {
             $this->labels = $this->readLabelsRow();
             $this->ignoredRowsCount++;
             // To pass labels row when reading data
         }
         $this->colsCount = count($this->labels);
     }
     if ($this->colsCount === null) {
         throw new Exception(__CLASS__ . '->colsCount not set.');
     }
     $this->filter = new Filter($this->colsCount);
     $reader->setReadFilter($this->filter);
 }

 /**
  * @param $path
  * @return void
  * @throws \PHPExcel_Reader_Exception
  */
 public function load($path)
 {
     //determine correct reader
     $reader = \PHPExcel_IOFactory::createReaderForFile($path);
     //load the file
     $this->object = $reader->load($path);
 }

 public function select($source)
 {
     $path = $this->connection;
     $excel = PHPExcel_IOFactory::createReaderForFile($path);
     $excel = $excel->load($path);
     $excRes = new ExcelResult();
     $excelWS = $excel->getActiveSheet();
     $addFields = true;
     $coords = array();
     if ($source->get_source() == '*') {
         $coords['start_row'] = 0;
         $coords['end_row'] = false;
     } else {
         $c = array();
         preg_match("/^([a-zA-Z]+)(\\d+)/", $source->get_source(), $c);
         if (count($c) > 0) {
             $coords['start_row'] = (int) $c[2];
         } else {
             $coords['start_row'] = 0;
         }
         $c = array();
         preg_match("/:(.+)(\\d+)\$/U", $source->get_source(), $c);
         if (count($c) > 0) {
             $coords['end_row'] = (int) $c[2];
         } else {
             $coords['end_row'] = false;
         }
     }
     $i = $coords['start_row'];
     $end = 0;
     while ($coords['end_row'] == false && $end < $this->emptyLimit || $coords['end_row'] !== false && $i < $coords['end_row']) {
         $r = array();
         $emptyNum = 0;
         for ($j = 0; $j < count($this->config->text); $j++) {
             $col = PHPExcel_Cell::columnIndexFromString($this->config->text[$j]['name']) - 1;
             $cell = $excelWS->getCellByColumnAndRow($col, $i);
             if ($cell->getDataType() == 'f') {
                 $r[PHPExcel_Cell::stringFromColumnIndex($col)] = $cell->getCalculatedValue();
             } else {
                 $r[PHPExcel_Cell::stringFromColumnIndex($col)] = $cell->getValue();
             }
             if ($r[PHPExcel_Cell::stringFromColumnIndex($col)] == '') {
                 $emptyNum++;
             }
         }
         if ($emptyNum < count($this->config->text)) {
             $r['id'] = $i;
             $excRes->addRecord($r);
             $end = 0;
         } else {
             if (DHX_IGNORE_EMPTY_ROWS == false) {
                 $r['id'] = $i;
                 $excRes->addRecord($r);
             }
             $end++;
         }
         $i++;
     }
     return $excRes;
 }

 /**
  * Dowloads the Spanish list of banks (from bde.es) and
  * creates/updates/deletes the corresponding BIC records.
  */
 public function update()
 {
     // First, download the file
     $file_name = sys_get_temp_dir() . '/' . CRM_Bic_Parser_ES::$country_code . '-banks.xls';
     $downloaded_file = $this->downloadFile(CRM_Bic_Parser_ES::$page_url);
     if (!$downloaded_file) {
         return $this->createParserOutdatedError(ts("Couldn't download the Spanish list of banks"));
     }
     // Save the downloaded file
     file_put_contents($file_name, $downloaded_file);
     unset($downloaded_file);
     // Automatically detect the correct reader to load for this file type
     $excel_reader = PHPExcel_IOFactory::createReaderForFile($file_name);
     // Set reader options
     $excel_reader->setReadDataOnly();
     $excel_reader->setLoadSheetsOnly(array('ENTIDADES'));
     // Read Excel file
     $excel_object = $excel_reader->load($file_name);
     $excel_rows = $excel_object->getActiveSheet()->toArray();
     // Process Excel data
     $is_header = true;
     $banks[] = array();
     foreach ($excel_rows as $excel_row) {
         if ($is_header) {
             // Get column Ids from first row
             $column_ids = array();
             foreach ($excel_row as $id => $column_name) {
                 $column_ids[$column_name] = $id;
             }
             // check, if BIC is there
             if (empty($column_ids['BIC'])) {
                 return $this->createParserOutdatedError(ts("The Spanish source file doesn't contain BICs"));
             }
             $is_header = false;
         } else {
             // If there's no bank code, I may assume we're at the end of the file
             if (!$excel_row[$column_ids['COD_BE']]) {
                 break;
             }
             // Add the office code, if it exists
             if ($excel_row[$column_ids['CODENTSUC']] == '0000') {
                 $value = $excel_row[$column_ids['COD_BE']];
             } else {
                 $value = $excel_row[$column_ids['COD_BE']] . $excel_row[$column_ids['CODENTSUC']];
             }
             // If there's a "closed date", we don't import this bank
             if (!$excel_row[$column_ids['FCHBAJA']]) {
                 $banks[] = array('value' => $value, 'name' => $excel_row[$column_ids['BIC']], 'label' => $excel_row[$column_ids['ANAGRAMA']], 'description' => '<b>CIF</b>: ' . $excel_row[$column_ids['CODIGOCIF']] . '<br>' . '<b>Phone</b>: ' . $excel_row[$column_ids['TELEFONO']] . '<br>' . '<b>Fax</b>: ' . $excel_row[$column_ids['NUMFAX']] . '<br>' . '<b>Web</b>: <a href="' . strtolower($excel_row[$column_ids['DIRINTERNET']]) . '">' . strtolower($excel_row[$column_ids['DIRINTERNET']]) . '</a><br>');
             }
         }
     }
     // Remove temporary file
     unlink($file_name);
     // Free some memory
     unset($excel_rows);
     unset($excel_object);
     unset($excel_reader);
     // Finally, update DB
     return $this->updateEntries(CRM_Bic_Parser_ES::$country_code, $banks);
 }

 public function loadExcelFile($filename)
 {
     $this->PHPExcelReader = PHPExcel_IOFactory::createReaderForFile($filename);
     $this->PHPExcelLoaded = true;
     $this->PHPExcelReader->setReadDataOnly(true);
     $excel = $this->PHPExcelReader->load($filename);
     $this->dataArray = $excel->getSheet(0)->toArray();
     return $this->dataArray;
 }

 /**
  * @param string $path
  * @return \PHPExcel_Worksheet
  */
 protected function getExcel($path, $sheet = '')
 {
     $reader = \PHPExcel_IOFactory::createReaderForFile($path);
     if ($sheet) {
         $reader->setLoadSheetsOnly(array($sheet));
     }
     /** @var \PHPExcel $excel */
     $excel = $reader->load($path);
     return $excel->getActiveSheet();
 }

 function parse()
 {
     $objReader = PHPExcel_IOFactory::createReaderForFile($this->file_info['tmp_name']);
     $chunkFilter = new SJB_ChunkReadFilter();
     $objReader->setReadFilter($chunkFilter);
     $objReader->setReadDataOnly(true);
     $this->data = new SJB_ImportXLSIterator();
     $this->data->setObjReader($objReader);
     $this->data->setChunkFilter($chunkFilter);
     $this->data->setFileName($this->file_info['tmp_name']);
 }