[Excel export] экспорт через phpoffice #bitrix#export#classes#excel

excel.php

<?php

/**
 * User: Feiron
 * Date: 15.05.2020
 */

namespace Fei\Core\Export;

use PhpOffice\PhpSpreadsheet;

class Excel
{
	static $strClassPath = '/local/php_interface/include/vendor/autoload.php';

	public $arHeaders = [];
	public $arData    = [];

	private $obExcel = null;

	/**
	 * Excel constructor.
	 *
	 * @param array $arHeaders
	 * @param array $arData
	 * @param array $arOptions
	 *
	 * @throws \Exception
	 */
	public function __construct(array $arData = [], array $arHeaders = [], array $arOptions = [])
	{

		if($arOptions['STR_CLASS_PATH']) {
			self::$strClassPath = $arOptions['STR_CLASS_PATH'];
		}

		/** @noinspection PhpIncludeInspection */
		require $_SERVER['DOCUMENT_ROOT'] . self::$strClassPath;

		try {

			$this->obExcel   = new PhpSpreadsheet\Spreadsheet();
			$this->arHeaders = empty($arHeaders) ?
				// ID => ID
				array_combine(array_keys($arData[0]), array_keys($arData[0])) :
				$arHeaders;
			$this->arData    = is_array($arData) ? $arData : [$arData];

		} catch (\Exception $e) {
			throw $e;
		}
	}

	public function Export($strFileName = 'export.xlsx')
	{
		try {

			$this->addHeaders($this->arHeaders);
			$this->addRows($this->arData);

			$GLOBALS['APPLICATION']->RestartBuffer();

			header('Content-type: application/vnd.ms-excel');
			header('Content-Disposition: attachment; filename="' . trim($strFileName));

			$objWriter = new PhpSpreadsheet\Writer\Xlsx($this->obExcel);
			$objWriter->save('php://output');

		} catch (\Exception $e) {
			throw $e;
		}

	}

	/**
	 * @param $arData
	 *
	 * @throws PhpSpreadsheet\Exception
	 */
	public function addRows($arData)
	{
		foreach ($arData as $iRow => $arDatum) {

			foreach (array_keys($this->arHeaders) as $iCol => $strHeader) {

				/*
				 * + 1 iCol потому что начинаем с 0
				 * + 2 iRow потому что начинаем со 2 строки ( если отключить хедеры надо убрать)
				 */
				$strType = false;
				if (is_array($arDatum[$strHeader])) {
					list($val, $strType) = $arDatum[$strHeader];
				} else {
					$val = $arDatum[$strHeader];
				}
				$this->addRow($iCol + 1, $iRow + 2, $val, $strType);
			}
		}
	}


	/**
	 * @param int    $iCol
	 * @param int    $iRow
	 * @param string $val
	 * @param bool   $strType
	 *
	 * @throws PhpSpreadsheet\Exception
	 */
	public function addRow($iCol = 1, $iRow = 2, $val = "", $strType = false)
	{
		$val = self::escape($val);

		if ($strType) {
			$this->obExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow($iCol, $iRow, self::escape($val), $strType);
		} else {
			$this->obExcel->getActiveSheet()->setCellValueByColumnAndRow($iCol, $iRow, self::escape($val));
		}
	}

	/**
	 * @param $val
	 *
	 * @return string
	 */
	public static function escape($val)
	{
		switch (true) {
			case stristr($val, '>'):
				$val = HTMLToTxt($val);
				break;
		}

		return trim($val);
	}

	/**
	 * @param $arHeaders
	 *
	 * @throws PhpSpreadsheet\Exception
	 */
	public function addHeaders($arHeaders)
	{
		foreach (array_values($arHeaders) as $key => $strHeader) {
			$this->addRow($key + 1, 1, $strHeader);
		}
	}
}