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)); } } } }
/** * 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); }
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)); }
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; }
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; }
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()); } }
/** * 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(); } }
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; }
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); }
/** * 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; }
/** * @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; }); }
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); }
/** * 读取一个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; } }