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