Как получить строки по отбору из файла Excel и загрузить их в 1С

← На главную

Работа с файлами Excel большого объема часто становится "узким местом" в производительности систем на базе 1С. Когда нам необходимо загрузить не все данные, а только определенный срез (например, за конкретный период дат), встает вопрос о предварительной фильтрации. Рассмотрим наиболее эффективные методы, которые позволяют быстро отобрать нужные строки и минимизировать нагрузку на систему.

Метод 1. Использование технологии ADO (SQL-запросы к Excel)

Проанализируем самый мощный способ работы с большими данными — технологию ADO (ActiveX Data Objects). Этот метод позволяет рассматривать лист Excel как таблицу базы данных и выполнять к ней полноценные SQL-запросы. Главное преимущество здесь в том, что мы не открываем приложение Excel визуально и не перебираем сотни тысяч строк в цикле 1С, а получаем сразу готовый результат.

Для реализации этого метода нам потребуются драйверы Microsoft.ACE.OLEDB.12.0 (для файлов .xlsx) или Microsoft.Jet.OLEDB.4.0 (для старого формата .xls). Разберем пример кода, который выполняет отбор по дате:


// Установим параметры соединения
ПутьКФайлу = "C:\Data\Sales.xlsx";
СтрокаСоединения = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ПутьКФайлу + ";Extended Properties=""Excel 12.0 Xml;HDR=YES"";";

Connection = Новый COMОбъект("ADODB.Connection");
Попытка
    Connection.Open(СтрокаСоединения);
Исключение
    Сообщить("Ошибка подключения: " + ОписаниеОшибки());
    Возврат;
КонецПопытки;

// Формируем SQL-запрос с фильтром по дате
// Обратите внимание: даты в ADO для Excel часто обрамляются символом #
ТекстЗапроса = "SELECT * FROM [Лист1$] WHERE [Дата] >= #2023-01-01# AND [Дата] <= #2023-01-31#";

Recordset = Connection.Execute(ТекстЗапроса);

Пока НЕ Recordset.EOF Цикл
    // Обрабатываем только нужные строки
    ЗначениеДаты = Recordset.Fields("Дата").Value;
    Сумма = Recordset.Fields("Сумма").Value;
    
    // Переходим к следующей записи
    Recordset.MoveNext();
КонецЦикла;

Recordset.Close();
Connection.Close();

Важный момент: использование ADO значительно экономит оперативную память, так как 1С получает только те колонки и строки, которые удовлетворяют условию WHERE.

Метод 2. Чтение через «Табличный документ» (Native-метод)

Выясним, как обойтись без установленного Microsoft Office на сервере или компьютере пользователя. Начиная с платформы 8.3.6, в 1С реализован встроенный механизм чтения файлов .xlsx. Этот способ работает быстрее стандартного COM-соединения, так как платформа напрямую парсит XML-структуру файла.

Алгоритм работы следующий: мы читаем файл в объект ТабличныйДокумент, а затем обходим его строки. Хотя фильтрация происходит уже в коде 1С, скорость доступа к ячейкам внутри объекта ТабличныйДокумент на порядок выше, чем через Excel.Application.


ТабДок = Новый ТабличныйДокумент;
Попытка
    ТабДок.Прочитать(ПутьКФайлу, СпособЧтенияЗначенийТабличногоДокумента.Значение);
Исключение
    Предупреждение("Не удалось прочитать файл!");
    Возврат;
КонецПопытки;

КоличествоСтрок = ТабДок.ВысотаТаблицы;
// Предположим, дата находится во второй колонке
Для НомерСтроки = 2 По КоличествоСтрок Цикл
    ЗначениеДаты = ТабДок.Область(НомерСтроки, 2).Текст;
    
    // Проверяем условие входа в диапазон
    Если Дата(ЗначениеДаты) >= НачалоПериода И Дата(ЗначениеДаты) <= КонецПериода Тогда
        // Загружаем данные в 1С
    КонецЕсли;
КонецЦикла;

Метод 3. Оптимизация COM-объекта: выгрузка в массив

Рассмотрим ситуацию, когда использование Excel.Application неизбежно. Основная ошибка программистов — обращение к каждой ячейке в цикле (например, Лист.Cells(i, j).Value). Каждое такое обращение — это дорогостоящий вызов через COM-интерфейс, который при 10 000 строк может занять минуты.

Чтобы ускорить процесс в десятки раз, мы можем считать весь диапазон данных в один двумерный массив 1С за одно обращение. Разберем этот прием:


ExcelПриложение = Новый COMОбъект("Excel.Application");
Книга = ExcelПриложение.Workbooks.Open(ПутьКФайлу);
Лист = Книга.Sheets(1);

// Получаем сразу все данные используемого диапазона в массив
МассивДанных = Лист.UsedRange.Value.Выгрузить();
Книга.Close(Ложь);
ExcelПриложение.Quit();

// Теперь работаем с обычным массивом 1С, что мгновенно
Для Каждого Строка Из МассивДанных Цикл
    // Индексация в массиве из Excel начинается с 0 или 1 в зависимости от версии
    ДатаФайла = Строка[1]; 
    Если ДатаФайла >= НужнаяДата Тогда
        // Обработка данных
    КонецЕсли;
КонецЦикла;

Метод 4. Конвертация в CSV для экстремально больших объемов

Если файл содержит сотни тысяч строк и памяти не хватает даже для загрузки в массив, проанализируем метод работы через CSV. Формат CSV — это обычный текстовый файл, который 1С может читать построчно с помощью объекта ЧтениеТекста. Это самый быстрый из всех возможных способов.

Мы можем программно заставить Excel сохранить текущий лист в формате CSV, а затем прочитать его:

Открываем файл через COM.
Вызываем Лист.SaveAs(ПутьКCSV, 6), где 6 — это код формата CSV.
Используем ЧтениеТекста для построчного разбора.
В процессе чтения каждой строки проверяем дату и решаем, загружать ли данные.

Подведение итогов

Выбор метода зависит от ваших условий:

Если важна максимальная скорость и есть возможность установить драйверы на сервере — выбирайте ADO.
Если нужно решение без внешних зависимостей (без Excel и драйверов) — используйте ТабличныйДокумент.Прочитать().
Если вы уже используете COM и хотите быстро оптимизировать существующий код — используйте выгрузку в массив через UsedRange.Value.

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

← На главную