Работа с файлами Microsoft Excel в среде 1С:Предприятие 7.7 часто становится «узким местом» системы, особенно когда речь идет о больших объемах данных. Стандартный метод перебора ячеек через OLE-объект Excel.Application и обращение к каждой ячейке через Cells(i, j).Value крайне медлителен. Для файла в 500–1000 строк такое чтение может занимать несколько минут. Рассмотрим подробнее способы, которые позволяют ускорить этот процесс в десятки раз, и разберем конкретные программные реализации.
Основная проблема медленной работы 1С 7.7 с Excel заключается в накладных расходах на каждое OLE-обращение. Когда мы вызываем Cells(i, j), происходит межпроцессное взаимодействие, которое повторяется для каждой ячейки. Проанализируем основные стратегии оптимизации:
.dbf или .csv с последующим быстрым чтением штатными средствами 1С.Этот метод позволяет прочитать тысячи строк за считанные секунды. Мы будем использовать ADODB.Connection и ADODB.Recordset. Выясним, как правильно настроить строку подключения для работы с форматом Excel.
Рассмотрим пример кода для загрузки данных:
Процедура ПрочитатьЧерезADO(ИмяФайла)
Перем Соединение, Рекордсет, СтрокаПодключения;
Соединение = СоздатьОбъект("ADODB.Connection");
Рекордсет = СоздатьОбъект("ADODB.Recordset");
// Для файлов .xls используем провайдер Jet 4.0
// HDR=NO означает, что первая строка содержит данные, а не заголовки
СтрокаПодключения = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ИмяФайла + ";Extended Properties=""Excel 8.0;HDR=NO;IMEX=1;""";
Попытка
Соединение.Open(СтрокаПодключения);
Исключение
Сообщить("Ошибка подключения: " + ОписаниеОшибки());
Возврат;
КонецПопытки;
// Выбираем данные с первого листа. Имя листа должно заканчиваться знаком $
ТекстЗапроса = "SELECT * FROM [Лист1$]";
Попытка
Рекордсет.Open(ТекстЗапроса, Соединение, 1, 1);
Исключение
Сообщить("Ошибка выполнения запроса: " + ОписаниеОшибки());
Возврат;
КонецПопытки;
Пока Рекордсет.EOF() = 0 Цикл
// Обработка данных
ЗначениеКолонки1 = Рекордсет.Fields(0).Value;
ЗначениеКолонки2 = Рекордсет.Fields(1).Value;
// Переход к следующей строке
Рекордсет.MoveNext();
КонецЦикла;
Рекордсет.Close();
Соединение.Close();
КонецПроцедуры
Важный момент: параметр IMEX=1 в строке подключения крайне важен. Он заставляет драйвер считывать данные разных типов в одной колонке как текст, предотвращая появление пустых значений (Null) там, где числа перемешаны со строками.
Если вам все же необходимо использовать Excel.Application (например, для сложного форматирования), можно значительно ускорить чтение, получив всю область UsedRange целиком в виде массива. Поскольку 1С 7.7 не понимает такой тип данных напрямую, используем внешнюю компоненту OleExSup.dll.
Разберем алгоритм работы:
ЗагрузитьВнешнююКомпоненту("OleExSup.dll");
OleExSup = СоздатьОбъект("OLEExSup");
Ексель = СоздатьОбъект("Excel.Application");
Книга = Ексель.Workbooks.Open(ПутьКФайлу);
Лист = Книга.Worksheets(1);
// Получаем всю используемую область в объект-массив
Массив = OleExSup.ПолучитьСвойство(Лист.UsedRange, "Value");
// Цикл по строкам и колонкам полученного массива
Для ТекСтр = Массив.НижняяГраница(1) По Массив.ВерхняяГраница(1) Цикл
Для ТекКол = Массив.НижняяГраница(2) По Массив.ВерхняяГраница(2) Цикл
Значение = Массив.ПолучитьЗначение(ТекСтр, ТекКол);
// Обработка значения
КонецЦикла;
КонецЦикла;
Книга.Close(0);
Ексель.Quit();
Этот метод позволяет избежать тысяч OLE-вызовов, заменяя их одним единственным получением свойства Value у всей области ячеек.
Йоксель — это мощная внешняя компонента, которая работает с файлами Excel напрямую через их внутреннюю структуру. Она не требует наличия установленного Excel на компьютере и работает быстрее всех OLE-методов. Особенно удобно её использовать, если нужно прочитать файл и сразу загрузить его в ТаблицуЗначений.
Хотя штатный «Йоксель» ориентирован на формат .xls (Excel 97-2003), его скорость и стабильность в среде 7.7 делают его эталонным инструментом для интеграции.
Автор темы столкнулся с проблемой: как не только быстро читать, но и быстро писать в Excel, избегая автоматического создания заголовков. При использовании ADO для записи данных (команда INSERT INTO), драйвер часто создает первую строку с именами полей (F1, F2 и т.д.).
Чтобы избежать этой проблемы и сохранить гибкость форматирования, проанализируем следующий метод:
HDR=YES, тогда драйвер будет считать первую строку шаблона заголовками и начнет запись со второй строки.По результатам тестов, приведенных участниками обсуждения, можно сделать следующие выводы:
Таким образом, для простых плоских таблиц идеальным выбором будет ADO. Если же требуется работа со сложным форматированием или чтение данных «как они есть» (с учетом цветов, шрифтов и т.д.), следует обратить внимание на метод Range с использованием внешних компонент для обработки массивов.