Last active
October 13, 2017 09:27
-
-
Save EvilWolf/eb5353436d342dacd874eec3cdbc60c2 to your computer and use it in GitHub Desktop.
Скрипт импорта XLSX в инфоблок. (Есть ещё что дорабатывать, но есть ли в этом смысл?) Зависит от https://github.com/nuovo/spreadsheet-reader
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?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