При автоматизации загрузки данных из внешних файлов Microsoft Excel в систему 1С программисты часто сталкиваются с неприятной особенностью: вместо корректных дат или длинных чисел в информационную базу попадает строка, состоящая из символов решетки #######. Разберем природу этой проблемы и рассмотрим несколько эффективных способов ее решения, от простого изменения свойств обращения к ячейке до программного управления интерфейсом Excel.
Прежде всего, проанализируем ситуацию с точки зрения механизмов работы Microsoft Excel. Символы решетки в ячейке — это не реальное значение данных, а визуальный эффект. Excel отображает их в том случае, если содержимое ячейки (чаще всего дата или число) не помещается в текущую ширину колонки. Когда мы обращаемся к ячейке через COM-объект, используя свойство .Text, мы получаем именно то, что «видит» пользователь на экране. Если колонка узкая — 1С получит решетки. Если колонка широкая — 1С получит строку с датой.
Рассмотрим по шагам, как можно обойти это ограничение, не заставляя пользователей вручную править каждый файл перед загрузкой.
Самый простой и правильный метод — изменить свойство, через которое мы считываем данные. В объектной модели Excel свойство .Text возвращает отформатированную строку, а свойство .Value возвращает реальное значение ячейки в соответствующем типе данных (Дата, Число, Строка).
Посмотрим на пример кода, который часто вызывает ошибку:
// Ошибочный вариант, возвращающий решетки при узкой колонке
ДатаД = СокрЛП(ExcelЛист.Cells(Ном, НомДатаТранз).Text);
А теперь проанализируем исправленный вариант:
// Правильный вариант, возвращающий значение даты
ДатаД = ExcelЛист.Cells(Ном, НомДатаТранз).Value;
При использовании .Value 1С получает объект типа Дата, и ширина колонки в Excel больше не имеет значения. Это наиболее стабильный метод для работы с типизированными данными.
В некоторых случаях свойство .Value может зависеть от региональных настроек системы. Если вы хотите получить максимально «сырые» данные, рассмотрим использование свойства .Value2. Разберем подробнее: в отличие от стандартного значения, .Value2 не преобразует данные в формат даты или валюты Excel, а выдает их в исходном виде.
Для дат это будет числовое представление (количество дней, прошедших с 1 января 1900 года). Несмотря на кажущуюся сложность, это избавляет от любых проблем с форматированием. Преобразовать такое число в дату 1С можно простым расчетом, но чаще всего достаточно обычного .Value.
Если по какой-то причине вам критично использовать именно свойство .Text (например, вам нужно получить дату именно в том строковом формате, в котором она введена), мы можем программно «раздвинуть» колонки перед чтением данных. Разберем, как это сделать правильно, чтобы Excel не задавал лишних вопросов при закрытии файла.
Воспользуемся методом AutoFit, который автоматически подбирает ширину колонки по содержимому:
// Выбираем нужную колонку по индексу и применяем автоподбор ширины
ExcelЛист.Columns(НомДатаТранз).AutoFit();
Однако здесь возникает проблема: после изменения ширины Excel помечает книгу как измененную. При закрытии приложения через Excel.Quit() пользователь может увидеть диалоговое окно с просьбой сохранить файл. Чтобы этого избежать, проанализируем два приема:
Истина. Тогда Excel «подумает», что файл уже сохранен, и закроется без вопросов.DisplayAlerts.Посмотрим на пример реализации:
ExcelПриложение = Новый COMОбъект("Excel.Application");
ExcelПриложение.DisplayAlerts = Ложь; // Отключаем все диалоговые окна
// ... работа с файлом, чтение данных ...
ExcelКнига.Saved = Истина; // Помечаем книгу как сохраненную
ExcelПриложение.Quit();
Если работа через COM-объект кажется слишком медленной или нестабильной, рассмотрим встроенный механизм платформы 1С. Метод Прочитать объекта ТабличныйДокумент позволяет загрузить данные из Excel напрямую, не запуская само приложение Microsoft Excel в фоне. Это полностью исключает проблему «решеток», так как 1С считывает структуру файла на уровне данных.
Разберем пример кода для этого метода:
ТабДок = Новый ТабличныйДокумент;
ТабДок.Прочитать(ПутьКФайлу, СпособЧтенияТабличногоДокумента.Excel);
Для НомерСтроки = 1 По ТабДок.ВысотаТаблицы Цикл
// Читаем значение из ячейки напрямую
ЗначениеДаты = ТабДок.Область(НомерСтроки, НомДатаТранз).Текст;
// Или используем типизированное значение, если оно определено
ЗначениеДата = ТабДок.Область(НомерСтроки, НомДатаТранз).Содержимое;
КонецЦикла;
Этот способ работает значительно быстрее, особенно на больших объемах данных, и не требует установленного Microsoft Office на сервере или компьютере пользователя.
Мы проанализировали ситуацию, когда визуальное представление данных в Excel мешает их корректной загрузке в 1С. Резюмируя вышесказанное, можно дать следующие рекомендации:
Применяя эти подходы, вы обеспечите стабильную работу своих обработок по загрузке данных, независимо от того, в каком виде (узкие или широкие колонки) присылают файлы конечные пользователи или внешние системы.