public function testMultiSheet()
{
$file = new \SplFileObject(__DIR__ . '/../Fixtures/data_multi_sheet.xls');
$sheet1reader = new ExcelReader($file, null, 0);
$this->assertEquals(3, $sheet1reader->count());
$sheet2reader = new ExcelReader($file, null, 1);
$this->assertEquals(2, $sheet2reader->count());
}
/**
* ExcelReader constructor.
* @param Configuration $config
*/
public function __construct(Configuration $config)
{
$filename = $config->get(array('filename'));
$header = $config->get(array('header'), false) ? 0 : null;
$sheet = $config->get(array('sheet'), 0);
parent::__construct(new \SplFileObject($filename), $header, $sheet);
}
/**
*
* @param string $jotFormUrl
* @param string|null $password
* @throws UnableToRetrieveCsvFile
*/
public function __construct($jotFormUrl, $password = null)
{
// My Constructor
$this->jotFormUrl = $jotFormUrl;
$this->password = $password;
$downloadHelper = new JotFormDownloadHelper();
// Download Excel-File from jotForm
$filePath = $downloadHelper->downloadFromJotForm($jotFormUrl, $password);
$splFile = new \SplFileObject($filePath);
// Parent Construct
parent::__construct($splFile, $rowNumber = 0);
}
public function rewind()
{
if (is_null($this->worksheet)) {
$this->reader->setReadFilter(new ReadFilter($this->maxRows, $this->maxCol));
/** @var \PHPExcel $excel */
$excel = $this->reader->load($this->file->getPathname());
if (null !== $this->activeSheet) {
$excel->setActiveSheetIndex($this->activeSheet);
}
$this->worksheet = $excel->getActiveSheet()->toArray();
}
parent::rewind();
}
public function load()
{
$headers = $this->getOption('headers');
$headerRow = $this->getOption('header_row');
$reader = new ExcelReader($this->getFile(), $headerRow, $this->getOption('worksheet'), $this->getOption('read_only'));
if (false !== $headerRow) {
// $duplicateHeaders = $headers == 'merge' ? ExcelReader::DUPLICATE_HEADERS_MERGE : CsvReader::DUPLICATE_HEADERS_INCREMENT;
$reader->setHeaderRowNumber($headerRow);
//, $duplicateHeaders);
}
if (is_array($headers)) {
$reader->setColumnHeaders($headers);
} elseif ('number' == $headers) {
$headers = [];
for ($i = 1; $i <= count($reader->getColumnHeaders()); ++$i) {
$headers[] = 'field' . $i;
}
$reader->setColumnHeaders($headers);
}
// $reader->setStrict($this->getOption('strict'));
return $this->setReader($reader);
}
public function updateEntityAction($object)
{
//override createAction in line 66;
//get path and set the file
$path = '../web/media/' . $object->getPath();
$file = new \SplFileObject($path);
//check extension to read
if ($object->getExtension() == 'csv') {
$reader = new CsvReader($file, ',');
$reader->setHeaderRowNumber(0);
} else {
if ($object->getExtension() == 'xls' || $object->getExtension() == 'xlsx') {
$reader = new ExcelReader($file, 0);
}
}
//find Key :applicationKey
$readerAllHeader = $reader->getColumnHeaders();
foreach ($readerAllHeader as $readerHeader) {
if (preg_match("/applicationKey/i", $readerHeader)) {
$key = $readerHeader;
}
}
$em = $this->getDoctrine()->getManager();
$em->getConnection()->beginTransaction();
// suspend auto-commit
//$doctrineWriter = new DoctrineWriter($em, 'LmcAdmissionBundle:Test','appPrefix');
$doctrineWriter = new DoctrineWriter($em, 'LmcAdmissionBundle\\Entity\\Application', $key);
$doctrineWriter->disableTruncate();
$doctrineWriter->prepare();
$line = 1;
$fail = 0;
$error_line = array();
$dateField = array('createdAt', 'deletedAt', 'updatedAt', 'birthDate');
try {
foreach ($reader as $row) {
foreach ($dateField as $field) {
if (!$row[$field]) {
unset($row[$field]);
} else {
$dateConverter = strtotime($row[$field]);
$new_dateConverter = date_create(date('Y-m-d H:i:s', $dateConverter));
$row[$field] = $new_dateConverter;
}
}
// if(is_float($row['interviewDate'])){
// $temp = date('Y-m-d H:i:s', $row['interviewDate']);
// $row['interviewDate'] = $temp;
// }
$line++;
$doctrineWriter->writeItem($row);
if ($doctrineWriter->getFail() != $fail) {
$error_line[] = $line;
$fail = $doctrineWriter->getFail();
}
}
$success = $doctrineWriter->getSuccess();
$doctrineWriter->finish();
$em->getConnection()->commit();
if ($fail != 0) {
$log = 'sccess:' . $success . ' fail:' . $fail . ' line (' . implode(",", $error_line) . ')';
} else {
$log = 'sccess:' . $success . ' fail:' . $fail;
}
$object->setLog($log);
$em->persist($object);
$em->flush();
} catch (Exception $e) {
$em->getConnection()->rollback();
throw $e;
}
}
