Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Скрипт импорта XLSX в инфоблок. (Есть ещё что дорабатывать, но есть ли в этом смысл?) Зависит от https://github.com/nuovo/spreadsheet-reader
<?php
// Libraries
require_once($_SERVER['DOCUMENT_ROOT'] . '/bitrix/modules/main/include/prolog_before.php');
require_once('xlsx_reader/SpreadsheetReader.php');
// Statistics
$startTime = microtime(true);
echo 'Start Memory usage ' . (memory_get_usage(true) / 1024) . ' K byte';
echo '<br>---------------------------------<br><br>';
// Script Settings
$file = 'rasvero.xlsx';
$debug = false;
$tick = 0;
$fails = 0;
$before = 0;
$exist = 0;
$deleted = 0;
if ($debug)
echo "<span style='color: red;'>DEBUG MODE (DryRun)</span><br><br>";
// BX Settings
\Bitrix\Main\Loader::includeModule('iblock');
define('CATALOG_IBLOCK_ID', 1); // ID Инфоблока
// Связи смещения и колонки. Копия из .xlsx
define('COL_NUM_NUMBER', 0); // № п/п
define('COL_NUM_PODRYAD', 1); // Подрядчик
define('COL_NUM_REGION', 2); // Регион
define('COL_NUM_FORMULA_PHOTO', 3); // Фото
define('COL_NUM_FORMULA_MAP', 4); // Карта
define('COL_NUM_KODCONST', 5); // Код конструкции
define('COL_NUM_ST', 6); // Ст
define('COL_NUM_ADDRESS', 7); // Адрес
define('COL_NUM_NAPRAVLENIE', 8); // Направление
define('COL_NUM_OKRUG', 9); // Округ
define('COL_NUM_FORMAT', 10); // Формат
define('COL_NUM_TYPE', 11); // Тип конструкции
define('COL_NUM_TYPE_SURFACE', 12); // Тип поверхности
define('COL_NUM_LIGHT', 13); // Свет
define('COL_NUM_ESPAR_ID', 14); // Espar ID
define('COL_NUM_GRP', 15); // GRP
define('COL_NUM_OTS', 16); // OTS
define('COL_NUM_MATERIAL', 17); // Материал
// Extracting formula
function extractHyperlink($formula) {
if (!$formula) {
return $formula;
}
$value = $formula;
$value = str_replace('HYPERLINK(', '', $value);
$value = str_replace(')', '', $value);
$value = str_replace('"', '', $value);
$value = explode(',', $value);
return $value[0];
}
/* Iblock Functions */
function getAllElementsFromIBlock($iblockID) {
$rsIblocks = CIBlockElement::GetList(['SORT' => 'ASC'], ['IBLOCK_ID' => $iblockID], false, false, ['ID', 'IBLOCK_ID', '*', 'PROPERTY_*']);
$arElements = [];
while ($arElement = $rsIblocks->GetNext()) {
$arElement['REMOVE'] = true;
$arElements[$arElement['XML_ID']] = $arElement;
}
return $arElements;
}
function addToIBlock($arFields) {
$iblockElement = new CIBlockElement();
return $iblockElement->Add($arFields);
}
function removeFromIBlock($itemID) {
return CIBlockElement::Delete($itemID);
}
function updateFromIBlock($itemID, $arFields) {
}
// Read XLSX File
$Reader = new SpreadsheetReader($file);
$AllElements = getAllElementsFromIBlock(CATALOG_IBLOCK_ID);
$before = count($AllElements);
foreach ($Reader as $row) {
if (!is_int($row[COL_NUM_NUMBER]))
continue;
// Format Array to CIBlockElement
$name = $row[COL_NUM_ADDRESS] . ' (№ ' . $row[COL_NUM_KODCONST] . ' ст ' . $row[COL_NUM_ST] . ')';
$code = 'NUM_' . $row[COL_NUM_NUMBER];
$map = extractHyperlink($row[COL_NUM_FORMULA_MAP]);
$photo = extractHyperlink($row[COL_NUM_FORMULA_PHOTO]);
$arProps = [
'NUMBER' => $row[COL_NUM_NUMBER],
'PODRYAD' => $row[COL_NUM_PODRYAD],
'REGION' => $row[COL_NUM_REGION],
'PHOTO' => $photo,
'MAP' => $map,
'KODCONST' => $row[COL_NUM_KODCONST],
'ST' => $row[COL_NUM_ST],
'ADDRESS' => $row[COL_NUM_ADDRESS],
'NAPRAVLENIE' => $row[COL_NUM_NAPRAVLENIE],
'OKRUG' => $row[COL_NUM_OKRUG],
'FORMAT' => $row[COL_NUM_FORMAT],
'TYPE' => $row[COL_NUM_TYPE],
'TYPE_SURFACE' => $row[COL_NUM_TYPE_SURFACE],
'LIGHT' => $row[COL_NUM_LIGHT],
'ESPAR_ID' => $row[COL_NUM_ESPAR_ID],
'GRP' => $row[COL_NUM_GRP],
'OTS' => $row[COL_NUM_OTS],
'MATERIAL' => $row[COL_NUM_MATERIAL]
];
$arFields = [
'IBLOCK_ID' => CATALOG_IBLOCK_ID,
'PROPERTY_VALUES' => $arProps,
'XML_ID' => $code,
'NAME' => $name,
'ACTIVE' => 'Y',
];
$isNew = false;
// Если элемент с таким XML_ID уже есть - обновим, иначе новый.
if (empty($AllElements[$code])) {
$isNew = true;
} else {
// Не удаляем, а обновляем
$AllElements[$code]['REMOVE'] = false;
}
if (!$isNew) {
// Если элемент не новый, обновим
if (!$debug) {
updateFromIBlock($itemID, $arFields);
}
$exist++;
} else {
// Элемент новый. Добавим
if (!$debug) {
$status = addToIBlock($arFields);
} else {
$status = true;
}
if ($status) {
$tick++;
} else {
$fails++;
}
}
// var_dump first row
if (($tick) === 0) {
echo "<b>First row:</b> <br>";
var_dump($arFields);
echo "<br>";
}
}
// Удаляем элементы которых нет в выгрузке.
foreach ($AllElements as $key => $arElement) {
if ($arElement['REMOVE']) {
removeFromIBlock($arElement['ID']);
$deleted++;
}
}
// Statistics
echo '<br>---------------------------------';
echo '<br> Before upload rows: ', $before;
echo '<br> Exist rows: ', $exist;
echo '<br> Uploaded rows: ', $tick;
echo '<br> Fails rows: ', $fails;
echo '<br> Deleted rows: ', $deleted;
echo '<br> Work ' . (microtime(true) - $startTime) . ' sec';
echo '<br> Memory usage ' . (memory_get_usage(true) / 1024) . ' K byte';
require_once($_SERVER['DOCUMENT_ROOT'] . '/bitrix/modules/main/include/epilog_after.php');
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.