При работе с крупными файлами импорта (например, прайс-листами или каталогами на 300 000 строк и более) разработчики часто сталкиваются с тем, что стандартные методы построчного обхода ячеек работают слишком медленно или приводят к зависанию системы. В процессе оптимизации кода легко допустить логические ошибки, из-за которых результирующая таблица значений (ТЗ) остается пустой, несмотря на то что сам файл успешно считывается.
В этой статье мы подробно разберем, почему возникает проблема пустой таблицы значений при работе через COM-соединение, исправим ошибки в исходном коде и рассмотрим альтернативные, более современные и быстрые способы загрузки больших объемов данных в 1С.
Проанализируем ситуацию, описанную в теме форума. Разработчик пытается считать данные из файла Excel, используя выгрузку области ячеек в многомерный массив типа COMSafeArray с последующей загрузкой колонок. Однако на выходе получается пустая таблица значений.
Основная ошибка крылась в неправильной инициализации COM-объектов и путанице в иерархии объектной модели Excel. В строке:
ЛистЭксель = ПолучитьCOMОбъект(ПолеВвода1);
переменной ЛистЭксель фактически присваивается объект самой рабочей книги (Workbook) или всего приложения Excel, а не конкретного листа (Worksheet). В результате последующие обращения вида ЛистЭксель.Cells.SpecialCells(11) возвращали некорректные области или вызывали скрытые ошибки выполнения, из-за чего получение данных срывалось.
Давайте посмотрим, как исправить эту логическую цепочку. Чтобы обратиться к данным конкретного листа, необходимо сначала открыть рабочую книгу, а затем явно получить нужный лист по его индексу или имени.
Правильный вариант инициализации выглядит следующим образом:
// Получаем объект приложения Excel или открываем файл напрямую
ExcelКнига = ПолучитьCOMОбъект(ПутьКФайлу);
// Явно позиционируемся на первом листе книги
ЛистЭксель = ExcelКнига.Sheets(1);
После этого объект ЛистЭксель будет содержать нужный нам лист, и методы по определению количества строк и колонок отработают корректно.
Этот способ основан на быстром получении значений всей области ячеек за один программный вызов. Вместо того чтобы в цикле обходить 300 000 строк (что заняло бы несколько часов), мы считываем свойство Value у всего диапазона ячеек Range. Excel возвращает двумерный массив COMSafeArray, который мы затем преобразуем в массив 1С методом Выгрузить().
Рассмотрим доработанную функцию загрузки данных:
Функция ПрочитатьЛистExcelВТЗ(ПутьКФайлу, НомерПервойСтроки = 1, НомерПервойКолонки = 1) Экспорт
// Инициализируем COM-соединение с книгой Excel
Попытка
ExcelКнига = ПолучитьCOMОбъект(ПутьКФайлу);
ЛистЭксель = ExcelКнига.Sheets(1); // Работаем с первым листом
Исключение
Предупреждение("Не удалось открыть файл Excel! Возможно, он заблокирован или отсутствует MS Excel.");
Возврат Неопределено;
КонецПопытки;
// Определяем границы заполненной области листа
ВсегоСтрок = ЛистЭксель.Cells.SpecialCells(11).Row;
ВсегоКолонок = ЛистЭксель.Cells.SpecialCells(11).Column;
// Создаем структуру Таблицы Значений
ТЗ = Новый ТаблицаЗначений;
Для СчетчикКолонок = 1 По ВсегоКолонок Цикл
ТЗ.Колонки.Добавить("Колонка" + СчетчикКолонок, Новый ОписаниеТипов("Строка"));
КонецЦикла;
// Добавляем пустые строки в ТЗ под размер загружаемых данных
КоличествоСтрокДляДобавления = ВсегоСтрок - НомерПервойСтроки + 1;
Для СчетчикСтрок = 1 По КоличествоСтрокДляДобавления Цикл
ТЗ.Добавить();
КонецЦикла;
// Получаем область данных и выгружаем её в двумерный массив
Область = ЛистЭксель.Range(ЛистЭксель.Cells(НомерПервойСтроки, НомерПервойКолонки), ЛистЭксель.Cells(ВсегоСтрок, ВсегоКолонок));
ДанныеМассива = Область.Value.Выгрузить(); // Возвращает двумерный массив 1С
// Переносим данные из массива в колонки ТЗ
Для ИндексКолонки = 0 По ВсегоКолонок - 1 Цикл
ТЗ.ЗагрузитьКолонку(ДанныеМассива[ИндексКолонки], ИндексКолонки);
КонецЦикла;
// Закрываем Excel-процесс и освобождаем память
ExcelКнига = Неопределено;
ЛистЭксель = Неопределено;
Возврат ТЗ;
КонецФункции
Важное предупреждение: Метод с использованием COMSafeArray работает быстро, но требователен к оперативной памяти. При объеме в 300 000 строк и большом числе колонок этот метод может вызвать ошибку нехватки памяти (Out of Memory) на 32-разрядных клиентах 1С, так как весь массив данных загружается в оперативную память одновременно.
В тексте обсуждения упоминался загадочный метод getrows(). Разберем по шагам, что это такое. Данный метод принадлежит не самому Excel, а технологии доступа к базам данных ADO (ActiveX Data Objects).
Используя ADO, мы можем подключиться к файлу Excel как к плоской реляционной базе данных, где листы книги выступают в роли таблиц, а строки — в роли записей. Данные считываются с помощью обычного SQL-запроса, а метод Recordset.GetRows() моментально выгружает весь результат запроса в двумерный массив.
Рассмотрим пример реализации загрузки через ADO:
Функция ЗагрузитьExcelЧерезADO(ПутьКФайлу, ИмяЛиста = "Лист1") Экспорт
ТЗ = Новый ТаблицаЗначений;
// Создаем объекты ADO для подключения и выполнения запроса
Connection = Новый COMОбъект("ADODB.Connection");
Recordset = Новый COMОбъект("ADODB.Recordset");
// Формируем строку подключения в зависимости от расширения файла
// Для файлов .xlsx используем провайдер ACE OLEDB
СтрокаПодключения = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ПутьКФайлу + ";Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"";";
Попытка
Connection.Open(СтрокаПодключения);
Исключение
Сообщить("Ошибка подключения к файлу через ADO: " + ОписаниеОшибки());
Возврат Неопределено;
КонецПопытки;
// Выполняем SQL-запрос к листу Excel
ТекстЗапроса = "SELECT * FROM [" + ИмяЛиста + "$]";
Попытка
Recordset.Open(ТекстЗапроса, Connection, 1, 1); // adOpenKeyset, adLockReadOnly
Исключение
Сообщить("Ошибка выполнения запроса к листу: " + ОписаниеОшибки());
Connection.Close();
Возврат Неопределено;
КонецПопытки;
Если Не Recordset.EOF Тогда
// Создаем колонки в таблице значений на основе полей Recordset
Для Счетчик = 0 По Recordset.Fields.Count - 1 Цикл
ИмяКолонки = Recordset.Fields.Item(Счетчик).Name;
// Убираем спецсимволы из имен колонок, если они есть
ИмяКолонкиДляТЗ = СтрЗаменить(ИмяКолонки, " ", "_");
ТЗ.Колонки.Добавить(ИмяКолонкиДляТЗ, Новый ОписаниеТипов("Строка"));
КонецЦикла;
// Получаем все данные в виде двумерного массива
МассивДанных = Recordset.GetRows(); // Метод возвращает транспонированный массив
// В полученном массиве первая размерность — это колонки, вторая — строки
КоличествоКолонок = МассивДанных.ВГраница(1) + 1;
КоличествоСтрок = МассивДанных.ВГраница(2) + 1;
// Заполняем ТЗ данными
Для ИндексСтроки = 0 По КоличествоСтрок - 1 Цикл
НоваяСтрока = ТЗ.Добавить();
Для ИндексКолонки = 0 По КоличествоКолонок - 1 Цикл
НоваяСтрока[ИндексКолонки] = МассивДанных[ИндексКолонки][ИндексСтроки];
КонецЦикла;
КонецЦикла;
КонецЕсли;
// Закрываем соединения
Recordset.Close();
Connection.Close();
Возврат ТЗ;
КонецФункции
Преимущества метода ADO:
Недостатки метода ADO: Требует наличия установленных в системе драйверов базы данных (Microsoft Access Database Engine). Кроме того, типы данных в колонках определяются драйвером автоматически по первым строкам, что иногда приводит к потере информации (например, обрезаются лидирующие нули в телефонных номерах или артикулах).
Начиная с платформы 8.3, в 1С появился мощный кроссплатформенный механизм прямого чтения файлов Excel. Мы можем загрузить файл во внутренний объект ТабличныйДокумент без использования каких-либо сторонних библиотек и COM-объектов. Этот способ работает как на Windows, так и на Linux-серверах.
Чтобы мгновенно преобразовать полученный табличный документ в таблицу значений без медленного построчного обхода ячеек, мы задействуем ПостроительЗапроса. Он способен преобразовать область табличного документа в ТЗ за доли секунды.
Разберем этот лаконичный и самый эффективный алгоритм по шагам:
Функция ЗагрузитьExcelЧерезТабДок(ПутьКФайлу) Экспорт
ТабДок = Новый ТабличныйДокумент;
Попытка
// Читаем файл в режиме "Текст" для сохранения исходного форматирования данных (сохраняются нули, пробелы)
ТабДок.Прочитать(ПутьКФайлу, СпособЧтенияЗначенийТабличногоДокумента.Текст);
Исключение
Сообщить("Не удалось прочитать файл: " + ОписаниеОшибки());
Возврат Неопределено;
КонецПопытки;
// Используем ПостроительЗапроса для быстрой конвертации области ТабДока в ТЗ
Построитель = Новый ПостроительЗапроса;
// Задаем источник данных на основе всей заполненной области табличного документа
Построитель.ИсточникДанных = Новый ОписаниеИсточникаДанных(ТабДок.Область());
// Инициализируем настройки построителя
Построитель.ЗаполнитьНастройки();
// Выполняем построение и выгружаем результат в готовую Таблицу Значений
Построитель.Выполнить();
ТаблицаРезультат = Построитель.Результат.Выгрузить();
Возврат ТаблицаРезультат;
КонецФункции
Почему этот метод является лучшим?
excel.exe.Если файл Excel содержит миллионы строк и даже загрузка через табличный документ вызывает нехватку памяти на сервере, применяется метод парсинга XML-структуры файла. Файл с расширением .xlsx представляет собой обычный ZIP-архив.
Мы можем распаковать его во временный каталог с помощью объекта ЧтениеZipФайла и последовательно прочитать файлы sharedStrings.xml (хранилище всех уникальных строк) и sheet1.xml (структура ячеек листа) через объект ЧтениеXML.
Этот способ требует написания сложного парсера, но его главное преимущество — чтение данных в режиме потока. Память сервера при этом практически не нагружается, так как данные не считываются целиком, а обрабатываются последовательно строка за строкой.
Для успешной загрузки больших таблиц рекомендуем придерживаться следующих правил:
Попытка...Исключение...КонецПопытки.