Как сохранить дату в ячейку Excel (XLS) из 1С с сохранением типа данных?

← На главную

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

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

Причина проблемы: Фича или баг?

Выясним причину такого поведения. Штатный механизм платформы 1С при вызове метода Записать() в формате ТипФайлаТабличногоДокумента.XLS формирует файл в стандарте BIFF8. На протяжении многих лет поведение платформы остается неизменным: данные в ячейках, которые мы видим как даты, передаются в Excel как строковые литералы. Даже если у ячейки установлено свойство СодержитЗначение = Истина и задан ТипЗначения = Новый ОписаниеТипов("Дата"), конвертер часто игнорирует эти метаданные в угоду визуальной идентичности документа.

Решение 1: Преобразование даты в числовой формат Excel

Рассмотрим внутреннюю логику Excel. В этой программе даты хранятся не как строки, а как вещественные числа. За точку отсчета (ноль) принято 30 декабря 1899 года. Дробная часть числа отвечает за время (часы, минуты, секунды). Чтобы обмануть стандартный экспорт, мы можем рассчитать числовое представление даты и передать его в ячейку, предварительно настроив формат отображения.

Посмотрим на пример кода, реализующего расчет «сериальной» даты:


Функция ПреобразоватьДату1С_В_Excel(Дата1С)
    // Excel считает дни с 30.12.1899
    ДатаНачалаЭпохи = '18991230000000';
    СекундВСутках = 86400;
    
    ЧислоДней = (НачалоДня(Дата1С) - ДатаНачалаЭпохи) / СекундВСутках;
    // Если нужно время, добавляем дробную часть
    СекундПрошло = Дата1С - НачалоДня(Дата1С);
    ЧислоВремя = СекундПрошло / СекундВСутках;
    
    Возврат ЧислоДней + ЧислоВремя;
КонецФункции

Применив этот метод, мы записываем в ячейку число, а в свойствах ячейки ТабличногоДокумента устанавливаем Формат типа ДФ=dd.MM.yyyy. Однако стоит помнить, что старый формат .xls все равно может воспринять это как число, если не использовать дополнительную обработку.

Решение 2: Использование COM-объекта Excel.Application

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

Разберем по шагам алгоритм действий:

Сохраняем ТабличныйДокумент в файл .xls.
Инициализируем COMОбъект("Excel.Application").
Открываем созданный файл.
Находим нужные области (колонки или ячейки) и устанавливаем им свойство NumberFormat.
Сохраняем и закрываем книгу.

Пример кода для постобработки файла:


Процедура ИсправитьФорматДатВExcel(ПутьКФайлу)
    Попытка
        Excel = Новый COMОбъект("Excel.Application");
        Excel.DisplayAlerts = Ложь;
        Книга = Excel.Workbooks.Open(ПутьКФайлу);
        Лист = Книга.Sheets(1);
        
        // Допустим, даты находятся в колонке C (номер 3)
        // Устанавливаем формат для всей колонки со второй строки по тысячную
        ОбластьДат = Лист.Range("C2:C1000");
        ОбластьДат.NumberFormat = "dd.mm.yyyy"; 
        
        // Принудительно заставляем Excel пересчитать значения как даты
        // (иногда требуется "толкнуть" ячейки через Value = Value)
        ОбластьДат.Value = ОбластьДат.Value;
        
        Книга.Save();
        Книга.Close();
        Excel.Quit();
    Исключение
        Сообщить("Ошибка при работе с Excel: " + ОписаниеОшибки());
        Если ЗначениеЗаполнено(Excel) Тогда
            Excel.Quit();
        КонецЕсли;
    КонецПопытки;
КонецПроцедуры

Решение 3: Запись через ADO (ActiveX Data Objects)

Если нам нужно создать файл с нуля и гарантировать типы данных без использования тяжеловесного Excel.Application, мы можем рассмотреть технологию ADO. В этом случае файл .xls воспринимается как база данных, а листы — как таблицы.

Выясним преимущества этого метода: он работает значительно быстрее COM-автоматизации и позволяет использовать SQL-запросы. При создании таблицы мы явно указываем тип поля DATETIME.


// Пример строки соединения для провайдера Jet
СтрокаСоединения = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ПутьКФайлу + ";Extended Properties=""Excel 8.0;HDR=YES;""";
Connection = Новый COMОбъект("ADODB.Connection");
Connection.Open(СтрокаСоединения);

// Создаем структуру таблицы
Command = Новый COMОбъект("ADODB.Command");
Command.ActiveConnection = Connection;
Command.CommandText = "CREATE TABLE [Отчет] ([Номенклатура] MEMO, [ДатаОтгрузки] DATETIME)";
Command.Execute();

// Вставляем данные
Command.CommandText = "INSERT INTO [Отчет] ([Номенклатура], [ДатаОтгрузки]) VALUES ('Товар 1', '2023-10-25')";
Command.Execute();

Connection.Close();

Решение 4: Пересохранение через промежуточный формат XLSX

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

Сохраните документ в формате XLSX. В этом формате даты с большой долей вероятности определятся корректно.
Если клиенту критически нужен именно старый XLS, используйте консольные утилиты конвертации (например, из пакета LibreOffice или специализированные Python-скрипты), чтобы перегнать XLSX в XLS.

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