<?php
namespace App\Excels\Imports\BasicInformation;
use Maatwebsite\Excel\Concerns\WithBatchInserts;
use Maatwebsite\Excel\Concerns\WithCalculatedFormulas;
use Maatwebsite\Excel\Concerns\WithChunkReading;
use Maatwebsite\Excel\Concerns\WithMapping;
use Maatwebsite\Excel\Concerns\WithStartRow;
use Maatwebsite\Excel\Events\BeforeImport;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use Maatwebsite\Excel\Concerns\ToArray;
use Maatwebsite\Excel\Concerns\WithEvents;
class StoreSiteImportExcel implements WithBatchInserts, WithMapping, WithEvents, WithStartRow, WithCalculatedFormulas, WithChunkReading
{
public $mergeCells = [];
public $filePath;
public function __construct($filePath = '')
{
$this->filePath = $filePath;
}
protected $header = [
0 => 'account',
1 => 'primary_account',
2 => 'platform',
3 => 'site',
4 => 'remark',
];
protected $chunk_size = 1000;
public function batchSize(): int
{
return 10000;
}
public function chunkSize(): int
{
return $this->chunk_size;
}
public function startRow(): int
{
return 2;
}
public function map($row): array
{
$mapped = [];
foreach ($this->header as $key => $value) {
if ($value) {
$val = preg_replace("/^[\s\v" . chr(194) . chr(160) . "]+/", "", $row[$key]);
$mapped[$value] = preg_replace("/[\s\v" . chr(194) . chr(160) . "]+$/", "", $val);
}
}
return $mapped;
}
public function array(array $array): array
{
if ($this->mergeCells) {
$startRow = 2;
foreach ($this->mergeCells as $key => $mergeCells) {
if ($mergeCells && is_array($mergeCells)) {
foreach ($mergeCells as $mergeCell) {
[$begin, $end] = Coordinate::getRangeBoundaries($mergeCell);
$beginRow = $begin[1] - $startRow;
$beginCol = Coordinate::columnIndexFromString($begin[0]) - 1;
$endRow = $end[1] - $startRow;
$endCol = Coordinate::columnIndexFromString($end[0]) - 1;
$value = $array[$key][$beginRow][$this->header[$beginCol]];
for ($intCol = $beginCol; $intCol <= $endCol; $intCol++) {
for ($intRow = $beginRow; $intRow <= $endRow; $intRow++) {
$array[$key][$intRow][$this->header[$intCol]] = $value;
}
}
}
}
}
}
return $array;
}
public function registerEvents(): array
{
return [
BeforeImport::class => function (BeforeImport $event) {
$iofReader = $event->reader->getPhpSpreadsheetReader();
$iofReader->setReadDataOnly(false);
$iofLoad = $iofReader->load($event->getConcernable()->filePath);
$mergeCells = [];
foreach ($iofLoad->getAllSheets() as $key => $item) {
$mergeCells[$key] = $item->getMergeCells();
}
$event->getConcernable()->mergeCells = $mergeCells;
},
];
}
}