Laravel Excel Service

ExcelService.php

<?php

namespace App\Services;

use Exception;
use Illuminate\Support\Carbon;
use PhpOffice\PhpSpreadsheet\Calculation\Functions;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Reader\IReader;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

class ExcelService
{

    /* @var Spreadsheet */
    private $spreadsheet;

    /* @var IReader */
    private $reader;

    /* @var string */
    private $inputFilename;

    /* @var string */
    private $inputFileType;

    /* @var Worksheet */
    private $activeWorkSheet;

    /* @var int */
    private $workSheetIndex;

    /**
     * ExcelService constructor.
     *
     * @param $inputFileName
     *
     * @throws Exception
     */
    public function __construct(string $inputFileName)
    {
        if (!file_exists($inputFileName)) {
            throw new Exception("Can't access the file.");
        } else {
            $this->inputFilename = $inputFileName;
        }
        /**
         * Identify the type of inputFileName
         */
        $this->inputFileType = IOFactory::identify($this->inputFilename);

        /**
         * Create a new Reader of the type that has been identified
         */
        $this->reader = IOFactory::createReader($this->inputFileType);

        Functions::setReturnDateType(Functions::RETURNDATE_PHP_OBJECT);

        /**
         * Load $inputFileName to a Spreadsheet Object
         */
        $this->spreadsheet = $this->reader->load($this->inputFilename);

        /**
         * Load first worksheet by default.
         */
        $this->activeWorkSheet = $this->spreadsheet->setActiveSheetIndex(0);
        $this->workSheetIndex  = 0;
    }

    /**
     * @param int $worksheetIndex
     *
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     */
    private function changeActiveWorksheet(int $worksheetIndex)
    {
        $this->activeWorkSheet = $this->spreadsheet->setActiveSheetIndex($worksheetIndex);
        $this->workSheetIndex  = $worksheetIndex;
    }

    /**
     * @param int|null $readDate
     *
     * @return Carbon|null
     */
    static function excelDateToDate(?int $readDate): ?Carbon
    {
        if ($readDate == null) {
            return null;
        }
        $phpEpochDate = $readDate - 25569; //to offset to Unix epoch
        $time         = strtotime("+$phpEpochDate days", mktime(0, 0, 0, 1, 1, 1970));
        return Carbon::parse($time);
    }

    /**
     * Return total number of worksheet in the active workbook.
     *
     * @return int
     */
    public function getTotalWorksheets(): int
    {
        /**
         * Load worksheets info from inputFilename
         */
        $worksheetData = $this->reader->listWorksheetInfo($this->inputFilename);

        /**
         * Return the count how many Worksheet in the Workbook
         */
        return count($worksheetData);
    }

    /**
     * Print total number of worksheet in the active workbook.
     *
     * @return void
     */
    public function totalWorksheets(): void
    {
        $totalWorksheet = $this->getTotalWorksheets();
        echo 'There is ' . $totalWorksheet . ' worksheet' . (($totalWorksheet > 1) ? 's' : '') . ' in your workbook.';
    }

    /**
     * Return total Rows in provided worksheet index.
     * Default to first worksheet if no index provided.
     *
     * @return int
     */
    public function getTotalRows(): int
    {
        /**
         * Load worksheets info from inputFilename
         */
        $worksheetData = $this->reader->listWorksheetInfo($this->inputFilename);

        return $worksheetData[$this->workSheetIndex]['totalRows'];
    }

    /**
     * Print the total Rows in provided worksheet index.
     * Default to first worksheet if no index provided.
     *
     * @return void
     */
    public function totalRows(): void
    {
        $totalRows = $this->getTotalRows();
        echo 'There is ' . $totalRows . ' rows on the ' . ordinal($this->workSheetIndex) . ' worksheet.';
    }

    /**
     * Return total Columns in provided worksheet index.
     * Default to first worksheet if no index provided.
     *
     * @return int
     */
    public function getTotalColumns(): int
    {
        /**
         * Load worksheets info from inputFilename
         */
        $worksheetData = $this->reader->listWorksheetInfo($this->inputFilename);

        return $worksheetData[$this->workSheetIndex]['totalColumns'];
    }

    /**
     * Print the total Columns in provided worksheet index.
     * Default to first worksheet if no index provided.
     *
     * @return void
     */
    public function totalColumns()
    {
        $totalColumns = $this->getTotalColumns();
        echo 'There is ' . $totalColumns . ' columns on the ' . ordinal($this->workSheetIndex) . ' worksheet.';
    }

    /**
     * Return or echo the headers of a sheet.
     *
     * @return array
     */
    public function getHeaders(): array
    {
        $totalColumns = $this->getTotalColumns();

        $headers = [];

        foreach (range(1, $totalColumns) as $colIndex) {
            $headers[$colIndex] = $this->activeWorkSheet->getCellByColumnAndRow($colIndex, 1)->getValue();
        }

        return $headers;
    }

    /**
     * Throw an error if headers of the given files don't match expected values.
     * Return true if headers are valid.
     *
     * @param array $validHeaders
     * @param array $headers
     *
     * @return bool
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws Exception
     */
    public function validateHeaders(array $validHeaders, array $headers): bool
    {
        $headers         = array_values($headers);
        $indexCorrection = 0;

        foreach ($validHeaders as $index => $validHeader) {

            if ($headers[$index + $indexCorrection] == "") {
                $this->removeColumn($index);
                $indexCorrection++;
            }

            if (trim($validHeader) != trim($headers[$index + $indexCorrection])) {
                throw new Exception('Invalid file format. Your file should have the following headers: ' . implode(', ', $validHeaders) . '. Problematic header: ' . $validHeader);
            }
        }

        return true;
    }

    /**
     * @param int $columnIndex
     *
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     */
    public function removeColumn(int $columnIndex)
    {
        $this->activeWorkSheet = $this->activeWorkSheet->removeColumnByIndex($columnIndex+1);
    }

    /**
     * Return an array with all the content excluding the header row.
     *
     * @return array
     */
    public function getContent(): array
    {
        $totalColumns = $this->getTotalColumns();
        $totalRows    = $this->getTotalRows();

        $content = [];

        foreach (range(2, $totalRows) as $rowIndex) {
            foreach (range(1, $totalColumns) as $colIndex) {
                $content[$rowIndex][$colIndex] = $this->activeWorkSheet->getCellByColumnAndRow($colIndex, $rowIndex)
                                                                       ->getValue();
            }
        }

        return $content;
    }
}