Всем привет! Разберем частую и весьма неприятную проблему, с которой сталкиваются разработчики 1С при загрузке данных из файлов Excel. Представим ситуацию: мы читаем файл формата .xlsx с помощью штатного объекта ТабличныйДокумент. В файле есть колонки со специфическими пользовательскими форматами, например, маска #0" л", которая визуально добавляет букву «л» к числу (например, «60 л»).
При попытке получить текст ячейки с помощью конструкции вида ТабДок.ПолучитьОбласть(...).ТекущаяОбласть.Текст мы получаем искаженные данные или сдвиг разрядов (например, читает как 0,#0" л" при наличии запятой). Если же мы попробуем обратиться к свойству Значение ячейки, платформа и вовсе выдаст ошибку: «Поле объекта недоступно для чтения (Значение)». Проанализируем причину такого поведения и рассмотрим несколько надежных способов обойти это ограничение.
Выясним причину. Проблема с некорректным чтением кастомных форматов связана с тем, что встроенный механизм конвертации 1С, вызываемый при выполнении метода Прочитать(), пытается сопоставить внутренние форматы Excel с форматами самой платформы 1С.
Если маска формата ячейки нестандартная и не содержит привычных для 1С разделителей, платформа не может корректно распознать тип данных или прочитать значение. В результате свойство Значение становится недоступным, а свойство Текст возвращает «битую» строку. Изменение способа чтения на СпособЧтенияЗначенийТабличногоДокумента.Значение также не всегда спасает ситуацию.
Посмотрим на практические решения этой проблемы.
Поскольку ТабличныйДокумент все же загружает ячейку в память, мы можем попробовать программно сбросить её формат перед чтением. В некоторых случаях платформа 1С после этого пересчитывает представление ячейки и отдает «голое» число без учета пользовательской маски из Excel.
Рассмотрим пример кода:
ТабличныйДокумент = Новый ТабличныйДокумент;
ТабличныйДокумент.Прочитать(ИмяФайла);
ОбластьЯчейки = ТабличныйДокумент.Область(НомерСтроки, НомерКолонки);
// Сбрасываем кастомный формат
ОбластьЯчейки.Формат = "";
// Пробуем получить чистое значение или текст
ПрочитанныйТекст = ОбластьЯчейки.Текст;
Если сервер или клиент работает на ОС Windows и там установлен MS Office, мы можем отказаться от встроенного объекта ТабличныйДокумент и использовать COM-соединение. Этот подход позволяет обратиться напрямую к ячейке и прочитать ее свойство Value (чистое числовое значение без форматов) или Text (визуальное отображение).
Чтобы чтение не занимало много времени, рекомендуется выгружать данные целым массивом через механизм COMSafeArray. Разберем по шагам:
Эксель = Новый COMОбъект("Excel.Application");
Книга = Эксель.WorkBooks.Open(ИмяФайла);
Лист = Книга.WorkSheets(1);
// Получаем область с данными
ОбластьДанных = Лист.UsedRange;
// Выгружаем все значения в COMSafeArray за одну долю секунды
МассивЗначений = ОбластьДанных.Value.Выгрузить();
Книга.Close(Ложь);
Эксель.Quit();
При таком подходе в массиве окажутся только чистые значения без примеси пользовательских масок Excel.
Формат .xlsx — это, по сути, ZIP-архив, содержащий набор XML-файлов. В файле sheet1.xml хранятся данные листа, а в sharedStrings.xml — текстовые значения. Если читать Excel как архив через ПостроительDOM и ЧтениеXML, мы полностью обходим движок форматов Excel.
Внутри XML-структуры листа значение ячейки лежит в виде тега <v>, внутри которого всегда находится чистое, неформатированное число (например, просто 60). Этот метод работает на любых ОС (включая Linux) и не требует установленного офисного пакета.
В 1С можно подключить Excel-файл как «Внешний источник данных». Используя строку соединения с драйвером ODBC для Excel, мы делегируем обработку типов самому драйверу.
Драйвер ODBC корректно отсекает пользовательское форматирование ячеек и возвращает чистые числа. Вы сможете обращаться к листу Excel обычным запросом 1С, что очень удобно для загрузки больших объемов данных.
// Пример строки соединения для ADODB
СтрокаПодключения = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ИмяФайла + ";Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"";";
Если проблема заключается именно в специфике форматов MS Excel, можно использовать COM-объект OpenOffice (через com.sun.star.ServiceManager). Движок LibreOffice иначе парсит XML-структуру xlsx-файла и зачастую более лоялен к «битым» или сложным числовым форматам.
Значения ячеек оттуда можно забирать встроенными методами getValue() (для получения чистого числа) или getString() (для текста).
Если штатный ТабличныйДокумент в 1С спотыкается о маски ячеек Excel и не может выдать корректное Значение, не стоит пытаться извлечь данные с помощью сложных строковых функций из искаженного свойства Текст. Наиболее правильным и надежным решением будет смена технологии чтения: используйте COM-объекты (Excel или LibreOffice) для простых сценариев под Windows, либо чтение XLSX как ZIP/XML архива для высокопроизводительной и кроссплатформенной работы.