Выгружаем контакты в ексель по департаментам

contacts-excel.php

<?
define('STOP_STATISTICS', true);
define('BX_SECURITY_SHOW_MESSAGE', true);
define("NOT_CHECK_PERMISSIONS", true);
$_SERVER['DOCUMENT_ROOT'] = '/home/bitrix/www';
require_once($_SERVER['DOCUMENT_ROOT'] . '/bitrix/modules/main/include/prolog_before.php');

use Bitrix\Crm\CompanyTable;
use Bitrix\Disk\Configuration;
use Bitrix\Disk\Driver;
use Bitrix\Disk\ExternalLink;
use Bitrix\Disk\File;
use Bitrix\Disk\Folder;
use Bitrix\Disk\Internals\Error\Error;
use Bitrix\Disk\Internals\ExternalLinkTable;
use Bitrix\Disk\Internals\ObjectTable;
use Bitrix\Disk\Version;
use Bitrix\Mail\Imap;
use Bitrix\Main\EventResult;
use Bitrix\Main\Localization\Loc;
use Bitrix\Main\Loader;
use GPW\C1Reports\C1CCustomerSettlements;
use GPW\C1Reports\C1CTracking;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;


\CModule::IncludeModule('crm');
\CModule::IncludeModule('gpw.1cdb');
\CModule::IncludeModule('gpw.payments');
\CModule::IncludeModule('gpw.rates');
\CModule::IncludeModule('rest');
\CModule::IncludeModule('intranet');
\CModule::IncludeModule('timeman');
require $_SERVER['DOCUMENT_ROOT'] . '/bitrix/php_interface/include/phpexcel/vendor/autoload.php';
set_time_limit(0);

\CModule::IncludeModule('crm');
\CModule::IncludeModule('gpw.1cdb');
\CModule::IncludeModule('gpw.payments');
\CModule::IncludeModule('rest');
\CModule::IncludeModule('intranet');

\CModule::IncludeModule('crm');
\CModule::IncludeModule('gpw.rates');
\CModule::IncludeModule('gpw.soapapi');
\CModule::IncludeModule('lol.asterisk');


$arAlsoMangers = [];
$arManagerIDS  = [];
$arData        = [];

$dbContacts = \Bitrix\Crm\ContactTable::getList(
	[
		'select' => ['*', 'ASSIGNED_BY_FULL_NAME' => 'ASSIGNED_BY.SHORT_NAME', 'TYPE_NAME' => 'TYPE_BY.NAME', 'SOURCE_NAME' => 'SOURCE_BY.NAME']
	]
);

while ($arContact = $dbContacts->fetch()) {

	$dbMultiFields = \CCrmFieldMulti::GetList(
		array(),
		array('ENTITY_ID' => CCrmOwnerType::ContactName, 'ELEMENT_ID' => $arContact['ID'])
	);
	$arMultiFields = array();
	while ($arMultiField = $dbMultiFields->Fetch()) {
		$arMultiFields[$arMultiField['TYPE_ID']][] = $arMultiField['VALUE'];
	}
	$arContact['PHONES'] = implode(';', $arMultiFields['PHONE']);
	$arContact['EMAILS'] = implode(';', $arMultiFields['EMAIL']);
	$arData[]            = $arContact;
}

$arHeaders = array(
	'ID'                    => 'ID',
	'DATE_CREATE'           => 'Дата создания',
	'TYPE_NAME'             => 'Тип',
	'POST'                  => 'Должность',
	'SOURCE_NAME'           => 'Источник',
	'SOURCE_DESCRIPTION'    => 'Описание источника',
	'NAME'                  => 'Имя',
	'LAST_NAME'             => 'Фамилия',
	'BIRTHDATE'             => 'Дата рождения',
	'ASSIGNED_BY_ID'        => 'Отвественный',
	'ASSIGNED_BY_FULL_NAME' => 'Отвественный (имя)',
	'EMAILS'                => 'Emails',
	'PHONES'                => 'Тел.',
	'COMPANY_TITLE'         => 'Компания',
	'COMPANY_ID'            => 'ID компании',
	'COMMENTS'              => 'Комментарий',
);

if (empty($arData)) {
	showError('EMPTY_CONTACT_DATA');
	die();
}

try {
	$obExcel = new Spreadsheet();
	foreach (array_values($arHeaders) as $key => $strHeader) {
		$obExcel->getActiveSheet()->setCellValueByColumnAndRow($key + 1, 1, $strHeader);
	}
	foreach ($arData as $iRowIndex => $arRowValues) {
		$iCellIndex = 1;
		foreach ($arHeaders as $code => $strHeaderName) {
			switch ($code) {
				case  'ASSIGNED_BY_ID':
					$val = $arRowValues['ASSIGNED_BY_NAME'] . ' ' . $arRowValues['ASSIGNED_BY_LAST_NAME'];
					break;
				case 'COMMENTS':
					$val = HTMLToTxt(htmlspecialchars_decode($arRowValues[$code]));
					$val = preg_replace('#\s#', " ", $val);
					break;
				default:
					$val = $arRowValues[$code] ? $arRowValues[$code] : '';
					$val = str_replace(['='], '', $val);
			}
			$obExcel->getActiveSheet()->setCellValueByColumnAndRow($iCellIndex, $iRowIndex + 2, $val);
			$iCellIndex++;
		}
	}
	$obExcel->getActiveSheet()->setTitle('contacts');
	$objWriter = new Xlsx($obExcel);
	$APPLICATION->RestartBuffer();
	header('Content-type: application/vnd.ms-excel');
	header('Content-Disposition: attachment; filename="' . date("m_d_Y") . '_' . 'contacts.xlsx"');
	$objWriter->save('php://output');
	die();
} catch (\Exception $e) {
	echo $e->getMessage();
}