Пример #1
6
 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;
 }
Пример #2
3
 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;
 }
Пример #3
1
 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);
 }
Пример #4
0
 /**
  * [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;
 }
Пример #5
0
 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);
 }
Пример #6
0
 /**
  * {@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);
     }
 }
Пример #7
0
 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']);
 }
Пример #8
0
 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;
 }
Пример #9
0
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;
}
Пример #10
0
 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;
 }
Пример #11
0
 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);
 }
Пример #12
0
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);
}
Пример #13
0
 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);
 }
Пример #15
0
 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));
             }
         }
     }
 }
Пример #16
0
 /**
  * Creates a new Car4 model.
  * If creation is successful, the browser will be redirected to the 'view' page.
  * @return mixed
  */
 public function actionCreate()
 {
     $model = new Car4();
     if ($model->load(Yii::$app->request->post())) {
         $uploaded = UploadedFile::getInstance($model, 'myfile');
         // $uploaded = UploadedFile::getInstances($model, 'myfile');
         if (!empty($uploaded)) {
             $upfiles = time() . "." . $uploaded->getExtension();
             if ($uploaded->saveAs('../../uploads/' . $upfiles)) {
                 //  $handle = fopen('../../uploads/' . $upfiles, 'r');
             }
             //                echo $handle;
             //                                return;
             $filename = '../../uploads/' . $upfiles;
             $excelfile = \PHPExcel_IOFactory::identify($filename);
             $objReader = \PHPExcel_IOFactory::createReader($excelfile);
             $objfile = $objReader->load($filename);
             $sheet = $objfile->getSheet(0);
             $rowcount = $sheet->getHighestRow();
             $columncount = $sheet->getHighestColumn();
             $result = 0;
             for ($row = 0; $row <= $rowcount; $row++) {
                 $rowdata = $sheet->rangeToArray('A' . $row . ':' . $columncount . $row, NULL, TRUE, FALSE);
                 if ($row == 1) {
                     continue;
                 }
                 $name = $rowdata[0][2];
                 $gen = explode("/", $name);
                 if ($name == '') {
                     continue;
                 }
                 //  echo $rowdata[0][2].'<BR />';
                 // foreach ($gen as $x){echo $x.'<BR />';}
                 // echo $gen[0] . ' ' . $gen[1] . ' ' . $gen[2];
                 //if($row>1){return;}
                 $model2 = new \backend\Models\Car4();
                 $model2->brand = $gen[0];
                 $model2->gen = $gen[1];
                 $model2->o1 = isset($gen[2]) ? $gen[2] : '';
                 $model2->o2 = isset($gen[3]) ? $gen[3] : '';
                 $model2->o3 = isset($gen[4]) ? $gen[4] : '';
                 $model2->o4 = isset($gen[5]) ? $gen[5] : '';
                 $model2->o5 = isset($gen[6]) ? $gen[6] : '';
                 if ($model2->save()) {
                     $result++;
                 }
             }
         }
         if ($result > 0) {
             return $this->redirect(['index']);
         }
     } else {
         return $this->render('create', ['model' => $model]);
     }
 }
 function convertXLStoCSV($infile, $outfile)
 {
     App::import('Vendor', 'phpexcel', array('file' => 'phpexcel' . DS . 'Classes' . DS . 'PHPExcel.php'));
     $fileType = PHPExcel_IOFactory::identify($infile);
     var_dump($fileType);
     $objReader = PHPExcel_IOFactory::createReader($fileType);
     $objReader->setReadDataOnly(true);
     $objPHPExcel = $objReader->load($infile);
     $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'CSV');
     $objWriter->save($outfile);
 }
Пример #18
0
function importUserFromExcel($inputFileName)
{
    if (!class_exists('PHPExcel_IOFactory')) {
        require_once 'phar://' . __DIR__ . '/phpexcel.phar';
    }
    //  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());
    }
    //  Get worksheet dimensions
    $sheet = $objPHPExcel->getSheet(0);
    $highestRow = $sheet->getHighestRow();
    $highestColumn = $sheet->getHighestColumn();
    $fields = array('user_id', 'title', 'first_name', 'last_name', 'email');
    $visible_fields = array();
    $result = array();
    for ($row = 1; $row <= $highestRow; $row++) {
        $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
        if (empty($visible_fields)) {
            foreach ($rowData[0] as $k => $v) {
                if (is_string($v)) {
                    $v = trim($v);
                }
                if (in_array($v, $fields)) {
                    $visible_fields[$v] = $k;
                }
            }
        } else {
            if (!(isset($visible_fields['user_id']) && isset($visible_fields['title']) && isset($visible_fields['first_name']) && isset($visible_fields['last_name']))) {
                echo "require fields (user_id, title, first_name, last_name)";
                break;
            }
            $it = array();
            foreach ($visible_fields as $a => $b) {
                $v = $rowData[0][$b];
                if (is_string($v)) {
                    $v = trim($v);
                }
                $it[$a] = $v;
            }
            if ($it['user_id'] && $it['title'] && $it['first_name'] && $it['last_name']) {
                $result[$it['user_id']] = $it;
            } else {
                unset($it);
            }
        }
    }
    var_dump(array_keys($result));
}
Пример #19
0
 public function display($tpl = null)
 {
     if ($this->getLayout() !== 'modal') {
         // Include helper submenu
         CostbenefitprojectionHelper::addSubmenu('import');
     }
     // Check for errors.
     if (count($errors = $this->get('Errors'))) {
         JError::raiseError(500, implode('<br />', $errors));
         return false;
     }
     $paths = new stdClass();
     $paths->first = '';
     $state = $this->get('state');
     $this->paths =& $paths;
     $this->state =& $state;
     // get global action permissions
     $this->canDo = CostbenefitprojectionHelper::getActions('import');
     // We don't need toolbar in the modal window.
     if ($this->getLayout() !== 'modal') {
         $this->addToolbar();
         $this->sidebar = JHtmlSidebar::render();
     }
     // get the session object
     $session = JFactory::getSession();
     // check if it has package
     $this->hasPackage = $session->get('hasPackage', false);
     $this->dataType = $session->get('dataType', false);
     if ($this->hasPackage && $this->dataType) {
         $this->headerList = json_decode($session->get($this->dataType . '_VDM_IMPORTHEADERS', false), true);
         $this->headerListAdv = array("location_name" => JText::_('COM_COSTBENEFITPROJECTION_LOCATION_NAME'), "year" => JText::_('COM_COSTBENEFITPROJECTION_YEAR'), "cause" => JText::_('COM_COSTBENEFITPROJECTION_CAUSE_ID'), "cause_name" => JText::_('COM_COSTBENEFITPROJECTION_CAUSE_NAME'), "risk" => JText::_('COM_COSTBENEFITPROJECTION_RISK_ID'), "risk_name" => JText::_('COM_COSTBENEFITPROJECTION_RISK_NAME'), "age" => JText::_('COM_COSTBENEFITPROJECTION_AGE_ID'), "age_name" => JText::_('COM_COSTBENEFITPROJECTION_AGE_NAME'), "sex" => JText::_('COM_COSTBENEFITPROJECTION_GENDER_ID'), "sex_name" => JText::_('COM_COSTBENEFITPROJECTION_GENDER_NAME'), "rt_mean" => JText::_('COM_COSTBENEFITPROJECTION_RT_MEAN_VALUE'), "metric" => JText::_('COM_COSTBENEFITPROJECTION_METRIC'), "metric_name" => JText::_('COM_COSTBENEFITPROJECTION_METRIC_NAME'));
         // make sure these files are loaded
         JLoader::import('PHPExcel', JPATH_COMPONENT_ADMINISTRATOR . '/helpers');
         $package = $session->get('package', null);
         $package = json_decode($package, true);
         $inputFileType = PHPExcel_IOFactory::identify($package['dir']);
         if ('csv' == trim(strtolower($inputFileType), '.')) {
             $this->headers = CostbenefitprojectionHelper::getFileHeadersCSV($package['dir']);
         } else {
             $this->headers = CostbenefitprojectionHelper::getFileHeaders($this->dataType);
         }
         // set active tab
         if (in_array('rt_mean', $this->headers) || in_array('metric', $this->headers)) {
             $this->activeTab = 'advanced';
         } else {
             $this->activeTab = 'basic';
         }
         // clear the data type
         $session->clear('dataType');
     }
     // Display the template
     parent::display($tpl);
 }
function excelToArray($filePath, $header = true, $sheets = null)
{
    //Create excel reader after determining the file type
    $inputFileName = $filePath;
    /*  Identify the type of $inputFileName  **/
    $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
    /*  Create a new Reader of the type that has been identified  **/
    $objReader = PHPExcel_IOFactory::createReader($inputFileType);
    /* Set read type to read cell data only **/
    $objReader->setReadDataOnly(true);
    if (!empty($sheets)) {
        # See
        # https://github.com/AmphibiaWeb/amphibian-disease-tracker/blob/master/phpexcel/Documentation/markdown/ReadingSpreadsheetFiles/05-Reader-Options.md#reading-only-named-worksheets-from-a-file
        $objReader->setLoadSheetsOnly($sheets);
    }
    /*  Load $inputFileName to a PHPExcel Object  **/
    $objPHPExcel = $objReader->load($inputFileName);
    $sheetNames = $objPHPExcel->getSheetNames();
    if(!in_array($sheets, $sheetNames, true)) {
        # If the sheet doesn't exist, just read the first one
        $objReader = PHPExcel_IOFactory::createReader($inputFileType);
        $objReader->setReadDataOnly(true);
        $objPHPExcel = $objReader->load($inputFileName);
    }
    # Get worksheet and built array with first row as header
    $objWorksheet = $objPHPExcel->getActiveSheet();

    //excel with first row header, use header as key
    if ($header) {
        $highestRow = $objWorksheet->getHighestRow();
        $highestColumn = $objWorksheet->getHighestColumn();
        $headingsArray = $objWorksheet->rangeToArray('A1:'.$highestColumn.'1', null, true, true, true);
        $headingsArray = $headingsArray[1];

        $r = -1;
        $namedDataArray = array();
        for ($row = 2; $row <= $highestRow; ++$row) {
            $dataRow = $objWorksheet->rangeToArray('A'.$row.':'.$highestColumn.$row, null, true, true, true);
            if ((isset($dataRow[$row]['A'])) && ($dataRow[$row]['A'] > '')) {
                ++$r;
                foreach ($headingsArray as $columnKey => $columnHeading) {
                    $columnHeading = trim($columnHeading);
                    $namedDataArray[$r][$columnHeading] = $dataRow[$row][$columnKey];
                }
            }
        }
    } else {
        //excel sheet with no header
        $namedDataArray = $objWorksheet->toArray(null, true, true, true);
    }

    return $namedDataArray;
}
Пример #21
0
 public static function getPHPExcelObject($file)
 {
     if (!file_exists($file)) {
         return false;
     }
     /**  Identify the type of $inputFileName  * */
     $inputFileType = PHPExcel_IOFactory::identify($file);
     /**  Create a new Reader of the type that has been identified  * */
     $objReader = PHPExcel_IOFactory::createReader($inputFileType);
     /**  Load $inputFileName to a PHPExcel Object  * */
     $objPHPExcel = $objReader->load($file);
     return $objPHPExcel;
 }
Пример #22
0
 public function defineObjPHPExcel($excelFileName)
 {
     //example "data/wells.xls"
     require_once 'lib/Classes/PHPExcel/IOFactory.php';
     try {
         $inputFileType = PHPExcel_IOFactory::identify($excelFileName);
         $objReader = PHPExcel_IOFactory::createReader($inputFileType);
         $objReader->setReadDataOnly(true);
         $this->objPHPExcel = $objReader->load($excelFileName);
     } catch (PHPExcel_Reader_Exception $e) {
         die('Error loading file: ' . $e->getMessage());
     }
 }
Пример #23
0
 /**
  * Excel with one sheet
  * @param string|null $filePath
  * @param bool $withoutSheets
  * @throws PhpExcelException
  */
 public function __construct($filePath = null, $withoutSheets = true)
 {
     try {
         $this->excel = $filePath ? PhpOffice_PHPExcel_IOFactory::load($filePath) : new PhpOffice_PHPExcel();
     } catch (PhpOffice_PHPExcel_Reader_Exception $ex) {
         throw new PhpExcelException('Unable to create excel object.', 0, $e);
     }
     if ($filePath) {
         $this->excelType = PhpOffice_PHPExcel_IOFactory::identify($filePath);
     }
     if (!$filePath && $withoutSheets) {
         $this->excel->removeSheetByIndex();
     }
 }
Пример #24
0
function parse_excel_file($filename)
{
    // подключаем библиотеку
    require_once dirname(__FILE__) . '/map_classes/PHPExcel.php';
    $result = array();
    // получаем тип файла (xls, xlsx), чтобы правильно его обработать
    $file_type = PHPExcel_IOFactory::identify($filename);
    // создаем объект для чтения
    $objReader = PHPExcel_IOFactory::createReader($file_type);
    $objPHPExcel = $objReader->load($filename);
    // загружаем данные файла в объект
    $result = $objPHPExcel->getActiveSheet()->toArray();
    // выгружаем данные из объекта в массив
    return $result;
}
Пример #25
0
 function importDatax()
 {
     // header('Content-Type: text/plain');
     // pre($_FILES);
     $inputFileName = $_FILES['userfile']['tmp_name'];
     $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
     $objReader = PHPExcel_IOFactory::createReader($inputFileType);
     $objPHPExcel = $objReader->load($inputFileName);
     // pre($objReader);
     $sheet = $objPHPExcel->getSheet(0);
     $highestRow = $sheet->getHighestRow();
     $highestColumn = $sheet->getHighestColumn();
     // $objWorksheet = $objPHPExcel->setActiveSheetIndexbyName('Sheet1');
     $objWorksheet = $objPHPExcel->setActiveSheetIndexbyName('Sheet1');
     $sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
     $count = count($sheetData);
     // pre($count);
     $dataexcel = array();
     $index = 0;
     foreach ($sheetData as $key => $value) {
         if ($key >= 3) {
             $dataexcel[$index]['A'] = $value['A'];
             $dataexcel[$index]['B'] = $value['B'];
             $dataexcel[$index]['C'] = $value['C'];
             $dataexcel[$index]['D'] = $value['D'];
             $dataexcel[$index]['E'] = $value['E'];
             $dataexcel[$index]['F'] = $value['F'];
             $dataexcel[$index]['G'] = $value['G'];
             $dataexcel[$index]['H'] = $value['H'];
             $dataexcel[$index]['I'] = $value['I'];
             $dataexcel[$index]['J'] = $value['J'];
             $dataexcel[$index]['K'] = $value['K'];
             $dataexcel[$index]['L'] = $value['L'];
             $dataexcel[$index]['M'] = $value['M'];
             $dataexcel[$index]['N'] = $value['N'];
             $dataexcel[$index]['O'] = $value['O'];
             $dataexcel[$index]['P'] = $value['P'];
             $dataexcel[$index]['Q'] = $value['Q'];
             $dataexcel[$index]['R'] = $value['R'];
             $index++;
         }
     }
     // pre($dataexcel);exit;
     $data['arrDB'] = $dataexcel;
     $_SESSION['dataexcel'] = $dataexcel;
     $data_layout["content"] = $this->load->view("kejadian/v_list_excel", $data, true);
     $this->load->view($this->admin_layout, $data_layout);
 }
Пример #26
0
 /**
  * reads a file PHPExcel can understand and converts a contained worksheet into an array
  * which can be used to build entities. If the File contains more than one worksheet and it is not named like the Controller
  * you have to provide the name of the workshhet to load in the options array.
  * If you set $options['append'] to true, the primary key will be deleted.
  * @todo Find a way to make to handle primary keys anmed other than id.
  *
  * @param string $file name of Excel-File with full path. Must be of a readable Filetype (xls, xlsx, csv, ods)
  * @param array $options Override Worksheet name, set append Mode
  * @return array . The Array has the same structure as provided by request->data
  * @throws MissingTableClassException
  */
 public function prepareEntityData($file = null, array $options = [])
 {
     /**  load and configure PHPExcelReader  * */
     \PHPExcel_Cell::setValueBinder(new \PHPExcel_Cell_AdvancedValueBinder());
     $fileType = \PHPExcel_IOFactory::identify($file);
     $PhpExcelReader = \PHPExcel_IOFactory::createReader($fileType);
     $PhpExcelReader->setReadDataOnly(true);
     if ($fileType !== 'CSV') {
         // csv-files have only one 'worksheet'
         /** identify worksheets in file * */
         $worksheets = $PhpExcelReader->listWorksheetNames($file);
         $worksheetToLoad = null;
         if (count($worksheets) === 1) {
             $worksheetToLoad = $worksheets[0];
             //first option: if there is only one worksheet, use it
         } elseif (isset($options['worksheet'])) {
             $worksheetToLoad = $options['worksheet'];
             //second option: desired worksheet was provided as option
         } else {
             $worksheetToLoad = $this->_registry->getController()->name;
             //last option: try to load worksheet with the name of current controller
         }
         if (!in_array($worksheetToLoad, $worksheets)) {
             throw new MissingTableClassException(__('No proper named worksheet found'));
         }
         /** load the sheet and convert data to an array */
         $PhpExcelReader->setLoadSheetsOnly($worksheetToLoad);
     }
     $PhpExcel = $PhpExcelReader->load($file);
     $data = $PhpExcel->getSheet(0)->toArray();
     /** convert data for building entities */
     $result = [];
     $properties = array_shift($data);
     //first row columns are the properties
     foreach ($data as $row) {
         $record = array_combine($properties, $row);
         if (isset($record['modified'])) {
             unset($record['modified']);
         }
         if (isset($options['type']) && $options['type'] == 'append' && isset($record['id'])) {
             unset($record['id']);
         }
         $result[] = $record;
     }
     /** log in debug mode */
     $this->log(count($result) . ' records were extracted from File ' . $file, 'debug');
     return $result;
 }
Пример #27
0
 /**
  * @inheritDoc
  */
 public function init()
 {
     parent::init();
     foreach ($this->columns as $key => $column) {
         if (is_array($column)) {
             $this->columns[$key] = \Yii::createObject(ArrayHelper::merge(['activeDataProvider' => $this->activeDataProvider], $column));
         }
     }
     $this->activeDataProvider->pagination = ['defaultPageSize' => false, 'pageSizeLimit' => false];
     $this->reader = \PHPExcel_IOFactory::createReader(\PHPExcel_IOFactory::identify($this->filename));
     $this->reader->setReadDataOnly(true);
     $this->phpExcel = $this->reader->load($this->filename);
     $this->models = ArrayHelper::map($this->activeDataProvider->getModels(), 'primaryKey', function ($item) {
         return $item;
     });
 }
Пример #28
0
 public function importXls($fileDestination)
 {
     $this->createSeoFilter();
     $this->loadLanguages();
     try {
         $this->reader = PHPExcel_IOFactory::createReader(PHPExcel_IOFactory::identify($fileDestination));
         $this->reader->setReadDataOnly(true);
         $this->excel = $this->reader->load($fileDestination);
     } catch (Exception $exc) {
         //echo $exc->getTraceAsString();
         $this->errors['ERR_IMPORT_XLS'] = 'Error reading the file, incorrect format';
         return false;
     }
     $this->truncateData();
     return $this->importSheets();
 }
/**
 * Create a XLS template
 *
 * @param string  $group     Group Name to include in template
 * @param string  $issue     Issue title to include in template
 * @param boolean $to_string return template as string or write to file
 *
 * @return list($xlsname, $xlsfile) $xlsname: name of xls file for client /
 *                                  $xlsfile: filepath to xls file or string containing xls file
 */
function createTemplate($group, $issue, $to_string = false, $format = "Excel5")
{
    global $tmpfiles;
    // get extension and make sure it's a supported type
    switch ($format) {
        case 'Excel2007':
            $ext = 'xlsx';
            break;
        case 'Excel5':
        default:
            $ext = 'xls';
            $format = 'Excel5';
            break;
    }
    // load template
    $inputFileType = PHPExcel_IOFactory::identify(XLS_TEMPLATE);
    $objReader = PHPExcel_IOFactory::createReader($inputFileType);
    $objPHPExcel = $objReader->load(XLS_TEMPLATE);
    $sheet = $objPHPExcel->getSheet(0);
    $objRichText = new PHPExcel_RichText();
    $objBold = $objRichText->createTextRun(ISSUE_TAG);
    $objBold->getFont()->setBold(true);
    $objRichText->createText($issue->Title);
    $sheet->getCell(ISSUE_CELL)->setValue($objRichText);
    $objRichText = new PHPExcel_RichText();
    $objBold = $objRichText->createTextRun(GROUP_TAG);
    $objBold->getFont()->setBold(true);
    $objRichText->createText($group->Name);
    $sheet->getCell(GROUP_CELL)->setValue($objRichText);
    $objRichText = new PHPExcel_RichText();
    $objRichText->createText($issue->Description);
    $sheet->setCellValue(DESCRIPTION_CELL, $objRichText);
    $sheet->setSelectedCells(DATA_COLUMN_STATEMENT . DATA_ROW_MIN);
    $objPHPExcel->getProperties()->setCreator("IC-Discuss");
    $xlsname = Utils::sanitizeFilename(APP_TITLE . ' - ' . $group->Name . ' - ' . $issue->Title) . '.' . $ext;
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, $format);
    if ($to_string) {
        ob_start();
        $objWriter->save('php://output');
        $xlsfile = ob_get_clean();
    } else {
        $xlsfile = tempnam("tmp", $ext);
        $tmpfiles[] = $xlsfile;
        $objWriter->save($xlsfile);
    }
    return array($xlsname, $xlsfile);
}
Пример #30
0
 /**
  * 读取一个excel并把内容放入到一个数组中
  * @param string $excel_file
  * @param integer $sheet_id
  * @param array $headers
  * @return array 
  */
 public function readExcel($excel_file, $sheet_id, $headers, $contain_all_column = true)
 {
     $excel_type = PHPExcel_IOFactory::identify($excel_file);
     $excel_reader = PHPExcel_IOFactory::createReader($excel_type);
     if (method_exists($excel_reader, "setReadDataOnly")) {
         $excel_reader->setReadDataOnly(true);
     }
     $excel = $excel_reader->load($excel_file);
     $count = $excel->getSheetCount();
     if ($count < $sheet_id) {
         return "无效的excel,工作表个数不对";
     } else {
         $sheet = $excel->getSheet($sheet_id);
         $data = self::readSheet($sheet, $headers, $contain_all_column);
         return $data;
     }
 }