При интеграции 1С с внешними системами разработчики часто используют технологию ADO (ActiveX Data Objects) для быстрой записи данных напрямую в файлы MS Excel. Этот способ работает значительно быстрее традиционного OLE-соединения. Однако при выгрузке текстовых данных вы можете столкнуться со странным поведением: в начале каждого строкового значения в ячейке Excel появляется одинарная кавычка (апостроф '), которая видна только в панели формул.
В этой статье мы подробно разберем природу этого явления, выясним, почему драйверы Microsoft ведут себя именно так, и рассмотрим несколько надежных практических способов устранения этой проблемы.
Согласно официальной базе знаний Microsoft (в частности, статье KB257819), добавление апострофа в начале текстовых полей при записи через драйверы Jet или ACE OLEDB — это поведение по умолчанию (by design). Драйвер принудительно устанавливает этот префикс для всех строковых значений.
Это делается со следующими целями:
000123 или номер телефона), Excel без апострофа автоматически преобразует ее в число и сотрет нули, превратив строку в 123. Апостроф принудительно заставляет Excel считать значение текстом.=), плюса (+) или минуса (-), Excel попытается вычислить ее как формулу, что приведет к ошибке в ячейке. Апостроф отключает эту логику.Важная деталь: этот апостроф не является физическим символом внутри строки. Если вы примените к этой ячейке функцию Excel ДЛСТР (LEN), она вернет длину текста без учета кавычки. С точки зрения объектной модели Excel, этот символ хранится в специальном свойстве ячейки PrefixCharacter. Это наследие старого табличного процессора Lotus 1-2-3, где символы-префиксы определяли выравнивание текста в ячейке.
Тем не менее, когда кавычка появляется на чисто текстовых значениях (где нет цифр), это выглядит неэстетично и мешает пользователям. Рассмотрим, как от этого избавиться.
Самый простой программный способ обойти автоматическое добавление апострофа при записи через ADO — вставить в начало строки специальный невидимый символ Backspace (забой), который в таблице ASCII имеет код 8. На стороне 1С это функция Символ(8).
Этот символ заставляет Excel «поглотить» автоматически подставляемый апостроф. Давайте посмотрим на пример реализации:
// Неправильно: может вызвать ошибку несоответствия типов в ADO
rs.Fields(ТекущееПоле.Значение).Value = Символ(8) + СтрокаДанных[ТекущееПоле.Ключ];
При прямой конкатенации, если в источнике данных окажется число или значение Null, ADO выбросит исключение «Несовпадение типов» (Type Mismatch). Чтобы этого избежать, необходимо принудительно приводить значение к строковому типу:
// Правильный и безопасный вариант записи строкового значения
ЗначениеДляЗаписи = "" + Символ(8) + СокрЛП(СтрокаДанных[ТекущееПоле.Ключ]);
rs.Fields(ТекущееПоле.Значение).Value = ЗначениеДляЗаписи;
В этом случае Excel корректно запишет текстовую строку, но скрытый апостроф в панели формул отображаться не будет.
Если вы выгружаете большие объемы данных, построчная запись через цикл rs.Fields().Value = ... крайне непроизводительна. Лучшим решением будет выгрузка всего набора данных (Recordset) за один раз с помощью встроенного метода Excel CopyFromRecordset.
Этот метод оптимизирован на уровне платформы, работает в десятки раз быстрее и, что самое главное, корректно переносит типы данных из ADO в ячейки Excel без принудительной подстановки апострофов-префиксов.
Разберем пример реализации этого подхода по шагам:
// Создаем COM-соединение с приложением Excel
Эксель = Новый COMОбъект("Excel.Application");
Книга = Эксель.Workbooks.Add();
Лист = Книга.Worksheets(1);
// Выгружаем весь рекордсет в ячейку A1 за одно обращение
Лист.Cells(1, 1).CopyFromRecordset(rs);
// Делаем Excel видимым для пользователя
Эксель.Visible = Истина;
Используя этот метод, вы решаете сразу две задачи: избавляетесь от кавычек в ячейках и колоссально увеличиваете скорость формирования отчетов.
Если данные уже записаны в файл и вам требуется программно «очистить» существующий лист от префиксов PrefixCharacter, вы можете применить пост-обработку средствами самого Excel через COM-соединение.
Вариант А: Использование инструмента «Текст по столбцам» (TextToColumns)
Это самый быстрый способ сбросить скрытые апострофы для целой колонки. Программно на 1С это выглядит так:
// Выбираем нужный лист и применяем метод к первому столбцу
Лист = Книга.ActiveSheet;
Лист.Columns(1).TextToColumns(Лист.Cells(1, 1), 1, 1, Ложь, Ложь, Ложь, Ложь, Ложь, Ложь);
Этот вызов заставляет Excel заново проанализировать тип данных в колонке. В результате текстовые числа превратятся в полноценные числа, а текстовые строки очистятся от нежелательных апострофов.
Вариант Б: Переприсвоение формул диапазона
Если вам нужно очистить произвольный диапазон ячеек, можно заставить Excel перезаписать значения «сами в себя». При этом префиксы будут автоматически удалены:
// Выбираем весь используемый диапазон на листе
Диапазон = Лист.UsedRange;
// Переприсваиваем формулы значениям ячеек
Диапазон.Formula = Диапазон.Value;
Иногда поведение драйвера ADO при определении типов данных можно скорректировать еще на этапе подключения к файлу Excel. Для этого используются параметры IMEX и HDR в строке подключения:
IMEX (Import Export Mode): Режим IMEX=1 заставляет драйвер импортировать смешанные типы данных как текст. Если вы записываете данные, попробуйте использовать IMEX=0 (режим экспорта) или IMEX=2 (режим прямого обновления), чтобы драйвер мягче подходил к определению типов.TypeGuessRows: Этот параметр в реестре Windows определяет, сколько строк драйвер сканирует для определения типа колонки (по умолчанию — первые 8 строк). Если в этих строках будут только тексты, драйвер применит к колонке строковый тип и начнет добавлять апострофы ко всем последующим значениям.Для стабильной работы рекомендуется заранее подготавливать файл-шаблон Excel с уже настроенным форматированием ячеек (например, явно задать формат «Текстовый» или «Общий» для целевых колонок) и производить запись ADO именно в этот шаблон.
Выяснив причины поведения Excel, мы можем составить оптимальный план действий для разработчика:
CopyFromRecordset (Способ 2). Это избавит вас от кавычек и ускорит работу.Символ(8), предварительно приведя значение к строке (Способ 1).TextToColumns (Способ 3).