Как сохранить дату из 1С в Excel так, чтобы она распознавалась как дата и работали фильтры?

← На главную

При выгрузке отчетов или печатных форм из 1С:Предприятие в формат Excel (XLSX) программисты часто сталкиваются с неприятной особенностью: визуально дата выглядит корректно, но Excel воспринимает её как текст или «Общий» формат. В результате пользователи не могут использовать встроенные фильтры по датам (по годам, кварталам, месяцам), а арифметические операции с такими ячейками становятся невозможными без предварительного «прокликивания» каждой ячейки. Для автоматического исправления проблем с типами данных в итоговых файлах можно использовать надстройку для Excel по коррекции данных из 1С.

В этой статье мы подробно разберем, почему возникает этот эффект, и рассмотрим несколько способов решения проблемы: от простых настроек в макете до низкоуровневой правки структуры файла.

Метод 1. Настройка свойств ячейки в макете 1С

Начнем с самого простого и правильного с точки зрения платформы метода. Часто разработчики ограничиваются установкой формата ДФ=dd.MM.yyyy в свойствах ячейки макета, забывая о типизации данных. Рассмотрим, как сделать это правильно.

Проанализируем шаги настройки в конфигураторе:

Откроем макет табличного документа.
Выберем ячейку (или параметр), в которую выводится дата.
В палитре свойств найдем раздел Макет.
Установим флажок в поле Содержит значение. Это критически важный момент: без этой галочки 1С передает в Excel просто текстовое представление, которое сформировано по правилам форматирования 1С.
В поле Тип значения выберем Дата (состав даты — Дата).
В поле Формат можно указать ДФ=dd.MM.yyyy, но помните, что Excel может переопределить это под свои региональные стандарты.

При заполнении такого макета программным кодом важно передавать именно объект типа Дата, а не результат функции Формат(). Разберем пример кода:


// Неправильно: передача строки
Область.Параметры.ДатаДокумента = Формат(ТекущаяДата(), "ДФ=dd.MM.yyyy"); 

// Правильно: передача типа Дата
Область.Параметры.ДатаДокумента = ТекущаяДата();

После вывода области в ТабличныйДокумент при вызове метода Записать(Путь, ТипФайлаТабличногоДокумента.XLSX) платформа попытается сопоставить тип 1С с внутренним типом Excel.

Метод 2. Понимание структуры XLSX и причины «эффекта двойного клика»

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

Файл XLSX — это фактически ZIP-архив с XML-файлами внутри. Если мы распакуем его, то увидим:

xl/worksheets/sheet1.xml — здесь хранятся сами значения.
xl/styles.xml — здесь описываются форматы (числовой, дата, денежный).

Для того чтобы Excel сразу понял, что в ячейке дата, в sheet1.xml значение должно быть записано как число (количество дней, прошедших с 30 декабря 1899 года), а в атрибуте стиля s должен стоять индекс, ссылающийся на формат даты в styles.xml.

Метод 3. Программная корректировка стилей (OpenXML)

Если штатные средства платформы (особенно старых версий, до 8.3.15) не справляются, можно прибегнуть к «хирургическому» вмешательству. Как было отмечено в обсуждении, необходимо убедиться, что ячейка ссылается на правильный numFmtId.

В спецификации Microsoft Office Open XML существуют предопределенные идентификаторы форматов. Например:

numFmtId="14" — соответствует формату даты d/m/yyyy (или региональному аналогу).
numFmtId="15" — d-mmm-yy.
numFmtId="16" — d-mmm.

Рассмотрим, как это выглядит в xl/styles.xml. Секция cellXfs содержит описания стилей ячеек. Если мы хотим, чтобы наши даты определялись корректно, мы можем найти или создать запись:


<xf numFmtId="14" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/>

Если вы конвертируете дату в число в самой 1С перед выводом, вам обязательно нужно гарантировать, что в итоговом XLSX файле в теге ячейки <c> указан атрибут стиля, указывающий на numFmtId="14". Без этого Excel просто покажет число (например, 45292 вместо 01.01.2024).

Метод 4. Использование COM-объекта для постобработки

Разберем ситуацию, когда файл уже сохранен методом ТабДок.Записать(), но форматы в нем «слетели». Чтобы не заставлять пользователя вручную менять типы данных, можно программно открыть файл через установленный Excel и принудительно обновить ячейки. Для программной генерации файлов с точным контролем форматирования ячеек подойдёт COM-компонента вывода в Excel.

Проанализируем пример кода на языке 1С:


Попытка
    Excel = Новый COMОбъект("Excel.Application");
    Книга = Excel.Workbooks.Open(ПутьКФайлу);
    Лист = Книга.Sheets(1);
    
    // Допустим, даты находятся в колонке 3
    Колонка = Лист.Columns(3);
    Колонка.NumberFormat = "dd.mm.yyyy"; // Устанавливаем формат
    
    // Трюк для пересчета: умножаем значения на 1, чтобы Excel превратил текст в числа
    Лист.Range("Z1").Value = 1;
    Лист.Range("Z1").Copy();
    Колонка.PasteSpecial(-4163, 4); // xlPasteValues, xlPasteSpecialOperationMultiply
    
    Книга.Save();
    Книга.Close();
    Excel.Quit();
Исключение
    // Обработка ошибок, если Excel не установлен
КонецПопытки;

Важный момент: использование COM-объекта требует наличия установленного Microsoft Excel на сервере или клиенте (в зависимости от того, где выполняется код), что не всегда возможно в современных серверных архитектурах или на Linux-серверах.

Как сохранить дату из 1С в Excel так, чтобы она распознавалась как дата и работали фильтры?

Метод 1. Настройка свойств ячейки в макете 1С

Метод 2. Понимание структуры XLSX и причины «эффекта двойного клика»

Метод 3. Программная корректировка стилей (OpenXML)

Метод 4. Использование COM-объекта для постобработки

Рекомендации по выбору решения

Читайте также