/** * Processes the uploaded CSV-file */ private function processCsvImport() { $uploaded_file_name = $_FILES['csvfile']['tmp_name']; try { /** try to read excel file **/ try { $objReader = PHPExcel_IOFactory::createReaderForFile($uploaded_file_name); } catch (Exception $e) { // if format could not be determined, we assume CSV $objReader = PHPExcel_IOFactory::createReader('CSV'); } if (method_exists($objReader, "setDelimiter")) { // its a csv file $separator = urldecode($_POST['separator']); if (!$separator) { $separator = ','; } $objReader->setDelimiter($separator); } else { // its an excel file $objReader->setReadDataOnly(true); } $objPHPExcel = $objReader->load($uploaded_file_name); $objWorksheet = $objPHPExcel->getActiveSheet(); foreach ($objWorksheet->getRowIterator() as $row) { $cellIterator = $row->getCellIterator(); $cellIterator->setIterateOnlyExistingCells(false); $data = array(); foreach ($cellIterator as $cell) { $data[] = $cell->getValue(); } if (!$this->is_multiArrayEmpty($data)) { $this->numberOfCsvCols = max($this->numberOfCsvCols, count($data)); $this->csvDataSets[] = $data; } } if (count($this->csvDataSets) == 0) { $this->processError = Messages::getString('No data found in input file.'); } } catch (Exception $e) { // some error occured $this->processError = Messages::getString('EnterDataPage.CouldNotReadInputFile') . ' ' . $_FILES['csvfile']['error'] . " (" . $e->getMessage() . ")"; } @unlink($uploaded_file_name); }
public function __construct($fileName, $csvDelimiter = null, $csvEnclosure = null, $sheetNumber = 1, $chunkSize = null, $fillMissingColumns = true, $removeCellsNotMatchingHeaderColumns = true) { $this->filename = $fileName; $this->sheetNumber = $sheetNumber; $this->fillMissingColumns = $fillMissingColumns; $this->removeCellsNotMatchingHeaderColumns = $removeCellsNotMatchingHeaderColumns; //@todo: Allow disabling chunked read $this->filter = new ChunkReadFilter(0, $chunkSize); $this->reader = \PHPExcel_IOFactory::createReaderForFile($this->filename); if ($this->reader instanceof \PHPExcel_Reader_CSV) { if ($csvDelimiter) { $this->reader->setDelimiter($csvDelimiter); } if ($csvEnclosure) { $this->reader->setEnclosure($csvEnclosure); } } $this->reader->setReadFilter($this->filter); $worksheet = $this->reloadIterator(1); $this->highestDataColumnName = $worksheet->getHighestDataColumn(); $this->highestRow = $worksheet->getHighestRow(); $this->rowIterator->rewind(); $this->header = $this->getFilteredArrayForCurrentRow(); foreach ($this->header as $headerIndex => &$columnHeader) { if (is_null($columnHeader) || $columnHeader === '') { $columnHeader = $headerIndex; } else { $columnHeader = trim($columnHeader); } } $this->rowIterator->next(); }
/** * get excel contents * @return array * @link http://fun-program.net/phpexcel-read-excel * @link http://stackoverflow.com/questions/4562527/how-to-find-out-how-many-rows-and-columns-to-read-from-an-excel-file-with-phpexc * @link http://stackoverflow.com/questions/27708459/getting-cell-as-string-in-phpexcel-by-column-and-row */ public function getContents() { if ($this->validate()) { $result = []; $reader = PHPExcel_IOFactory::createReaderForFile($this->file->tempName); $reader->setReadDataOnly(false); $excel = $reader->load($this->file->tempName); $sheet = $excel->getSheetByName($this->activeSheetName); if (!isset($sheet)) { throw new Exception("取得Excel資料,發生錯誤,請確認[{$this->activeSheetName}]標籤是否存在或合法(標籤名稱必須為英文)"); } $rows = $sheet->getRowIterator(); $rowIndex = 0; foreach ($rows as $row) { $cellIndex = 0; $cellIterator = $row->getCellIterator(); $cellIterator->setIterateOnlyExistingCells(false); $result[++$rowIndex] = []; foreach ($cellIterator as $cell) { $result[$rowIndex][++$cellIndex] = (string) $cell->getCalculatedValue(); } } return $result; /* // 另一種簡潔寫法 $reader = PHPExcel_IOFactory::load($this->file->tempName); return $reader->getActiveSheet()->toArray(null, true, true, true); */ } throw new Exception('must be validate excel file.', 1); }
public function import($pathToFile) { // $pathToFile = realpath($pathToFile); // var_dump($pathToFile); // $fileType = \PHPExcel_IOFactory::identify($pathToFile); $objReader = \PHPExcel_IOFactory::createReaderForFile($pathToFile); $objReader->setReadDataOnly(true); $objReader->setLoadSheetsOnly('Сотрудники'); $excel = $objReader->load($pathToFile); // $excel->setActiveSheetIndex(0); $sheet = $excel->getSheet(0); $rowIterator = $sheet->getRowIterator(); $break = 0; foreach ($rowIterator as $row) { // var_dump($row); var_dump('END OF ROW'); // var_dump($row); // dd(); foreach ($row as $cell) { var_dump($cell); } if ($break++ > 10) { dd(); } } }
protected function execute(InputInterface $input, OutputInterface $output) { $this->container = $this->getContainer(); $this->em = $this->getContainer()->get('doctrine')->getManager(); //$this->em->getConnection()->getConfiguration()->setSQLLogger(null); $this->formCSRF = $this->container->get('form.csrf_provider'); $this->adminPool = $this->container->get('sonata.admin.pool'); $this->user = $this->em->getRepository('ApplicationSonataUserBundle:User')->findOneBy(array('id' => $input->getArgument('user_id'))); $this->translator = \AppKernel::getStaticContainer()->get('translator'); $token = new UsernamePasswordToken($this->user, $this->user->getPassword(), "public", $this->user->getRoles()); $this->getContainer()->get("security.context")->setToken($token); // Fire the login event $event = new InteractiveLoginEvent($this->getContainer()->get('request'), $token); $this->getContainer()->get("event_dispatcher")->dispatch("security.interactive_login", $event); $this->targetProcess = $input->getArgument('target'); $this->user_id = $input->getArgument('user_id'); $this->pid = $input->getArgument('pid'); $file = $input->getArgument('file'); $objReader = \PHPExcel_IOFactory::createReaderForFile($file); $objReader->setReadDataOnly(true); $objPHPExcel = $objReader->load($file); $sheet = $objPHPExcel->getSheet(0); $rows = $sheet->toArray(); $this->processClient($rows); $this->messages = $this->getCountMessageImports(); $this->sendNotification(); echo serialize(array('messages' => $this->messages, 'import_counts' => $this->_import_counts, 'pid' => $this->pid, 'absErrorLogFilename' => $this->absErrorLogFilename)); }
/** * read * */ public function read($filePath) { $xlsReader = PHPExcel_IOFactory::createReaderForFile($filePath); $this->type = preg_replace('/^.+_/', '', get_class($xlsReader)); $this->xls = $xlsReader->load($filePath); return $this; }
public static function read($filename) { $path = Yii::$app->basePath . "/uploads/" . $filename; $objReader = \PHPExcel_IOFactory::createReaderForFile($path); //$objReader->setLoadSheetsOnly($sheets); $objReader->setReadDataOnly(true); $objPHPExcel = $objReader->load($path); $reader = new ExcelReader(); foreach ($objPHPExcel->setActiveSheetIndex(0)->getRowIterator() as $row) { $cellIterator = $row->getCellIterator(); $cellIterator->setIterateOnlyExistingCells(false); $rowData = []; foreach ($cellIterator as $cell) { if (!is_null($cell)) { $rowData[] = $cell->getCalculatedValue(); } } $reader->data[] = $rowData; } $reader->columnCount = count($reader->data[0]); $colrange = range(0, $reader->columnCount - 1); foreach ($colrange as $column) { $reader->columns[] = strval($column); } return $reader; }
/** * */ public function parseResource() { $configuration = $this->getConfiguration(); if (!ExtensionManagementUtility::isLoaded('phpexcel_library')) { throw new \Exception('phpexcel_library is not loaded', 12367812368); } $filename = GeneralUtility::getFileAbsFileName($this->filepath); GeneralUtility::makeInstanceService('phpexcel'); $objReader = \PHPExcel_IOFactory::createReaderForFile($filename); $objReader->setReadDataOnly(true); $objPHPExcel = $objReader->load($filename); if ($configuration['sheet'] >= 0) { $objWorksheet = $objPHPExcel->getSheet($configuration['sheet']); } else { $objWorksheet = $objPHPExcel->getActiveSheet(); } $highestRow = $objWorksheet->getHighestRow(); $highestColumn = $objWorksheet->getHighestColumn(); $highestColumnIndex = \PHPExcel_Cell::columnIndexFromString($highestColumn); for ($row = 1 + $configuration['skipRows']; $row <= $highestRow; ++$row) { $rowRecord = []; for ($col = 0; $col <= $highestColumnIndex; ++$col) { $rowRecord[] = trim($objWorksheet->getCellByColumnAndRow($col, $row)->getValue()); } $this->content[] = $rowRecord; } }
public function init() { $reader = \PHPExcel_IOFactory::createReaderForFile($this->filePath); /* @var $reader \PHPExcel_Reader_IReader */ $reader->setReadDataOnly(true); // Load only 1st sheet if (method_exists($reader, 'listWorksheetNames')) { $sheets = $reader->listWorksheetNames($this->filePath); $reader->setLoadSheetsOnly(array($sheets[0])); } $this->reader = $reader; $this->excel = $this->reader->load($this->filePath); $this->excel->setActiveSheetIndex(0); $this->sheet = $this->excel->getActiveSheet(); if ($this->useLabels) { if ($this->labels === null) { $this->labels = $this->readLabelsRow(); $this->ignoredRowsCount++; // To pass labels row when reading data } $this->colsCount = count($this->labels); } if ($this->colsCount === null) { throw new Exception(__CLASS__ . '->colsCount not set.'); } $this->filter = new Filter($this->colsCount); $reader->setReadFilter($this->filter); }
/** * @param $path * @return void * @throws \PHPExcel_Reader_Exception */ public function load($path) { //determine correct reader $reader = \PHPExcel_IOFactory::createReaderForFile($path); //load the file $this->object = $reader->load($path); }
public function select($source) { $path = $this->connection; $excel = PHPExcel_IOFactory::createReaderForFile($path); $excel = $excel->load($path); $excRes = new ExcelResult(); $excelWS = $excel->getActiveSheet(); $addFields = true; $coords = array(); if ($source->get_source() == '*') { $coords['start_row'] = 0; $coords['end_row'] = false; } else { $c = array(); preg_match("/^([a-zA-Z]+)(\\d+)/", $source->get_source(), $c); if (count($c) > 0) { $coords['start_row'] = (int) $c[2]; } else { $coords['start_row'] = 0; } $c = array(); preg_match("/:(.+)(\\d+)\$/U", $source->get_source(), $c); if (count($c) > 0) { $coords['end_row'] = (int) $c[2]; } else { $coords['end_row'] = false; } } $i = $coords['start_row']; $end = 0; while ($coords['end_row'] == false && $end < $this->emptyLimit || $coords['end_row'] !== false && $i < $coords['end_row']) { $r = array(); $emptyNum = 0; for ($j = 0; $j < count($this->config->text); $j++) { $col = PHPExcel_Cell::columnIndexFromString($this->config->text[$j]['name']) - 1; $cell = $excelWS->getCellByColumnAndRow($col, $i); if ($cell->getDataType() == 'f') { $r[PHPExcel_Cell::stringFromColumnIndex($col)] = $cell->getCalculatedValue(); } else { $r[PHPExcel_Cell::stringFromColumnIndex($col)] = $cell->getValue(); } if ($r[PHPExcel_Cell::stringFromColumnIndex($col)] == '') { $emptyNum++; } } if ($emptyNum < count($this->config->text)) { $r['id'] = $i; $excRes->addRecord($r); $end = 0; } else { if (DHX_IGNORE_EMPTY_ROWS == false) { $r['id'] = $i; $excRes->addRecord($r); } $end++; } $i++; } return $excRes; }
/** * Dowloads the Spanish list of banks (from bde.es) and * creates/updates/deletes the corresponding BIC records. */ public function update() { // First, download the file $file_name = sys_get_temp_dir() . '/' . CRM_Bic_Parser_ES::$country_code . '-banks.xls'; $downloaded_file = $this->downloadFile(CRM_Bic_Parser_ES::$page_url); if (!$downloaded_file) { return $this->createParserOutdatedError(ts("Couldn't download the Spanish list of banks")); } // Save the downloaded file file_put_contents($file_name, $downloaded_file); unset($downloaded_file); // Automatically detect the correct reader to load for this file type $excel_reader = PHPExcel_IOFactory::createReaderForFile($file_name); // Set reader options $excel_reader->setReadDataOnly(); $excel_reader->setLoadSheetsOnly(array('ENTIDADES')); // Read Excel file $excel_object = $excel_reader->load($file_name); $excel_rows = $excel_object->getActiveSheet()->toArray(); // Process Excel data $is_header = true; $banks[] = array(); foreach ($excel_rows as $excel_row) { if ($is_header) { // Get column Ids from first row $column_ids = array(); foreach ($excel_row as $id => $column_name) { $column_ids[$column_name] = $id; } // check, if BIC is there if (empty($column_ids['BIC'])) { return $this->createParserOutdatedError(ts("The Spanish source file doesn't contain BICs")); } $is_header = false; } else { // If there's no bank code, I may assume we're at the end of the file if (!$excel_row[$column_ids['COD_BE']]) { break; } // Add the office code, if it exists if ($excel_row[$column_ids['CODENTSUC']] == '0000') { $value = $excel_row[$column_ids['COD_BE']]; } else { $value = $excel_row[$column_ids['COD_BE']] . $excel_row[$column_ids['CODENTSUC']]; } // If there's a "closed date", we don't import this bank if (!$excel_row[$column_ids['FCHBAJA']]) { $banks[] = array('value' => $value, 'name' => $excel_row[$column_ids['BIC']], 'label' => $excel_row[$column_ids['ANAGRAMA']], 'description' => '<b>CIF</b>: ' . $excel_row[$column_ids['CODIGOCIF']] . '<br>' . '<b>Phone</b>: ' . $excel_row[$column_ids['TELEFONO']] . '<br>' . '<b>Fax</b>: ' . $excel_row[$column_ids['NUMFAX']] . '<br>' . '<b>Web</b>: <a href="' . strtolower($excel_row[$column_ids['DIRINTERNET']]) . '">' . strtolower($excel_row[$column_ids['DIRINTERNET']]) . '</a><br>'); } } } // Remove temporary file unlink($file_name); // Free some memory unset($excel_rows); unset($excel_object); unset($excel_reader); // Finally, update DB return $this->updateEntries(CRM_Bic_Parser_ES::$country_code, $banks); }
public function loadExcelFile($filename) { $this->PHPExcelReader = PHPExcel_IOFactory::createReaderForFile($filename); $this->PHPExcelLoaded = true; $this->PHPExcelReader->setReadDataOnly(true); $excel = $this->PHPExcelReader->load($filename); $this->dataArray = $excel->getSheet(0)->toArray(); return $this->dataArray; }
/** * @param string $path * @return \PHPExcel_Worksheet */ protected function getExcel($path, $sheet = '') { $reader = \PHPExcel_IOFactory::createReaderForFile($path); if ($sheet) { $reader->setLoadSheetsOnly(array($sheet)); } /** @var \PHPExcel $excel */ $excel = $reader->load($path); return $excel->getActiveSheet(); }
function parse() { $objReader = PHPExcel_IOFactory::createReaderForFile($this->file_info['tmp_name']); $chunkFilter = new SJB_ChunkReadFilter(); $objReader->setReadFilter($chunkFilter); $objReader->setReadDataOnly(true); $this->data = new SJB_ImportXLSIterator(); $this->data->setObjReader($objReader); $this->data->setChunkFilter($chunkFilter); $this->data->setFileName($this->file_info['tmp_name']); }
/** * Construct CSV reader * * @param \SplFileObject $file Excel file * @param int $headerRowNumber Optional number of header row * @param int $activeSheet Index of active sheet to read from */ public function __construct(\SplFileObject $file, $headerRowNumber = null, $activeSheet = null) { $reader = \PHPExcel_IOFactory::createReaderForFile($file->getPathName()); $reader->setReadDataOnly(true); $excel = $reader->load($file->getPathname()); if (null !== $activeSheet) { $excel->setActiveSheetIndex($activeSheet); } $this->worksheet = $excel->getActiveSheet()->toArray(); if (null !== $headerRowNumber) { $this->setHeaderRowNumber($headerRowNumber); } }
public function update() { // First, download the file $file_name = sys_get_temp_dir() . '/be-banks.xls'; $downloaded_file = $this->downloadFile(CRM_Bic_Parser_BE::$page_url); if (empty($downloaded_file)) { return $this->createParserOutdatedError(ts("Couldn't download data file")); } // store file file_put_contents($file_name, $downloaded_file); unset($downloaded_file); // Automatically detect the correct reader to load for this file type $excel_reader = PHPExcel_IOFactory::createReaderForFile($file_name); // Set reader options $excel_reader->setReadDataOnly(); $excel_reader->setLoadSheetsOnly(array("Q_FULL_LIST_XLS_REPORT")); // Read Excel file $excel_object = $excel_reader->load($file_name); $excel_rows = $excel_object->getActiveSheet()->toArray(); // Process Excel data $skip_lines = 2; $banks[] = array(); foreach ($excel_rows as $excel_row) { $skip_lines -= 1; if ($skip_lines >= 0) { continue; } // Process row // compile bank name $bank_name = ''; for ($i = 2; $i < 5; $i++) { $localized_name = trim($excel_row[$i]); if (!empty($localized_name)) { if (!empty($bank_name)) { $bank_name .= ' / '; } $bank_name .= $localized_name; } } $bank = array('value' => $excel_row[0], 'name' => str_replace(' ', '', $excel_row[1]), 'label' => $bank_name, 'description' => ''); $banks[] = $bank; } // clean up before importing unset($excel_rows); unset($excel_object); unset($excel_reader); unlink($file_name); // Finally, update DB return $this->updateEntries(CRM_Bic_Parser_BE::$country_code, $banks); }
/** * @param \SplFileObject $file Excel file * @param integer $headerRowNumber Optional number of header row * @param integer $activeSheet Index of active sheet to read from * @param boolean $readOnly If set to false, the reader take care of the excel formatting (slow) */ public function __construct(\SplFileObject $file, $headerRowNumber = null, $activeSheet = null, $readOnly = true) { $reader = \PHPExcel_IOFactory::createReaderForFile($file->getPathName()); $reader->setReadDataOnly($readOnly); /** @var \PHPExcel $excel */ $excel = $reader->load($file->getPathname()); if (null !== $activeSheet) { $excel->setActiveSheetIndex($activeSheet); } $this->worksheet = $excel->getActiveSheet(); $this->maxColumn = $this->worksheet->getHighestColumn(); $this->maxRow = $this->worksheet->getHighestRow(); if (null !== $headerRowNumber) { $this->setHeaderRowNumber($headerRowNumber); } }
/** * @param string $filename * @return \PHPExcel_Reader_IReader */ protected function getPhpExcelReader(string $filename) { $this->setPhpExcelConfigurations(); $reader = \PHPExcel_IOFactory::createReaderForFile($filename); $instanceName = \get_class($reader); switch ($instanceName) { case 'PHPExcel_Reader_Excel2007': case 'PHPExcel_Reader_Excel5': case 'PHPExcel_Reader_OOCalc': /* @var $reader \PHPExcel_Reader_Excel2007 */ $reader->setReadDataOnly(true); break; } $phpExcel = $reader->load($filename); return $phpExcel; }
public function __construct($file) { if (!is_readable($file)) { throw new \Exception('Can\'t find the file'); } try { $reader = \PHPExcel_IOFactory::createReaderForFile($file)->setReadDataOnly(); $this->_xls = $reader->load($file); } catch (\PHPExcel_Reader_Exception $e) { throw new ImportException('Can\'t read the file'); } // iterator initialization $this->_xls_lowest_row = 2; $this->rewind(); $this->_xls_highest_row = $this->_xls->getSheet()->getHighestDataRow(); }
/** * * 读取excel文件时创建Reader * @param string $fileName 要读取的文件的全路径 * @param string $fileType Excel2007,Excel2003XML,OOCalc,SYLK,Gnumeric,CSV */ public function setReader($fileName = '', $fileType = '') { if (is_file($fileName)) { $this->fileName = $fileName; } if (!empty($fileType) && checkType($fileType)) { $this->fileType = $fileType; } //如果不知道文档类型,则使用PHPExcel_IOFactory的createReaderForFile方法创建reader if (empty($this->fileType)) { $this->excelReader = PHPExcel_IOFactory::createReaderForFile($this->fileName); return true; } else { //如果不知道文档类型,则使用PHPExcel_IOFactory的createReader方法创建reader $this->excelReader = PHPExcel_IOFactory::createReader($this->fileType); return true; } }
protected function execute(InputInterface $input, OutputInterface $output) { $this->container = $this->getContainer(); $this->em = $this->getContainer()->get('doctrine')->getManager(); $this->em->getConnection()->getConfiguration()->setSQLLogger(null); $this->formCSRF = $this->container->get('form.csrf_provider'); $this->adminPool = $this->container->get('sonata.admin.pool'); $this->user = $this->em->getRepository('ApplicationSonataUserBundle:User')->findOneBy(array('id' => $input->getArgument('user_id'))); $token = new UsernamePasswordToken($this->user, $this->user->getPassword(), "public", $this->user->getRoles()); $this->getContainer()->get("security.context")->setToken($token); // Fire the login event $event = new InteractiveLoginEvent($this->getContainer()->get('request'), $token); $this->getContainer()->get("event_dispatcher")->dispatch("security.interactive_login", $event); $this->admin = $admin = $this->adminPool->getAdminByAdminCode($input->getArgument('admin')); $this->client_id = $input->getArgument('client_id'); $this->user_id = $input->getArgument('user_id'); $this->pid = $input->getArgument('pid'); $this->client = $this->em->getRepository("ApplicationSonataClientBundle:Client")->findOneBy(array('id' => $this->client_id)); $file = $input->getArgument('file'); $inputFileName = $input->getArgument('inputFileName'); $this->_locking = $input->getArgument('locking'); $this->_year = $input->getArgument('year'); $this->_month = $input->getArgument('month'); /* @var $objReader \PHPExcel_Reader_Excel2007 */ $objReader = \PHPExcel_IOFactory::createReaderForFile($file); $objReader->setReadDataOnly(true); $objPHPExcel = $objReader->load($file); $sheets = $objPHPExcel->getAllSheets(); $content_arr = array(); foreach ($sheets as $sheet) { $title = $sheet->getTitle(); $content_arr[$title] = $sheet->toArray(); } $objPHPExcel->disconnectWorksheets(); unset($sheets, $objPHPExcel, $objReader); $this->importValidateAndSave($content_arr); if (empty($this->_import_counts['rows']['errors'])) { $this->saveImports($inputFileName); $this->importValidateAndSave($content_arr, true); } $this->messages = $this->getCountMessageImports(); $this->sendNotification(); echo serialize(array('messages' => $this->messages, 'error_counts' => !empty($this->_import_counts['rows']['errors']) ? $this->_import_counts['rows']['errors'] : 0, 'import_counts' => $this->_import_counts, 'pid' => $this->pid)); }
public function fillInSchedule() { $response = array("nonExistantRooms" => []); $ext = pathinfo($_FILES['file']['name'], PATHINFO_EXTENSION); if (!preg_match('/^(csv|xlsx?)$/im', $ext)) { return Response::json(array('message' => "Only xls, xlsx, and csv files are accepted."), 400); } $uploadName = $_FILES['file']['tmp_name']; $excelReader = PHPExcel_IOFactory::createReaderForFile($uploadName); $excelReader->setReadDataOnly(); $excelReader->setLoadSheetsOnly('Sheet1'); $excelObj = $excelReader->load($uploadName); $spreadsheet = $excelObj->getActiveSheet()->toArray(null, true, true, true); $this->setSemester($spreadsheet[1]); if ($this->dayBeforeSemesterStart < strtotime("01/01/1970")) { return Response::json(array('message' => "Sorry. That semester is not in the database. Please make sure you are formatting the spreadsheet correctly. If you're trying to schedule classes for a semester that is more than a month away, we probably haven't yet created a record for the semester in our database. Please wait a while and try again closer to the semester start date."), 400); } $formattedArray = $this->parseSpreadsheet($spreadsheet); $this->createEvents($formattedArray, $response); return Response::json($response, 200); }
public function load($filename, $basename = null) { $results = new AvailLoaderResults(); $results->filename = $filename; $results->basename = $basename; // Tosses exception $reader = \PHPExcel_IOFactory::createReaderForFile($filename); $reader->setReadDataOnly(true); $wb = $reader->load($filename); $ws = $wb->getSheet(0); /* foreach($ws->getRowIterator() as $row) { $cells = $row->getCellIterator(); $cells->setIterateOnlyExistingCells(false); $data = []; foreach($cells as $cell) { $data[] = $cell->getValue(); } $this->processRow($results,$data); } */ $rowMax = $ws->getHighestRow(); $colMax = $ws->getHighestColumn(); // Letter //echo sprintf("WS rows %d, cols %s\n",$rowMax,$colMax); for ($row = 1; $row < $rowMax; $row++) { $range = sprintf('A%d:%s%d', $row, $colMax, $row); $data = $ws->rangeToArray($range, null, false, false, false); $this->processRow($results, $data[0]); } /* $rows = $ws->toArray(); foreach($rows as $row) { $this->processRow($results,$row); } */ $this->addOfficial(); $results->officials = $this->officials; return $results; }
public function update() { // First, download the file $file_name = sys_get_temp_dir() . '/nl-banks.xls'; $downloaded_file = $this->downloadFile(CRM_Bic_Parser_NL::$page_url); if (empty($downloaded_file)) { return $this->createParserOutdatedError(ts("Couldn't download data file")); } // store file file_put_contents($file_name, $downloaded_file); unset($downloaded_file); // Automatically detect the correct reader to load for this file type $excel_reader = PHPExcel_IOFactory::createReaderForFile($file_name); // Set reader options $excel_reader->setReadDataOnly(); //$excel_reader->setLoadSheetsOnly(array("BIC-lijst")); // Read Excel file $excel_object = $excel_reader->load($file_name); $excel_rows = $excel_object->getActiveSheet()->toArray(); // Process Excel data $skip_lines = 2; $banks[] = array(); foreach ($excel_rows as $excel_row) { $skip_lines -= 1; if ($skip_lines >= 0) { continue; } // Process row $bank = array('value' => $excel_row[0], 'name' => $excel_row[1], 'label' => $excel_row[2], 'description' => ''); $banks[] = $bank; } // clean up before importing unset($excel_rows); unset($excel_object); unset($excel_reader); unlink($file_name); // Finally, update DB return $this->updateEntries(CRM_Bic_Parser_NL::$country_code, $banks); }
/** * Opens excel file * @return \Meridius\PhpExcel\Reader * @throws PhpExcelException */ public function open() { $reader = PhpOffice_PHPExcel_IOFactory::createReaderForFile($this->file); if (count($this->sheetsToLoad) > 0) { $reader->setLoadSheetsOnly($this->sheetsToLoad); } $reader->setReadDataOnly(true); $this->excel = $reader->load($this->file); if (is_null($this->sheetName)) { try { $this->activeSheet = $this->excel->getSheet($this->sheetIndex); } catch (Exception $e) { throw new PhpExcelException("Sheet index '{$this->sheetIndex}' is out of range of excel indexes."); } } else { $this->activeSheet = $this->excel->getSheetByName($this->sheetName); } if (!$this->activeSheet instanceof PhpOffice_PHPExcel_Worksheet) { throw new PhpExcelException("Sheet with name '{$this->sheetName}' does not exist in input excel."); } $this->mapHeaders(); return $this; }
public function __construct(\SplFileObject $file, $headerRowNumber = null, $activeSheet = null, $readOnly = true, $maxRows = null, $maxCol = null) { $this->file = $file; $this->activeSheet = $activeSheet; $this->reader = \PHPExcel_IOFactory::createReaderForFile($file->getPathName()); $this->reader->setReadDataOnly($readOnly); if (!is_null($headerRowNumber)) { $this->headerRowNumber = $headerRowNumber; $headerReader = clone $this->reader; $headerReader->setReadFilter(new ReadFilter($headerRowNumber + 1)); /** @var \PHPExcel $excel */ $excel = $headerReader->load($file->getPathname()); if (null !== $activeSheet) { $excel->setActiveSheetIndex($activeSheet); } $rows = $excel->getActiveSheet()->toArray(); $this->columnHeaders = $rows[$headerRowNumber]; //set max col from header length if not already given if (is_null($maxCol)) { $maxCol = \PHPExcel_Cell::stringFromColumnIndex(count($this->columnHeaders) - 1); } } $this->setBoundaries($maxRows, $maxCol); }
/** * * @param type $ext * @param type $file * @return type */ public function readXLS($file) { $PHPExcel = new \PHPExcel(); $excelReader = PHPExcel_IOFactory::createReaderForFile($file); $excelReader->setReadDataOnly(); $excelReader->setLoadAllSheets(); $excelObj = $excelReader->load($fileName); $worksheetNames = $excelObj->getSheetNames($fileName); $return = array(); foreach ($worksheetNames as $key => $sheetName) { $excelObj->setActiveSheetIndexByName($sheetName); $return[$sheetName] = $excelObj->getActiveSheet()->toArray(null, true, true, true); } return $return; }
protected function excelToCsvFile($filename) { if (preg_match('#(.*?)\\.(csv)#is', $filename)) { $dest_file = AdminImportController::getPath(strval(preg_replace('/\\.{2,}/', '.', $filename))); } else { $csv_folder = AdminImportController::getPath(); $excel_folder = $csv_folder . 'csvfromexcel/'; $info = pathinfo($filename); $csv_name = basename($filename, '.' . $info['extension']) . '.csv'; $dest_file = $excel_folder . $csv_name; if (!is_dir($excel_folder)) { mkdir($excel_folder); } if (!is_file($dest_file)) { $reader_excel = PHPExcel_IOFactory::createReaderForFile($csv_folder . $filename); $reader_excel->setReadDataOnly(true); $excel_file = $reader_excel->load($csv_folder . $filename); $csv_writer = PHPExcel_IOFactory::createWriter($excel_file, 'CSV'); $csv_writer->setSheetIndex(0); $csv_writer->setDelimiter(';'); $csv_writer->save($dest_file); } } return $dest_file; }
function loadExcel($filename, $pars) { $pars_default = array('sheetIndex' => 0, 'headerKey' => FALSE, 'readColumn' => array()); $pars = array_merge($pars_default, $pars); // error_reporting(E_ALL); // ini_set('display_errors', TRUE); // ini_set('display_startup_errors', TRUE); require_once APPPATH . 'libraries/PHPExcel.php'; $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp; $cacheSettings = array('memoryCacheSize' => '16MB'); PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings); $objPHPExcel = new PHPExcel(); $objReader = PHPExcel_IOFactory::createReaderForFile($filename); $objPHPExcel = $objReader->load($filename); $objPHPExcel->setActiveSheetIndex($pars['sheetIndex']); $objWorksheet = $objPHPExcel->getActiveSheet(); $i = 0; $temp_rows = array(); $temp_header = array(); foreach ($objWorksheet->getRowIterator() as $row) { $cellIterator = $row->getCellIterator(); $cellIterator->setIterateOnlyExistingCells(false); if ($pars['headerKey'] == FALSE) { //不用读取表头 // if( $i == 0 && $pars['includeHeader'] == FALSE ){ // $i++; // continue; // } $temp_row = array(); foreach ($cellIterator as $cell) { $temp_row[] = trim($cell->getValue()); } $temp_rows[] = $temp_row; $i++; } else { //以表头为键 if ($i == 0) { foreach ($cellIterator as $cell) { $temp_header[] = $cell->getValue(); } //指定列头必须在excel中 if (!array_in_array($temp_header, $pars['readColumn'])) { return array(); } $i++; } else { $temp_row = array(); foreach ($cellIterator as $cell) { $temp_row[] = $cell->getValue(); } //$temp_rows[] = array_combine($temp_header, $temp_row); $all_row = array_combine($temp_header, $temp_row); if ($pars['readColumn']) { $temp = array(); foreach ($all_row as $key => $val) { if (in_array($key, $pars['readColumn'])) { $temp[$key] = $val; } } $temp_rows[] = $temp; } else { $temp_rows[] = $all_row; } $i++; } } } return $temp_rows; }