При загрузке данных из внешних источников программисты часто сталкиваются с тем, что номера телефонов или другие длинные цифровые коды превращаются в странные строки вида "7,9E+10". Это происходит из-за того, что Excel по умолчанию использует научный (экспоненциальный) формат для отображения чисел, длина которых превышает 11 знаков. Если в ячейке установлен формат Общий, Excel «сворачивает» число, и именно в таком виде 1С может его считать.
В этой статье мы подробно разберем, как бороться с этим поведением, проанализируем различные методы программного доступа к файлам и выясним, как вернуть данным их первозданный вид.
Давайте разберем ситуацию: для Excel число 89001112233 — это просто математическая величина. Чтобы сэкономить место в ячейке, программа отображает его как 8,9E+10. При чтении через COM-объект или ТабличныйДокумент система 1С получает либо это «свернутое» строковое представление, либо число с плавающей точкой, которое при попытке преобразования в обычную строку Строка(Значение) снова превращается в текст с буквой «E».
Если вы используете прямое подключение к Excel через COMОбъект, у вас есть несколько путей решения. Рассмотрим их по порядку.
Вариант А: Использование свойства Text вместо Value
Свойство Value возвращает содержимое ячейки в том типе, который определил Excel (часто это Число). Свойство Text возвращает ровно то, что пользователь видит на экране. Если в Excel колонка расширена достаточно, чтобы число отображалось полностью, Text вернет его правильно. Однако здесь есть ловушка: если колонка слишком узкая, Text вернет решетки #######.
Разберем пример кода с предварительным расширением колонок:
// Создаем объект подключения
Эксель = Новый COMОбъект("Excel.Application");
Книга = Эксель.Workbooks.Open(ПутьКФайлу);
Лист = Книга.Sheets(1);
// Важно: Автоподбор ширины, чтобы свойство .Text не вернуло решетки
Лист.Columns("A:Z").AutoFit();
// Читаем значение как текст
НомерТелефона = Лист.Cells(Строка, Колонка).Text;
Вариант Б: Принудительная установка текстового формата колонки
Мы можем программно приказать Excel считать всю колонку текстовой перед тем, как начнем перебор строк. Это гарантирует, что данные будут переданы «как есть».
// Устанавливаем текстовый формат для всей колонки (символ "@")
Лист.Columns(НомерКолонкиТелефона).NumberFormat = "@";
// Теперь чтение будет корректным
НомерТелефона = СокрЛП(Лист.Cells(Строка, НомерКолонкиТелефона).Value);
Вариант В: Использование свойства Value2
Проанализируем свойство Value2. В отличие от Value, оно не проверяет формат ячейки (например, дату или валюту) и работает быстрее. В некоторых случаях оно позволяет получить «чистое» число без лишних преобразований со стороны Excel.
Если вы используете метод ТабДок.Прочитать(ПутьКФайлу), данные попадают в ячейки табличного документа 1С. В этом случае проблема решается на стороне кода 1С с помощью функции Формат().
Даже если число пришло в экспоненциальной форме, оно остается числом. Нам нужно преобразовать его в строку, убрав группировку разрядов (пробелы между тысячами) и лишние символы.
Рассмотрим по шагам, как правильно преобразовать такое значение:
// Допустим, в переменной ЗначениеИзЭксель лежит 7.9E+10
Если ТипЗнч(ЗначениеИзЭксель) = Тип("Число") Тогда
// ЧГ=0 означает "Группировка разрядов отсутствует"
НомерТелефона = Формат(ЗначениеИзЭксель, "ЧГ=0");
КонецЕсли;
Важное уточнение про неразрывные пробелы:
В некоторых региональных настройках системы функция Формат все равно может добавить невидимые символы. Чтобы обезопасить себя, добавим очистку от неразрывных пробелов:
НомерТелефона = СтрЗаменить(Формат(ЗначениеИзЭксель, "ЧГ=0"), Символы.НПП, "");
При чтении файла Excel как базы данных (через ADODB.Connection), драйвер пытается угадать тип данных в колонке по первым нескольким строкам. Если в начале идут короткие числа, а потом длинные телефоны, драйвер может вернуть NULL или ошибку.
Чтобы этого избежать, проанализируем строку соединения. В ней обязательно должен присутствовать параметр IMEX=1. Этот параметр заставляет систему импорта воспринимать колонки со смешанными данными как текстовые.
Пример строки соединения:
СтрокаСоединения = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ПутьКФайлу + ";Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"";";
Если вы можете влиять на процесс создания файла, воспользуйтесь проверенным «хаком» профессионалов. Чтобы Excel никогда не превращал номер телефона в число, перед вводом номера нужно поставить одинарную кавычку '. Например: '79001112233.
В самой ячейке кавычка не видна, но она принудительно устанавливает текстовый тип данных. При загрузке в 1С эта кавычка автоматически отсекается, и вы получаете чистую строку.
Выясним, какой способ лучше выбрать в вашей ситуации:
Формат(Значение, "ЧГ=0").NumberFormat = "@" для всей колонки перед началом цикла чтения.ТекущаяОбласть.Текст, предварительно убедившись, что колонки достаточно широки.Использование этих подходов позволит вам избежать искажения контактных данных клиентов и обеспечит корректную работу бизнес-процессов в вашей конфигурации 1С.