Как сохранить дату из табличного документа 1С в Excel именно как дату, а не как текст?

← На главную

При выгрузке отчетов и печатных форм из 1С в формат Microsoft Excel (xlsx) разработчики часто сталкиваются с неприятной особенностью: ячейки с датами переносятся в итоговый файл как обычные текстовые строки. При попытке выполнить фильтрацию, сортировку или математические расчеты в Excel такие данные ведут себя некорректно. Пользователям приходится вручную преобразовывать формат ячеек, что вызывает массу жалоб.

В этой статье мы подробно разберем, почему возникает эта проблема, проанализируем внутренние механизмы хранения дат в 1С и Excel, а также рассмотрим несколько эффективных способов решения задачи — от простых манипуляций со свойствами ячеек в 1С до продвинутых серверных методов без использования COM-объектов.

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

Прежде чем переходить к коду, давайте разберем физическую разницу между хранением дат в обеих системах:

В платформе 1С:Предприятие дата — это самостоятельный примитивный тип данных, который всегда хранит в себе как календарный день (год, месяц, число), так и точное время (часы, минуты, секунды).
В Microsoft Excel физического типа данных «Дата» на низком уровне XML-структуры просто не существует. Все даты в Excel хранятся как обычные вещественные числа (количество дней, прошедших с вымышленного 0 января 1900 года). То, что мы видим в ячейке как «31.12.2023» — это лишь визуальная маска, наложенная поверх системного числа 45291.

Из-за этих различий при стандартном вызове метода ТабличныйДокумент.Записать() платформа 1С пытается перестраховаться и выгружает дату в виде отформатированной строки, чтобы избежать системных ошибок округления или часовых поясов. В итоге Excel воспринимает ячейку исключительно как текстовое поле.

Рассмотрим по шагам все возможные способы решения этой проблемы.

Решение 1. Тонкая настройка свойств ячейки в табличном документе 1С

Самый простой способ, который работает непосредственно в коде 1С без привлечения сторонних инструментов — это правильная комбинация свойств ячейки табличного документа. Основная ошибка разработчиков заключается в одновременном использовании свойств СодержитЗначение и Формат.

Выясним важное правило: если у ячейки установлено свойство СодержитЗначение = Истина, и тип значения определен как Дата, платформа принудительно преобразует ее в текст при экспорте в Excel.

Чтобы дата перенеслась корректно, выполним следующую программную настройку ячеек:


// Предположим, у нас есть ОбластьМакета, в которой находится параметр ДатаДокумента
ОбластьМакета = Макет.ПолучитьОбласть("Строка");
ТекущаяЯчейка = ОбластьМакета.Параметры.ДатаДокумента; // или обращение через Область()

// Настраиваем свойства ячейки табличного документа
Ячейка = ОбластьМакета.Область("R1C2"); // Обращаемся к конкретной ячейке области
Ячейка.СодержитЗначение = Ложь; // Важно! Отключаем признак хранения типизированного значения
Ячейка.Формат = "ДФ=dd.MM.yyyy"; // Задаем маску отображения даты
Ячейка.Значение = ТекущаяДата(); // Записываем стандартную дату 1С

При такой комбинации параметров платформа транслирует формат маски ДФ=dd.MM.yyyy в соответствующий XML-код формата даты в Excel, и ячейка распознается офисным пакетом правильно.

Решение 2. Математическая конвертация даты в число Excel (серверный обходной путь)

Если стандартный механизм форматирования платформы все равно дает сбои (что иногда случается на старых версиях платформы или в специфических конфигурациях на Linux-серверах), мы можем применить математический подход. Переведем дату в числовой эквивалент, который Excel гарантированно поймет.

Для этого нам нужно рассчитать разницу в днях между нашей датой и базовой датой Excel (01.01.1900). При этом важно учесть известную историческую ошибку Excel (баг совместимости с Lotus 1-2-3), из-за которой 1900 год ошибочно считается високосным. Это сдвигает все расчеты на один день для дат, начиная с 1 марта 1900 года.

Напишем функцию конвертации даты 1С в число Excel:


Функция ПреобразоватьДатуВЧислоExcel(ВходнаяДата) Экспорт
    Если Не ЗначениеЗаполнено(ВходнаяДата) Тогда
        Возврат 0;
    КонецЕсли;
    
    НачалоЭпохиExcel = Дата(1900, 1, 1);
    СекундВСутках = 86400;
    
    // Разница в секундах между датами
    РазницаВСекундах = ВходнаяДата - НачалоЭпохиExcel;
    КоличествоДней = РазницаВСекундах / СекундВСутках;
    
    // Учитываем баг Excel с невисокосным 1900 годом для всех дат после 28.02.1900
    Если ВходнаяДата > Дата(1900, 2, 28) Тогда
        КоличествоДней = КоличествоДней + 2; 
    Иначе
        КоличествоДней = КоличествоДней + 1;
    EndIf;
    
    Возврат Цел(КоличествоДней);
КонецФункции

Теперь применим эту функцию при заполнении табличного документа:


ЧисловоеЗначениеДаты = ПреобразоватьДатуВЧислоExcel(ВыгружаемаяДата);

Ячейка = ОбластьМакета.Область("R1C2");
Ячейка.СодержитЗначение = Истина;
Ячейка.ТипЗначения = Новый ОписаниеТипов("Число");
Ячейка.Значение = ЧисловоеЗначениеДаты;
Ячейка.Формат = "ЧЦ=10; ЧДЦ=0; ЧГ=0"; // Выводим как обычное целое число

После выгрузки такого файла в Excel пользователь увидит число (например, 45291). Чтобы превратить его в дату, достаточно будет выделить колонку и изменить ее формат на «Дата». Плюс данного метода в том, что данные гарантированно не превратятся в нередактируемый текст.

Решение 3. Прямая модификация структуры XLSX как ZIP-архива (OpenXML)

Рассмотрим самый прогрессивный и технологичный вариант, который часто применяется в крупных энтерпрайз-системах. Файл формата .xlsx — это обычный ZIP-архив, содержащий набор XML-документов. Мы можем программно распаковать его прямо на сервере (даже под Linux без установленного Excel), изменить XML-описание стилей и упаковать обратно.

Проанализируем алгоритм этого метода:

Сохраняем табличный документ в файл с помощью стандартного метода ТабличныйДокумент.Записать(ПутьКФайлу, ТипФайлаТабличногоДокумента.XLSX).
Используем объект 1С ЧтениеZipФайла для распаковки полученного файла во временную директорию.
Находим файл описания листов (например, xl/worksheets/sheet1.xml) и файл стилей xl/styles.xml.
С помощью объектов ЧтениеXML и ЗаписьXML (или ПостроительDOM) вносим изменения: удаляем у ячеек с датами атрибут t="s" (который указывает, что значение текстовое) и заменяем строковые значения на числовые идентификаторы дат.
Записываем файлы обратно и архивируем их с помощью объекта ЗаписьZipФайла.

Этот метод полностью исключает влияние платформенных ограничений 1С на итоговый файл и дает на выходе «чистокровный» Excel-документ с аппаратной поддержкой типов данных.

Решение 4. Постобработка файла через COM-соединение (если Excel установлен на клиенте)

Если формирование отчета происходит на стороне клиента под управлением ОС Windows с установленным пакетом Microsoft Office, мы можем прибегнуть к автоматизации через COM-объект. Это решает задачу буквально в несколько строк кода за счет вызова внутренних макросов Excel.

Посмотрим на пример реализации кода:


ПутьКФайлу = "C:\Reports\Report.xlsx";
ТабДок.Записать(ПутьКФайлу, ТипФайлаТабличногоДокумента.XLSX);

Попытка
    ExcelПриложение = Новый COMОбъект("Excel.Application");
    ExcelПриложение.Visible = Ложь;
    ExcelПриложение.DisplayAlerts = Ложь;
    
    Книга = ExcelПриложение.Workbooks.Open(ПутьКФайлу);
    Лист = Книга.Sheets(1);
    
    // Самый быстрый способ заставить Excel перечитать текстовые даты как системные:
    // Производим фиктивную замену точек в датах на точки. Excel принудительно выполнит парсинг.
    Лист.Cells.Replace(".", ".", 2); // 2 соответствует xlPart
    
    Книга.Save();
    Книга.Close();
    ExcelПриложение.Quit();
Исключение
    Сообщить("Не удалось запустить Excel для постобработки данных: " + ОписаниеОшибки());
    Если ЗначениеЗаполнено(ExcelПриложение) Тогда
        ExcelПриложение.Quit();
    КонецЕсли;
КонецПопытки;

Использование метода Replace(".", ".") заставляет движок Excel пересчитать текстовое содержимое ячеек. Если строка соответствует формату даты, принятому в региональных настройках операционной системы, Excel моментально конвертирует ее в полноценный тип даты.

Решение 5. Лайфхаки для пользователей (когда файл уже сохранен)

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

Вариант А. Инструмент «Текст по столбцам»

Выделите всю колонку с текстовыми датами в Excel.
Перейдите на вкладку верхнего меню «Данные» и выберите пункт «Текст по столбцам».
В открывшемся мастере на первом и втором шаге просто нажимайте кнопку «Далее», ничего не меняя.
На третьем шаге в блоке «Формат данных столбца» переключите тумблер в положение «Дата: ДМГ».
Нажмите кнопку «Готово». Весь столбец мгновенно преобразуется в реальные даты.

Вариант Б. Специальная вставка (сложение с нулем)

В любой пустой ячейке листа Excel напишите цифру 0 и скопируйте эту ячейку (Ctrl+C).
Выделите диапазон ячеек с вашими текстовыми датами.
Нажмите правой кнопкой мыши и выберите пункт «Специальная вставка».
В разделе «Операция» выберите пункт «Сложить» и нажмите «ОК».
Прибавив ноль, Excel преобразует текст в числовые коды дат. После этого пользователю останется лишь назначить диапазону формат отображения «Дата».

Мы подробно разобрали все технические нюансы выгрузки дат из 1С в Excel. Выбирайте наиболее подходящий метод исходя из архитектуры вашего решения (наличие Windows/Linux на сервере, требования к производительности и необходимость автономной работы системы) и делайте ваши отчеты максимально удобными для конечных пользователей!