При формировании печатных форм и выгрузке данных в Excel программисты часто сталкиваются с неприятной особенностью: Excel «умничает» и автоматически преобразует строки, состоящие из цифр, в числовой формат. Это критично для таких данных, как артикулы, номера счетов, штрихкоды или коды регионов, где ведущие нули (например, «034324») имеют значение. Если Excel решит, что это число, он превратит его в «34324», что делает данные недостоверными.
В этой статье мы подробно разберем, как заставить Excel сохранять строковый формат данных, используя как стандартные настройки макета 1С, так и программные хитрости.
Рассмотрим наиболее простой и надежный метод, который не требует написания сложного кода. Все манипуляции проводятся непосредственно в макете вашей обработки или документа. Выясним, почему простого указания типа Строка в параметре иногда бывает недостаточно.
Разберем по шагам, что нужно сделать в конфигураторе:
Строка.Проанализируем ситуацию: когда вы устанавливаете эти настройки, 1С при формировании файла Excel (особенно в формате XLSX) записывает в структуру документа явное указание на тип данных. Excel, видя этот маркер, перестает применять алгоритмы автоопределения и оставляет строку «как есть».
Иногда настройки макета «слетают» или данные заполняются динамически из кода. В таких случаях нужно понимать разницу между свойствами Текст и Параметры области табличного документа. Посмотрим на примере, как правильно передавать данные.
Если вы используете параметры, код может выглядеть так:
ОбластьМакета = Макет.ПолучитьОбласть("Строка");
// Если присвоить так, 1С попытается сама определить тип:
ОбластьМакета.Параметры.Артикул = "000123";
Чтобы гарантировать строковый формат при записи в ТабличныйДокумент, попробуем обратиться к свойству Текст конкретной ячейки после того, как область была получена, но до того, как она выведена в итоговый документ. Однако более эффективным считается управление через ТекущаяОбласть:
ОбластьМакета = Макет.ПолучитьОбласть("Строка");
Ячейка = ОбластьМакета.Область("Р1С2"); // Указываем координаты ячейки в области
Ячейка.СодержитЗначение = Истина;
Ячейка.ТипЗначения = Новый ОписаниеТипов("Строка");
Ячейка.Значение = "000123";
ТабДок.Вывести(ОбластьМакета);
В данном примере мы программно дублируем действия из первого решения, обеспечивая жесткую типизацию каждой ячейки.
Если штатные средства типизации не помогают (например, при сохранении в старые форматы .XLS), можно прибегнуть к «хитростям» визуального обмана Excel. Проанализируем, как добавить символ, который не позволит Excel распознать число, но останется незаметным для пользователя.
Обычный пробел в начале строки часто удаляется функцией СокрЛП() или самим Excel. Вместо него используем неразрывный пробел (код 160).
// Символы.НПП - это неразрывный пробел
ОбластьМакета.Параметры.НомерСчета = Символы.НПП + "034324";
Excel увидит в начале строки символ, который не является цифрой, и автоматически определит всю ячейку как текстовую. Визуально в ячейке этот пробел практически не виден. Еще более продвинутый вариант — использование «нулевого пробела» (символ с кодом 8203), который имеет нулевую ширину.
Выясним причину, почему иногда Excel разбивает длинные числа на группы (например, «10 000»). Это происходит из-за региональных настроек. Даже если тип установлен верно, форматная строка в 1С может влиять на то, как данные упаковываются при экспорте.
В свойствах ячейки макета найдите поле «Формат». Попробуйте прописать там ЧГ=0 (ЧГ — это группировка разрядов, 0 — ее отсутствие). Хотя это свойство числового формата, для строк, которые «прикидываются» числами, это иногда служит дополнительным сигналом для драйвера экспорта 1С не применять никаких преобразований.
Если ваша обработка выгружает данные не через метод ТабДок.Записать(), а напрямую открывает приложение Excel через COMОбъект("Excel.Application"), то подходы меняются. Рассмотрим, как установить текстовый формат программно в самом Excel.
Важно: Формат ячейки нужно устанавливать ДО того, как в нее записывается значение. Если сначала записать «001», Excel сразу превратит его в «1», и последующая смена формата уже не вернет потерянный ноль.
// Пример работы через COM
ExcelApp = Новый COMОбъект("Excel.Application");
Книга = ExcelApp.Workbooks.Add();
Лист = Книга.Worksheets(1);
// Устанавливаем текстовый формат для колонки или ячейки
// Символ "@" в Excel означает текстовый формат
Лист.Columns(1).NumberFormat = "@";
// Теперь записываем значение - ведущие нули сохранятся
Лист.Cells(1, 1).Value = "034324";
Выясним, какой формат файла лучше всего подходит для сохранения типизации. Начиная с версии платформы 8.3.10, механизмы работы с форматом XLSX (Excel 2007 и выше) были значительно улучшены.
Используйте метод:
ТабДок.Записать(ПутьКФайлу, ТипФайлаТабличногоДокумента.XLSX);
В формате XLSX (который по сути является набором XML-файлов) 1С более четко прописывает теги типов данных. Старый формат XLS (Excel 97) гораздо чаще страдает от вольных интерпретаций типов данных со стороны программы открытия.
Мы рассмотрели несколько способов сохранения строкового формата. Рекомендуемый алгоритм действий:
ТипЗначения для областей ТабличногоДокумента.NumberFormat = "@" или добавляйте неразрывный пробел Символы.НПП.Следуя этим правилам, вы обеспечите корректность выгружаемых данных и избавите пользователей от необходимости вручную исправлять форматы в Excel.