При загрузке данных из файлов Excel в 1С разработчики часто сталкиваются с ситуацией, когда ячейка с датой считывается не как привычное "01.09.2023", а как целое число, например, 45175. Это не ошибка, а особенность хранения дат в Microsoft Excel. Давайте разберемся, почему так происходит и как с этим правильно работать, чтобы получить корректное значение даты в 1С.
Проанализируем ситуацию. Excel хранит любую дату как количество дней, прошедших с базовой даты — 1 января 1900 года. Так, число 1 соответствует дате 01.01.1900, число 2 — 02.01.1900, а наше число 45175 — это 6 сентября 2023 года. Наша задача — научить 1С правильно интерпретировать это число. Рассмотрим несколько способов решения этой задачи, от простого математического до наиболее современного и предпочтительного.
Это самый "прямолинейный" подход, который основан на знании того, как Excel и 1С работают с датами. Мы можем взять число из Excel и с помощью формулы преобразовать его в дату формата 1С.
Идея проста: нужно к начальной дате Excel (01.01.1900) прибавить количество дней, которое мы получили из ячейки. Поскольку 1С оперирует секундами, количество дней нужно умножить на 86400 (количество секунд в сутках).
Первоначальная формула может выглядеть так:
// Не совсем корректный вариант!
ЧислоИзExcel = 45175;
ИскомаяДата = Дата('19000101') + ЧислоИзExcel * 86400;
Однако, если выполнить этот код, мы получим дату на два дня больше ожидаемой. В нашем примере вместо 06.09.2023 получится 08.09.2023. Откуда взялась эта "таинственная" разница в два дня? У этого есть две причины, заложенные в историю Excel:
Чтобы получить правильный результат, нам нужно вычесть эти два дня из числа, полученного из Excel. Корректная формула будет выглядеть так:
ЧислоИзExcel = 45175; // Пример числа, соответствующего 06.09.2023
// Вычитаем 2 дня, чтобы компенсировать ошибку Excel и разницу в отсчете
ИскомаяДата = Дата('19000101') + (ЧислоИзExcel - 2) * 86400;
// Теперь в переменной ИскомаяДата будет правильное значение: 06.09.2023 00:00:00
Этот способ надежен и будет работать всегда, когда вы получаете из ячейки Excel именно числовое значение даты. Он особенно полезен в старых конфигурациях или в сложных случаях, когда другие методы по какой-то причине не срабатывают.
Начиная с платформы 8.3.6, в 1С появился более удобный и современный способ чтения данных из Excel, который позволяет избежать ручных математических преобразований. Весь секрет кроется во втором параметре метода Прочитать() объекта ТабличныйДокумент. В качестве готовой основы под такой алгоритм можно использовать шаблон простой обработки загрузки данных из Excel.
Рассмотрим синтаксис метода:
Прочитать(<ИмяФайла>, <СпособЧтенияЗначений>)
Параметр СпособЧтенияЗначений как раз и определяет, как 1С будет интерпретировать содержимое ячеек. У него есть два ключевых значения:
СпособЧтенияЗначенийТабличногоДокумента.Текст — это значение по умолчанию. В этом режиме 1С пытается прочитать из ячейки ее текстовое представление. То есть, если в ячейке Excel отображается "06.09.2023", то 1С прочитает именно эту строку. После этого строку можно легко преобразовать в дату стандартной функцией Дата().СпособЧтенияЗначенийТабличногоДокумента.Значение — это наиболее предпочтительный режим. Он заставляет 1С считывать не отображаемый текст, а фактическое значение ячейки с учетом ее типа. Если в ячейке Excel хранится дата, то 1С сразу поместит в ячейку табличного документа значение типа Дата.Посмотрим на пример кода, использующий этот современный подход.
ТабДок = Новый ТабличныйДокумент;
// Используем самый надежный способ чтения значений
СпособЧтения = СпособЧтенияЗначенийТабличногоДокумента.Значение;
Попытка
ТабДок.Прочитать(ИмяФайлаExcel, СпособЧтения);
Исключение
Сообщить("Не удалось прочитать файл: " + ОписаниеОшибки());
Возврат;
КонецПопытки;
// Теперь просто обходим ячейки и получаем их значения
// Предположим, дата находится в 5-й колонке
Для НомерСтроки = 2 По ТабДок.ВысотаТаблицы Цикл
// Обращаемся к свойству "Значение", а не "Текст"
ЗначениеЯчейки = ТабДок.Область(НомерСтроки, 5).Значение;
// Проверяем тип полученного значения
Если ТипЗнч(ЗначениеЯчейки) = Тип("Дата") Тогда
// Отлично! 1С уже все сделала за нас.
ДатаСотрудника = ЗначениеЯчейки;
Сообщить("Прочитана дата: " + Формат(ДатаСотрудника, "ДФ=dd.MM.yyyy"));
ИначеЕсли ТипЗнч(ЗначениеЯчейки) = Тип("Число") Тогда
// Если все же пришло число, можем использовать Способ 1 как запасной
Сообщить("Ячейка с датой прочитана как число. Используем математическое преобразование.");
ДатаСотрудника = Дата('19000101') + (ЗначениеЯчейки - 2) * 86400;
Иначе
// Ячейка может быть пустой или содержать текст
Сообщить("В ячейке не найдена дата. Пропускаем.");
КонецЕсли;
КонецЦикла;
Почему этот способ лучше? Он избавляет от "магических чисел" (таких как 2 и 86400) в коде, делает его чище, понятнее и менее подверженным ошибкам. Платформа 1С берет на себя всю работу по правильной интерпретации форматов Excel.
Для более сложных сценариев, когда требуется не просто прочитать данные, а, например, управлять самим файлом Excel, можно использовать технологию COM. Этот метод позволяет программно запустить Excel, открыть в нем книгу, обратиться к любому листу и ячейке и прочитать ее свойства. Наглядным примером работы с этой технологией служит обработка для чтения и записи области листа Excel.
Преимущества:
Недостатки:
ТабличныйДокумент.Пример кода для чтения даты через COM:
// Этот код должен выполняться на клиенте или на сервере, где установлен MS Excel
Попытка
Excel = Новый COMОбъект("Excel.Application");
Исключение
Сообщить("Microsoft Excel не установлен на данном компьютере.");
Возврат;
КонецПопытки;
Excel.Workbooks.Open(ИмяФайлаExcel);
ЛистExcel = Excel.ActiveWorkbook.Sheets(1);
// Читаем значение из ячейки A1
ЗначениеЯчейки = ЛистExcel.Cells(1, 1).Value;
Если ТипЗнч(ЗначениеЯчейки) = Тип("Дата") Тогда
Сообщить("Дата успешно прочитана через COM: " + ЗначениеЯчейки);
КонецЕсли;
// Обязательно нужно закрыть Excel, чтобы он не "завис" в процессах
Excel.Quit();
Этот способ стоит использовать только тогда, когда стандартные средства 1С не справляются с задачей или требуются расширенные возможности по взаимодействию с файлом.
Подведем итог. Для решения задачи чтения дат из Excel в 1С рекомендуется всегда в первую очередь использовать метод ТабличныйДокумент.Прочитать() с параметром СпособЧтенияЗначенийТабличногоДокумента.Значение. Это самый современный, надежный и простой способ. Если по каким-то причинам вы получаете числовое представление даты, используйте проверенную математическую формулу с вычитанием двух дней. А к COM-объекту прибегайте в исключительных случаях, требующих сложного взаимодействия с Excel.