Как изменить значения в ячейках таблицы Excel используя технологию ADO в 1С

При работе с большими объемами данных в Excel стандартные механизмы OLE Automation (использование объекта Excel.Application) часто демонстрируют низкую производительность. В ситуациях, когда файлы содержат десятки тысяч строк, процесс записи может «зависать» или потреблять чрезмерное количество системных ресурсов. В таких случаях на помощь приходит технология ADO (ActiveX Data Objects), которая позволяет работать с листом Excel как с полноценной базой данных. Разберем подробно, как правильно настроить подключение и выполнить запись или изменение значений в ячейках.

Почему возникает ошибка «Recordset не поддерживает обновление»?

Проанализируем типичную ситуацию: программист успешно открывает файл для чтения, но при попытке изменить значение в Recordset или вызвать метод Update получает отказ. Основная причина кроется в настройках провайдера и параметрах открытия набора записей. По умолчанию многие подключения открываются в режиме «только чтение» или с типом курсора, который не предполагает изменения данных. Чтобы решить эту проблему, необходимо последовательно настроить три составляющих: строку подключения, параметры самого объекта RecordSet и, в некоторых случаях, настройки системного реестра.

Шаг 1. Настройка строки подключения (Connection String)

Выясним, какие параметры строки подключения критичны для записи. Рассмотрим два основных провайдера: Microsoft.ACE.OLEDB.12.0 (для файлов .xlsx и .xlsm) и Microsoft.Jet.OLEDB.4.0 (для старых файлов .xls).

Важным параметром является Mode. Для возможности записи необходимо явно указать Mode=ReadWrite или использовать числовое значение 3. Также обратите внимание на параметр IMEX.

1. IMEX=0 — режим экспорта (записи). Драйвер будет пытаться определить тип данных колонки и позволит записывать значения, соответствующие этому типу.
2. IMEX=1 — режим импорта (только чтение). Самый стабильный для чтения «грязных» данных, но блокирует запись.
3. IMEX=2 — смешанный режим, который часто используется для обновлений.

Посмотрим на пример формирования строки подключения для формата XLSX:

ПутьКФайлу = "C:\my_folder\data.xlsx";
СтрокаПодключения = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ПутьКФайлу + ";";
// Добавляем параметры расширенных свойств
// HDR=NO означает, что первая строка — это данные, а не заголовки
// IMEX=0 позволяет производить запись
СтрокаПодключения = СтрокаПодключения + "Extended Properties=""Excel 12.0 Xml;HDR=NO;IMEX=0"";";
// Устанавливаем режим доступа к файлу
СтрокаПодключения = СтрокаПодключения + "Mode=ReadWrite;";

Connection = Новый COMОбъект("ADODB.Connection");
Connection.Open(СтрокаПодключения);

Шаг 2. Правильная настройка объекта RecordSet

Даже если соединение открыто на запись, объект RecordSet по умолчанию может создаваться с типом «ForwardOnly» и блокировкой «ReadOnly». Чтобы иметь возможность менять значения Fields(...).Value, проанализируем необходимые свойства:

• CursorType = 3 (Static) — позволяет перемещаться в любом направлении.
• LockType = 3 (Optimistic) — тип блокировки, разрешающий обновление записей.

Разберем пример кода для изменения значения в первой строке третьей колонки:

RecordSet = Новый COMОбъект("ADODB.RecordSet");
RecordSet.ActiveConnection = Connection;
RecordSet.CursorType = 3;
RecordSet.LockType = 3;

// Запрос к листу. Не забываем про знак $ в конце имени листа
ТекстЗапроса = "SELECT * FROM [Лист1$]";
RecordSet.Open(ТекстЗапроса);

Если НЕ RecordSet.EOF Тогда
    // Обращаемся к колонке по индексу (начиная с 0)
    // Записываем новое значение в третью колонку (индекс 2)
    RecordSet.Fields(2).Value = "Тестовое значение ADO";
    
    // КРИТИЧЕСКИ ВАЖНО: вызвать метод Update для сохранения изменений
    RecordSet.Update();
КонецЕсли;

RecordSet.Close();
Connection.Close();

Шаг 3. Использование SQL-запроса UPDATE

Иногда удобнее не перебирать строки в RecordSet, а отправить прямой SQL-запрос на обновление. Это особенно эффективно, если нужно изменить конкретную ячейку или диапазон, зная условия.

Проанализируем синтаксис обращения к диапазонам. В ADO лист Excel воспринимается как таблица, а диапазон ячеек — как подмножество этой таблицы. Если мы используем HDR=NO, то имена колонок будут генерироваться автоматически как F1, F2, F3 и так далее.

// Пример обновления значения в колонке F2, где в колонке F1 стоит определенный код
Команда = Новый COMОбъект("ADODB.Command");
Команда.ActiveConnection = Connection;

// Обновляем диапазон ячеек с A2 по C100 на Листе1
// Устанавливаем значение в колонке F2 для строки, где F1 = 'Товар123'
ТекстSQL = "UPDATE [Лист1$A2:C100] SET F2 = '1500' WHERE F1 = 'Товар123'";

Команда.CommandText = ТекстSQL;
Команда.CommandType = 1; // adCmdText
Команда.Execute();

Важные технические нюансы и ограничения

Рассмотрим подводные камни, с которыми часто сталкиваются разработчики при внедрении ADO для Excel:

1. Типы данных и TypeGuessRows: Драйвер ADO сканирует первые 8 строк, чтобы «угадать» тип данных в колонке. Если в первых 8 строках числа, а вы пытаетесь записать текст в 10-ю строку, возникнет ошибка. Это поведение регулируется в реестре Windows параметром TypeGuessRows. Если установить его в 0, драйвер будет сканировать все строки, что надежнее, но медленнее.
2. Разрядность драйверов: Если ваша 1С работает в 64-битном режиме (сервер или 64-битный клиент), вам необходим 64-битный драйвер Microsoft Access Database Engine. Если 1С 32-битная — соответственно 32-битный. Установка обеих версий на одну машину требует использования ключа /passive при инсталляции драйвера.
3. Форматирование: Помните, что ADO работает только с данными. Вы не сможете через ADO изменить цвет ячейки, шрифт или границы. Форматирование должно быть настроено в шаблоне заранее.
4. Удаление данных: Команда DELETE не поддерживается драйвером Excel. Вы можете только обновлять ячейки (например, записывать туда пустые строки или нули), но физически удалить строку листа через ADO нельзя.
5. Монопольный доступ: Если файл Excel открыт у пользователя в самом приложении Excel, ADO может выдать ошибку доступа. Желательно убедиться, что файл не заблокирован перед началом операции.

Резюме: Для успешного изменения данных в Excel через ADO всегда проверяйте наличие Mode=ReadWrite в строке подключения, устанавливайте LockType = 3 для RecordSet и обязательно вызывайте метод Update(). Этот метод значительно ускорит выгрузку сложных отчетов из 1С в Excel по сравнению с классическим OLE.