PHP PHPExcel_IOFactory::identify方法代码示例

 public function run_import($file_upload)
 {
     $file_path = './upload/files/excel/' . $file_upload['file_name'];
     //load the excel library
     $this->load->library('excel');
     //read file from path
     $inputFileType = PHPExcel_IOFactory::identify($file_path);
     //die(print_r($inputFileType));
     /**  Create a new Reader of the type defined in $inputFileType  **/
     $objReader = PHPExcel_IOFactory::createReader($inputFileType);
     $objPHPExcel = $objReader->load($file_path);
     //die(print_r($objPHPExcel));
     //get only the Cell Collection
     $cell_collection = $objPHPExcel->getActiveSheet()->getCellCollection();
     //extract to a PHP readable array format
     foreach ($cell_collection as $cell) {
         $column = $objPHPExcel->getActiveSheet()->getCell($cell)->getColumn();
         $row = $objPHPExcel->getActiveSheet()->getCell($cell)->getRow();
         $data_value = $objPHPExcel->getActiveSheet()->getCell($cell)->getValue();
         //header will/should be in row 1 only. of course this can be modified to suit your need.
         if ($row == 1) {
             $header[$row][$column] = $data_value;
         } else {
             $arr_data[$row][$column] = $data_value;
         }
     }
     //send the data in an array format
     $data['header'] = $header;
     $data['values'] = $arr_data;
     return $arr_data;
 }

 public static function excelParsing($fileExcel)
 {
     //        $cacheMethod = \PHPExcel_CachedObjectStorageFactory::cache_to_sqlite3;  /* here i added */
     //        $cacheEnabled = \PHPExcel_Settings::setCacheStorageMethod($cacheMethod);
     //        if (!$cacheEnabled) {
     //            echo "### WARNING - Sqlite3 not enabled ###" . PHP_EOL;
     //        }
     $objPHPExcel = new \PHPExcel();
     //$fileExcel = Yii::getAlias('@webroot/templates/operator.xls');
     $inputFileType = \PHPExcel_IOFactory::identify($fileExcel);
     $objReader = \PHPExcel_IOFactory::createReader($inputFileType);
     $objReader->setReadDataOnly(true);
     /**  Load $inputFileName to a PHPExcel Object  * */
     $objPHPExcel = $objReader->load($fileExcel);
     $total_sheets = $objPHPExcel->getSheetCount();
     $allSheetName = $objPHPExcel->getSheetNames();
     $objWorksheet = $objPHPExcel->setActiveSheetIndex(0);
     $highestRow = $objWorksheet->getHighestRow();
     $highestColumn = $objWorksheet->getHighestColumn();
     $highestColumnIndex = \PHPExcel_Cell::columnIndexFromString($highestColumn);
     for ($row = 1; $row <= $highestRow; ++$row) {
         for ($col = 0; $col < $highestColumnIndex; ++$col) {
             $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getValue();
             $arraydata[$row - 1][$col] = $value;
         }
     }
     return $arraydata;
 }

 public function test_read_write_excel()
 {
     $inputFileName = 'print_docs/excel/excel_template/KEMSA Customer Order Form.xlsx';
     $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
     // echo "$inputFileType";die;
     $file_name = time() . '.xlsx';
     $excel2 = PHPExcel_IOFactory::createReader($inputFileType);
     $excel2 = $objPHPExcel = $excel2->load($inputFileName);
     // Empty Sheet
     $sheet = $objPHPExcel->getSheet(0);
     $highestRow = $sheet->getHighestRow();
     $highestColumn = $sheet->getHighestColumn();
     $excel2->setActiveSheetIndex(0);
     $excel2->getActiveSheet()->setCellValue('H4', '4')->setCellValue('H5', '5')->setCellValue('H6', '6')->setCellValue('H7', '7')->setCellValue('H8', '7');
     //  Loop through each row of the worksheet in turn
     for ($row = 1; $row <= $highestRow; $row++) {
         //  Read a row of data into an array
         $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
         if (isset($rowData[0][2]) && $rowData[0][2] != 'Product Code') {
             $excel2->getActiveSheet()->setCellValue("H{$row}", '7');
         }
     }
     $objWriter = PHPExcel_IOFactory::createWriter($excel2, $inputFileType);
     $objWriter->save("print_docs/excel/excel_files/" . $file_name);
 }

 /**
  * [excel2Array description]
  * 
  * @param  [type] $filepath [description]
  * @param  array  $result   [description]
  * @return [type]           [description]
  */
 public function excel2Array($filepath = null, $result = array())
 {
     if (!file_exists($filepath)) {
         App::abort('500', "Error loading file " . $filepath . ": File does not exist");
     }
     // Read your Excel workbook
     try {
         $inputFileType = \PHPExcel_IOFactory::identify($filepath);
         $objReader = \PHPExcel_IOFactory::createReader($inputFileType);
         $objPHPExcel = $objReader->load($filepath);
     } catch (Exception $e) {
         App::abort('500', "Error loading file " . pathinfo($filepath, PATHINFO_BASENAME) . ": " . $e->getMessage());
     }
     $i = 0;
     // Loop through each worksheet
     foreach ($objPHPExcel->getWorksheetIterator() as $sheet) {
         // Get worksheet dimensions
         $highestRow = $sheet->getHighestRow();
         $highestColumn = $sheet->getHighestColumn();
         // Loop through each row of the worksheet in turn
         for ($row = 1; $row <= $highestRow; $row++) {
             // Read a row of data into an array
             $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
             $result[$i][] = $rowData;
         }
         $i++;
     }
     return $result;
 }

 public function __construct($filename = null, $sheet = 0)
 {
     $this->filename = $filename;
     if (file_exists($filename)) {
         /* on charge le contenu du fichier dans l'objet phpExcel */
         $inputFileType = PHPExcel_IOFactory::identify($filename);
         $objReader = PHPExcel_IOFactory::createReader($inputFileType);
         $this->phpExcel = $objReader->load($filename);
         /* on implémente dans le tableau */
         //  Get worksheet dimensions
         $sheet = $this->phpExcel->getSheet($sheet);
         $highestRow = $sheet->getHighestRow();
         $highestColumn = $sheet->getHighestColumn();
         //  Loop through each row of the worksheet in turn
         for ($row = 1; $row <= $highestRow; $row++) {
             //  Read a row of data into an array
             $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
             $this->container[] = $rowData[0];
         }
     } else {
         $this->phpExcel = new PHPExcel();
         $this->phpExcel->setActiveSheetIndex($sheet);
     }
     parent::__construct($this->container);
 }

 /**
  * {@inheritDoc}
  */
 public function read()
 {
     $inputFileName = $this->filename;
     try {
         $inputFileType = \PHPExcel_IOFactory::identify($inputFileName);
         $objReader = \PHPExcel_IOFactory::createReader($inputFileType);
         $objPHPExcel = $objReader->load($inputFileName);
     } catch (\Exception $e) {
         die(sprintf('Error loading file "%s": %s', pathinfo($inputFileName, PATHINFO_BASENAME), $e->getMessage()));
     }
     $sheet = $objPHPExcel->getSheet(0);
     $highestRow = $sheet->getHighestRow();
     $highestColumn = $sheet->getHighestColumn();
     if (0 !== $this->offset) {
         //@todo implement
         throw new \InvalidArgumentException('Offset is not yet supported');
     }
     $properties = [];
     for ($rowIndex = 1; $rowIndex <= $highestRow; $rowIndex++) {
         $values = $sheet->rangeToArray('A' . $rowIndex . ':' . $highestColumn . $rowIndex, null, null, false);
         $values = $values[0];
         if ($this->headerDetector->isHeader($rowIndex, $values)) {
             $properties = $this->describeProperties($values);
             continue;
         }
         if ($rowIndex < $this->offset) {
             continue;
         }
         if (!count($properties)) {
             continue;
         }
         $this->processValues($properties, $values);
     }
 }

 public function actionImport()
 {
     $field = ['fileImport' => 'File Import'];
     $modelImport = DynamicModel::validateData($field, [[['fileImport'], 'required'], [['fileImport'], 'file', 'extensions' => 'xls,xlsx', 'maxSize' => 1024 * 1024]]);
     if (Yii::$app->request->post()) {
         $modelImport->fileImport = \yii\web\UploadedFile::getInstance($modelImport, 'fileImport');
         if ($modelImport->fileImport && $modelImport->validate()) {
             $inputFileType = \PHPExcel_IOFactory::identify($modelImport->fileImport->tempName);
             $objReader = \PHPExcel_IOFactory::createReader($inputFileType);
             $objPHPExcel = $objReader->load($modelImport->fileImport->tempName);
             $sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
             $baseRow = 2;
             while (!empty($sheetData[$baseRow]['A'])) {
                 $model = new Mahasiswa();
                 $model->nama = (string) $sheetData[$baseRow]['B'];
                 $model->nim = (string) $sheetData[$baseRow]['C'];
                 $model->save();
                 //die(print_r($model->errors));
                 $baseRow++;
             }
             Yii::$app->getSession()->setFlash('success', 'Success');
         } else {
             Yii::$app->getSession()->setFlash('error', 'Error');
         }
     }
     return $this->redirect(['index']);
 }

 public function test()
 {
     $inputFileName = 'application/xls/Catalogo_clientes.xlsx';
     //  Read your Excel workbook
     try {
         $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
         // var_dump($inputFileType);
         $objReader = PHPExcel_IOFactory::createReader($inputFileType);
         $objPHPExcel = $objReader->load($inputFileName);
         //var_dump($objPHPExcel);
     } catch (Exception $e) {
         die('Error loading file "' . pathinfo($inputFileName, PATHINFO_BASENAME) . '": ' . $e->getMessage());
     }
     //  Get worksheet dimensions
     $sheet = $objPHPExcel->getSheet(0);
     $highestRow = $sheet->getHighestRow();
     $highestColumn = $sheet->getHighestColumn();
     //  Loop through each row of the worksheet in turn
     for ($row = 4; $row <= $highestRow; $row++) {
         //  Read a row of data into an array
         $rowData[] = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
         //  Insert row data array into your database of choice here
     }
     return $rowData;
 }

function read_sheet($file, $employ_array)
{
    require_once dirname(__FILE__) . '/Classes/PHPExcel/IOFactory.php';
    $inputFileName = $file;
    //  Read your Excel workbook
    try {
        $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
        $objReader = PHPExcel_IOFactory::createReader($inputFileType);
        $objPHPExcel = $objReader->load($inputFileName);
    } catch (Exception $e) {
        die('Error loading file "' . pathinfo($inputFileName, PATHINFO_BASENAME) . '": ' . $e->getMessage());
    }
    $rowIterator = $objPHPExcel->getActiveSheet()->getRowIterator();
    $array_data = array();
    $i = 1;
    foreach ($rowIterator as $row) {
        if ($i > sizeof($employ_array) + 2) {
            break;
        }
        $cellIterator = $row->getCellIterator();
        $cellIterator->setIterateOnlyExistingCells(false);
        $rowIndex = $row->getRowIndex();
        $array_data[$rowIndex] = array('A' => '', 'B' => '', 'C' => '', 'D' => '', 'E' => '');
        foreach ($cellIterator as $cell) {
            if ('A' == $cell->getColumn()) {
                $array_data[$rowIndex][$cell->getColumn()] = $cell->getCalculatedValue();
            } else {
                if ('B' == $cell->getColumn()) {
                    $array_data[$rowIndex][$cell->getColumn()] = $cell->getCalculatedValue();
                } else {
                    if ('C' == $cell->getColumn()) {
                        $array_data[$rowIndex][$cell->getColumn()] = $cell->getCalculatedValue();
                    } else {
                        if ('D' == $cell->getColumn()) {
                            $array_data[$rowIndex][$cell->getColumn()] = $cell->getCalculatedValue();
                        } else {
                            if ('E' == $cell->getColumn()) {
                                $array_data[$rowIndex][$cell->getColumn()] = $cell->getCalculatedValue();
                            } else {
                                if ('F' == $cell->getColumn()) {
                                    $array_data[$rowIndex][$cell->getColumn()] = $cell->getCalculatedValue();
                                } else {
                                    if ('G' == $cell->getColumn()) {
                                        $array_data[$rowIndex][$cell->getColumn()] = $cell->getCalculatedValue();
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
        $i++;
    }
    unset($array_data[1]);
    unset($array_data[2]);
    echo '<pre>';
    return $array_data;
}

 public static function readXLS($xlsfile)
 {
     $fileType = PHPExcel_IOFactory::identify($xlsfile);
     $objReader = PHPExcel_IOFactory::createReader($fileType);
     $objPHPExcel = $objReader->load($xlsfile);
     $sheets = $objPHPExcel->getSheet(0)->toArray();
     return $sheets;
 }

 public function __construct($file_path)
 {
     require_once "../app/classes/PHPExcel.php";
     #将单元格序列化后再进行Gzip压缩，然后保存在内存中
     PHPExcel_Settings::setCacheStorageMethod(PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip);
     $file_type = PHPExcel_IOFactory::identify($file_path);
     $objReader = PHPExcel_IOFactory::createReader($file_type);
     self::$objPHPExcel = $objReader->load($file_path);
 }

function convertXLStoCSV($infile, $outfile)
{
    $fileType = PHPExcel_IOFactory::identify($infile);
    $objReader = PHPExcel_IOFactory::createReader($fileType);
    $objReader->setReadDataOnly(true);
    $objPHPExcel = $objReader->load($infile);
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'CSV');
    $objWriter->save($outfile);
}

 function importXLS($filename)
 {
     //precondition: XLS file has been uploaded ($this->uploadXLS)
     //use PHPExcel for this
     ini_set('memory_limit', '512M');
     $this->load->library('PHPExcel');
     $msg = '';
     $inputFileName = $filename;
     $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
     $objReader = PHPExcel_IOFactory::createReader($inputFileType);
     //find sheet data_import_spec
     $sheetname = 'IWP participants';
     $objReader->setLoadSheetsOnly($sheetname);
     $objPHPExcel = $objReader->load($inputFileName);
     if ($objPHPExcel->getSheetCount() != 1) {
         //sheet not found
         $msg .= "A worksheet named 'IWP Participants' was not found in this workbook";
         return $msg;
     } else {
         $this->dbAdmin = $this->load->database('admin', true);
         $this->dbAdmin->truncate('masterlist');
         $sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
         //in fieldmap, the excel column name is the key and the table field name is the value
         $fieldmap = array('macroregion' => 'macro_region', 'REGION' => 'region', 'COUNTRY' => 'country', 'IWP PROGRAM' => 'iwp_program', 'YEAR(S)' => 'years', 'FAMILY NAME' => 'family_name', 'GIVEN NAME' => 'given_name', 'OCLC' => 'oclc', 'penname' => 'penname', 'drupal_family_name' => 'drupal_family_name', 'drupal_given_name' => 'drupal_given_name', 'drupal_nid' => 'drupal_nid');
         foreach ($sheetData as $rownum => $columns) {
             if ($rownum > 2) {
                 //skip first two rows
                 if ($rownum == 3) {
                     //build fieldlist array by matching column headings in first row stored in third row
                     foreach ($columns as $column_name => $column_value) {
                         $column_value = trim($column_value);
                         if (array_key_exists($column_value, $fieldmap)) {
                             $fieldlist[$column_name] = $fieldmap[$column_value];
                             //ex $fieldlist['B'] = $fieldmap['macroregion']
                         }
                     }
                 } else {
                     //build insert array on subsequent rows and submit query
                     //foreach ($columns as $column_name=>$column_value){
                     $blankrow = true;
                     foreach ($fieldlist as $xcelCol => $masterlistField) {
                         if (!empty($columns[$xcelCol])) {
                             $blankrow = false;
                         }
                         $data[$masterlistField] = $columns[$xcelCol];
                     }
                     if (!$blankrow) {
                         $this->db->insert('masterlist', $data);
                         $recordsprocessed = $rownum - 1;
                     }
                 }
             }
         }
         $msg = "Processed {$recordsprocessed} rows from the masterlist worksheet.";
     }
     return $msg;
 }

 /**
  * @return \PHPExcel
  */
 public function get($id)
 {
     Assertion::notEmpty($id);
     Assertion::string($id);
     $filePath = $this->directory . $id;
     $inputFileType = \PHPExcel_IOFactory::identify($filePath);
     $objReader = \PHPExcel_IOFactory::createReader($inputFileType);
     return $objReader->load($filePath);
 }

 public function prepare()
 {
     $this->files = array();
     $templatePath = $this->templatePath;
     if (!empty($_FILES)) {
         $tempFile = $_FILES['CrudCode']['tmp_name']['file'];
         $fileTypes = array('xls', 'xlsx');
         // File extensions
         $fileParts = pathinfo($_FILES['CrudCode']['name']['file']);
         if (in_array(@$fileParts['extension'], $fileTypes)) {
             Yii::import('ext.heart.excel.EHeartExcel', true);
             EHeartExcel::init();
             $inputFileType = PHPExcel_IOFactory::identify($tempFile);
             $objReader = PHPExcel_IOFactory::createReader($inputFileType);
             $objPHPExcel = $objReader->load($tempFile);
             $sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
             $baseRow = 2;
             $inserted = 0;
             $read_status = false;
             //die();
             while (!empty($sheetData[$baseRow]['A'])) {
                 $read_status = true;
                 $this->modelType = $sheetData[$baseRow]['B'];
                 $this->model = $sheetData[$baseRow]['C'];
                 $this->controller = $sheetData[$baseRow]['D'];
                 $this->modelParent = $sheetData[$baseRow]['E'];
                 $this->controllerParent = $sheetData[$baseRow]['F'];
                 $class = @Yii::import($this->model, true);
                 $table = CActiveRecord::model($class)->tableSchema;
                 $this->_modelClass = $class;
                 $this->_table = $table;
                 $controllerTemplateFile = $templatePath . DIRECTORY_SEPARATOR . 'controller.php';
                 $this->files[] = new CCodeFile($this->controllerFile, $this->render($controllerTemplateFile));
                 $files = scandir($templatePath);
                 foreach ($files as $file) {
                     if (is_file($templatePath . '/' . $file) && CFileHelper::getExtension($file) === 'php' && $file !== 'controller.php') {
                         $this->files[] = new CCodeFile($this->viewPath . DIRECTORY_SEPARATOR . $file, $this->render($templatePath . '/' . $file));
                     }
                 }
                 $baseRow++;
             }
             if ($this->files != array()) {
                 $this->save();
             }
         }
     } else {
         $controllerTemplateFile = $templatePath . DIRECTORY_SEPARATOR . 'controller.php';
         $this->files[] = new CCodeFile($this->controllerFile, $this->render($controllerTemplateFile));
         $files = scandir($templatePath);
         foreach ($files as $file) {
             if (is_file($templatePath . '/' . $file) && CFileHelper::getExtension($file) === 'php' && $file !== 'controller.php') {
                 $this->files[] = new CCodeFile($this->viewPath . DIRECTORY_SEPARATOR . $file, $this->render($templatePath . '/' . $file));
             }
         }
     }
 }