Почему компонента Йоксель или ODBC портят числовые значения при чтении из Excel и как это исправить?

При работе с файлами формата XLS через внешние компоненты или ODBC-драйверы часто возникают ситуации, когда числовые данные считываются с избыточной точностью (например, вместо 12345,67 получается 12345,6699999999). Разберем, почему это происходит и как программно обеспечить корректную загрузку данных в 1С:Предприятие.

Причина возникновения ошибки: стандарт IEEE 754

Основная причина кроется не в компоненте Йоксель или драйвере, а в стандарте представления чисел с плавающей запятой IEEE 754, который используют практически все современные системы. Десятичные дроби не всегда могут быть точно представлены в двоичном виде. В результате, при вычислениях или преобразованиях форматов возникают микроскопические погрешности. Excel скрывает их от глаз пользователя, применяя визуальное округление, но при программном доступе мы получаем «сырое» значение с программным «хвостом» из девяток или нулей.

Способ 1: Округление на стороне 1С

Самым надежным и простым методом для большинства задач является принудительное округление полученных данных. Если вы знаете, что загружаемые числа представляют собой суммы или количества с двумя знаками после запятой, используйте стандартную функцию Окр().

Пример применения:

// Предположим, переменная ЗнЯч содержит полученное из Excel число
ТочноеЗначение = Окр(ЗнЯч, 2, 1);

Это действие отсекает лишние разряды, вызванные спецификой хранения чисел, и приводит данные к ожидаемому виду.

Способ 2: Использование COM-объекта Excel

Если вам требуется высокая точность и вы хотите получать данные именно в том виде, в котором они отображаются в ячейках Excel (игнорируя особенности хранения), лучше отказаться от прямых SQL-запросов и использовать COM-интерфейс.

Вместо обращения к свойству Value, используйте свойство Text. Оно возвращает строковое представление данных, которое видит пользователь.

Пример использования через OLEExSup:

// Получение данных через OLE
Область = Лист.Range(Лист.Cells(1, 1), Лист.Cells(10, 10));
Массив = OLEExSup.ПолучитьСвойство(Область, "Value"); // Используйте Text для получения экранного вида
Для нСтроки = Массив.GetLBound(1) По Массив.GetUBound(1) Цикл
    Для нКолонки = Массив.GetLBound(2) По Массив.GetUBound(2) Цикл
        ЗнЯч = Массив.GetValue(нСтроки, нКолонки);
        // Обработка данных
    КонецЦикла;
КонецЦикла;

Способ 3: Работа с ODBC и настройка IMEX=1

Если вы продолжаете использовать ODBCDatabase для выполнения SQL-запросов к файлам Excel, столкнетесь с проблемой типизации данных (например, смешение текста и чисел в одном столбце). Чтобы драйвер более гибко относился к типам, используйте параметр IMEX=1 в строке подключения.

Это заставляет драйвер работать в режиме импорта, анализируя содержимое строк. Для корректной конвертации типов данных внутри самого запроса можно использовать функции преобразования, такие как CStr():

// Пример использования функции CStr в запросе
ТекстЗапроса = "SELECT CStr([ИмяКолонки]), [ДругаяКолонка] FROM [Sheet1$]";

Если драйвер возвращает Null при наличии смешанных типов, используйте конструкцию IIF(IsNull(Value), 0, CStr(Value)), чтобы обработать пустые ячейки и избежать ошибок выполнения.

Рекомендации по выбору метода

1. Если объем данных небольшой — используйте Excel.Application через COM. Это самый медленный, но самый предсказуемый способ получения данных «как на экране».
2. Для массовой загрузки больших прайс-листов используйте компоненту Йоксель, но обязательно применяйте Окр() к каждому числовому значению.
3. При использовании ODBC помните, что драйвер Excel ограничен в возможностях SQL-языка (отсутствие полноценного CAST), поэтому сложные преобразования типов данных лучше переносить на сторону кода 1С после получения выборки.

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