Преобразование 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 в структурированном виде. Что делать с этими данными дальше — зависит от Ваших задач. По ним например, можно сделать аналитику, загрузить в базу и т.д.
