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

← На главную

При разработке механизмов импорта данных из файлов Microsoft Excel без использования COM-объектов программисты часто применяют штатный метод платформы ТабличныйДокумент.Прочитать(). Однако при таком подходе нередко возникает проблема: ячейки, содержащие дату, считываются как обычные пятизначные числа (например, 45123), независимо от выбранного режима СпособЧтенияЗначенийТабличногоДокумента.

Разберем по шагам, почему возникает эта особенность и как правильно восстановить исходную дату средствами встроенного языка 1С:Предприятия.

Выясним причину появления чисел вместо дат

Внутренний формат Microsoft Excel хранит дату и время в виде вещественного числа (серийного номера):

Целая часть числа — это количество дней, прошедших с начальной базовой даты (по умолчанию 1 января 1900 года).
Дробная часть числа — это доля суток, определяющая точное время (часы, минуты и секунды).

Когда мы считываем файл через ТабличныйДокумент, платформа 1С получает сырое числовое значение ячейки, если формат отображения не удалось распознать автоматически. Кроме того, в Excel исторически присутствует ошибка совместимости с Lotus 1-2-3 (1900 год ошибочно считается високосным, содержащим несуществующий день 29.02.1900). Поэтому для корректного пересчета дат, начиная с 1 марта 1900 года, необходимо учитывать смещение на 2 дня назад от 01.01.1900, что соответствует базовой точке отсчета 30 декабря 1899 года.

Решение 1. Математический пересчет серийного номера в дату

Рассмотрим базовую формулу. Чтобы перевести число дней в тип Дата, прибавим к начальной дате количество секунд, содержащихся в указанном числе дней. В одних сутках содержится 86 400 секунд (24 часа × 60 минут × 60 секунд).

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


// ЧислоExcel — число, полученное из ячейки (например, 45123)
БазоваяДата = Дата(1899, 12, 30);
КоличествоСекунд = ЧислоExcel * 86400;
ИтоговаяДата = БазоваяДата + Окр(КоличествоСекунд);

Этот же расчет можно записать через смещение от 1 января 1900 года с вычитанием двух дней:

ИтоговаяДата = Дата(1900, 1, 1) + (ЧислоExcel - 2) * 86400;

Решение 2. Универсальная функция загрузки с поддержкой времени

Создадим универсальную функцию, которая проверяет тип переданного значения и корректно преобразует как целые числа (только дата), так и дробные (дата со временем):


Функция ПреобразоватьЧислоExcelВДату(Знач ИсходноеЗначение) Экспорт

    Если Не ЗначениеЗаполнено(ИсходноеЗначение) Тогда
        Возврат '00010101';
    КонецЕсли;

    // Если значение уже является датой, возвращаем без изменений
    Если ТипЗнч(ИсходноеЗначение) = Тип("Дата") Тогда
        Возврат ИсходноеЗначение;
    КонецЕсли;

    // Преобразуем строку в число, если чтение выполнялось как текст
    Если ТипЗнч(ИсходноеЗначение) = Тип("Строка") Тогда
        ИсходноеЗначение = СтрЗаменить(ИсходноеЗначение, " ", "");
        ИсходноеЗначение = СтрЗаменить(ИсходноеЗначение, ",", ".");
        Попытка
            ИсходноеЗначение = Число(ИсходноеЗначение);
        Исключение
            Возврат '00010101';
        КонецПопытки;
    КонецЕсли;

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

    Возврат '00010101';

КонецФункции

Решение 3. Чтение файла через ТабличныйДокумент и обработка строк

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


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

Для НомерСтроки = 1 По ТабДок.ВысотаТаблицы Цикл

    ЗначениеЯчейки = ТабДок.Область(НомерСтроки, 1).Значение;
    ДатаДокумента = ПреобразоватьЧислоExcelВДату(ЗначениеЯчейки);

    Если ЗначениеЗаполнено(ДатаДокумента) Тогда
        // Дальнейшая обработка полученной даты
    КонецЕсли;

КонецЦикла;

Альтернативные способы чтения данных Excel без стороннего ПО

Если в файле используются нестандартные форматирования ячеек или система дат 1904 года (характерна для некоторых версий на macOS), можно использовать следующие механизмы:

Подсистемы БСП: Использование встроенных функций подсистемы «Загрузка данных из внешних файлов» Библиотеки стандартных подсистем, где уже заложены алгоритмы нормализации числовых и текстовых представлений дат.
Прямой парсинг архива XLSX: Файл формата .xlsx представляет собой ZIP-архив с XML-документами. При помощи объектов ЧтениеZipФайла и ЧтениеXML можно получить доступ к исходным стилям книги (xl/styles.xml) и определить точный тип ячейки без привязки к операционной системе или наличию офисного пакета.