public function importExcel($function = null) { if ($this->upload_file()) { if ($function) { $this->onImportRow = $function; } //$reader = \PHPExcel_IOFactory::createReader( /*$this->defaultFormat*/ ); $objPHPExcel = \PHPExcel_IOFactory::load($this->_uploaded_file->tempName); //$objPHPExcel->setActiveSheetIndex(0); $objWorksheet = $objPHPExcel->getActiveSheet(); $highestRow = $objWorksheet->getHighestRow(); foreach ($objWorksheet->getRowIterator() as $i => $row) { $cellIterator = $row->getCellIterator(); $cellIterator->setIterateOnlyExistingCells(false); $row = []; foreach ($cellIterator as $cell) { $row[] = $cell->getValue(); } $this->_current_row = $i; if ($this->import_row(['row' => $row, 'index' => $i, 'max_row' => $highestRow])) { } else { break; } } } }
function readexcel() { $file = './excel/akun.xls'; //load the excel library $this->load->library('excel'); //read file from path $objPHPExcel = PHPExcel_IOFactory::load($file); //get only the Cell Collection $cell_collection = $objPHPExcel->getActiveSheet()->getCellCollection(); //extract to a PHP readable array format foreach ($cell_collection as $cell) { $column = $objPHPExcel->getActiveSheet()->getCell($cell)->getColumn(); $row = $objPHPExcel->getActiveSheet()->getCell($cell)->getRow(); $data_value = $objPHPExcel->getActiveSheet()->getCell($cell)->getValue(); //header will/should be in row 1 only. of course this can be modified to suit your need. if ($row == 1) { $header[$row][$columns] = $data_value; } else { $arr_data[$row][$col] = $data_value; } } //send the data in an array format $data['header'] = $header; $data['values'] = $arr_data; }
public function Run() { $resArray = []; $objPHPExcel = @\PHPExcel_IOFactory::load($this->filePath); if ($objPHPExcel) { $sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true); $headerArray = $sheetData[1]; unset($sheetData[1]); foreach ($sheetData as $k => $itemArray) { $itemArray = @array_combine($headerArray, $itemArray); if ($itemArray) { try { $model = DynamicModel::validateData($itemArray, [[['id', 'type', 'available', 'bid', 'price', 'currencyId', 'categoryId', 'name', 'ISBN'], 'required']]); } catch (UnknownPropertyException $e) { continue; } if (isset($model) && !$model->hasErrors()) { $resArray[] = $itemArray; } } } } $this->response = $resArray; unlink($this->filePath); return $this; }
function addxls() { $data['error'] = ''; //$area=$_POST['area_id']; //$city=$_POST['city_id']; $file_path = $_FILES['csv']['tmp_name']; $file_type = $_FILES['csv']['type']; $this->load->library('PHPExcel'); if ($file_type == 'text/csv') { $objReader = new PHPExcel_Reader_CSV(); $PHPExcel = $objReader->load($file_path); } else { //echo "bye";die(); $PHPExcel = PHPExcel_IOFactory::load($file_path); } $objWorksheet = $PHPExcel->getActiveSheet(); $highestrow = $objWorksheet->getHighestRow(); //echo $highestrow;die(); for ($i = 2; $i <= $highestrow; $i++) { $obj_insData = array('State' => addslashes($PHPExcel->getActiveSheet()->getCell('A' . $i)->getCalculatedValue())); //$saveddata = $this->xls_model->getdata($obj_insData['Code']); if ($obj_insData == '' && count($obj_insData) == '0') { continue; } else { //echo "hello"; die(); mysql_query("INSERT INTO addessdata (state,city,pincode) VALUES\n\t\t\t\t\t(\n\t\t\t\t\t\t\n\t\t\t'" . addslashes($PHPExcel->getActiveSheet()->getCell('A' . $i)->getCalculatedValue()) . "',\n\t\t\t'" . addslashes($PHPExcel->getActiveSheet()->getCell('B' . $i)->getCalculatedValue()) . "',\n\t\t\t'" . addslashes($PHPExcel->getActiveSheet()->getCell('C' . $i)->getCalculatedValue()) . "'\n\t\t\t \n\t\t\t \n\t\t\t \n\t\t\t\t\t)\n\t\t\t\t"); } } $this->session->set_flashdata('message', 'Your Data File Uploaded Succcessfully.!!'); redirect($this->config->item('base_url') . 'xlsdata/lists'); }
/** * Load excel file * @param string $filename filename to be loaded */ protected function loadFile($filename) { if (!file_exists($filename)) { throw new \Exception($filename . " Not Found!"); } $this->phpExcel = \PHPExcel_IOFactory::load($filename); }
function import_Books($file) { set_include_path(get_include_path() . PATH_SEPARATOR . 'Classes/'); include 'PHPExcel/IOFactory.php'; require_once 'private/LMS_Engine.php'; $engine = new LMS_Engine(); try { $objPHPExcel = PHPExcel_IOFactory::load($file); $Total_Sheet = $objPHPExcel->getSheetCount(); for ($num = 0; $num < $Total_Sheet; $num++) { $Sheet = $objPHPExcel->getSheet($num)->toArray(null, true, true, true); $Row = count($Sheet); for ($pos = 3; $pos < $Row; $pos++) { $Title = trim($Sheet[$pos]['B']); $Author = trim($Sheet[$pos]['C']); $Publisher = trim($Sheet[$pos]['D']); $Pub_Year = trim($Sheet[$pos]['E']); $Pub_Add = trim($Sheet[$pos]['F']); $Call_ID = trim($Sheet[$pos]['G']); $Copy_Num = trim($Sheet[$pos]['H']); $Category = trim($Sheet[$pos]['I']); $Shelf_Store = trim($Sheet[$pos]['J']); $engine->add_new_book($Title, 1, $Author, $Publisher, $Pub_Year, $Pub_Add, $Call_ID, $Copy_Num, $Shelf_Store); } } } catch (Exception $e) { die('Error loading file "' . pathinfo($file, PATHINFO_BASENAME) . '": ' . $e->getMessage()); } }
/** * * @param unknown_type $tipoArchivo */ function procesarArchivo($tipoArchivo) { //$dbLink = getConnection(); //ajustamos el maximo de tiempo de ejecucion a 10 minutos para la carga de los archivos ini_set("max_execution_time", 60 * 10); //limpiamos el archivo de errores para esta corrida initErrorFile(); //leemos el archivo Excel en una estructura mas manejable $objPHPExcel = PHPExcel_IOFactory::load(getUploadedXLSFileToProcess()); if ($tipoArchivo == 'clientes') { return insertarCliente($objPHPExcel); } else { if ($tipoArchivo == 'lineasVentasPaquetes') { return insertarLineaVentasPaquetesCredito($objPHPExcel); } else { if ($tipoArchivo == 'recibos') { return insertarRecibo($objPHPExcel); } else { if ($tipoArchivo == 'ventasPaquetes') { return insertarVentasPaquetesCredito($objPHPExcel); } } } } //cerramos la conexion a la base de datos //mysql_close($dbLink); //eliminamos el archivo temporal $objPHPExcel->disconnectWorksheets(); $objPHPExcel = null; unlink(getUploadedXLSFileToProcess()); }
/** * Read Excel File Content and parse it into array * @param string $file * @return array */ public function readFile($file) { App::import('Vendor', 'PHPExcel/IOFactory'); $info = PHPExcel_IOFactory::load($file); $sheetData = $info->getActiveSheet()->toArray(null, true, true, true); return $sheetData; }
public function __construct($filePath = '') { //如果存在就实例化读取对象 if (file_exists($filePath)) { $this->PHPExcel = PHPExcel_IOFactory::load($filePath); } }
public function excel_oku() { $this->load->library('PHPExcel'); $dosyaAdi = 'dosyalar/upload/Servis Takip.xlsx'; $objPHPExcel = PHPExcel_IOFactory::load($dosyaAdi); $objPHPExcel->getProperties()->setCreator("Kadir TUTAK")->setLastModifiedBy("Kadir TUTAK")->setTitle("Servis Takip")->setSubject("Servis Takip")->setDescription("Servis Takip")->setKeywords("Kadir TUTAK Servis Takip")->setCategory("Servis Takip"); /* $cell_collection = $objPHPExcel->getActiveSheet()->getCellCollection(); foreach($cell_collection AS $cell) { $column = $objPHPExcel->getActiveSheet()->getCell($cell)->getColumn(); $row = $objPHPExcel->getActiveSheet()->getCell($cell)->getRow(); $data_value = $objPHPExcel->getActiveSheet()->getCell($cell)->getValue(); if($row == 1) { $header[$row][$column] = $data_value; } else { $arr_data[$row][$column] = $data_value; } } $basliklar = $header; $veriler = $arr_data; foreach($veriler AS $veri) { echo '<div style="clear: both;">'.$veri['A'].' - '.$veri['B'].' - '.$veri['C'].'</div>'; } */ }
protected function executeImpl($params, $type, $name, $key) { if (!$params->check("upload") || isset($_POST[$params->get("upload")])) { $loader = new Vizualizer_Plugin($type); // アップされたファイルのデータを取得する。 if ($_FILES[$key]["error"] == UPLOAD_ERR_OK) { // Excelファイルを読み込む $book = PHPExcel_IOFactory::load($_FILES[$key]["tmp_name"]); // 処理を実行する $data = $this->process($params, $book); // トランザクションの開始 $connection = Vizualizer_Database_Factory::begin(strtolower($type)); try { foreach ($data as $item) { $model = $loader->loadModel($name); foreach ($item as $col => $value) { $model->{$col} = $value; } $model->save(); } // エラーが無かった場合、処理をコミットする。 Vizualizer_Database_Factory::commit($connection); // 画面をリロードする。 if (!$this->continue) { // 登録に使用したキーを無効化 $this->removeInput("upload"); $this->reload(); } } catch (Exception $e) { Vizualizer_Database_Factory::rollback($connection); throw new Vizualizer_Exception_Database($e); } } } }
public function getXLS($xls, $Activsheet = 0) { //открывет xls и возвращает масив if (file_exists($xls)) { include_once 'Classes/PHPExcel/IOFactory.php'; $objPHPExcel = PHPExcel_IOFactory::load($xls); $objPHPExcel->setActiveSheetIndex($Activsheet); $aSheet = $objPHPExcel->getActiveSheet(); $array = array(); //этот массив будет содержать массивы содержащие в себе значения ячеек каждой строки //получим итератор строки и пройдемся по нему циклом foreach ($aSheet->getRowIterator() as $row) { //получим итератор ячеек текущей строки $cellIterator = $row->getCellIterator(); //пройдемся циклом по ячейкам строки $item = array(); //этот массив будет содержать значения каждой отдельной строки foreach ($cellIterator as $cell) { //заносим значения ячеек одной строки в отдельный массив array_push($item, iconv('utf-8', 'cp1251', $cell->getCalculatedValue())); } //заносим массив со значениями ячеек отдельной строки в "общий массв строк" array_push($array, $item); } } else { $array = false; echo '<br>файл не существует<br>'; } return $array; }
function read_xlsfile($filename) { /** PHPExcel_IOFactory */ include_once 'Classes/PHPExcel/IOFactory.php'; $inputFileName = $filename; try { PHPExcel_Settings::setZipClass(PHPExcel_Settings::PCLZIP); $objPHPExcel = PHPExcel_IOFactory::load($inputFileName); } catch (Exception $e) { die('Error loading file "' . pathinfo($inputFileName, PATHINFO_BASENAME) . '": ' . $e->getMessage()); } $sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true); $row = 0; foreach ($sheetData as $v => $i) { if ($row == 0) { $row = 1; continue; } if (count($i) == 3 && filter_var($i['C'], FILTER_VALIDATE_EMAIL)) { $query[] = "('" . implode("','", $i) . "')"; } } if (count($query) == 1000) { bulk_insert($query); $query = ''; } return $query; }
/** * Excel 파일을 CSV 파일로 변환 * * @param $excelPath string excel 파일 경로 * @param $csvPath string csv 파일 경로 * @return void */ public static function convertExcelIntoCsv(string $excelPath, string $csvPath) { echo "\nConverting Excel into CSV format...\n"; try { // excel 파일을 로드하여 PHPExcel 선언 $objPhpExcel = \PHPExcel_IOFactory::load($excelPath); // Excel->CSV 형식의 Object로 변환 $objWriter = new \PHPExcel_Writer_CSV($objPhpExcel); // csv 경로에 같은 파일이 있으면 삭제 if (file_exists($csvPath)) { echo "CSV file rewriting...\n"; unlink($csvPath); } // 해당 경로에 csv 파일 저장 $objWriter->save($csvPath); echo "Conversion success! \n"; } catch (\PHPExcel_Reader_Exception $re) { die('Error loading file: ' . $e->getMessage()); } finally { // 메모리 release 작업 if ($objPhpExcel instanceof \PHPExcel_IOFactory) { $objPhpExcel->disconnectWorksheets(); unset($objPhpExcel); } unset($objWriter); } }
protected function _beforeSave(Kwf_Model_Row_Interface $row) { $model = Kwf_Model_Abstract::getInstance('Kwf_Uploads_Model'); $uploadsRow = $model->getRow($row->upload_id); if (!$uploadsRow) { throw new Kwf_Exception_Client(trlKwf('File not found.')); } $source = $uploadsRow->getFileSource(); $target = 'temp/xlsimport_' . date('YmdHis') . '.' . $uploadsRow->extension; copy($source, $target); // copy with extension for xlsimport $excel = PHPExcel_IOFactory::load($target); if (!$excel) { throw new Kwf_Exception_Client(trlKwf('Could not read excel')); } $excel = $excel->getActiveSheet(); $message = $this->_import($excel); $this->view->message = null; if ($message) { if (is_string($message)) { $this->view->message = nl2br($message); } } $uploadsRow->delete(); unlink($target); }
public function testExportXLSFile() { $this->_object = new Controller_ExtJS_Attribute_Export_Text_Default($this->_context); $manager = MShop_Attribute_Manager_Factory::createManager($this->_context); $ids = array(); foreach ($manager->searchItems($manager->createSearch()) as $item) { $ids[] = $item->getId(); } $params = new stdClass(); $params->lang = array('de'); $params->items = $ids; $params->site = 'unittest'; $result = $this->_object->exportFile($params); $this->assertTrue(array_key_exists('file', $result)); $file = substr($result['file'], 9, -14); $this->assertTrue(file_exists($file)); $phpExcel = PHPExcel_IOFactory::load($file); if (unlink($file) === false) { throw new Exception('Unable to remove export file'); } $phpExcel->setActiveSheetIndex(0); $sheet = $phpExcel->getActiveSheet(); $this->assertEquals('Language ID', $sheet->getCell('A1')->getValue()); $this->assertEquals('Text', $sheet->getCell('G1')->getValue()); $this->assertEquals('de', $sheet->getCell('A9')->getValue()); $this->assertEquals('color', $sheet->getCell('B9')->getValue()); $this->assertEquals('red', $sheet->getCell('C9')->getValue()); $this->assertEquals('default', $sheet->getCell('D9')->getValue()); $this->assertEquals('name', $sheet->getCell('E9')->getValue()); $this->assertEquals('', $sheet->getCell('G9')->getValue()); }
/** * * @return type */ public function getExelData() { $this->objPHPExcel = PHPExcel_IOFactory::load($this->filename); $this->collectData(); $this->dump($this->data, "Parsed xls"); return $this->data; }
public function bulkadd() { $this->load->library('excel'); //echo base_url();die; $file = 'D:\\xampp\\htdocs\\CI\\application\\third_party\\testdata.xlsx'; //load the excel library //read file from path $objPHPExcel = PHPExcel_IOFactory::load($file); echo "!11"; //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; } echo "=--="; } //send the data in an array format $data['header'] = $header; $data['values'] = $arr_data; $this->load->view('header'); $this->load->view('user/user_buld_add'); $this->load->view('footer'); }
public function load($filePath) { $this->filePath = $filePath; $this->phpExcel = PHPExcel_IOFactory::load($filePath); $data = $this->phpExcel->getActiveSheet()->toArray(null, true, true); $this->setArrayData($data, false); }
function read() { $file = './assets/antenna/antenna.xlsx'; //load the excel library $this->load->library('excel'); //read file from path $objPHPExcel = PHPExcel_IOFactory::load($file); //get only the Cell Collection $cell_collection = $objPHPExcel->getActiveSheet('')->getCellCollection(); //extract to a PHP readable array format foreach ($cell_collection as $cell) { $column = $objPHPExcel->getActiveSheet()->getCell($cell)->getColumn(); $row = $objPHPExcel->getActiveSheet()->getCell($cell)->getRow(); $data_value = $objPHPExcel->getActiveSheet()->getCell($cell)->getValue(); //header will/should be in row 1 only. of course this can be modified to suit your need. if ($row == 1) { $header[$row][$column] = $data_value; } else { $arr_data[$row][$column] = $data_value; } } //send the data in an array format $data['header'] = $header; $data['values'] = $arr_data; $table = "antenna"; foreach ($arr_data as $d) { echo json_encode($d); $ins = array('antenna_model' => $d['A'], 'antenna_gain' => $d['B'], 'aperture' => $d['C'], 'hor_bw' => $d['D']); $this->Modeldb->insert($ins, $table); } }
public function getArray() { # Start Lib $objPHPExcel = PHPExcel_IOFactory::load($this->fileName); $objPHPExcel->setActiveSheetIndex(0); $aSheet = $objPHPExcel->getActiveSheet(); # Собрать массивы $rows = array(); $i = 0; foreach ($aSheet->getRowIterator() as $row) { $cellIterator = $row->getCellIterator(); foreach ($cellIterator as $cell) { $cell->getCalculatedValue() != '' ? $rows[$i][] = trim($cell->getCalculatedValue()) : FALSE; } $i++; } if (!empty($rows[0])) { $rows['cols'] = $rows[0]; unset($rows[0]); } $newRows = array(); foreach ($rows as $k => $v) { if ('cols' !== $k) { foreach ($v as $a => $b) { !empty($rows['cols'][$a]) && ($newRows[$k][$rows['cols'][$a]] = $b); } } } return $newRows; }
protected function execute(InputInterface $input, OutputInterface $output) { ini_set('memory_limit', -1); $output->writeln('--- vidal:user_specialty started'); $container = $this->getContainer(); $em = $container->get('doctrine')->getManager(); $pdo = $em->getConnection(); $filename = $this->getContainer()->get('kernel')->getRootDir() . '/User.orig.xlsx'; $objPHPExcel = \PHPExcel_IOFactory::load($filename); // Get worksheet dimensions $sheet = $objPHPExcel->getSheet(0); $highestRow = $sheet->getHighestRow(); $highestColumn = $sheet->getHighestColumn(); $stmt = $pdo->prepare('UPDATE user SET primarySpecialty_id = ?, secondarySpecialty_id = ? WHERE username = ?'); $i = 0; $output->writeln('Total rows: ' . $highestRow); for ($row = 1; $row <= $highestRow; $row++) { $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE); if (isset($rowData[0]) && isset($rowData[0][6]) && isset($rowData[0][8]) && !empty($rowData[0][1]) && !empty($rowData[0][6])) { $email = $rowData[0][1]; $primary = $this->getSpecialty($rowData[0][6]); $secondary = $this->getSpecialty($rowData[0][8]); $stmt->bindParam(1, $primary); $stmt->bindParam(2, $secondary); $stmt->bindParam(3, $email); $stmt->execute(); $i++; if ($i && $i % 100 == 0) { $output->writeln('... ' . $i); } } } $output->writeln("+++ vidal:user_specialty {$i} loaded!"); }
/** * Creates an instance of PHPExcel based of a file path. * @param $filePath * @return \PHPExcel */ public function createWorkBook($filePath) { $info = pathinfo($filePath); $this->fileExtension = $info['extension']; switch ($this->fileExtension) { // if text or csv use PHPExcel_Reader_CSV else use loader //due to inability of the PHPExcel IOfactory being //unable to handle csv/txt case 'txt': $objReader = new \PHPExcel_Reader_CSV(); $objReader->setInputEncoding($this->fileInfo['encoding']); $objReader->setDelimiter($this->fileInfo['delimiter']); $objReader->setEnclosure($this->fileInfo['enclosure']); $objReader->setSheetIndex(0); $this->workbook = $objReader->load($filePath); break; case 'csv': $objReader = new \PHPExcel_Reader_CSV(); $objReader->setInputEncoding($this->fileInfo['encoding']); $objReader->setDelimiter($this->fileInfo['delimiter']); $objReader->setEnclosure($this->fileInfo['enclosure']); $objReader->setSheetIndex(0); $this->workbook = $objReader->load($filePath); break; default: $this->workbook = \PHPExcel_IOFactory::load($filePath); break; } return $this->workbook; // return created workbook }
function read_ou($fname) { $xls = PHPExcel_IOFactory::load($fname); $xls->setActiveSheetIndex(0); $sheet = $xls->getActiveSheet(); $nRow = $sheet->getHighestRow(); $nColumn = PHPExcel_Cell::columnIndexFromString($sheet->getHighestColumn()); //$cat = ''; $arr = []; for ($i = 2; $i <= $nRow; $i++) { for ($j = 0; $j <= $nColumn; $j++) { $row[$j] = trim($sheet->getCellByColumnAndRow($j, $i)->getValue()); } if ($row[0] != '' and $row[1] != '' and $row[3] != '') { $code = trim($row[0]); $mr = trim($row[1]); $name = trim($row[3]); if ($mr = validate_mr($mr)) { //$arr[] = ['code'=>$code, 'mr'=>$mr, 'mr_new'=>validate_mr($mr), 'name'=>$name, 'name_new'=>validate_ou($name)]; $arr[] = ['code' => $code, 'mr' => $mr, 'name' => $name]; } } else { //категория //$cat = $row['1']; } } return $arr; }
function read_ilias_users($fname) { $xls = PHPExcel_IOFactory::load($fname); $xls->setActiveSheetIndex(0); $sheet = $xls->getActiveSheet(); $nRow = $sheet->getHighestRow(); $nColumn = PHPExcel_Cell::columnIndexFromString($sheet->getHighestColumn()); $arr = []; for ($i = 5; $i <= $nRow; $i++) { for ($j = 0; $j <= $nColumn; $j++) { $row[$j] = trim($sheet->getCellByColumnAndRow($j, $i)->getValue()); } if ($row[0] != '' and $row[1] != '') { $fname = explode(' ', trim($row[0])); $lname = explode(' ', trim($row[1])); foreach ($fname as $item) { if ($item != '') { $arr[$i][] = $item; } } foreach ($lname as $item) { if ($item != '') { $arr[$i][] = $item; } } } } return $arr; }
/** * Private/protected methods */ protected function getExcelReader($filename) { if (!$this->excelReader) { $this->excelReader = \PHPExcel_IOFactory::load($filename); } return $this->excelReader; }
function PrepareValues() { set_include_path(get_include_path() . PATH_SEPARATOR . '../../../Classes/'); include 'PHPExcel/IOFactory.php'; $inputFileName = $this->destination; $objPHPExcel = PHPExcel_IOFactory::load($inputFileName); $sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true); $NumRow = 0; foreach ($sheetData as $key => $value) { if ($NumRow == 0) { $NumRow++; } else { switch ($this->method) { case 'additions': $this->saveDBrow($value); echo "<p>Row succesfully added!</p>"; break; case 'removals': $this->removeDBrow($value); echo "<p>Row succesfully removed!</p>"; break; case 'updates': $result = $this->UpdateDBrow($value); if ($result) { echo "<p>Row succesfully Updated!</p>"; } break; } } } }
/** * @param string $filePath * * @return array|Student[] */ public function read($filePath) { $students = []; $objPHPExcel = \PHPExcel_IOFactory::load($filePath); /** @var \PHPExcel_Worksheet $worksheet */ foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) { /** @var \PHPExcel_Worksheet_Row $row */ foreach ($worksheet->getRowIterator() as $row) { if (1 === $row->getRowIndex()) { continue; } $data = []; /** @var \PHPExcel_Cell $cell */ foreach ($row->getCellIterator('A', 'D') as $cell) { switch ($cell->getColumn()) { case 'A': $data['grade'] = (int) $cell->getValue(); break; case 'B': $data['full_name'] = $cell->getValue(); break; case 'C': $data['school'] = $cell->getValue(); break; case 'D': $data['score'] = floatval($cell->getValue()); break; } } $students[] = StudentFactory::createFromArray($data); } } return $students; }
function parse($file) { $objExcel = PHPExcel_IOFactory::load($file); $sheets = $objExcel->getAllSheets(); $sheetNum = 1; foreach ($sheets as $sheet) { echo 'Processing Sheet ', $sheetNum++, '<br>'; $row = 1; while (true) { echo 'Processing Row ', $row, '<br>'; if ($sheet->cellExistsByColumnAndRow(0, $row)) { $firstName = $sheet->getCellByColumnAndRow(0, $row)->__toString(); if ($firstName == 'First Name') { $row++; continue; } $lastName = $sheet->getCellByColumnAndRow(1, $row)->__toString(); $email = $sheet->getCellByColumnAndRow(2, $row)->__toString(); $company = $sheet->getCellByColumnAndRow(3, $row)->__toString(); $job = $sheet->getCellByColumnAndRow(4, $row)->__toString(); $tags = $sheet->getCellByColumnAndRow(5, $row)->__toString(); echo $firstName, ' ', $lastName, ' ', $email, ' ', $company, ' ', $job, ' ', $tags, '<br>'; addContact($firstName, $lastName, $email, $company, $job, $tags); } else { $row = -1; break; } $row++; } echo '<br><br>'; } }
public function uploadExcel($filename = '', $productID, $planID, $sprintID) { if ($_FILES['excelFile']['size'] == 0) { return; } // uploadFile $filePath = $this->getSavePath(); $fname = $this->setPathName(0, $file['extension']); $uploadfile = $filePath . $fname; $tmp_name = $_FILES['excelFile']["tmp_name"]; require_once 'PHPExcel.php'; require_once 'PHPExcel/IOFactory.php'; require_once 'PHPExcel/Reader/Excel2007.php'; $result = move_uploaded_file($_FILES['excelFile']["tmp_name"], $this->getSavePath() . $fname); if ($result) { $objReader = PHPExcel_IOFactory::createReader('Excel2007'); $objPHPExcel = PHPExcel_IOFactory::load($uploadfile); $sheet = $objPHPExcel->getSheet(0); $highestRow = $sheet->getHighestRow(); $highestColumn = $sheet->getHighestColumn(); for ($j = 2; $j <= $highestRow; $j++) { $str = ""; for ($k = 'A'; $k <= $highestColumn; $k++) { // $str .= iconv('utf-8', 'gbk', $objPHPExcel->getActiveSheet()->getCell("$k$j")->getValue())."|";//读取单元格 $str .= $objPHPExcel->getActiveSheet()->getCell("{$k}{$j}")->getValue() . "|"; //读取单元格 } $strs = explode("|", $str); $bug = new stdclass(); $bug->product = $productID; $bug->plan = $planID; if ($sprintID) { $bug->project = $sprintID; } $bug->title = $strs[0]; $bug->stage = $this->lang->importbugs->stageMap[$strs[1]]; $bug->type = $this->lang->importbugs->typeMap[$strs[2]]; $bug->severity = $strs[3]; $bug->steps = $strs[4]; $bug->openedBy = $this->app->user->account; $this->dao->insert(TABLE_BUG)->data($bug)->exec(); $bugID = $this->dao->lastInsertID(); $action = new stdclass(); $action->objectType = 'bug'; $action->objectID = $bugID; $action->product = $productID; $action->project = $sprintID; $action->actor = $this->app->user->account; $action->action = 'opened'; $action->date = helper::now(); $action->read = 0; $this->dao->insert(TABLE_ACTION)->data($action)->exec(); } unlink($uploadfile); $msg = $this->lang->importbugs->success; } else { $msg = $this->lang->importbugs->fail; } return $msg; }