При автоматизации загрузки данных из внешних источников программисты 1С часто сталкиваются с неприятной особенностью: числа в файлах Excel, которые выглядят как целые или аккуратно округленные до двух знаков, после считывания превращаются в «длинные» дроби. Например, сумма 8733,00 превращается в 8732,99999999998. Это создает серьезные проблемы при проверке итогов, сравнении сумм и записи данных в регистры. В этой статье мы подробно разберем, почему это происходит и как правильно обрабатывать такие данные, используя встроенные инструменты платформы 1С.
Прежде чем переходить к коду, давайте проанализируем ситуацию с технической точки зрения. Основная причина кроется не в ошибке 1С, а в спецификации IEEE 754, которую использует Microsoft Excel для хранения и вычисления чисел с плавающей точкой. Компьютеры хранят числа в двоичной системе, и многие десятичные дроби не могут быть представлены в ней абсолютно точно. В результате возникают микроскопические погрешности на 15–16 знаке после запятой.
Когда мы используем метод Прочитать() объекта ТабличныйДокумент, система пытается интерпретировать эти внутренние значения. Даже если в самом Excel настроено отображение «2 знака после запятой», в «сырых» данных файла (особенно формата .xlsx) может храниться то самое неточное значение. Разберем, как с этим бороться на стороне 1С.
Часто разработчики используют режим СпособЧтенияЗначенийТабличногоДокумента.Текст, надеясь получить число именно в том виде, в котором оно отображается в ячейке. Однако, как показывает практика, это не всегда спасает от особенностей формата XML внутри файла Excel. Рассмотрим более надежный подход — чтение типизированного значения с последующей обработкой в коде 1С.
Посмотрим на пример кода, который позволяет считать файл более адекватно:
ТабДок = Новый ТабличныйДокумент;
// Читаем файл, указывая способ чтения "Значение"
ТабДок.Прочитать(ПутьКФайлу, СпособЧтенияЗначенийТабличногоДокумента.Значение);
Для Строка = 1 По ТабДок.ВысотаТаблицы Цикл
ТекущееЗначение = ТабДок.Область(Строка, НомерКолонки).Содержимое;
Если ТипЗнч(ТекущееЗначение) = Тип("Число") Тогда
// Принудительно округляем до нужной точности (например, до 2 знаков)
// Используем режим Окр15как20 для финансовых расчетов
ЧистаяСумма = Окр(ТекущееЗначение, 2, РежимОкругления.Окр15как20);
// Теперь ЧистаяСумма будет ровно 8733 вместо 8732.99...
КонецЕсли;
КонецЦикла;
Использование метода Окр() является обязательным этапом при загрузке любых финансовых данных из Excel. Это гарантирует, что «хвосты», возникшие из-за специфики хранения Double Precision в двоичном виде, будут отсечены.
Если у вас есть возможность влиять на процесс подготовки файлов (например, это внутренние отчеты компании), можно решить проблему «на берегу». В параметрах Excel существует опция, которая заставляет программу сохранять значения ровно с той точностью, которая отображается на экране.
Проанализируем шаги по настройке файла:
После этой манипуляции Excel принудительно округлит внутренние значения ячеек до формата отображения, и при чтении в 1С числа будут приходить «чистыми».
Для тех случаев, когда файлы огромны, а скорость чтения критична (например, на серверах под Linux, где нет COM-объектов), можно заглянуть «под капот» файла. Напомним, что .xlsx — это обычный ZIP-архив с набором XML-файлов. Если мы распакуем его, то в папке xl/worksheets/ найдем файлы листов (например, sheet1.xml).
Разберем структуру данных в XML:
<row r="4">
<c r="B4" s="1">
<v>8732.9999999999982</v>
</c>
</row>
Как мы видим, тег <v> хранит то самое неточное число. Если вы пишете собственный парсер на основе ЧтениеXML, крайне важно применять округление сразу при получении значения из узла. Это позволит избежать накопления ошибок при программном сложении загруженных данных.
Рассмотрим важные обновления платформы, которые упростили жизнь разработчикам. Начиная с версии 8.3.10, объект ТабличныйДокумент получил значительное развитие в части работы с книгами Excel:
Прочитать() позволяет обращаться к конкретным страницам книги.ПакетТабличныхДокументов.Пример записи нескольких листов в один файл .xlsx:
Пакет = Новый ПакетТабличныхДокументов;
// Создаем первый лист
Лист1 = Новый ТабличныйДокумент;
Лист1.Область("R1C1").Текст = "Данные продаж";
// Добавляем в пакет, указывая имя листа
Пакет.Состав.Добавить(Лист1, "Продажи");
// Создаем второй лист
Лист2 = Новый ТабличныйДокумент;
Лист2.Область("R1C1").Текст = "Данные остатков";
Пакет.Состав.Добавить(Лист2, "Остатки");
// Сохраняем весь пакет как один файл Excel
Пакет.Записать(ПутьКФайлу, ТипФайлаПакетТабличныхДокументов.XLSX);
Подводя итог, проанализируем лучшие практики работы с Excel в 1С:
Окр(Значение, 2) при получении числовых данных из внешних файлов. Никогда не доверяйте «визуальной» точности Excel.ТабличныйДокумент.Прочитать(), так как он работает без запуска тяжелого процесса excel.exe.8733 превращается в 8732.99), проверяйте настройки книги Excel «Точность как на экране».Соблюдение этих правил позволит вам создавать надежные механизмы обмена данными, которые не будут давать сбоев из-за разницы в форматах хранения чисел.