При импорте данных из Excel в 1С программисты часто сталкиваются с ситуацией, когда вместо привычной даты (например, "15.05.2020") система считывает странную строку вида DD43966 или просто пятизначное число. Это происходит из-за особенностей хранения дат в движке Microsoft Excel и того, как механизм ТабличныйДокумент в 1С интерпретирует эти данные. В этой статье мы подробно разберем, почему так происходит, и рассмотрим несколько способов корректного получения даты.
Для начала проанализируем ситуацию. Excel хранит даты не как текст, а как порядковые числа. Число 1 соответствует 1 января 1900 года. Соответственно, число 43966 — это количество дней, прошедших с начала прошлого века до 15 мая 2020 года. Такой формат называется OADate.
Почему же в коде автора появилось DD перед числом? Это результат применения пользовательского формата ячейки в самом файле Excel. Когда мы используем метод Ячейка.Текст, 1С пытается считать визуальное представление данных. Если в Excel настроена маска отображения, она может добавиться к значению. Если же мы хотим получить "сырые" данные, нам нужно изменить подход к чтению файла.
Самый правильный и надежный способ — заставить 1С считывать именно значение ячейки, а не ее текстовое представление. Рассмотрим, как это сделать, изменив параметры метода Прочитать().
По умолчанию метод Прочитать() для объекта ТабличныйДокумент использует текстовый режим. Чтобы получить доступ к типизированным данным, необходимо указать второй параметр СпособЧтенияЗначенийТабличногоДокумента.Значение. Разберем этот шаг на примере кода:
&НаСервере
Процедура ЗагрузитьВТаблицу(ИмяВременногоФайла)
ЗагруженныйШаблонСДанными = Новый ТабличныйДокумент;
// Используем СпособЧтенияЗначенийТабличногоДокумента.Значение
ЗагруженныйШаблонСДанными.Прочитать(ИмяВременногоФайла, СпособЧтенияЗначенийТабличногоДокумента.Значение);
ТабличныйДокументВТаблицуЗначений(ЗагруженныйШаблонСДанными);
КонецПроцедуры
После такого изменения в свойстве Ячейка.Значение будет содержаться число (например, 43966). Однако 1С не преобразует его в дату автоматически, так как для платформы это просто число. Нам потребуется математический пересчет.
Поскольку мы выяснили, что дата в Excel — это количество дней, нам нужно прибавить это количество к "базовой дате" в 1С. Важно помнить, что в Excel существует историческая особенность (ошибка с високосным 1900 годом), поэтому базовой датой для расчетов в 1С следует считать 30 декабря 1899 года.
Рассмотрим алгоритм пересчета:
'18991230'.Посмотрим на практическую реализацию функции преобразования:
// Допустим, ЧислоИзExcel = 43966
БазоваяДата = Дата(1899, 12, 30);
КоличествоСекунд = ЧислоИзExcel * 86400;
ИтоговаяДата = БазоваяДата + КоличествоСекунд;
// Результат: 15.05.2020 00:00:00
Если в ячейке Excel указано еще и время, число будет дробным (например, 43966.5). Дробная часть соответствует времени (0.5 — это ровно полдень). Приведенная выше формула корректно обработает и время, если вы не будете использовать функцию Цел().
Если по каким-то причинам вы не можете изменить способ чтения файла (например, это часть сложной старой обработки) и продолжаете получать строку вида DD43966, можно применить "строковую очистку". Проанализируем пример из обсуждения:
// Извлекаем только цифровую часть
ТекТекст = Ячейка.Текст; // "DD43966"
ЧислоСтрокой = СтрЗаменить(ТекТекст, "DD", "");
Чис = Число(ЧислоСтрокой);
// Применяем расчет
НоваяДата = Дата(1899, 12, 30);
// Вычитаем один день, если расчет идет от 31 декабря,
// или просто используем 30-е число как базу
ДатаРезультат = НоваяДата + (Чис * 86400);
Важное замечание: В сообщениях форума предлагалось использовать 31.12.1899 и вычитать секунды. Однако стандарт де-факто для синхронизации с OADate (Excel) — это именно прибавление к 30.12.1899. Это избавляет от путаницы с високосными годами в начале века.
Рассмотрим, как модифицировать процедуру ТабличныйДокументВТаблицуЗначений, чтобы она корректно обрабатывала колонку с датой. Предположим, дата находится во второй колонке.
Для НомерСтроки = ВысотаШапки + 1 По ШаблонСДанными.ВысотаТаблицы Цикл
НоваяСтрока = Объект.Платежи.Добавить();
Для НомерКолонки = 1 По ШаблонСДанными.ШиринаТаблицы Цикл
Ячейка = ШаблонСДанными.ПолучитьОбласть(НомерСтроки, НомерКолонки, НомерСтроки, НомерКолонки).ТекущаяОбласть;
Если НомерКолонки = 2 Тогда // Наша колонка с датой
Если ТипЗнч(Ячейка.Значение) = Тип("Число") Тогда
НоваяСтрока.Дата = Дата(1899, 12, 30) + (Ячейка.Значение * 86400);
Иначе
// Если все же пришел текст, пытаемся его очистить
ЧисловыеДанные = СтрЗаменить(Ячейка.Текст, "DD", "");
Попытка
НоваяСтрока.Дата = Дата(1899, 12, 30) + (Число(ЧисловыеДанные) * 86400);
Исключение
НоваяСтрока.Дата = Дата(1, 1, 1);
КонецПопытки;
КонецЕсли;
КонецЕсли;
// ... обработка остальных колонок
КонецЦикла;
КонецЦикла;
Проанализируем еще несколько нюансов, которые помогут сделать вашу загрузку стабильнее:
ЗначениеЗаполнено(Ячейка.Значение) перед математическими операциями, чтобы избежать ошибок преобразования типов.ПолучитьИмяВременногоФайла() всегда оборачивайте удаление файла в блок Попытка...Исключение, так как файл может быть заблокирован системой.DD43966 вызовет ошибку или пустую дату. Описанный выше метод конвертации в Дата обязателен.Таким образом, мы разобрали, что проблема "странных дат" из Excel — это не ошибка программы, а разница в стандартах хранения данных. Использование СпособЧтенияЗначенийТабличногоДокумента.Значение и базовой даты 30.12.1899 позволяет легко и эффективно загружать любые данные из электронных таблиц в 1С.