Как в 1С преобразовать числовой формат даты Excel при чтении через ADODB?

← На главную

При загрузке данных из файлов Microsoft Excel в 1С через технологию ADO (провайдер Microsoft.ACE.OLEDB) разработчики часто сталкиваются с ситуацией, когда вместо привычной даты и времени (например, 23.05.2017 8:52:00) из объекта Recordset считывается числовое значение в строковом виде — например, "42878.369444". Попытка вызвать конструктор Дата(...) от такой строки приводит к ошибке преобразования типа.

Разберем подробно, почему возникает такое поведение, как устроено хранение дат в Excel и какими способами можно получить корректную дату в 1С.

Причины появления серийного номера вместо даты

Проанализируем механизм работы Excel и драйвера OLEDB:

Внутреннее представление дат в Excel: В таблицах Excel дата и время хранятся как вещественные числа (серийные номера). Целая часть числа обозначает количество дней, прошедших с базовой даты, а дробная часть — долю суток (время).
Параметр IMEX в строке соединения: При подключении с параметром IMEX=1 (Import Mode) драйвер считывает смешанные типы ячеек в колонке как текст. Так как физически в ячейке находится число, драйвер возвращает его прямое текстовое представление, игнорируя визуальное форматирование ячейки.
Механизм TypeGuessRows: По умолчанию OLEDB-драйвер сканирует первые несколько строк (параметр TypeGuessRows в реестре) для автоматического определения типа данных столбца. Если в выборке присутствуют разнородные данные, столбец может интерпретироваться как строковый или числовой.

Способ 1. Преобразование серийного номера в дату средствами встроенного языка 1С

Выясним математическую основу пересчета. В Excel опорной точкой отсчета для серийных номеров (с учетом известной исторической эмуляции бага 1900 года) является дата 30.12.1899 00:00:00. Чтобы получить точную дату и время в 1С, достаточно умножить полученное число на количество секунд в сутках (86400) и прибавить результат к базовой дате.

Посмотрим на пример универсального кода преобразования:


ЗначениеИзПоля = Recordset.Fields.Item(0).Value;

// Нормализуем разделитель дробной части и переводим в число
СтрокаЧисла = СтрЗаменить(СокрЛП(ЗначениеИзПоля), ",", ".");
ЧисловоеЗначение = Число(СтрокаЧисла);

Если ЧисловоеЗначение > 0 Тогда
    БазоваяДата = Дата(1899, 12, 30, 0, 0, 0);
    Секунды = Окр(ЧисловоеЗначение * 86400);
    Значение_ДатаВремя = БазоваяДата + Секунды;
Иначе
    Значение_ДатаВремя = Дата('00010101');
КонецЕсли;

Рассмотрим, как это работает на конкретных цифрах: для значения 42878.369444 умножение на 86400 дает количество секунд с 30 декабря 1899 года, что в результате сложения с базовой датой дает 23.05.2017 08:52:00.

Способ 2. Корректировка параметров подключения ADODB

Если в колонке файла Excel содержатся исключительно даты и нет смешанных типов данных, можно скорректировать строку подключения:

Удалим из строки подключения параметр IMEX=1 или явно укажем IMEX=0.
Убедимся, что формат ячеек в первых строках файла явно установлен как «Дата».

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


Connection = Новый COMОбъект("ADODB.Connection");
ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" +
                   "Data Source=" + Файл + ";" +
                   "Extended Properties=""Excel 12.0;HDR=NO;IMEX=0;""";
Connection.Open(ConnectionString);

В этом случае драйвер определит тип столбца как дату и вернет значение, которое платформа 1С автоматически прочитает как тип Дата.

Альтернативные способы чтения Excel без COM и ADO

Использование COM-объектов имеет ряд ограничений: они работают только в среде Windows, зависят от разрядности установленных драйверов (32/64 бит) и не функционируют на серверах под управлением Linux. Рассмотрим платформенные альтернативы:

Объект ТабличныйДокумент: Метод ТабличныйДокумент.Прочитать() поддерживает файлы .xlsx «из коробки» на любой операционной системе и корректно типизирует даты.
Прямой разбор через ЧтениеZipФайла и ЧтениеXML: Файлы XLSX являются ZIP-архивами с XML-структурой. Извлечение данных напрямую из файлов листов позволяет работать максимально быстро и независимо от окружения. Серийные даты из тегов XML в этом случае конвертируются по той же формуле со смещением от 30.12.1899.