Преобразование Excel-файла в PHP класс

Преобразование Excel-файла в PHP класс

Доброго времени суток! В данной статье я покажу Вам как можно преобразовать Excel-файл в PHP объект для дальнейшей работы с ним.

Допустим у нас есть некий Excel-документ, например бухгалтерский баланс некой кредитной организации. И нам необходимо вытащить из его столбцов значения, которые нужно будет назначить соответствующим полям класса.

Для начала создадим файл composer.json со следующим содержимым:


{
    "autoload": {
        "psr-4": {
            "SampleApp\BanksReporting\": "src"
        }
    },
    "require": {
        "phpoffice/phpspreadsheet": "^1.20"
    }
}

Файловая структура проекта следующая:


.
├── files
│   ├── report_01102021.xlsx
│   └── report_01072021.xlsx
├── src
│   ├── BalanceSheet.php
│   ├── currentPeriodMapper.php
│   ├── previousPeriodMapper.php
│   └── read_excel.php
├── vendor
├── composer.json
└── composer.lock

Далее выполним команду в директории проекта для установки пакета phpoffice/phpspreadsheet:


$ composer install

Содержимое файла класса BalanceSheet. Его содержимое полностью зависит от структуры Вашего Excel-файла. В данном случае он такой:


<?php

    namespace SampleAppBanksReporting;

    class BalanceSheet
    {
        // метаданные отчета
        public $reportDate;
        public $bankName;
        public $postAddress;

        // активы
        public $moneyAssets;
        public $fundsInTheCentralBank;
        public $mandatoryReserves;
        public $fundsInCreditInstitutions;
        public $loanDebt;
        public $fixedAssets;
        public $requirementsForInterest;
        public $otherAssets;
        public $totalAssets;

        // пассивы
        public $loansOfTheCentralBank;
        public $fundsOfCreditInstitutions;
        public $fundsOfClientsNonCreditOrganizations;
        public $depositsOfIndividuals;
        public $debtObligationsIssued;
        public $iterestPaymentOobligations;
        public $otherLiabilities;
        public $provisionsForPpossibleLosses;
        public $totalObligations;

        // источники собственных средств
        public $fundsOfShareholders;
        public $sharesRepurchasedFromShareholders;
        public $seigniorage;
        public $revaluationOfFixedAssets;
        public $deferredExpenses;
        public $unusedProfitsOrOutstandingLossesOfPreviousYears;
        public $profitOrLossForTheReportingPeriod;
        public $totalSourcesOfOwnFunds;
        public $totalLiabilities;

        // забалансовые пассивы
        public $irrevocableObligations;
        public $guaranteesByBank;
    }

В данном случае я рассматриваю пример отчета в котором есть два столбца — отчетный период и предыдущий период. Для каждого период я создам отдельный файл, возвращающий массив, в котором ключ — это название поля класса BalanceSheet, а значение — название ячейки, в которой лежат необходимые данные.

Файл для предыдущего периода:


<?php

    return [

        // metadata
        'reportDate'  => 'A1',
        'bankName'    => 'C2',
        'postAddress' => 'A3',

        // Cells
        'moneyAssets'               => 'D8',
        'fundsInTheCentralBank'     => 'D9',
        'mandatoryReserves'         => 'D10',
        'fundsInCreditInstitutions' => 'D11',
        'loanDebt'                  => 'D12',
        'fixedAssets'               => 'D13',
        'requirementsForInterest'   => 'D14',
        'otherAssets'               => 'D15',
        'totalAssets'               => 'D16',

        // Liabilities
        'loansOfTheCentralBank'                 => 'D18',
        'fundsOfCreditInstitutions'             => 'D19',
        'fundsOfClientsNonCreditOrganizations'  => 'D20',
        'depositsOfIndividuals'                 => 'D21',
        'debtObligationsIssued'                 => 'D22',
        'iterestPaymentOobligations'            => 'D23',
        'otherLiabilities'                      => 'D24',
        'provisionsForPpossibleLosses'          => 'D25',
        'totalObligations'                      => 'D26',

        // Sources of own funds
        'fundsOfShareholders'                  => 'D28',
        'sharesRepurchasedFromShareholders'    => 'D29',
        'seigniorage'                          => 'D30',
        'revaluationOfFixedAssets'             => 'D31',
        'deferredExpenses'                     => 'D32',
        'unusedProfitsOrOutstandingLossesOfPreviousYears' => 'D33',
        'profitOrLossForTheReportingPeriod'    => 'D34',
        'totalSourcesOfOwnFunds'               => 'D35',
        'totalLiabilities'                     => 'D36',

        // Off-balance sheet liabilities
        'irrevocableObligations' => 'D38',
        'guaranteesByBank' => 'D39',

    ];

Для текущего периода:


<?php

    return [

        // metadata
        'reportDate'  => 'A1',
        'bankName'    => 'C2',
        'postAddress' => 'A3',

        // Cells
        'moneyAssets'               => 'E8',
        'fundsInTheCentralBank'     => 'E9',
        'mandatoryReserves'         => 'E10',
        'fundsInCreditInstitutions' => 'E11',
        'loanDebt'                  => 'E12',
        'fixedAssets'               => 'E13',
        'requirementsForInterest'   => 'E14',
        'otherAssets'               => 'E15',
        'totalAssets'               => 'E16',

        // Liabilities
        'loansOfTheCentralBank'                 => 'E18',
        'fundsOfCreditInstitutions'             => 'E19',
        'fundsOfClientsNonCreditOrganizations'  => 'E20',
        'depositsOfIndividuals'                 => 'E21',
        'debtObligationsIssued'                 => 'E22',
        'iterestPaymentOobligations'            => 'E23',
        'otherLiabilities'                      => 'E24',
        'provisionsForPpossibleLosses'          => 'E25',
        'totalObligations'                      => 'E26',

        // Sources of own funds
        'fundsOfShareholders'                  => 'E28',
        'sharesRepurchasedFromShareholders'    => 'E29',
        'seigniorage'                          => 'E30',
        'revaluationOfFixedAssets'             => 'E31',
        'deferredExpenses'                     => 'E32',
        'unusedProfitsOrOutstandingLossesOfPreviousYears' => 'E33',
        'profitOrLossForTheReportingPeriod'    => 'E34',
        'totalSourcesOfOwnFunds'               => 'E35',
        'totalLiabilities'                     => 'E36',

        // Off-balance sheet liabilities
        'irrevocableObligations' => 'E38',
        'guaranteesByBank' => 'E39',

    ];

Обратите внимание, что значения E8, E9, E10 и т.д. — жестко заданные названия ячеек в которых лежат данные. Т.е. предполагается, что нужные значения будут лежать именно в этих ячейках, если нет, то вместо значения Вы получите null. Таким образом, структура разбираемого Excel-файла жестко задана.

Теперь непосредственно код, который будет вытаскивать значения и присваивать их полям класса. Обратите внимание как PHP рефлексия упрощает инициализацию полей класса.


<?php

    require __DIR__ . '/../vendor/autoload.php';

    // подключаем классы
    use SampleAppBanksReportingBalanceSheet;
    use PhpOfficePhpSpreadsheetReaderXlsx;
    use PhpOfficePhpSpreadsheetWorksheetWorksheet;
    use PhpOfficePhpSpreadsheetRichTextRichText;

    // получаем ссылки на ячейки листа
    function getCells(Worksheet $sheet)
    {
        return function(string $cellName) use ($sheet) {

            // получаем значение ячейки
            $cellValue = $sheet->getCell($cellName)->getValue();

            // если значение ячейки является объектом
            if ( $cellValue instanceof RichText ) {

                $richTextElements = $cellValue->getRichTextElements();
                $outputValue = '';

                // пробегаемся по всем элементам
                foreach($richTextElements as $richTextElement)
                {
                    // и объединяем их в одну строку
                    $outputValue .= trim($richTextElement->getText()) . ' ';
                }

                return $outputValue;
            }  

            return $cellValue;
        };
    }

    // конвертируем значения листа в нужный нам объекта
    function createBalanceFromSheet(Worksheet $sheet, array $period)
    {
        $cells = getCells($sheet);

        $balance = new BalanceSheet();
        // с помощью рефлексии
        $refClass = new ReflectionClass(BalanceSheet::class);
        // получаем все поля объекта
        $properties = $refClass->getProperties();

        // проходимся по каждому полю
        foreach($properties as $property) 
        {
            // смотрим, что это за поле
            switch($property->name)
            {
                // если дата
                case 'reportDate':
                    // получаем значение из Excel-файла, которое находится в ячейке возвращаемой из массива $period['reportDate']                     // в данном случае такова специфика файла, что нужно вырезать часть строки
                    // полученное значение назначаем свойству класса
                    $balance->{$property->name} = trim(substr($cells($period['reportDate']), -12));    
                    break;

                case 'bankName':
                    $balance->{$property->name} = $cells($period['bankName']);    
                    break;

                case 'postAddress':
                    $balance->{$property->name} = trim(substr($cells($period['postAddress']), 32));
                    break;

                default:
                    $balance->{$property->name} = $cells($period[$property->name]);    
            };    
        }

        // возвращаем объект 
        return $balance;
    }

    // открываем первый лист Excel-файла
    $reader = new Xlsx();
    $spreadsheet = $reader->load('./files/report_01102021.xlsx');
    $sheet = $spreadsheet->getSheet(0);

    // массивы с адресами ячеек, где лежат данные
    $currentPeriod = require('currentPeriodMapper.php');
    $previousPeriod = require('previousPeriodMapper.php');

    // создаем объекты из листа отдельно для каждого периода
    // на этом этапе Вы уже может сохранить эти данные в базу например,
    // или провести какие либо с ними вычисления
    $currentBalance = createBalanceFromSheet($sheet, $currentPeriod);
    $previousBalance = createBalanceFromSheet($sheet, $previousPeriod);

    // преобразуем это все в JSON для отладки
    $json_c = json_encode($currentBalance, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE);
    $json_p = json_encode($previousBalance, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE);

    // выводим в консоль
    print($json_c);
    print($json_p);

Таким образом в данной статье мы рассмотрели пример, то как можно вытащить данные из какого-либо отчета на PHP в структурированном виде. Что делать с этими данными дальше — зависит от Ваших задач. По ним например, можно сделать аналитику, загрузить в базу и т.д.

Источник

Статьи по теме

Добавить комментарий

Ваш адрес email не будет опубликован.

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.

Проверьте также
Close
Back to top button