Code Coverage |
||||||||||
Classes and Traits |
Functions and Methods |
Lines |
||||||||
| Total | |
0.00% |
0 / 2 |
|
0.00% |
0 / 8 |
CRAP | |
0.00% |
0 / 54 |
| ia\Import\uploadXlsx | |
0.00% |
0 / 1 |
56.00 | |
0.00% |
0 / 20 |
|||
| XlsxReader | |
0.00% |
0 / 1 |
|
0.00% |
0 / 5 |
420.00 | |
0.00% |
0 / 31 |
| __constructor | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 2 |
|||
| __destruct | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 2 |
|||
| close | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 5 |
|||
| sheets | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 4 |
|||
| rows | |
0.00% |
0 / 1 |
182.00 | |
0.00% |
0 / 18 |
|||
| HeaderException | |
0.00% |
0 / 1 |
|
0.00% |
0 / 2 |
6.00 | |
0.00% |
0 / 3 |
| __construct | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 2 |
|||
| __toString | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 1 |
|||
| <?php | |
| namespace ia\Import; | |
| use \DateTime; | |
| use \Exception; | |
| use ia\Lib\Synonyms; | |
| use ia\Sql\Mysql\MultipleInsert; | |
| use ia\Util\Str; | |
| use ia\Lib\iaReMapKeys; | |
| function uploadXlsx($filePath, $db) { | |
| try { | |
| $reader = new XlsxReader($filePath); | |
| } catch(Exception $fileReadError) { | |
| return ['ok'=>false]; | |
| } | |
| foreach($reader->spreadSheets() as $sheetIndex => $sheetName) { | |
| // prepare for sheet | |
| $needHeaders = []; | |
| $valueSynonyms = new Synonyms(); | |
| $validator = new Validator(); | |
| $insert = new MultipleInsert($db, $sqlBuilder->insertInto('table', $needHeaders), "ON DUP CLAUSE"); | |
| $keyMap = new iaReMapKeys($needHeaders); | |
| try { //HeaderException wrong headers | |
| foreach($reader->rows($sheetIndex, $keyMap, XlsxReader::EMPTY_ROWS_SKIP, XlsxReader::CELL_TRIM, 500) as $rowNumber => $rowData) { | |
| try { // Exception in row | |
| $mappedRow = $rowData['mapped']; | |
| $synonimed = $synonims->std($mappedRow); | |
| $standarized = $std->standard($synonimed); | |
| $errorMessages = $validator($standarized); | |
| if(!empty($errorMessages)) { | |
| // error in rows | |
| // register and continue or break | |
| } | |
| $insert->valuesArray($standarized); | |
| } catch(RowException $rowException) { | |
| } | |
| } | |
| } catch(HeaderException $wrongHeaders) { | |
| } | |
| $insert->insertNow(); | |
| } | |
| return ['ok'=>true]; | |
| } | |
| //// inline strings test with https://github.com/akeneo-labs/spreadsheet-parser/files/2091295/Product.Lagerdata_6_11_2018.1.xlsx | |
| // https://github.com/akeneo-labs/spreadsheet-parser/pull/43 | |
| // https://github.com/akeneo-labs/spreadsheet-parser/pull/55 | |
| // We had the exact same issue. Turns out the the generated xlsx hat no SharedStrings in it. Manage to fixe it and submited a pr :) #55 | |
| class XlsxReader { | |
| const EMPTY_ROWS_SKIP = 0; | |
| const EMPTY_ROWS_RETURN = 1; | |
| const CELL_TRIM = 0; | |
| const CELL_NO_TRIM = 2; | |
| protected $workbook; | |
| protected $progressShow; | |
| public function __constructor($filePath, $showProgressCallable = null) { | |
| $this->workbook = SpreadsheetParser::open($filePath); | |
| } | |
| public function __destruct() { | |
| $this->close(); | |
| } | |
| protected function close() { | |
| if(isset($this->workbook)) { | |
| try { | |
| $this->workbook->close(); | |
| unset($this->workbook); | |
| } catch(Exception $e) { | |
| } | |
| } | |
| } | |
| public function sheets() { | |
| foreach($this->workbook->spreadSheets() as $sheetIndex => $sheetName) { | |
| //@TODO show progress | |
| yield $sheetIndex => $sheetName; | |
| } | |
| $this->close(); | |
| } | |
| /** | |
| * | |
| * | |
| * @param int $sheetIndex | |
| * @param iaReMapKeys $keyMap | |
| * @param int $returnEmptyRows | |
| * @param int $trimValues | |
| * @param int $progressShowEachRows | |
| * @return \Generator | |
| * @throws HeaderException | |
| */ | |
| public function rows($sheetIndex, $keyMap, $returnEmptyRows = self::EMPTY_ROWS_SKIP, $trimValues = self::CELL_TRIM, $progressShowEachRows = 500) { | |
| foreach($this->workbook->createRowIterator($sheetIndex) as $rowIndex => $row) { | |
| if($rowIndex == 1) { // process header row | |
| $duplicatedHeaders = $keyMap->mapHeaderRow($row); | |
| if($duplicatedHeaders || $keyMap->getHeaderMissing() === []) { // header row valid | |
| throw new HeaderException('Error in headers: '); | |
| } | |
| continue; | |
| } | |
| //@TODO show progress | |
| $mapped = $keyMap->row2key($row); | |
| if($keyMap->isLastRowEmpty() && $returnEmptyRows === self::EMPTY_ROWS_SKIP) { | |
| continue; | |
| } | |
| foreach($mapped as $k => &$d) { | |
| if($d === '' || $d === null) { | |
| continue; | |
| } | |
| if($d instanceof DateTime) { | |
| $d = $d->format('Y-m-d H:i'); | |
| } | |
| if($trimValues === self::CELL_TRIM && is_string($d)) { | |
| $d = Str::strim($d); | |
| } | |
| } | |
| yield $rowIndex => ['mapped'=>$mapped, 'raw'=>$row]; | |
| } | |
| } | |
| } | |
| class HeaderException extends Exception { | |
| public function __construct($message, $code = 0, Exception $previous = null) { | |
| // some code | |
| // make sure everything is assigned properly | |
| parent::__construct($message, $code, $previous); | |
| } | |
| public function __toString() { | |
| return __CLASS__ . ": [{$this->code}]: {$this->message}\n"; | |
| } | |
| } |