Загрузка данных из Excel — одна из самых частых задач в практике программиста 1С. Однако при работе с файлами, содержащими десятки и сотни тысяч строк (например, более 70 000), стандартные методы могут работать крайне медленно или выдавать ошибки. Рассмотрим основные способы решения этой задачи, проанализируем причины низкой производительности и разберем, как оптимизировать код для достижения максимальной скорости.
Технология ADODB (ActiveX Data Objects Database) позволяет работать с файлом Excel как с базой данных, выполняя к нему SQL-запросы. Это один из самых быстрых способов, так как он не требует визуального запуска приложения Excel и работает напрямую с данными.
Проанализируем ситуацию, когда стандартный драйвер Microsoft.Jet.OLEDB.4.0 перестает справляться. Главное ограничение этого драйвера — он предназначен для старых форматов Excel 97-2003 (.xls) и имеет лимит в 65 536 строк. Если ваш файл содержит 70 000 строк и более, необходимо использовать драйвер Microsoft.ACE.OLEDB.12.0.
Разберем пример функции для загрузки данных через ADODB с использованием правильной строки соединения:
Функция ЗагрузитьЧерезADODB(Знач ПутьКФайлу, Знач ИмяЛиста) Экспорт
РезультатТЗ = Новый ТаблицаЗначений;
// Формируем строку соединения для файлов .xlsx и больших объемов данных
// IMEX=1 позволяет читать смешанные данные как текст, предотвращая потерю информации
ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ПутьКФайлу + ";Extended Properties=""Excel 12.0;HDR=YES;IMEX=1;""";
Connection = Новый COMОбъект("ADODB.Connection");
Connection.ConnectionString = ConnectionString;
Попытка
Connection.Open();
Исключение
Сообщить("Не удалось подключиться к Excel: " + ОписаниеОшибки());
Возврат РезультатТЗ;
КонецПопытки;
// SQL-запрос к листу Excel. Обратите внимание на символ $ в конце имени листа
ТекстЗапроса = "SELECT * FROM [" + ИмяЛиста + "$]";
RecordSet = Новый COMОбъект("ADODB.Recordset");
Попытка
RecordSet.Open(ТекстЗапроса, Connection);
Исключение
Сообщить("Ошибка выполнения запроса: " + ОписаниеОшибки());
Connection.Close();
Возврат РезультатТЗ;
КонецПопытки;
// Создаем колонки в ТЗ на основе полей Recordset
Для НомерПоля = 0 По RecordSet.Fields.Count - 1 Цикл
РезультатТЗ.Колонки.Добавить(СтрЗаменить(RecordSet.Fields.Item(НомерПоля).Name, "#", "."));
КонецЦикла;
// Загружаем данные
Пока НЕ RecordSet.EOF Цикл
НоваяСтрока = РезультатТЗ.Добавить();
Для НомерПоля = 0 По RecordSet.Fields.Count - 1 Цикл
НоваяСтрока[НомерПоля] = RecordSet.Fields.Item(НомерПоля).Value;
КонецЦикла;
RecordSet.MoveNext();
КонецЦикла;
RecordSet.Close();
Connection.Close();
Возврат РезультатТЗ;
КонецФункции
Важный нюанс: Чтобы драйвер ACE OLEDB работал, на компьютере должен быть установлен Microsoft Access Database Engine. Также помните, что битность драйвера должна совпадать с битностью приложения 1С (32 или 64 бита).
Многие программисты используют перебор ячеек в цикле через ExcelЛист.Cells(индекс, колонка).Value. Это самый медленный способ, так как каждое обращение к ячейке — это отдельный ресурсоемкий вызов между процессами 1С и Excel.
Выясним, как ускорить этот процесс в десятки раз. Вместо обращения к каждой ячейке, мы прочитаем весь используемый диапазон (UsedRange) в один двумерный массив 1С за один вызов. Посмотрим на пример кода:
Процедура ЗагрузитьЧерезМассив(ПутьКФайлу)
Excel = Новый COMОбъект("Excel.Application");
Excel.Visible = Ложь;
Excel.DisplayAlerts = Ложь;
Книга = Excel.Workbooks.Open(ПутьКФайлу);
Лист = Книга.Sheets(1);
// Получаем все данные листа в двумерный массив COM
Данные = Лист.UsedRange.Value.Выгрузить();
Книга.Close(Ложь);
Excel.Quit();
// Теперь работаем с обычным массивом 1С, что происходит мгновенно
ТЗ = Новый ТаблицаЗначений;
// (Код создания колонок в ТЗ пропущен)
ВсегоСтрок = Данные.ВГраница(2); // Второе измерение массива
ВсегоКолонок = Данные.ВГраница(1); // Первое измерение массива
Для Стр = 1 По ВсегоСтрок Цикл
НоваяСтрока = ТЗ.Добавить();
Для Кол = 1 По ВсегоКолонок Цикл
НоваяСтрока[Кол-1] = Данные[Кол-1][Стр-1];
КонецЦикла;
КонецЦикла;
КонецПроцедуры
Этот метод сочетает в себе простоту объектной модели Excel и высокую скорость работы с памятью 1С.
Этот способ часто называют "нативным", так как он не требует установленного Excel (при использовании метода Прочитать). Однако пользователи часто сталкиваются с тем, что ПостроительОтчета.Результат оказывается пуст.
Рассмотрим причины этой проблемы. Если база работает в режиме совместимости с версией 8.1, ПостроительОтчета может некорректно определять типы данных или области, если в файле есть "мусорные" строки или сложное форматирование. Для корректной работы важно правильно инициализировать ОписаниеИсточникаДанных.
Разберем рабочий алгоритм:
ТабличныйДокумент.ВысотаТаблицы и ШиринаТаблицы.ОписаниеИсточникаДанных на основе конкретной области ячеек.
ТабДок = Новый ТабличныйДокумент;
ТабДок.Прочитать(ПутьКФайлу);
// Определяем область данных
Высота = ТабДок.ВысотаТаблицы;
Ширина = ТабДок.ШиринаТаблицы;
Если Высота > 0 Тогда
Область = ТабДок.Область(1, 1, Высота, Ширина);
Источник = Новый ОписаниеИсточникаДанных(Область);
Источник.Колонки.НомерСтроки.Имя = "Удалить_НомерСтроки"; // Исключаем служебные поля
Построитель = Новый ПостроительОтчета;
Построитель.ИсточникДанных = Источник;
Построитель.Выполнить();
ТаблицаРезультат = Построитель.Результат.Выгрузить();
КонецЕсли;
В современных релизах платформы появился объект ЧтениеДанныхExcel. Проанализируем его преимущества: он работает максимально быстро, не требует установленного Office и, что самое важное, читает данные в потоковом режиме. Это идеальный выбор для файлов объемом в сотни тысяч строк, так как он практически не потребляет оперативную память сервера.
Проанализируем ситуацию с выбором метода:
Сводная таблица рекомендаций:
.xlsx.Подведем итог: для быстрой загрузки 70 000 строк забудьте о переборе ячеек Cells(i, j). Используйте либо ADODB с драйвером ACE 12.0, либо выгружайте весь диапазон в массив 1С. Это сократит время ожидания пользователя с десятков минут до нескольких секунд.