Работа с excel битрикс

Зачем Excel

Иногда при разработке сайта на Битриксе возникает необходимость генерировать файлы Excel. Лично мне этот формат (т.е. родные форматы Excel) не нравится. Но менеджерам, зачастую, это ближе к сердцу, чем CSV. Как показывает практика, офисные сотрудники не всегда могут совладать с юникодом при открытии файлов CSV через MS Excel (вот, пора уже переходить на открытые аналоги, типа LibreOffice). Для таких вот запущенных случаев, нужно генерировать родные форматы Excel.

Из PHP генерацию фалов Excel можно делать с помощью распространённой библиотеки PHPExcel. Говорят, что эта библиотека не подходит для генерации больших и сложных документов. Но мне нужно было делать совсем маленькие Excel документы с простой таблицей и особым именем листа. Кстати, если вам не нужно давать листам названия, то можно поступить проще, — так же, как это делает сам Битрикс, — генерировать Excel документ через HTML. Но я использовал старую версию библиотеки PHPexcel — https://github.com/PHPOffice/PHPExcel, т.к. мне нужно было настроить работу на PHP 5.5. Вообще же лучше последовать рекомендациям разработчиков и использовать более новый вариант этой библиотеки — https://github.com/PHPOffice/PhpSpreadsheet

Битрикс требует настройки mbstring.func_overload=2 и не работает с другими значениями. PHPExcel требует mbstring.func_overload=0 и не работает с другими значениями. Если на сервере используется Apache, то есть вариант настроить этот параметр в целом для хоста и задать отдельное значение для определённого каталога. Если у вас Apache, то дальше читать вам не обязателно. Но при использовании nginx + php-fpm задать mbstring.func_overload можно только для всего пула, без каких-либо исключений. Так как же быть в этом случае? При определённом стечении обстоятельств, PHPExcel нормально работает и с mbstring.func_overload=2. Проверено, что генерация работает успешно при настройке сайта на работу с UTF-8 и использовании XLSX формата. Нужно лишь отключить встроенную проверку mbstring.func_overload в библиотеке PHPExcel.

Отключаем проверку mbstring.func_overload

Для установки библиотек в Битриксе удобно использовать composer. Если composer установлен глобально, то можно выполнить команду установки:

composer require phpoffice/phpexcel

Я, например, это делал из каталога bitrix/php_interface/include/lib
В моём случае файл, в котором происходит проверка значения mbstring.func_overload — это bitrix/php_interface/include/lib/vendor/phpoffice/phpexcel/Classes/PHPExcel/Autoloader.php
В этом скрипте есть такие строки:

// check mbstring.func_overload
if (ini_get('mbstring.func_overload') & 2) {
    throw new PHPExcel_Exception('Multibyte function overloading in PHP must be disabled for string functions (2).');
}

Если эти строки удалить, то проверка значения mbstring.func_overload производиться не будет. Но, если это сделать простам редактированием файла, то при следующем обновлении библиотек через composer update велика вероятность, что обновится и PHPExcel, и это изменение затрётся. Тогда придётся повторять удаление ещё раз. Чтобы немного упростить себе жизнь, автоматизируем это удаление.
Composer позволяет выполнять определённые скрипты по событиям. Нам нужно определить выполнение нашего патча на событие установки (для внедрения) и на событие обновления. В моём случае, в каталоге bitrix/php_interface/include/lib лежит файл composer.json следующего содержимого:

{
    "require": {
        "phpoffice/phpexcel": "^1.8" 
   },
    "config": {
        "bin-dir": "vendor/bin"
    },
    "scripts": {
        "post-install-cmd": [
            "phpexcel_mbstring_patch.sh"
        ],
        "post-update-cmd": [
            "phpexcel_mbstring_patch.sh"
        ]
    }
}

Тут в секции «require» задана установка библиотеки PHPExcel. В секции
«config» задан каталог для размещения дополнительных скриптов (куда мы и разместим свой патч). В секции «scripts» определены на какие события, какие скрипты выполнять, — в нашем случае это один и тот же патч для установки и для обновления пакетов через composer.
Создаём скрипт-патч bitrix/php_interface/include/lib/vendor/bin/phpexcel_mbstring_patch.sh со следующим содержимым:

#!/bin/bash

# Change directory to the script's location
cd $(dirname $(readlink -e $0))

file4Patch="../phpoffice/phpexcel/Classes/PHPExcel/Autoloader.php"
fileTmp="../phpoffice/phpexcel/Classes/PHPExcel/Autoloader.php.tmp"

# Remove this code:
# // check mbstring.func_overload
# if (ini_get('mbstring.func_overload') & 2) {
#     throw new PHPExcel_Exception('Multibyte function overloading in PHP must be disabled for string functions (2).');
# }
cat $file4Patch | awk -v p=1 '/mbstring/ {p=0} p {print $0} /}/ {p=1}' > $fileTmp
mv -v $fileTmp $file4Patch

Поясню, что тут происходит. Это bash скрипт. Сначала определяется рабочий каталог (тот, откуда запускается патч). Затем в две переменные задаём файл, который будем патчить и временный файл, который нужен для обработки (он удаляется в конце). А дальше происходит сама магия. С помощью утилиты awk удаляется всё содержимое между строками (включая сами эти строки), содержащими «mbstring» и «}». Это как раз проверка, которая нам не нужна в PHPExcel (что и обозначено в комментарии перед магической командой). Далее просто временным файлом (в котором произведено удаление строк) подменяется исходный файл.
Это будет работать при каждом обновлении пакетов через composer до тех пор, пока в PHPExcel сохраняется такая проверка mbstring.func_overload.

Бонус

В качестве бонуса в статье приведу код функции, который генерирует XLSX файл с особым названием листа. Первая строка документа — названия колонок. Все последующие строки — данные колонок.

/**
 * Метод формирует файл Excel из массива данных
 * @param string $fileFullName Полный путь к создаваемому файлу
 * @param string $fileTitle Заголовок файла
 * @param array $arData Массив данных для записи в файл. Массив из двух вложенных массивов:
 *     первая позиция с ключём "HEADER"
 *     вторая позиция с ключём "ROWS"
 * @return mixed Возвращает сформированный файл (его полный путь) либо ЛОЖЬ в случае ошибки
 *
 */
public function arrayToExcel($fileFullName, $fileTitle, $arData)
{
    if (!empty($fileFullName) && is_array($arData)) {
        try {
            $objPHPExcel = new PHPExcel();
            // Set document properties
            $objPHPExcel->getProperties()->setCreator('Site's script')
                                         ->setLastModifiedBy('Site's script')
                                         ->setTitle($fileTitle)
                                         ->setSubject($fileTitle);
            // Add some data
            foreach ($arData as $dataType => $arDataset) {
                if ($dataType == 'HEADER') {
                    $arColumnValType = [];
                    foreach ($arDataset as $keyHeader => $valueHeader) {
                        if (isset($valueHeader['NAME'])) {
                            $objPHPExcel->setActiveSheetIndex(0)->setCellValueByColumnAndRow(
                                $keyHeader,
                                1,
                                $valueHeader['NAME']
                            );
                            if (isset($valueHeader['TYPE'])) {
                                $arColumnValType[$keyHeader] = $valueHeader['TYPE'];
                            } else {
                                $arColumnValType[$keyHeader] = PHPExcel_Cell_DataType::TYPE_STRING2;
                            }
                        }
                    }
                } elseif ($dataType == 'ROWS') {
                    foreach ($arDataset as $indexRow => $row) {
                        if (count($row) == count($arData['HEADER'])) {
                            foreach ($row as $indexColumn => $value) {
                                $objPHPExcel->setActiveSheetIndex(0)->setCellValueExplicitByColumnAndRow(
                                    $indexColumn,
                                    ($indexRow + 2),
                                    $value,
                                    $arColumnValType[$indexColumn]
                                );
                            }
                        } else {
                            AddMessage2Log('Переданы данные с ошибкой: не совпадает количество столбцов заголовка и количество столбцов данных в строке #' . $indexRow);
                        }
                    }
                }
            }

            // Установим выравнивание ячеек по ширине содержимого
            for ($i = 0; $i <= count($arData['HEADER']); $i++) {
                $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($i)->setAutoSize(true);
            }
                
            // Rename worksheet
            $objPHPExcel->getActiveSheet()->setTitle('MyNameOfList');
            // Set active sheet index to the first sheet, so Excel opens this as the first sheet
            $objPHPExcel->setActiveSheetIndex(0);
            $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
            $objWriter->save($fileFullName);

            return $fileFullName;
        } catch(Exception $e) {
            AddMessage2Log('Не удалось сформировать файл Excel');
        }
    }
}

Формируются данные таким образом:

// Формируем табличный файл со списком товаров для менеджера
$arItems = self::getOffers(null, $saveId, null);
// Заголовки столбцов
$arItemsData['HEADER'] = [
    [
        'NAME' => 'Артикул',
        'TYPE' => PHPExcel_Cell_DataType::TYPE_STRING2
    ],
    [
        'NAME' => 'Наименование',
        'TYPE' => PHPExcel_Cell_DataType::TYPE_STRING2
    ],
    [
        'NAME' => 'Количество',
        'TYPE' => PHPExcel_Cell_DataType::TYPE_NUMERIC
    ],
    [
        'NAME' => 'Единица',
        'TYPE' => PHPExcel_Cell_DataType::TYPE_STRING2
    ],
    [
        'NAME' => 'Цена',
        'TYPE' => PHPExcel_Cell_DataType::TYPE_NUMERIC
    ],
    [
        'NAME' => 'Состояние',
        'TYPE' => PHPExcel_Cell_DataType::TYPE_STRING2
    ]
];
// Строки для табличного файла
foreach ($arItems as $prodId => $arItem) {
    $arItemsData['ROWS'][] = [
        $arItem['EXTERNAL_ID'],
        html_entity_decode($arItem['NAME']),
        $arItem['LIST']['COUNT'],
        $arItem['PROPERTIES']['UNITS']['VALUE'],
        $arItem['PRICES']['VIEW'],
        '' // TODO состояние
    ];
}

// Формируем полный путь к файлу
$uploadDir = '/upload/wishlist_excel';
if (!is_dir($_SERVER["DOCUMENT_ROOT"] . $uploadDir)) {
    mkdir($_SERVER["DOCUMENT_ROOT"] . $uploadDir, 0664);
}
$fileTitle = 'смета#' . $saveId;
$fileName = $fileTitle . '.xlsx';
$fileFullName = $_SERVER["DOCUMENT_ROOT"] . $uploadDir . '/' . $fileName;

$excelFile = arrayToExcel($fileFullName, $fileTitle, $arItemsData);

<?php

ini_set(‘display_errors’, 1);

ini_set(‘error_reporting’, E_ALL);

if (ini_get(‘mbstring.func_overload’) & 2) {

    $PHPEXCELPATH =  «lib/PHPExcel/Classes_overload2»;

} else {

    $PHPEXCELPATH =  «lib/PHPExcel/Classes_overload0»;

}

//require_once ‘Classes/PHPExcel.php’;

// Подключаем класс для работы с excel

require_once($PHPEXCELPATH.‘/PHPExcel.php’);

// Подключаем класс для вывода данных в формате excel

require_once($PHPEXCELPATH.‘/PHPExcel/Writer/Excel5.php’);

// Создаем объект класса PHPExcel

$xls = new PHPExcel();

// Устанавливаем индекс активного листа

$xls->setActiveSheetIndex(0);

// Получаем активный лист

$sheet = $xls->getActiveSheet();

// Подписываем лист

$sheet->setTitle(‘Таблица умножения’);

// Вставляем текст в ячейку A1

$sheet->setCellValue(«A1», ‘Таблица умножения mbstring.func_overload=’. ini_get(‘mbstring.func_overload’));

$sheet->getStyle(‘A1’)->getFill()->setFillType(

    PHPExcel_Style_Fill::FILL_SOLID);

$sheet->getStyle(‘A1’)->getFill()->getStartColor()->setRGB(‘EEEEEE’);

// Объединяем ячейки

$sheet->mergeCells(‘A1:H1’);

// Выравнивание текста

$sheet->getStyle(‘A1’)->getAlignment()->setHorizontal(

    PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

for ($i = 2; $i < 10; $i++) {

    for ($j = 2; $j < 10; $j++) {

        // Выводим таблицу умножения

        $sheet->setCellValueByColumnAndRow(

            $i — 2,

            $j,

            $i . «x» .$j . «=» . ($i*$j));

        // Применяем выравнивание

        $sheet->getStyleByColumnAndRow($i — 2, $j)->getAlignment()->

        setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

    }

}

// Выводим HTTP-заголовки

header ( «Expires: Mon, 1 Apr 1974 05:00:00 GMT» );

header ( «Last-Modified: « . gmdate(«D,d M YH:i:s») . » GMT» );

header ( «Cache-Control: no-cache, must-revalidate» );

header ( «Pragma: no-cache» );

header ( «Content-type: application/vnd.ms-excel» );

header ( «Content-Disposition: attachment; filename=matrix.xls» );

// Выводим содержимое файла

$objWriter = new PHPExcel_Writer_Excel5($xls);

$objWriter->save(‘php://output’);

Ранее в одном из наших КП добавление задач обрабатывалось бизнес-процессами, сейчас задач стало столько, что бизнес-процессы стали неуместны, и понадобился инструмент для массовой заливки задач на КП.

Итак, есть xlsx файл со списком задач. Из него можно узнать

• TITLE
• DESCRIPTION
• RESPONSIBLE_ID
• CREATED_BY
• START_DATE_PLAN
• END_DATE_PLAN

а также можно вычислить GROUP_ID и несколько свойств, которые были добавлены к задачам.

Сегодня:
— загружаем файл без перезагрузки страницы
— обрабатываем xlsx файл с помощью 

сторонней библиотеки

— добавляем свои свойства к задачам
— добавляем задачи в базу КП по конкретным группам

FormData
Я уже где-то упоминала что не помню как работать с обычными формами, поэтому всё делаю на аджаксе. У меня так быстрее получается. Самой большой проблемой тут виделось скачивание файла и отправка его серверу без перезагрузки страницы. Недолгое гугление выдало мне FormData, что и было использовано.
Да, это не поддерживается «неновыми» браузерами, но у меня не стояло задачи написать универсальный инструмент для всех.
Итак, пишем обработчик клика по кнопочке, который и будет запускать всё действо.

$(document).ready(function()
{
 $('#btn_send_form').click(function(event)
 {
  //b_PS - это айдишка поля для показа статуса
  $("#b_PS").empty().append('<img src="loading.gif">'); //это "чтобы крутилось" пока скрипт выполняется
 
  var fd = new FormData();  //то что нам надо!
  //PS - это айдишка инпута типа файл, от которого и берём инфу
  fd.append('file', $('#PS')[0].files[0]); //добавляем туда нужные поля - файл
  fd.append('action', 'import_tasks'); //и поле для указания аджакс скрипту что делать то )
 
  $.ajax({
   url : 'ajax_import.php',
   type: 'POST',
   data : fd,
   processData: false, //вот это облизательно!
   contentType: false,
   success:function(data, textStatus, jqXHR){
    $("#b_PS").empty().append(data); //чистим крутилку и добавляем ответ от обработчика
   }
  });
 
  event.preventDefault();  //на всякий случай
 
 });
});
//ну и добавляем строчечку для неандертальцев
if(!window.FormData)
{
 document.write("<span style="color:red">Ваш браузер не поддерживается этим скриптом</span>");
  

XLSXReader
Эта замечательная библиотечка позволяет нам считывать содержимое xlsx файла. Даже со всех содержащихся там листов. Так как всяких извращений типа графиков и формул задачи не содержат, то спокойно используем этого клопа для нашей работы.
Для дальнейшего использования была накарябана функция, которая на вход получает путь к файлу и возвращает нам массив с построчными данными, содержащимися в импортируемом файле. Так как первая строка содержит названия столбцов, то было решено привести массив в ассоциативный вид, где индексами будут названия столбцов, а значениями — соответственно построчное содержимое.

Замечание: даты в xlsx содержатся в каком-то своём формате, соответственно надо их переработать в человеческий вид. Библиотека XLSXReader предоставляет для этого метод, который и используется.
* string $filename — полный путь к файлу
* array $date_indexes — массив с индексами столбцов, где содержится дата (см. код)

function read_xlsx($filename, $date_indexes = array())
{
 //начинаем обработку икселя
 $xlsx = new XLSXReader($filename);
 //имена листов (мы юзаем например первый)
 $sheetNames = $xlsx->getSheetNames();
 //полная инфа с листа
 $sheetData = $xlsx->getSheetData($sheetNames[1]);
 
 //перерабатываем инфу в нужный вид
 $arData = array();
 $first_row = array();
 foreach($sheetData as $key=>$row)
 {
  //запоминаем первую строку и будем использовать её как индексы
  if (!count($first_row))
  {
   $first_row = $row;
   continue;
  }
 
  //формируем нужную строку-массив
  $arrow = array();
  foreach($row as $rowkey=>$value)
  {
   $index = $first_row[$rowkey];
   //если заданы индексы для дат, то преобразовываем их из формата 
   //    экселя в формат человеческой даты
   if (in_array($rowkey, $date_indexes) && is_numeric($value))
    $value = date('d.m.Y', XLSXReader::toUnixTimeStamp($value));
 
   $arrow[$index] = $value;
  }
  $arData[$key] = $arrow;
 }
 
 return $arData;
}
 

Собственно, в скрипте обработчике фукнция вызывается вот так:

$file_array = read_xlsx($_FILES['file']['tmp_name']);
 

Пользовательские свойства задач
Идём Настройки — Настройки продукта — Пользовательские поля
Добавляем новое свойство. Для того, чтобы оно принадлежало задаче надо задать поле Объект в TASKS_TASK, например вот так

Таким образом это поле будет принадлежать задаче и спрашиваться при её добавлении и, если заполнено, то отображаться при просмотре.

Группа задачи
Для того, чтобы в списке задач была сортировка по группам, к задаче надо добавлять группу. Это делается при добавлении задачи указанием поля GROUP_ID
Но у нас в документе были указаны только названия групп, без айдишек. Дабы не добавлять работы операторам по поиску нужных айдишек групп сделаем это сами. А если группа по имени не найдена, пропускаем эту строку и записываем в отчёт как ошибку.

Для этого сначала считываем все группы, которые существуют в портале. А затем по имени находим айдишку. Собственно всё =)

 //собираем группы
if (!CModule::IncludeModule("socialnetwork"))
 die('<b style="color:red">module socialnetwork problem</b>');
 
$arGroups = array();
$hGroups = CSocNetGroup::GetList(
 array("ID" => "DESC"),
 array('ACTIVE'=>'Y'),
 false,
 false,
 array('ID', 'NAME')
);
while($row = $hGroups->Fetch())
 $arGroups[$row['NAME']] = $row['ID'];

Добавление задачи
Использую старый класс CTasks, ибо новый у меня так и не взлетел. Может, у меня руки кривые, а может я слишком ленивая чтобы разбираться.
Собираем поля, нужные для создания задачи:

• RESPONSIBLE_ID — айдишка ответственного
• CREATED_BY — айдишка постановщика задачи
• UF_TASK_TP_ADRES — наше пользовательское поле. у нас тут адрес.
• GROUP_ID — айдишка соц.группы
• TITLE — название задачи
• DESCRIPTION — описание задачи
• START_DATE_PLAN — дата начала
• END_DATE_PLAN — дата окончания

 if (!CModule::IncludeModule("tasks"))
 die('<b style="color:red">module tasks problem</b>');
$ctio = new CTasks;
//создаём задачу
$task_ID = $ctio->Add($arTaskFields);

Добавляем немного кода с обработками ошибок и — готово!

Для создания документа Excel на основании шаблона с динамическими данными из инфоблока bitrix используем библиотеку PHPExcel

1. Скачать библиотеку можно тут: https://github.com/PHPOffice/PHPExcel

2. При использовании данной библиотеки в CMS битрикс возможны ошибки связанные с mbstring.func_overload, что бы их избежать нужно закомментировать следующие строчки в файле PhpExcelAutoloader.php

   if (ini_get(‘mbstring.func_overload’) & 2) {
   throw new PHPExcel_Exception(‘Multibyte function overloading in PHP must be disabled for string functions (2).’);
   }

3. Подключение библиотеки к файлу

   include_once ‘PHPExcel.php’;

4. Основные функции для работы с файлом

   $objPHPExcel = new PHPExcel(); //создание объекта excel
   $objReader = PHPExcel_IOFactory::createReader(‘Excel2007’); //присвоение типа файла шаблона
   $objPHPExcel = $objReader->load(‘template.xlsx’); //загрузка шаблона
   $objPHPExcel->setActiveSheetIndex(0); //Присвоение активности листа
   $sheet = $objPHPExcel->getActiveSheet();
   $sheet->setCellValue(«B1», ‘TEST’); //Добавление текста в ячейку
   $sheet->NewRowBefore(«B2», 1); //Добавление новой строки
   $sheet->mergeCells(«B1″:»C1»); //Объединение ячеек
   $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, ‘Excel2007’);
   $objWriter->save(«test.xlsx»); //сохранение шаблона со всеми изменениями в новый файл

5. Важно! При создании шаблона поставить совместимость с предыдущими версиями

6. Заполнение данными шаблона.

   Шаблон Exсel файла:

   

   Пример:

   include_once ‘PHPExcel.php’;
   $objPHPExcel = new PHPExcel();
   $objReader = PHPExcel_IOFactory::createReader(‘Excel2007’);
   $objPHPExcel = $objReader->load(‘template.xlsx’);
   $objPHPExcel->setActiveSheetIndex(0);
   $sheet = $objPHPExcel->getActiveSheet();
   $sheet->setCellValue(«A2», ‘Товар1’);
   $sheet->setCellValue(«B2», ‘1’);
   $sheet->setCellValue(«C2», ‘200’);
   $sheet->NewRowBefore(«A2», 1);
   $sheet->setCellValue(«A3», ‘Товар2’);
   $sheet->setCellValue(«B3», ‘1’);
   $sheet->setCellValue(«C3», ‘500’);
   $sheet->mergeCells(«B4″:»C4»);
   $sheet->setCellValue(«B4», ‘700’);
   $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, ‘Excel2007’);
   $objWriter->save(«test.xlsx»);

   Результат:

   

О приложении

Приложение Импорт из Excel переносит данные из Excel-таблицы в CRM Битрикс24. 

Приложение подойдет, если вы:

• ведете базу клиентов и сделок в Excel или другой внешней системе и хотите переехать в Битрикс24;

• часто пополняете рабочую CRM новыми данными из внешних источников;

• только знакомитесь с Битрикс24 и хотите “поэкспериментировать” с импортом данных.   

Главные фишки:

• загрузка не по шаблону — в приложении нет стандарта для загружаемых файлов;

• контроль дубликатов — в CRM попадут только уникальные записи, а старые карточки обновятся;

• возможность откатиться — отмените последнюю загрузку и CRM вернется в состояние на начало загрузки;

• автоматическое распределение полей для загрузки данных по совпадающим названиям столбцов;

• сохранение распределения полей между импортами — при повторной загрузке документа поля распределяются также, как и при первой загрузке.

Как работать с приложением

Шаг 1. Выберите и загрузите Excel-файл

В приложении нет шаблона загрузки, так что предварительно обрабатывать файл не нужно. Сохраните базу клиентов на локальный компьютер в формате .xls/.xlsx или .csv одним листом и загрузите в приложение. 

Если в исходном Excel-файле есть пустые ячейки, нужно сделать следующие настройки в вашем Битрикс24: CRM -> Настройки -> Другое -> Прочие настройки -> Маркет -> «Проверять наличие обязательных пользовательских полей».

При первичной загрузке документа поля для данных будут автоматически распределены по совпадающим названиям. Далее при необходимости  можно распределить поля вручную.  

Шаг 2. Сопоставьте данные из файла с полями в CRM

Выберите сущности CRM, которые нужно создать или дополнить:

• лиды — все данные по клиенту попадут в одну карточку CRM;

• компании + контакты + сделки — данные по клиенту попадут в разные карточки: например, информация об организации пойдет в компании, а личные данные контактного лица — в контакты. При этом приложение автоматически свяжет между собой карточки, которые относятся к одному клиенту. Не обязательно создавать все три сущности одновременно. Выбирайте сочетание, исходя из потребностей бизнеса и содержания базы клиентов.

Затем сопоставьте данные из файла с полями в карточке CRM. Таблица сопоставления состоит из трех колонок:

• столбец в файле — названия заполненных столбцов в Excel-таблице из первой строки;

• поле в CRM — выпадающий список с полями, доступными для заполнения;

• пример данных — содержимое поля в карточке CRM. 

Для каждого столбца в файле выберите поле CRM, куда приложение запишет соответствующие данные.

Завершив настройку, перейдите на следующий шаг. 

Шаг 3. Загрузите данные

По умолчанию приложение автоматически определит дубли в файле и сохранит уникальные контакты без проверки уникальности на стороне CRM. Вы можете изменить это перед загрузкой, тогда  приложение найдет совпадения по ключевым полям в лидах, контактах и компаниях и обновит или дополнит данные в них. 

Во время загрузки не закрывайте и не обновляйте страницу в браузере, не выключайте компьютер. Приложение подскажет, сколько минут осталось до завершения обработки данных.

После загрузки. Проверьте данные в CRM

По окончании процесса проверьте загруженные данные в CRM При необходимости вы можете отменить последнюю выполненную загрузку в приложении, при этом  CRM вернется в состояние на начало загрузки. Не закрывайте и не обновляйте страницу с приложением во время работы с ним, иначе отменить загрузку будет невозможно.

На вкладке “История загрузок” доступна информация и логи предыдущих загрузок.

Часто задаваемые вопросы

Что делать, если на вашем портале настроено распределение Лидов по воронкам сразу?

1 вариант. В импортируемом файле создать столбец “направления” с названием воронки для каждого лида и при импорте соотнести с полем в CRM 

2 вариант. На 3 шаге импорта выбрать воронку по умолчанию 

Как записать добавочный номер телефона в одно поле с основным?

Используйте формат: Х-ХХХ-ХХХ-ХХ-ХХ #ХХХ, где последние три цифры — это добавочный номер.

Остались вопросы о том, как работает приложение? Пишите нам в чате, и мы с радостью на них ответим.

Зачастую возникает необходимость перенести данные из существующих таблиц Excel, Google Sheets и т.п. Для Лидов, Сделок, Контактов и Компаний предусмотрен штатный импорт, а в коробочной версии Битрикс24 это можно сделать внутренними инструментами через панель управления, а в облачной таких инструментов нет. И как быть если таблица абсолютно индивидуальна, например, реестр техники и оборудования?

Для начала давайте посмотрим на то, как хранятся такие таблицы в Битрикс24. Для этого используются универсальные списки, хранящиеся в группах. Если вы не знакомы с этим инструментом Битрикс24, то рекомендуем вам ознакомиться с ним https://helpdesk.bitrix24.ru/open/1428438/?sphrase_id=7138119
В двух словах — в универсальном списке вы определяете названия и тип данных полей (колонок таблицы) и в дальнейшем заполняете и редактируете таблицу через форму или через Бизнес-процесс (БП).

Благодаря возможности формировать строки такого списка через бизнес-процесс мы и проведем наш импорт. Для этого воспользуемся штатным импортом Сделок. Для сделок можно создавать пользовательские поля, которые мы можем использовать при импорте. Сами сделки нам не нужны, поэтому, чтобы не мешать работе сотрудников и не портить статистику в CRM для этих задач, создадим отдельное направление сделок, ограничив доступ к нему.

Шаг 1. Подготовка данных для импорта

Для начала нам нужно определиться с типами полей в имеющейся у нас таблице. Их не так много:

• Строка
• Список
• Ссылка
• Дата/время
• Да/Нет
• Число
• Привязка к пользователю
• Привязка к элементам CRM
• Адрес Google карты
• Бронирование ресурсов
• Файл
• Деньги
• Привязка к справочникам CRM
• Привязка к разделам инф. блоков
• Привязка к элементам инф. блоков

При такой процедуре лучше свести все данные к первым 8 типам. Таким образом, у нас должны появиться столько же доп. полей в карточке сделки (если мы не используем штатные поля сделки) сколько колонок есть в нашей таблице. Получится вот такая таблица необходимых преобразований:

При их создании нам нужно будет учесть следующие свойства типов данных. Далее речь пойдет о данных в исходном документе:

Строка, Текст — поле не ограничено традиционными 256 символами, поэтому в него можно передавать достаточно большой текст, который в результате на уровне списка мы сохраним в поле типа “Детальный текст”.

Список — если в работе с таблицей для какого-либо поля вы используете набор вариантов, то для импорта значения этого поля необходимо создать “Строку”, т.к. у облачного Битрикс24 есть ряд ограничений на работу со списками, т.е. значение строки нельзя “отдать” полю типа список. Самый простой способ — это в бизнес-процессе, отвечающем за импорт, описать условия, назначающие соответствующие значения полям.

Ссылка — список не поддерживает ссылки, поэтому передавать нужно как обычную строку.

Дата, дата/время — тут важно не ошибиться с форматом и включить учет времени при создании поля в сделке.

Да/Нет, чекбокс — штатного аналогичного типа поля в списке нет, поэтому тут самым правильным решением будет создание элемента типа «Список», назначение пункта описано выше. Но важно иметь в виду то, что теперь у вас будет 3 статуса (Да, нет, не выбран), а не 2 (да, нет).

Число — число везде остается числом, и в данном случае оно будет работать и с целыми, и с дробными числами.

Employee Сотрудник — Сотрудника (если он есть в Битрикс24) можно передать и в виде строки (Имя Фамилия), но тогда придется писать в БП условие для каждого, чтобы связать его c сотрудником. Мы предлагаем заменить имена сотрудников на их ID в портале еще в самой исходной таблице. А полученное число назначить полю.

Привязка к элементам CRM — для этого нам будут нужны уникальные поля, поэтому лучше использовать название компании или имя контакта и его e-mail или телефон.

Давайте теперь пройдемся по остальным шагам, которые необходимо сделать для такого импорта.

Шаг 2. Подготовка CRM к импорту

Со стороны CRM нам неоходимо выполнить 3 действия:

• Создаем направление для сделок, через которое пройдет импорт, чтобы не портить статистику и отчеты. Подробное описание вы можете найти здесь.
• Создаем пользовательские поля для сделок в настройках CRM. Подробное описание вы можете найти здесь.
• Формируем список в группе, в которой мы будем хранить полученные данные. Подробное описание вы можете найти здесь.

Шаг 3. Формируем бизнес-процесс отвечающий за импорт данных

На изображении выше приведена принципиальная схема бизнес-процесса. Основные шаги следующие:

• Ожидаем стадии сделки в созданном нами направлении.
• Формируем запись в списке.
• Обрабатываем преобразования, необходимые для работы с полями типа «Список»
• Дописываем необходимые значения в строку списка
• Завершаем работу со сделкой

Шаг 4. Процесс импорта

Скачиваем CSV шаблон для загрузки сделок. Процедура импорта подробно описана здесь.
При работе с ним не использовать Microsoft Excel т.к. он интерпретирует этот формат достаточно фривольно. У вас могут возникнуть сложности с его последующим открытием и дальнейшим импортом. Рекомендуем воспользоваться бесплатным Libre Office или другой программой.

Переносим данные, в таблицу — добавленные нами поля появились в ее конце. Не забывая заполнить направление сделки и ответственного.

Проводим импорт и переходим в группу со списком. В дальнейшем список может пополняться бизнес-процессом или управляться в ручном режиме.

В системе битрикс предусмотрено всего несколько видов стандартной загрузки и выгрузки данных. Первый из них это ИмпортЭкспорт Информационных блоков:

Именно инфоблоки являются основным инструментом, в которым мы храним данные. Этакая “часть базы данных” в удобном представлении.

При ИмпортЭкспорте доступны 2 формата:
1. CSV – это простейший формат, который можно открыть в экселе и увидеть наглядно структуру файла в виде таблицы (правда, иногда в сломанной кодировке, т.к. эта программа почему-то любит windows-1251). Если открыть в блокноте, этот формат имеет вид:

Артикул; Название товара; Цена (руб.)
123; Жвачка для рук; 1000
234; Шар желаний; 1500

В качестве разделителя выступает точка с запятой. С CSV приятно работать, и файлы в таком формате имеют наименьший вес – в нем просто нет ничего лишнего.

Правда есть один неприятный нюанс – В Битриксе вы не сможете адекватно выгрузить и затем загрузить данные о товаре, если делать это через Импорт Экспорт инфоблока. При этом варианте отсутствуют основные поля каталога, цены и количество, какой в этом смысл? Кроме того, ссылки для изображений указывают на папку /upload/, как вы поймете что вам копировать а что нет? Это можно исправить написав свой скрипт, но об этом позже.

2. XML – тут дела обстоят получше, когда речь заходит о более полной структуре данных. Тут вам и дерево из разделов, и все свойства из информационного блока, и даже цены. По сравнению с CSV форматом, все изображения собираются в отдельную папку, с таким же самым названием, которое мы давали файлу, что несомненно плюс.

Из частых проблем я бы выделил:
– сам формат сложнее в плане чтения и работы с ним (в случае если захотите что-то добавить через редактор)
– при выгрузке товары выгружаются в новый инфоблок (а должно загружаться в старый)

Как видите, если вы автоматом собираете XML выгрузку, например, из программы 1С, и после этого загружаете без проблем на свой сайт, то XML очень даже хороший формат. Но как показывает практика, это очень редкие случаи.

Какая выгрузка на проектах сделанных на 1С-Битрикс самая лучшая?

Выгрузка в формате CSV, XLSX или XLS для CMS 1С-Битрикс написанная вручную

По моему опыту, самый лучший вариант, это написать уникальный загрузчик. Почему?

1. Будет использован формат данных, к которому привыкли вы и ваши коллеги / партнеры. Например, если у вас сохраняется определенная последовательность полей – “Название, Артикул, Цена, Вес”, то в самом скрипте будет прописано “искать товары по Артикулу, если ничего не найдено искать по Названию, если товар найден поменять его Цену и Вес“.
2. При желании файл может загружать картинки из определенных папок и даже сторонних сайтов. Задавать картинки для товаров можно будет не только для полей “Картинка для анонса” и “Детальная картинка”, но и перезаписать часто используемое свойство типа Файл, с отметкой Множественное.
3. Можно будет задать более сложную логику загрузки. Например, у ваших товаров есть свойство бренд, и вы хотите чтобы всем товарам также отмечался раздел, с соответствующим брендом. Все ограничивается лишь поставленной задачей, в рамках более сложной логики загрузки можно учесть многие нюансы, которые применимы только в вашем бизнесе или проекте.

При разработке такого скрипта код пишется с нуля на языке PHP при использовании Bitrix API, и не содержит лишних строчек.

Форматы файлов могут быть любыми: CSV, XML, XLSX, XLS, YML, JSON и т.д.

Вас интересует данная услуга? Свяжитесь со мной, отвечу на все вопросы и помогу с реализацией.

Для примера отчет одного из таких загрузчиков, который видно сразу после выполнения скрипта:

Отчет помогает искать любые несоответствия в базе, исправлять ошибки.
Во время выполнения скрипта можно добавить прелоадер и разбить загрузку на шаги:

Также, есть вариант загрузки в битрикс с другой базы данных (при условии что к ней есть доступ). Плюс этого способа в том, что запросы к базе данных работают очень быстро.