Каждый разработчик и администратор 1С рано или поздно сталкивается с задачей интеграции данных из внешних источников. Одной из самых частых операций является перенос крупных таблиц (прайс-листов, справочников номенклатуры, адресных классификаторов или логов доставки) из файлов Microsoft Excel напрямую в табличный документ 1С. Однако при стандартном копировании выделенного диапазона (более 65 536 строк) через буфер обмена пользователи сталкиваются с неприятным ограничением: вставляется ровно 65 534 или 65 536 строк, а остальные данные бесследно теряются.
В этой статье мы подробно разберем, почему возникает это ограничение, рассмотрим простые интерактивные способы его обхода и изучим современные высокопроизводительные программные методы загрузки файлов с объемом данных свыше 100 000 строк.
Для понимания природы проблемы проанализируем поведение операционной системы Windows и программы Microsoft Excel при работе с буфером обмена. Когда вы выделяете строки в Excel и нажимаете комбинацию клавиш Ctrl + C, операционная система не просто копирует текст. Excel помещает данные в системный буфер обмена одновременно в нескольких десятках различных форматов. Среди них присутствуют HTML, XML Spreadsheet, обычный текст Unicode и двоичный формат BIFF8 (внутренний стандарт хранения данных Excel 97–2003).
Платформа 1С:Предприятие при обычном нажатии клавиш Ctrl + V в поле табличного документа стремится максимально точно сохранить исходное форматирование данных (шрифты, цвета ячеек, границы, объединения). Для этого 1С запрашивает из буфера обмена именно структурированный формат BIFF8. Но этот формат имеет жесткое архитектурное ограничение, унаследованное от старых версий Excel, — не более 65 536 строк. Сам Excel при формировании буфера обмена в формате BIFF8 принудительно обрезает всю таблицу по этому лимиту. В результате в табличный документ 1С «прилетает» усеченный массив данных.
Если вам или вашим пользователям необходимо разово перенести таблицу объемом 100 000 – 200 000 строк без программирования, самым простым решением будет использование текстового представления буфера обмена, которое не имеет ограничений по количеству строк.
Выполним следующие шаги:
Ctrl + V выберите в главном меню или контекстном меню команду «Специальная вставка».При таком подходе платформа 1С проигнорирует разметку BIFF8 и напрямую прочитает «сырой» текстовый поток, разделенный символами табуляции. Все 100 000 строк мгновенно загрузятся в табличный документ за пару секунд. Единственный минус этого способа — потеря исходного оформления ячеек (цветов и рамок), но сами данные будут перенесены в полном объеме.
Полезный лайфхак: Если специальная вставка по какой-то причине недоступна в вашей конфигурации, скопируйте данные из Excel, вставьте их в обычный текстовый редактор «Блокнот» (он принудительно сбросит форматирование Excel), затем снова выделите всё в Блокноте, скопируйте и вставьте в 1С. Результат будет аналогичным.
Попытки реализовать чтение файлов программно через COM-объект Excel.Application методом поячеечного обхода часто приводят к катастрофическому падению производительности: чтение 100 000 строк в 4 колонках может занимать до 20 минут. Это происходит из-за постоянных ресурсоемких обращений платформы к внешнему процессу операционной системы через OLE-границу.
Начиная с современных версий платформы 8.3, разработчикам доступен встроенный высокоскоростной метод прямого чтения файлов Excel. Рассмотрим пример кода, который позволяет прочитать файл формата `.xlsx` напрямую в таблицу значений за несколько секунд:
// Создаем объект табличного документа в оперативной памяти
ТабДок = Новый ТабличныйДокумент;
// Читаем файл средствами платформы.
// СпособЧтенияЗначенийТабличногоДокумента.Значение позволяет получить именно чистые данные ячеек
ТабДок.Прочитать(ПутьКФайлу, СпособЧтенияЗначенийТабличногоДокумента.Значение);
// Быстро преобразуем табличный документ в таблицу значений с помощью Построителя Запроса
Построитель = Новый ПостроительЗапроса;
Построитель.ИсточникДанных = Новый ОписаниеИсточникаДанных(ТабДок.Область());
Построитель.Выполнить();
ТабЗначений = Построитель.Результат.Выгрузить();
// Теперь в переменной ТабЗначений лежат все наши 100 000+ строк для дальнейшей обработки
Этот метод работает на порядок быстрее COM-соединения, так как чтение файла выполняется оптимизированными внутренними алгоритмами платформы 1С на языке C++. Кроме того, данный код абсолютно кроссплатформенный: он будет одинаково успешно работать как на клиенте под управлением ОС Windows, так и на сервере 1С под управлением ОС Linux, где Microsoft Excel физически не может быть установлен.
Если ваша система жестко привязана к операционной системе Windows и вам необходимо работать через COM-соединение, то главной ошибкой является перебор ячеек в цикле. Вместо этого следует использовать механизм пакетной выгрузки данных диапазона в двумерный массив 1С.
Проанализируем пример быстрой загрузки, выполняемой за несколько секунд:
Функция БыстраяЗагрузкаИзExcel(ИмяФайла) Экспорт
Попытка
Excel = Новый COMОбъект("Excel.Application");
Исключение
ВызватьИсключение "На компьютере не установлен MS Excel! Чтение невозможно.";
КонецПопытки;
Попытка
Excel.Workbooks.Open(ИмяФайла);
Исключение
Excel.Quit();
ВызватьИсключение "Не удалось открыть файл: " + ИмяФайла;
КонецПопытки;
Попытка
// Ключевой момент оптимизации: выгружаем ВСЮ заполненную область листа в COMSafeArray одним обращением
ДанныеМассива = Excel.ActiveSheet.UsedRange.Value.Выгрузить();
Исключение
Excel.ActiveWorkbook.Close(0);
Excel.Quit();
ВызватьИсключение "Ошибка при получении данных из буфера Excel.";
КонецПопытки;
Excel.ActiveWorkbook.Close(0);
Excel.Quit();
Возврат ДанныеМассива; // Получаем двумерный массив со всеми данными
КонецФункции
Использование свойства UsedRange.Value.Выгрузить() позволяет передать весь массив данных из процесса Excel в процесс 1С за одно-единственное обращение. После этого вы можете моментально обойти уже стандартный массив 1С в оперативной памяти, что займет не более 1–2 секунд для 100 000 строк.
Если перед вами стоит задача сделать максимально удобный интерфейс для пользователя, чтобы он мог просто нажать кнопку «Вставить» или использовать сочетание клавиш Ctrl + V, и при этом программа автоматически обрабатывала данные без ограничений по строкам, обратите внимание на новые возможности работы с буфером обмена.
Начиная с версии платформы 8.3.24, разработчики получили доступ к асинхронной работе с системным буфером обмена через объект СредстваБуфераОбмена и событие формы ПриВставкеИзБуфераОбмена(). Это позволяет перехватывать действия пользователя на лету.
Рассмотрим пример реализации такого перехвата в модуле управляемой формы:
&НаКлиенте
Асинх Процедура ПриВставкеИзБуфераОбмена(ДанныеБуфера, СтандартнаяОбработка)
// Отменяем стандартное поведение платформы, чтобы избежать усечения до 65к строк
СтандартнаяОбработка = Ложь;
// Запрашиваем данные буфера обмена в текстовом формате асинхронно
ТекстовыеДанные = Ждать СредстваБуфераОбмена.ПолучитьДанныеАсинх(ТипыДанныхБуфераОбмена.Текст);
Если ТекстовыеДанные <> Неопределено Тогда
// Вызываем серверный метод для быстрого парсинга текстового потока
РаспарситьИЗаполнитьТаблицуНаСервере(ТекстовыеДанные);
КонецЕсли;
КонецПроцедуры
В серверном методе РаспарситьИЗаполнитьТаблицуНаСервере полученную строку достаточно разбить по строкам (символы перевода строки Символы.ПС) и колонкам (символ табуляции Символы.Таб). Это решение является наиболее современным и изящным, объединяя простоту использования для конечного пользователя и абсолютную техническую надежность без ограничения на размер импортируемого файла.