PHP PHPExcel_IOFactory::load方法代码示例

 public function importExcel($function = null)
 {
     if ($this->upload_file()) {
         if ($function) {
             $this->onImportRow = $function;
         }
         //$reader = \PHPExcel_IOFactory::createReader( /*$this->defaultFormat*/ );
         $objPHPExcel = \PHPExcel_IOFactory::load($this->_uploaded_file->tempName);
         //$objPHPExcel->setActiveSheetIndex(0);
         $objWorksheet = $objPHPExcel->getActiveSheet();
         $highestRow = $objWorksheet->getHighestRow();
         foreach ($objWorksheet->getRowIterator() as $i => $row) {
             $cellIterator = $row->getCellIterator();
             $cellIterator->setIterateOnlyExistingCells(false);
             $row = [];
             foreach ($cellIterator as $cell) {
                 $row[] = $cell->getValue();
             }
             $this->_current_row = $i;
             if ($this->import_row(['row' => $row, 'index' => $i, 'max_row' => $highestRow])) {
             } else {
                 break;
             }
         }
     }
 }

 public function Run()
 {
     $resArray = [];
     $objPHPExcel = @\PHPExcel_IOFactory::load($this->filePath);
     if ($objPHPExcel) {
         $sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
         $headerArray = $sheetData[1];
         unset($sheetData[1]);
         foreach ($sheetData as $k => $itemArray) {
             $itemArray = @array_combine($headerArray, $itemArray);
             if ($itemArray) {
                 try {
                     $model = DynamicModel::validateData($itemArray, [[['id', 'type', 'available', 'bid', 'price', 'currencyId', 'categoryId', 'name', 'ISBN'], 'required']]);
                 } catch (UnknownPropertyException $e) {
                     continue;
                 }
                 if (isset($model) && !$model->hasErrors()) {
                     $resArray[] = $itemArray;
                 }
             }
         }
     }
     $this->response = $resArray;
     unlink($this->filePath);
     return $this;
 }

 function addxls()
 {
     $data['error'] = '';
     //$area=$_POST['area_id'];
     //$city=$_POST['city_id'];
     $file_path = $_FILES['csv']['tmp_name'];
     $file_type = $_FILES['csv']['type'];
     $this->load->library('PHPExcel');
     if ($file_type == 'text/csv') {
         $objReader = new PHPExcel_Reader_CSV();
         $PHPExcel = $objReader->load($file_path);
     } else {
         //echo "bye";die();
         $PHPExcel = PHPExcel_IOFactory::load($file_path);
     }
     $objWorksheet = $PHPExcel->getActiveSheet();
     $highestrow = $objWorksheet->getHighestRow();
     //echo $highestrow;die();
     for ($i = 2; $i <= $highestrow; $i++) {
         $obj_insData = array('State' => addslashes($PHPExcel->getActiveSheet()->getCell('A' . $i)->getCalculatedValue()));
         //$saveddata = $this->xls_model->getdata($obj_insData['Code']);
         if ($obj_insData == '' && count($obj_insData) == '0') {
             continue;
         } else {
             //echo "hello"; die();
             mysql_query("INSERT INTO  addessdata (state,city,pincode) VALUES\n\t\t\t\t\t(\n\t\t\t\t\t\t\n\t\t\t'" . addslashes($PHPExcel->getActiveSheet()->getCell('A' . $i)->getCalculatedValue()) . "',\n\t\t\t'" . addslashes($PHPExcel->getActiveSheet()->getCell('B' . $i)->getCalculatedValue()) . "',\n\t\t\t'" . addslashes($PHPExcel->getActiveSheet()->getCell('C' . $i)->getCalculatedValue()) . "'\n\t\t\t \n\t\t\t \n\t\t\t \n\t\t\t\t\t)\n\t\t\t\t");
         }
     }
     $this->session->set_flashdata('message', 'Your Data File Uploaded Succcessfully.!!');
     redirect($this->config->item('base_url') . 'xlsdata/lists');
 }

 /**
  * Load excel file
  * @param  string $filename filename to be loaded
  */
 protected function loadFile($filename)
 {
     if (!file_exists($filename)) {
         throw new \Exception($filename . " Not Found!");
     }
     $this->phpExcel = \PHPExcel_IOFactory::load($filename);
 }

function import_Books($file)
{
    set_include_path(get_include_path() . PATH_SEPARATOR . 'Classes/');
    include 'PHPExcel/IOFactory.php';
    require_once 'private/LMS_Engine.php';
    $engine = new LMS_Engine();
    try {
        $objPHPExcel = PHPExcel_IOFactory::load($file);
        $Total_Sheet = $objPHPExcel->getSheetCount();
        for ($num = 0; $num < $Total_Sheet; $num++) {
            $Sheet = $objPHPExcel->getSheet($num)->toArray(null, true, true, true);
            $Row = count($Sheet);
            for ($pos = 3; $pos < $Row; $pos++) {
                $Title = trim($Sheet[$pos]['B']);
                $Author = trim($Sheet[$pos]['C']);
                $Publisher = trim($Sheet[$pos]['D']);
                $Pub_Year = trim($Sheet[$pos]['E']);
                $Pub_Add = trim($Sheet[$pos]['F']);
                $Call_ID = trim($Sheet[$pos]['G']);
                $Copy_Num = trim($Sheet[$pos]['H']);
                $Category = trim($Sheet[$pos]['I']);
                $Shelf_Store = trim($Sheet[$pos]['J']);
                $engine->add_new_book($Title, 1, $Author, $Publisher, $Pub_Year, $Pub_Add, $Call_ID, $Copy_Num, $Shelf_Store);
            }
        }
    } catch (Exception $e) {
        die('Error loading file "' . pathinfo($file, PATHINFO_BASENAME) . '": ' . $e->getMessage());
    }
}

/**
 * 
 * @param unknown_type $tipoArchivo
 */
function procesarArchivo($tipoArchivo)
{
    //$dbLink = getConnection();
    //ajustamos el maximo de tiempo de ejecucion a 10 minutos para la carga de los archivos
    ini_set("max_execution_time", 60 * 10);
    //limpiamos el archivo de errores para esta corrida
    initErrorFile();
    //leemos el archivo Excel en una estructura mas manejable
    $objPHPExcel = PHPExcel_IOFactory::load(getUploadedXLSFileToProcess());
    if ($tipoArchivo == 'clientes') {
        return insertarCliente($objPHPExcel);
    } else {
        if ($tipoArchivo == 'lineasVentasPaquetes') {
            return insertarLineaVentasPaquetesCredito($objPHPExcel);
        } else {
            if ($tipoArchivo == 'recibos') {
                return insertarRecibo($objPHPExcel);
            } else {
                if ($tipoArchivo == 'ventasPaquetes') {
                    return insertarVentasPaquetesCredito($objPHPExcel);
                }
            }
        }
    }
    //cerramos la conexion a la base de datos
    //mysql_close($dbLink);
    //eliminamos el archivo temporal
    $objPHPExcel->disconnectWorksheets();
    $objPHPExcel = null;
    unlink(getUploadedXLSFileToProcess());
}

 /**
  * Read Excel File Content and parse it into array
  * @param string $file
  * @return array
  */
 public function readFile($file)
 {
     App::import('Vendor', 'PHPExcel/IOFactory');
     $info = PHPExcel_IOFactory::load($file);
     $sheetData = $info->getActiveSheet()->toArray(null, true, true, true);
     return $sheetData;
 }

 public function __construct($filePath = '')
 {
     //如果存在就实例化读取对象
     if (file_exists($filePath)) {
         $this->PHPExcel = PHPExcel_IOFactory::load($filePath);
     }
 }

 public function excel_oku()
 {
     $this->load->library('PHPExcel');
     $dosyaAdi = 'dosyalar/upload/Servis Takip.xlsx';
     $objPHPExcel = PHPExcel_IOFactory::load($dosyaAdi);
     $objPHPExcel->getProperties()->setCreator("Kadir TUTAK")->setLastModifiedBy("Kadir TUTAK")->setTitle("Servis Takip")->setSubject("Servis Takip")->setDescription("Servis Takip")->setKeywords("Kadir TUTAK Servis Takip")->setCategory("Servis Takip");
     /*
     $cell_collection = $objPHPExcel->getActiveSheet()->getCellCollection();
     foreach($cell_collection AS $cell) {
     	$column		= $objPHPExcel->getActiveSheet()->getCell($cell)->getColumn();
     	$row			= $objPHPExcel->getActiveSheet()->getCell($cell)->getRow();
     	$data_value = $objPHPExcel->getActiveSheet()->getCell($cell)->getValue();
     	if($row == 1) {
     		$header[$row][$column] = $data_value;
     	} else {
     		$arr_data[$row][$column] = $data_value;
     	}
     }
     $basliklar = $header;
     $veriler = $arr_data;
     foreach($veriler AS $veri) {
     	echo '<div style="clear: both;">'.$veri['A'].' - '.$veri['B'].' - '.$veri['C'].'</div>';
     }
     */
 }

 function readexcel()
 {
     $file = './excel/akun.xls';
     //load the excel library
     $this->load->library('excel');
     //read file from path
     $objPHPExcel = PHPExcel_IOFactory::load($file);
     //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][$columns] = $data_value;
         } else {
             $arr_data[$row][$col] = $data_value;
         }
     }
     //send the data in an array format
     $data['header'] = $header;
     $data['values'] = $arr_data;
 }

 protected function executeImpl($params, $type, $name, $key)
 {
     if (!$params->check("upload") || isset($_POST[$params->get("upload")])) {
         $loader = new Vizualizer_Plugin($type);
         // アップされたファイルのデータを取得する。
         if ($_FILES[$key]["error"] == UPLOAD_ERR_OK) {
             // Excelファイルを読み込む
             $book = PHPExcel_IOFactory::load($_FILES[$key]["tmp_name"]);
             // 処理を実行する
             $data = $this->process($params, $book);
             // トランザクションの開始
             $connection = Vizualizer_Database_Factory::begin(strtolower($type));
             try {
                 foreach ($data as $item) {
                     $model = $loader->loadModel($name);
                     foreach ($item as $col => $value) {
                         $model->{$col} = $value;
                     }
                     $model->save();
                 }
                 // エラーが無かった場合、処理をコミットする。
                 Vizualizer_Database_Factory::commit($connection);
                 // 画面をリロードする。
                 if (!$this->continue) {
                     // 登録に使用したキーを無効化
                     $this->removeInput("upload");
                     $this->reload();
                 }
             } catch (Exception $e) {
                 Vizualizer_Database_Factory::rollback($connection);
                 throw new Vizualizer_Exception_Database($e);
             }
         }
     }
 }

 public function getXLS($xls, $Activsheet = 0)
 {
     //открывет xls и возвращает масив
     if (file_exists($xls)) {
         include_once 'Classes/PHPExcel/IOFactory.php';
         $objPHPExcel = PHPExcel_IOFactory::load($xls);
         $objPHPExcel->setActiveSheetIndex($Activsheet);
         $aSheet = $objPHPExcel->getActiveSheet();
         $array = array();
         //этот массив будет содержать массивы содержащие в себе значения ячеек каждой строки
         //получим итератор строки и пройдемся по нему циклом
         foreach ($aSheet->getRowIterator() as $row) {
             //получим итератор ячеек текущей строки
             $cellIterator = $row->getCellIterator();
             //пройдемся циклом по ячейкам строки
             $item = array();
             //этот массив будет содержать значения каждой отдельной строки
             foreach ($cellIterator as $cell) {
                 //заносим значения ячеек одной строки в отдельный массив
                 array_push($item, iconv('utf-8', 'cp1251', $cell->getCalculatedValue()));
             }
             //заносим массив со значениями ячеек отдельной строки в "общий массв строк"
             array_push($array, $item);
         }
     } else {
         $array = false;
         echo '<br>файл не существует<br>';
     }
     return $array;
 }

function read_xlsfile($filename)
{
    /** PHPExcel_IOFactory */
    include_once 'Classes/PHPExcel/IOFactory.php';
    $inputFileName = $filename;
    try {
        PHPExcel_Settings::setZipClass(PHPExcel_Settings::PCLZIP);
        $objPHPExcel = PHPExcel_IOFactory::load($inputFileName);
    } catch (Exception $e) {
        die('Error loading file "' . pathinfo($inputFileName, PATHINFO_BASENAME) . '": ' . $e->getMessage());
    }
    $sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
    $row = 0;
    foreach ($sheetData as $v => $i) {
        if ($row == 0) {
            $row = 1;
            continue;
        }
        if (count($i) == 3 && filter_var($i['C'], FILTER_VALIDATE_EMAIL)) {
            $query[] = "('" . implode("','", $i) . "')";
        }
    }
    if (count($query) == 1000) {
        bulk_insert($query);
        $query = '';
    }
    return $query;
}

 /**
  *  Excel 파일을 CSV 파일로 변환
  *
  *  @param  $excelPath string excel 파일 경로
  *  @param  $csvPath   string csv 파일 경로
  *  @return void
  */
 public static function convertExcelIntoCsv(string $excelPath, string $csvPath)
 {
     echo "\nConverting Excel into CSV format...\n";
     try {
         // excel 파일을 로드하여 PHPExcel 선언
         $objPhpExcel = \PHPExcel_IOFactory::load($excelPath);
         // Excel->CSV 형식의 Object로 변환
         $objWriter = new \PHPExcel_Writer_CSV($objPhpExcel);
         // csv 경로에 같은 파일이 있으면 삭제
         if (file_exists($csvPath)) {
             echo "CSV file rewriting...\n";
             unlink($csvPath);
         }
         // 해당 경로에 csv 파일 저장
         $objWriter->save($csvPath);
         echo "Conversion success! \n";
     } catch (\PHPExcel_Reader_Exception $re) {
         die('Error loading file: ' . $e->getMessage());
     } finally {
         // 메모리 release 작업
         if ($objPhpExcel instanceof \PHPExcel_IOFactory) {
             $objPhpExcel->disconnectWorksheets();
             unset($objPhpExcel);
         }
         unset($objWriter);
     }
 }

 protected function _beforeSave(Kwf_Model_Row_Interface $row)
 {
     $model = Kwf_Model_Abstract::getInstance('Kwf_Uploads_Model');
     $uploadsRow = $model->getRow($row->upload_id);
     if (!$uploadsRow) {
         throw new Kwf_Exception_Client(trlKwf('File not found.'));
     }
     $source = $uploadsRow->getFileSource();
     $target = 'temp/xlsimport_' . date('YmdHis') . '.' . $uploadsRow->extension;
     copy($source, $target);
     // copy with extension for xlsimport
     $excel = PHPExcel_IOFactory::load($target);
     if (!$excel) {
         throw new Kwf_Exception_Client(trlKwf('Could not read excel'));
     }
     $excel = $excel->getActiveSheet();
     $message = $this->_import($excel);
     $this->view->message = null;
     if ($message) {
         if (is_string($message)) {
             $this->view->message = nl2br($message);
         }
     }
     $uploadsRow->delete();
     unlink($target);
 }