| Server IP : 37.220.80.31 / Your IP : 18.117.74.41 Web Server : Apache/2.4.52 (Ubuntu) System : Linux 3051455-guretool.twc1.net 5.15.0-107-generic #117-Ubuntu SMP Fri Apr 26 12:26:49 UTC 2024 x86_64 User : www-root ( 1010) PHP Version : 7.4.33 Disable Function : pcntl_alarm,pcntl_fork,pcntl_waitpid,pcntl_wait,pcntl_wifexited,pcntl_wifstopped,pcntl_wifsignaled,pcntl_wexitstatus,pcntl_wtermsig,pcntl_wstopsig,pcntl_signal,pcntl_signal_dispatch,pcntl_get_last_error,pcntl_strerror,pcntl_sigprocmask,pcntl_sigwaitinfo,pcntl_sigtimedwait,pcntl_exec,pcntl_getpriority,pcntl_setpriority, MySQL : OFF | cURL : ON | WGET : OFF | Perl : OFF | Python : OFF | Sudo : OFF | Pkexec : OFF Directory : /var/www/www-root/data/www/dev.artlot24.ru/local/php_interface/lib/Excel/ |
Upload File : |
<?php
namespace LocalLib\Excel;
use PhpOffice\PhpSpreadsheet as PS;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
final class ExcelWriter extends BaseExcel {
const FORMAT_TEXT = '@';
const FORMAT_DATE_DDMMYYYY = 'dd.mm.yyyy';
const FORMAT_DATETIME_DDMMYYYY_HHMM = 'dd.mm.yyyy h:mm';
const FORMAT_NUMBER = '# ##0';
const FORMAT_NUMBER_0 = '# ##0.0';
const FORMAT_NUMBER_00 = '# ##0.00';
const FORMAT_PRICE = '# ##0 руб\.';
const FORMAT_PRICE_0 = '# ##0.0 руб\.';
const FORMAT_PRICE_00 = '# ##0.00 руб\.';
public function __construct() {
parent::__construct();
$this->obSpreadsheet = new PS\Spreadsheet();
$this->obSpreadsheet->setActiveSheetIndex(0);
$this->obSheet = $this->obSpreadsheet->getActiveSheet();
}
public function setCellValueByColumnAndRow($columnIndex, $row, $value) {
return $this->obSheet->setCellValueByColumnAndRow($columnIndex, $row, $value);
}
public function setDefaultColumnWidth($v) {
return $this->obSheet->getDefaultColumnDimension()->setWidth($v);
}
public function setDefaultRowHeight($v) {
return $this->obSheet->getDefaultRowDimension()->setRowHeight($v);
}
public function setColumnWidth($columnIndex, $v) {
$columnLetter = Coordinate::stringFromColumnIndex($columnIndex);
return $this->obSheet->getColumnDimension($columnLetter)->setWidth($v);
}
public function setRowHeight($row, $v) {
return $this->obSheet->getRowDimension($row)->setRowHeight($v);
}
public function setAlignmentHorizontal($pCoordinate, $v) {
return $this->obSheet->getStyle($pCoordinate)->getAlignment()->setHorizontal($v);
}
public function setAlignmentVertical($pCoordinate, $v) {
return $this->obSheet->getStyle($pCoordinate)->getAlignment()->setVertical($v);
}
public function setNumberFormat($pCoordinate, $v) {
return $this->obSheet->getStyle($pCoordinate)->getNumberFormat()->setFormatCode($v);
}
public function setAutoFilter($pCoordinate) {
return $this->obSheet->setAutoFilter($pCoordinate);
}
public function setStyleForHeader($pCoordinate) {
$this->obSheet->getStyle($pCoordinate)->applyFromArray([
'font' => [
'bold' => true,
],
'alignment' => [
'horizontal' => PS\Style\Alignment::HORIZONTAL_CENTER,
'vertical' => PS\Style\Alignment::VERTICAL_CENTER,
],
'borders' => [
'bottom' => [
'borderStyle' => PS\Style\Border::BORDER_THIN,
],
],
]);
}
public function setStyleForBody($pCoordinate) {
$this->setAlignmentVertical($pCoordinate, PS\Style\Alignment::VERTICAL_TOP);
}
public function addRows($arRows) {
if(!is_array($arRows)){
return false;
}
foreach ($arRows as $row => $arCells) {
$rowIndex = $row+1;
if(!is_array($arCells)){
continue;
}
foreach ($arCells as $col => $arValue) {
$columnIndex = $col+1;
$columnLetter = Coordinate::stringFromColumnIndex($columnIndex);
$pCoordinate = $columnLetter.$rowIndex;
if(!is_array($arValue)){
$this->obSheet->setCellValue($pCoordinate, $arValue);
continue;
}
if($this->addImage($columnLetter, $rowIndex, $arValue)){
continue;
}
$this->obSheet->setCellValue($pCoordinate, $arValue['value']);
if (!empty($arValue['format'])) {
$this->setNumberFormat($pCoordinate, $arValue['format']);
}
if (!empty($arValue['style'])) {
$this->obSheet->getStyle($pCoordinate)->applyFromArray($arValue['style']);
}
if(!empty($arValue['url'])){
$this->obSheet->getCell($pCoordinate)->getHyperlink()->setUrl($arValue['url']);
continue;
}
}
}
return true;
}
protected function addImage($columnLetter, $row, $arValue) {
if(!$arValue['isImage']){
return false;
}
if (empty($arValue['value'])) {
return false;
}
if(!array_key_exists('offsetX', $arValue)){
$arValue['offsetX'] = 5;
}
if(!array_key_exists('offsetY', $arValue)){
$arValue['offsetY'] = 5;
}
$drawing = new PS\Worksheet\Drawing();
$drawing->setPath($arValue['value']);
$drawing->setCoordinates($columnLetter.$row);
$drawing->setOffsetX($arValue['offsetX']);
$drawing->setOffsetY($arValue['offsetY']);
$drawing->setResizeProportional(true);
$arValue['width'] = floatval($arValue['width']);
$arValue['height'] = floatval($arValue['height']);
if($arValue['width'] > 0 && $arValue['height'] > 0){
$drawing->setWidthAndHeight($arValue['width'], $arValue['height']);
$this->setRowHeight($row, $arValue['height']);
}elseif($arValue['height'] > 0){
$drawing->setHeight($arValue['height']);
$this->setRowHeight($row, $arValue['height']);
}elseif($arValue['width'] > 0){
$drawing->setWidth($arValue['width']);
}
$drawing->setWorksheet($this->obSheet);
return true;
}
public function saveToFile($pFilename) {
$this->obSheet->setSelectedCell('A1');
$this->obSpreadsheet->setActiveSheetIndex(0);
$writer = PS\IOFactory::createWriter($this->obSpreadsheet, 'Xlsx');
$writer->save($pFilename);
}
public function saveToBrowser($fileBaseName = 'excel') {
$GLOBALS['APPLICATION']->RestartBuffer();
$fileBaseName = filter_var($fileBaseName, FILTER_VALIDATE_REGEXP, ['options' => ['regexp' => '/^[-_#.A-z0-9]+$/mis']]);
if (empty($fileBaseName)) {
$fileBaseName = 'excel';
}
$this->obSheet->setSelectedCell('A1');
$this->obSpreadsheet->setActiveSheetIndex(0);
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="'.$fileBaseName.'.xlsx"');
header('Cache-Control: max-age=0');
$writer = PS\IOFactory::createWriter($this->obSpreadsheet, 'Xlsx');
$writer->setOffice2003Compatibility(true);
$writer->save('php://output');
exit;
}
}