Как установить нужный формат даты (ДД.ММ.ГГГГ) в Excel при выгрузке из 1С?

При работе с выгрузкой данных из 1С в Microsoft Excel через COM-объект часто возникает задача установить для ячеек определенный формат отображения данных. Это актуально как для простых отчетов, так и в случаях, когда выполняется выгрузка журнала регистрации в Excel с группировкой по дням. Особенно это касается дат. Начинающие разработчики часто сталкиваются с ситуацией, когда попытка установить привычный русский формат, например "ДД.ММ.ГГГГ", не приводит к ожидаемому результату, и дата в Excel отображается некорректно, например, как ДД.ММ.ГГ. Давайте разберемся, почему так происходит и как правильно решить эту задачу.

Анализ проблемы: почему Excel не понимает формат "ДД.ММ.ГГГГ"?

Рассмотрим типичный код, который приводит к ошибке форматирования. Подобные сложности часто возникают, когда программист реализует экспорт справочников в Excel с фильтрами. Разработчик пытается установить числовой формат ячейки, используя русскоязычные обозначения дня (ДД), месяца (ММ) и года (ГГГГ).

// ... код подключения к Excel ...
Ячейка = Лист.Cells(1, 1);
Ячейка.Value = ТекущаяДата();

// Неправильная попытка установить формат
Ячейка.NumberFormat = "ДД.ММ.ГГГГ"; 

Проблема заключается не в коде 1С, а в том, как устроен COM-интерфейс Microsoft Excel. Когда мы из 1С обращаемся к свойству Ячейка.NumberFormat, мы передаем текстовую строку непосредственно в приложение Excel. Внутренний язык макросов Excel (VBA) и его COM-модель для установки форматов ожидают строки, основанные на англоязычной локали, вне зависимости от региональных настроек вашей операционной системы или языка интерфейса Excel.

Таким образом, Excel не распознает символы "Д", "М" и "Г" как специальные коды для даты. Он воспринимает их как обычные буквы и пытается применить формат, что приводит к некорректному отображению.

Правильное решение: используем международные коды формата

Чтобы Excel нас правильно понял, необходимо использовать стандартные англоязычные коды для форматирования даты:

• d или dd — для дня (с ведущим нулем или без).
• m или mm — для месяца.
• yy или yyyy — для года (две или четыре цифры).

Таким образом, для получения желаемого формата ДД.ММ.ГГГГ, нам нужно передать в свойство NumberFormat строку "dd.mm.yyyy". Это знание будет крайне полезно, если вы готовите, например, анализ изменений конфигурации для выгрузки в xls-файл.

Посмотрим на исправленный пример кода:

// Получаем COM-объект Excel
Попытка
    Excel = Новый COMОбъект("Excel.Application");
Исключение
    Сообщить("Ошибка при запуске Excel: " + ОписаниеОшибки());
    Возврат;
КонецПопытки;

Книга = Excel.Workbooks.Add();
Лист = Книга.Worksheets(1);

// Получаем ячейку для даты
Ячейка = Лист.Cells(1, 1);

// Записываем в ячейку значение типа "Дата"
Ячейка.Value = ТекущаяДата();

// Устанавливаем ПРАВИЛЬНЫЙ формат отображения
Ячейка.NumberFormat = "dd.mm.yyyy";

// Для наглядности можно установить и другие форматы
Лист.Cells(2, 1).Value = ТекущаяДата();
Лист.Cells(2, 1).NumberFormat = "d mmmm yyyy"; // Пример: 21 октября 2023

Лист.Cells(3, 1).Value = ТекущаяДата();
Лист.Cells(3, 1).NumberFormat = "dd.mm.yy hh:mm:ss"; // Пример: 21.10.23 15:45:10

// Делаем Excel видимым для проверки результата
Excel.Visible = Истина;

После выполнения этого кода дата в ячейке A1 будет отображаться именно так, как мы хотели: "21.10.2023".

Универсальный способ найти любой формат: Макрорекордер Excel

Что делать, если вам нужен сложный или нестандартный формат, и вы не знаете его англоязычный код? Существует простой и надежный способ это выяснить — использовать встроенный в Excel макрорекордер. Давайте разберем этот процесс по шагам.

1. Включите вкладку "Разработчик" в Excel. Если ее нет на ленте, зайдите в Файл -> Параметры -> Настроить ленту и поставьте галочку напротив пункта "Разработчик".
2. Начните запись макроса. Перейдите на вкладку "Разработчик" и нажмите кнопку "Запись макроса". Появится небольшое окно, где можно задать имя макроса — можно оставить имя по умолчанию и просто нажать "OK".
3. Отформатируйте ячейку вручную. Выделите любую ячейку на листе. Нажмите на нее правой кнопкой мыши и выберите пункт "Формат ячеек...".
4. Задайте нужный формат. В открывшемся окне перейдите на вкладку "Число". В списке форматов выберите "(все форматы)". В поле "Тип" введите формат так, как вы привыкли это делать в Excel, например: ДД.ММ.ГГГГ "г." чч:мм. Нажмите "OK".
5. Остановите запись. Вернитесь на вкладку "Разработчик" и нажмите "Остановить запись".
6. Посмотрите сгенерированный код. Теперь самое интересное. Нажмите на вкладке "Разработчик" кнопку "Visual Basic" (или сочетание клавиш Alt + F11), чтобы открыть редактор VBA. В окне "Project - VBAProject" слева найдите папку "Modules" и дважды щелкните по модулю (обычно Module1).

В открывшемся окне вы увидите код вашего макроса. Он будет выглядеть примерно так:

Sub Макрос1()
'
' Макрос1 Макрос
'
    Selection.NumberFormat = "dd.mm.yyyy ""г."" hh:mm"
End Sub

Строка "dd.mm.yyyy ""г."" hh:mm" — это и есть тот самый код формата, который Excel использует внутри себя. Обратите внимание, что литерал "г." был автоматически заключен в двойные кавычки. Именно эту строку вам и нужно скопировать и вставить в свой код на 1С:

Ячейка.NumberFormat = "dd.mm.yyyy ""г."" hh:mm";

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

Важный момент: существует также свойство NumberFormatLocal, которое принимает формат в виде строки, соответствующей региональным настройкам пользователя. Однако его использование не рекомендуется, так как код станет зависимым от языка системы, на которой он выполняется. Код, написанный с использованием NumberFormat и англоязычных кодов, будет работать одинаково на любом компьютере. Это особенно критично, когда осуществляется импорт данных из различных форматов, таких как xlsx или csv, где точность интерпретации данных имеет решающее значение.