Новости

Преобразование 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 для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.

Back to top button