При интеграции 1С с внешними файлами Microsoft Excel программисты часто сталкиваются с тем, что типы данных интерпретируются некорректно. Одной из самых распространенных проблем является ситуация, когда колонка, визуально содержащая даты, считывается в 1С как обычная строка. Это блокирует возможность выполнения арифметических операций с датами, их корректную сортировку и запись в реквизиты объектов метаданных. В данной статье мы подробно разберем, почему это происходит, и рассмотрим несколько эффективных способов решения проблемы.
Рассмотрим стандартный сценарий получения данных через COM-соединение, который часто используется в типовых и самописных обработках. Проанализируем типичный фрагмент кода:
Попытка
Excel = Новый COMОбъект("Excel.Application");
WB = Excel.Workbooks.Open(ПутьКФайлу);
WS = WB.Worksheets(1);
arr = WS.UsedRange.Value;
WB.Close(0);
Исключение
Предупреждение("Внимание! Файл не открыт.");
Возврат;
КонецПопытки;
В данном примере используется свойство Value объекта UsedRange. Проблема заключается в том, что при обращении к Value Excel пытается самостоятельно определить тип данных, основываясь на региональных настройках операционной системы и формате ячейки. Если в колонке встречается хотя бы одно текстовое значение (например, заголовок) или если ячейки имеют формат "Общий" или "Текстовый", Excel может передать весь массив данных как строки. В итоге в 1С мы получаем значение типа Строка, которое невозможно преобразовать функцией Дата().
Если данные уже получены в виде строки (например, "15.05.1990"), наиболее прямолинейным способом будет написание функции-парсера. Рассмотрим алгоритм, который разбивает строку на составляющие по разделителю (точке) и формирует объект типа Дата.
Для начала создадим вспомогательную функцию для выделения частей даты:
Функция элВыделитьСловоТочка(ИсходнаяСтрока)
Буфер = СокрЛ(ИсходнаяСтрока);
ПозицияТочки = Найти(Буфер, ".");
Если ПозицияТочки < 2 Тогда
Результат = Буфер;
ИсходнаяСтрока = "";
Возврат Результат;
КонецЕсли;
ВыделенноеСлово = СокрЛП(Лев(Буфер, ПозицияТочки - 1));
ИсходнаяСтрока = Сред(Буфер, ПозицияТочки + 1);
Возврат ВыделенноеСлово;
КонецФункции
Теперь разберем основной блок кода, который преобразует полученную из Excel строку ДатаСтр0 в полноценную дату 1С:
ДатаСтр = ДатаСтр0;
День_Стр = элВыделитьСловоТочка(ДатаСтр);
Месяц_Стр = элВыделитьСловоТочка(ДатаСтр);
Год_Стр = элВыделитьСловоТочка(ДатаСтр);
// Обработка случая, если год указан в сокращенном или некорректном формате
Если СтрДлина(Год_Стр) > 4 Тогда
Если ((КодСимвола(Год_Стр, 3) >= 48)) И ((КодСимвола(Год_Стр, 3) <= 57)) Тогда
Год_Стр = Лев(Год_Стр, 4);
Иначе
Год_Стр = Лев(Год_Стр, 2);
КонецЕсли;
КонецЕсли;
Попытка
День_Числ = Число(День_Стр);
Месяц_Числ = Число(Месяц_Стр);
Год_Числ = Число(Год_Стр);
// Коррекция двухзначного года
Если Год_Числ < 100 Тогда
Если Год_Числ < 30 Тогда // Условная граница веков
Год_Числ = Год_Числ + 2000;
Иначе
Год_Числ = Год_Числ + 1900;
КонецЕсли;
КонецЕсли;
РезультатДата = Дата(Год_Числ, Месяц_Числ, День_Числ);
Исключение
Сообщить("Ошибка преобразования даты: " + ДатаСтр0);
КонецПопытки;
Преимущество этого метода в том, что он работает даже с "грязными" данными, где формат ячеек в Excel был принудительно установлен как текстовый.
Проанализируем более профессиональный подход к работе с COM-объектом. У объекта Range в Excel есть менее известное свойство — Value2. В отличие от Value, оно не использует типы данных Currency и Date. Вместо этого оно возвращает дату как Serial Date — число с плавающей точкой, представляющее количество дней, прошедших с 30 декабря 1899 года.
Рассмотрим, почему это выгодно. Получая число вместо строки, мы избавляемся от проблем с региональными настройками (разделители, порядок следования дня и месяца). Выясним, как преобразовать это число в дату 1С:
// Предположим, из Excel через Value2 получено число 45100
ЧислоИзЭксель = WS.Cells(строкаТП, 4).Value2;
Если ТипЗнч(ЧислоИзЭксель) = Тип("Число") Тогда
// В 1С дата - это количество секунд с 0001 года.
// В Excel - количество дней с 1899 года.
ДатаНачалаЭксель = '18991230000000';
РезультатДата = ДатаНачалаЭксель + (ЧислоИзЭксель * 86400); // 86400 - секунд в сутках
КонецЕсли;
Использование Value2 — это самый надежный способ при работе через COM, так как он исключает влияние строковых форматов на стороне Excel.
Если ваша конфигурация работает на платформе 8.3.6 и выше, мы рекомендуем отказаться от COM-соединения в пользу встроенных механизмов 1С. Объект ТабличныйДокумент позволяет прочитать файл Excel напрямую без установленного Microsoft Office на сервере.
Разберем, как это работает:
ТабДок = Новый ТабличныйДокумент;
ТабДок.Прочитать(ПутьКФайлу, СпособЧтенияЗначенийТабличногоДокумента.Значение);
Для НомерСтроки = 1 По ТабДок.ВысотаТаблицы Цикл
ТекущееЗначение = ТабДок.Область(НомерСтроки, 4).Текст; // Для вывода
ИстинноеЗначение = ТабДок.Область(НомерСтроки, 4).Значение; // Для получения типа Дата
Если ТипЗнч(ИстинноеЗначение) = Тип("Дата") Тогда
// 1С автоматически распознает формат даты Excel
ДатаРождения = ИстинноеЗначение;
КонецЕсли;
КонецЦикла;
Этот метод работает значительно быстрее COM-объекта и гораздо корректнее обрабатывает типы данных "из коробки".
Иногда преобразование в дату не удается из-за невидимых символов. Проанализируем ситуацию: в ячейке Excel может стоять одинарная кавычка перед датой (апостроф) или присутствовать неразрывные пробелы. Функция СокрЛП() не всегда справляется с символами Unicode.
Перед парсингом строки рекомендуется выполнить глубокую очистку:
// Удаляем неразрывные пробелы (код 160)
ЧистаяСтрока = СтрЗаменить(ДатаСтр0, Символы.НПП, "");
// Удаляем возможные кавычки и лишние пробелы
ЧистаяСтрока = СтрЗаменить(ЧистаяСтрока, "'", "");
ЧистаяСтрока = СокрЛП(ЧистаяСтрока);
Подводя итог, можно сказать, что проблема "даты как строки" — это следствие особенностей работы COM-интерфейса Excel. Для ее решения можно использовать либо жесткий парсинг строк, либо переход на использование Value2, либо современный метод Прочитать() объекта ТабличныйДокумент. Последний вариант является наиболее предпочтительным для современных систем на платформе 1С:Предприятие 8.3.