При выгрузке отчетов и печатных форм из программы 1С в формат Excel разработчики и пользователи часто сталкиваются со следующей проблемой: числовые данные отображаются на листе Excel как текстовые строки или получают тип «Общий». В результате с ними невозможно выполнять стандартные арифметические операции (складывать, вычислять среднее значение или использовать в формулах) без предварительной конвертации. В этой статье мы подробно разберем, почему возникает эта проблема, и рассмотрим несколько эффективных способов ее решения — от простых визуальных хитростей до профессиональных методов программирования.
Прежде чем перейти к методам решения, давайте проанализируем ситуацию и поймем логику работы платформы 1С и программы Microsoft Excel при импорте данных. Когда табличный документ сохраняется в файл формата .xls или .xlsx, 1С передает данные ячеек в соответствии с их свойствами.
Основные причины, по которым число превращается в текст:
Формат), Excel запишет ее как текст.Рассмотрим забавный, но очень действенный фокус, который пришел в современную платформу 1С еще со времен версии 7.7. Если выгружаемое значение по своей сути является строкой, содержащей цифры и правильный разделитель (запятую), мы можем «заставить» Excel распознавать его как число, просто изменив выравнивание ячейки в макете.
Разберем по шагам, как применить это решение в конфигураторе:
Когда Excel импортирует документ, выровненный по правому краю текст, состоящий из цифр и запятой, автоматически преобразуется внутренним парсером в полноценное число. Это простое и элегантное решение, не требующее изменения программного кода.
Хотя трюк с выравниванием работает во многих ситуациях, профессиональный стандарт разработки требует явного указания типов данных. Самый надежный способ гарантировать, что число останется числом — это использование свойств СодержитЗначение, ТипЗначения и Значение вместо простого заполнения свойства Текст.
Давайте посмотрим на пример правильной настройки ячейки в макете:
Теперь при выводе этой области в табличный документ программным путем мы должны заполнять именно свойство Значение ячейки, передавая туда переменную числового типа, а не форматированную строку.
Рассмотрим пример кода, реализующий этот подход:
// Получаем область макета
ОбластьСтрока = Макет.ПолучитьОбласть("Строка");
// Находим нужную ячейку в области
ЯчейкаСумма = ОбластьСтрока.Область("R1C3"); // или обращение по имени ячейки
// Настраиваем свойства ячейки программно (если они не настроены в макете)
ЯчейкаСумма.СодержитЗначение = Истина;
ЯчейкаСумма.ТипЗначения = Новый ОписаниеТипов("Число", Новый КвалификаторыЧисла(15, 2));
// Присваиваем ЧИСЛОВОЕ значение, а не строку!
ЯчейкаСумма.Значение = Выборка.СуммаОборота;
// Выводим область в результирующий табличный документ
ТабличныйДокумент.Вывести(ОбластьСтрока);
При таком подходе платформа 1С при сохранении файла в Excel запишет данные с жестким указанием числового типа метаданных. В результате Excel откроет файл без каких-либо предупреждений, и с числами сразу можно будет производить любые математические расчеты.
Часто проблема возникает из-за использования некорректных разделителей групп (тысяч). Пробел, разделяющий миллионы и тысячи, воспринимается программой Excel как нетекстовый символ, из-за чего число «ломается». Чтобы избежать этого, мы можем отключить группировку триад в форматной строке ячейки макета.
Проанализируем настройку формата ячейки:
ЧГ=0).Сформированная форматная строка должна выглядеть примерно так: ЧЦ=15; ЧДЦ=2; ЧГ=0. Отсутствие группировки позволяет передавать «чистые» цифры, которые Excel с легкостью преобразует в числовой формат при открытии.
Если табличный документ генерируется сложным образом или вам необходимо гарантировать стопроцентную совместимость на компьютерах пользователей с установленным MS Excel, можно применить постобработку сохраненного файла через COM-объект. Этот метод подходит для работы на Windows-серверах и клиентских ПК.
Рассмотрим пример кода, который открывает сохраненный файл Excel и принудительно задает числовой формат для нужного диапазона ячеек:
// Сохраняем табличный документ во временный файл Excel
ПутьКФайлу = ПолучитьИмяВременногоФайла("xlsx");
ТабличныйДокумент.Записать(ПутьКФайлу, ТипФайлаТабличногоДокумента.XLSX);
Попытка
// Создаем COM-объект Excel.Application
ExcelПриложение = Новый COMОбъект("Excel.Application");
ExcelПриложение.Visible = Ложь;
ExcelПриложение.DisplayAlerts = Ложь;
// Открываем сохраненную книгу
Книга = ExcelПриложение.Workbooks.Open(ПутьКФайлу);
Лист = Книга.Sheets(1);
// Определяем заполненный диапазон (например, колонку C, начиная со 2-й строки)
ПоследняяСтрока = Лист.UsedRange.Rows.Count;
Диапазон = Лист.Range("C2:C" + ПоследняяСтрока);
// Задаем числовой формат (например, с двумя знаками после запятой)
Диапазон.NumberFormat = "0,00";
// Сохраняем изменения и закрываем Excel
Книга.Save();
Книга.Close();
ExcelПриложение.Quit();
Исключение
ЗаписьЖурналаРегистрации("Экспорт в Excel", УровеньЖурналаРегистрации.Предупреждение, , , ОписаниеОшибки());
Если Не ExcelПриложение = Неопределено Тогда
ExcelПриложение.Quit();
КонецЕсли;
КонецПопытки;
Использование свойства NumberFormat гарантирует, что даже если Excel изначально сомневался в типе данных ячейки, он принудительно переведет ее в числовой вид.
Давайте сопоставим все методы, чтобы вам было проще выбрать оптимальный для вашей задачи:
Применяя данные подходы, вы навсегда избавите пользователей ваших отчетов от необходимости вручную преобразовывать текстовые ячейки в числа в Microsoft Excel.