PHP PHPExcel_IOFactory::createReader方法代码示例

 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 function convertCsvToExcel($loadPath, $outputPath)
 {
     $objReader = \PHPExcel_IOFactory::createReader('CSV');
     $objPHPExcel = $objReader->load($loadPath);
     $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
     $objWriter->save($outputPath);
 }

 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;
 }

function parseFile($file, $type)
{
    $sql = new MySQL();
    $sql->connect('127.0.0.1', 'root', 'root');
    $objReader = PHPExcel_IOFactory::createReader($type);
    $chunkSize = 200;
    $i = 1;
    $sql->clear('price_liga');
    $r = array();
    for ($startRow = 0; $startRow <= 5000; $startRow += $chunkSize + 1) {
        $chunkFilter = new chunkReadFilter($startRow, $chunkSize);
        $objReader->setReadFilter($chunkFilter);
        $objReader->setReadDataOnly(true);
        $objPHPExcel = $objReader->load($file);
        $data = $objPHPExcel->getActiveSheet()->toArray();
        foreach ($data as $k => $v) {
            if ($data[$k][0] == '') {
                unset($data[$k]);
            } else {
                $sql->insert('price_liga', array('id' => $i, 'cat_num' => $data[$k][0], 'brand' => ucwords(strtolower($data[$k][1])), 'article' => $data[$k][2], 'descr' => str_replace("'", "\\'", $data[$k][3]), 'model' => str_replace("'", "\\'", $data[$k][4]), 'size' => $data[$k][5], 'price' => $data[$k][6], 'amount' => $data[$k][8]), true);
                $i++;
            }
        }
    }
    //print_r($r);
    $sql->close();
    return array('counter' => $i);
}

 public function actionExcel($id)
 {
     $po = $this->loadModel($id);
     $poItemCriteria = new CDbCriteria();
     $poItemCriteria->with = array('material');
     $poItemCriteria->compare('po_number', $id);
     $poItemDataProvider = new CActiveDataProvider('PoItems', array('criteria' => $poItemCriteria, 'pagination' => false));
     Yii::import('ext.phpexcel.XPHPExcel');
     $objPHPExcel = XPHPExcel::createPHPExcel();
     $objReader = PHPExcel_IOFactory::createReader('Excel5');
     $objPHPExcel = $objReader->load(Yii::app()->basePath . DIRECTORY_SEPARATOR . 'data' . DIRECTORY_SEPARATOR . "templates" . DIRECTORY_SEPARATOR . "PO_Template.xls");
     $objPHPExcel->getProperties()->setCreator(Yii::app()->user->name)->setLastModifiedBy(Yii::app()->user->name)->setTitle("PO Order-" . $id);
     //->setSubject("Office 2007 XLSX Test Document")
     //->setDescription("Sales Order#")
     //->setKeywords("office 2007 openxml php")
     //->setCategory("Test result file");
     // Add the data
     $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C2', $po->po_number)->setCellValue('C3', Yii::app()->dateFormatter->formatDateTime($po->maturity_date, "short", null))->setCellValue('C4', Yii::app()->dateFormatter->formatDateTime($po->created, "short", null))->setCellValue('C5', Yii::app()->dateFormatter->formatDateTime($po->updated, "short", null))->setCellValue('E2', $po->comp->name)->setCellValue('E3', $po->contact)->setCellValue('E4', $po->contact_telephone)->setCellValue('C6', $po->paymentTerm->description)->setCellValue('C7', $po->is_open ? "Open" : "Closed")->setCellValue('A8', "Comments:\r" . $po->comments);
     $row = 13;
     //$i = 1;
     // Write the sale items now
     $items = $poItemDataProvider->getData();
     foreach ($items as $item) {
         $objPHPExcel->setActiveSheetIndex(0)->setCellValue("B" . $row, $item->material->cat->description . "-" . $item->material->description)->setCellValue("D" . $row, $item->qty)->setCellValue("E" . $row, $item->qty_units)->setCellValue("F" . $row, $item->unit_price)->setCellValue("G" . $row, $item->price_units)->setCellValue("H" . $row, $item->qty_recieved)->setCellValue("I" . $row, $item->qty_diff);
         //$i++;
         $row++;
     }
     $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
     // Redirect output to a client’s web browser
     //header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
     header('Content-Type: application/vnd.ms-excel');
     header('Content-Disposition: attachment;filename=' . '"PurchaseOrder-' . $id . '.xls"');
     header('Cache-Control: max-age=0');
     $objWriter->save('php://output');
 }

 private function readExcel($key = 0)
 {
     error_reporting(E_ALL);
     date_default_timezone_set('Asia/ShangHai');
     require_once 'Classes/PHPExcel/IOFactory.php';
     $reader = PHPExcel_IOFactory::createReader('Excel2007');
     //设置格式
     foreach ($GLOBALS['excelfile'] as $key => $value) {
         $value = EXCEL_PATH . $value;
         if (!file_exists($value)) {
             exit("not found {$value}.\n");
         }
         $PHPExcel = $reader->load($value);
         // 载入excel文件
         $sheet = $PHPExcel->getSheet(0);
         // 读取第一個工作表
         $sheetName = $sheet->getTitle();
         $allRow = $sheet->getHighestRow();
         // 取得总行数
         $allColumm = $sheet->getHighestColumn();
         // 取得总列数
         if ($key == 1) {
             $this->outputLuaByMap($sheet, $sheetName, $allRow, $allColumm);
         } else {
             $this->outputLuaByList($sheet, $sheetName, $allRow, $allColumm);
         }
         $this->outputXml($sheet, $sheetName, $allRow, $allColumm, $key);
     }
 }

 public function importExcel($fileID)
 {
     $filename = $this->storeExcel($fileID);
     if ($filename != false) {
         $objReader = PHPExcel_IOFactory::createReader('Excel2007');
         $objPHPExcel = PHPExcel_IOFactory::load($this->path . $filename);
         $sheet = $objPHPExcel->getSheet(0);
         $highestRow = $sheet->getHighestRow();
         //取得行数
         $highestColumn = $sheet->getHighestColumn();
         // 取得列数
         $result = array();
         for ($j = 1; $j <= $highestRow; $j++) {
             for ($k = 'A'; $k <= $highestColumn; $k++) {
                 $str = $objPHPExcel->getActiveSheet()->getCell("{$k}{$j}")->getValue();
                 //这个地方的字符处理有问题，当把array传递到createAccount_run页面的时候会出现乱码
                 //                     $str = iconv('gbk','utf-8',$str);
                 $result[$j][$k] = $str;
             }
         }
         unlink($this->path . $filename);
         //删除文件
         return $result;
     } else {
         return false;
     }
 }

 /**
  * Write statistics to Excel table
  *
  * @param  string $file File name
  * @return void
  */
 public function Write($file)
 {
     if (file_exists($file) && NULL !== $this->session->userdata('Write_statistics') && $this->session->userdata('Write_statistics')) {
         $this->session->unset_userdata('Write_statistics');
         $this->load->library('Excel');
         // Open file
         $fileType = 'Excel2007';
         $objReader = PHPExcel_IOFactory::createReader($fileType);
         $objPHPExcel = $objReader->load($file);
         $objPHPExcel->setActiveSheetIndex(0);
         $objWorksheet = $objPHPExcel->getActiveSheet();
         $highestRow = $objWorksheet->getHighestRow();
         $dateFound = FALSE;
         $sum = 0;
         $currentDate = date('Y.m.d');
         for ($row = 1; $row <= $highestRow; ++$row) {
             $value = $objWorksheet->getCellByColumnAndRow(0, $row)->getValue();
             $sum = max($sum, $objWorksheet->getCellByColumnAndRow(1, $row)->getValue());
             if ($value == $currentDate) {
                 $dateFound = TRUE;
                 $objPHPExcel = $this->Update($objPHPExcel, $row, $sum, $currentDate);
             }
         }
         if (!$dateFound) {
             $objPHPExcel = $this->Update($objPHPExcel, $highestRow + 1, $sum, $currentDate);
         }
         // Write the file
         $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, $fileType);
         $objWriter->save($file);
     }
 }

function excelToArray($file)
{
    $objReader = PHPExcel_IOFactory::createReader('Excel5');
    $objReader->setReadDataOnly(true);
    $objPHPExcel = $objReader->load($file);
    //读取文件
    $objWorksheet = $objPHPExcel->getActiveSheet(0);
    //读取excel文件中的第一个工作表
    $highestRow = $objWorksheet->getHighestRow();
    //计算总行数
    $highestColumn = $objWorksheet->getHighestColumn();
    //取得列数中最大的字母。如(J)
    $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
    //通过字母计算总列数
    $excelData = array();
    //存放读取的数据
    for ($row = 2; $row <= $highestRow; ++$row) {
        //从第二行开始读取数据
        for ($col = 0; $col <= $highestColumnIndex; ++$col) {
            //读取每行中的各列
            //把读取的数据放入数组中
            $excelData[$row - 2][] = $objWorksheet->getCellByColumnAndRow($col, $row)->getValue();
        }
    }
    return $excelData;
}

 public function customerimport()
 {
     require_once APPPATH . 'third_party/phpexcel/Classes/PHPExcel/IOFactory.php';
     $path = "C://inetpub/ftproot/retailersurvey/";
     $filename = 'NAV Active Customer and Ship-to Address List.xlsx';
     if (!file_exists($path . $filename)) {
         exit("file not found" . EOL);
     }
     $objReader = PHPExcel_IOFactory::createReader('Excel2007');
     $objPHPExcel = $objReader->load($path . $filename);
     $sheetNames = $objPHPExcel->getSheetNames();
     $sheetindex = '0';
     $sheet = $objPHPExcel->getSheet($sheetindex);
     $startRow = 2;
     $highestRow = $sheet->getHighestRow();
     $comma = "";
     $customeridlist = '';
     $data = array();
     for ($row = $startRow; $row <= $highestRow; $row++) {
         if (trim($sheet->getCell('A' . $row)->getValue()) != '') {
             $temp = $this->cronjob_model->updatecustomer(trim($sheet->getCell('A' . $row)->getValue()), trim($sheet->getCell('B' . $row)->getValue()));
             if (!$temp) {
             } else {
                 $customeridlist .= $comma . $temp;
                 $comma = ', ';
             }
             //echo trim($sheet->getCell('A'.$row)->getValue())." | ".trim($sheet->getCell('B'.$row)->getValue())."\n";
         }
     }
     $this->cronjob_model->updatecustomerlist($customeridlist);
 }

 public function index()
 {
     //load library excel
     $this->load->library('excel');
     //Here i used microsoft excel 2007
     $objReader = PHPExcel_IOFactory::createReader('Excel2007');
     //Set to read only
     $objReader->setReadDataOnly(true);
     //Load excel file
     $objPHPExcel = $objReader->load('assets/uploads/files/data.xlsx');
     $objWorksheet = $objPHPExcel->setActiveSheetIndex(0);
     //load model
     $this->load->model('excelModel');
     //loop from first data untill last data
     for ($i = 2; $i <= 4; $i++) {
         $n_nota = $objWorksheet->getCellByColumnAndRow(0, $i)->getValue();
         $nit_cc = $objWorksheet->getCellByColumnAndRow(1, $i)->getValue();
         $nombre = $objWorksheet->getCellByColumnAndRow(2, $i)->getValue();
         $direccion = $objWorksheet->getCellByColumnAndRow(3, $i)->getValue();
         $barrio = $objWorksheet->getCellByColumnAndRow(4, $i)->getValue();
         $telefono = $objWorksheet->getCellByColumnAndRow(5, $i)->getValue();
         $descripcion = $objWorksheet->getCellByColumnAndRow(6, $i)->getValue();
         $cantidad = $objWorksheet->getCellByColumnAndRow(7, $i)->getValue();
         $fecha = $objWorksheet->getCellByColumnAndRow(8, $i)->getValue();
         $hora = $objWorksheet->getCellByColumnAndRow(9, $i)->getValue();
         $data_user = array('n_nota' => $n_nota, 'nit_cc' => $nit_cc, 'nombre' => $nombre, 'direccion' => $direccion, 'barrio' => $barrio, 'telefono' => $telefono, 'descripcion' => $descripcion, 'cantidad' => $cantidad, 'fecha' => $fecha, 'hora_servicio' => $hora, 'cod_zona' => $i, 'cod_estado' => 2);
         $this->excelModel->add_data($data_user);
     }
     echo 'listo';
 }

 function insertarExcel($array)
 {
     $uploadOk = 1;
     $time = time();
     $fecha = date("Y-m-d", $time);
     $target_dir = "../documents/";
     $target_file = $target_dir . basename($_FILES["archivoExcel"]["name"]);
     move_uploaded_file($array["archivoExcel"]["tmp_name"], $target_file);
     set_include_path(get_include_path() . PATH_SEPARATOR . '../complements/PHPExcel-1.8/Classes/');
     $inputFileType = 'Excel2007';
     include 'PHPExcel/IOFactory.php';
     $inputFileName = $target_file;
     $objReader = PHPExcel_IOFactory::createReader($inputFileType);
     $objReader->setReadDataOnly(true);
     $objPHPExcel = $objReader->load($inputFileName);
     $sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
     require_once "../db/conexiones.php";
     $consulta = new Conexion();
     foreach ($sheetData as $datos) {
         $nombreSinAcentos = sanear_string($datos['B']);
         $nombre = strtoupper(trim($nombreSinAcentos));
         $datosEmpleado = $consulta->Conectar("postgres", "SELECT * FROM userinfo WHERE UPPER(name)='" . $nombre . "'");
         if ($datosEmpleado) {
             $sqlInsert = $this->invoco->Conectar("postgres", "INSERT INTO horario_personal (user_id, banda_id, fecha) VALUES (" . $datosEmpleado[0]['userid'] . "," . $datos['C'] . ", '" . $fecha . "')");
         }
     }
     return "Se insertaron los datos Exitosamente!";
 }

 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);
 }

 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 import_dt($inputFileName)
 {
     try {
         //$inputFileType = 'Excel2007';
         $inputFileType = 'Excel5';
         $objReader = PHPExcel_IOFactory::createReader($inputFileType);
         $objPHPExcel = $objReader->load($inputFileName);
         $data = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
         $result = '';
         $session = new Zend_Session_Namespace('import_dt');
         $deTais = array();
         $thanhViens = array();
         $count = -1;
         for ($row = 10; $row <= count($data); $row++) {
             //if cell[$row,B] là đề tài mới
             if (!empty($data[$row]['B'])) {
                 //luu de tai
                 $cap_quan_ly = Default_Model_Functions::convert_vi_to_en(strtolower(trim($data[$row]['M'])));
                 $deTais[] = array('ten' => $data[$row]['B'], 'linh_vuc' => trim($data[$row]['C']), 'thoi_gian_bat_dau' => trim($data[$row]['K']), 'thoi_gian_hoan_thanh' => trim($data[$row]['L']), 'cap_quan_ly' => $cap_quan_ly, 'kinh_phi' => trim($data[$row]['N']));
                 //luu chu nhiem
                 $count++;
                 $thanhViens[$count][] = array('ma_giang_vien' => trim($data[$row]['E']), 'ho_ten' => preg_replace('/\\s+/u', ' ', trim($data[$row]['F'])), 'hoc_vi' => trim($data[$row]['G']), 'ghi_chu' => trim($data[$row]['H']), 'nhiem_vu' => '1', 'email' => trim($data[$row]['I']), 'so_dien_thoai' => trim($data[$row]['J']));
             } else {
                 //luu thanh vien neu co
                 if (!empty($data[$row]['F'])) {
                     $thanhViens[$count][] = array('ma_giang_vien' => trim($data[$row]['E']), 'ho_ten' => preg_replace('/\\s+/u', ' ', trim($data[$row]['F'])), 'hoc_vi' => trim($data[$row]['G']), 'ghi_chu' => trim($data[$row]['H']), 'nhiem_vu' => '0', 'email' => trim($data[$row]['I']), 'so_dien_thoai' => trim($data[$row]['J']));
                 }
             }
         }
         $session->deTais = $deTais;
         $session->thanhViens = $thanhViens;
     } catch (Zend_Exception $ex) {
         throw $ex;
     }
 }