При импорте данных из файлов Microsoft Excel с использованием технологии ADO разработчики часто сталкиваются с неприятной особенностью: вместо привычной строки с датой (например, «18.12.2018») программа считывает странное дробное число (например, 41990.5960416667). Попытка стандартного приведения этого числа к типу даты в 1С может дать непредсказуемый результат — дата смещается на несколько лет назад (в нашем примере получается «17.12.2014»).
В этой статье мы подробно разберем причины возникновения такого смещения, выясним, как устроено хранение дат в Excel, и разработаем надежные алгоритмы для точного восстановления даты и времени в среде «1С:Предприятие».
Для начала проанализируем поведение драйвера базы данных. При подключении к файлу Excel через провайдер Microsoft.ACE.OLEDB.12.0 или Microsoft.Jet.OLEDB.4.0 мы часто используем в строке соединения параметр IMEX=1. Этот параметр указывает драйверу принудительно приводить данные в колонках со смешанным типом к текстовому формату. Драйвер сканирует первые строки таблицы (количество строк определяется системным параметром TypeGuessRows, по умолчанию равным 8) и, обнаружив текстовые значения наряду с датами, считывает ячейку даты в ее исходном, «сыром» числовом представлении.
Внутреннее представление даты в Excel — это число с плавающей точкой (тип Double):
Главная причина смещения даты на 4 года и 1 день (ровно 1462 дня) кроется в существовании двух альтернативных систем исчисления времени в файлах Excel:
Точкой отсчета (днем номер 1) в этой системе принято считать 1 января 1900 года. Однако из-за исторического бага в ранней версии табличного процессора Lotus 1-2-3, который Microsoft скопировала для сохранения совместимости, 1900 год ошибочно считается високосным. Из-за несуществующего дня 29 февраля 1900 года реальной базовой датой для математических расчетов в 1С является 30 декабря 1899 года.
В этой системе точкой отсчета (днем номер 0) является 1 января 1904 года. Данная система создавалась для того, чтобы избежать проблемы високосного 1900 года на компьютерах Apple Macintosh. Разница между этими двумя системами составляет ровно 1462 дня.
Если файл Excel был создан на компьютере Mac или в его параметрах принудительно включена опция «Использовать систему дат 1904», число 41990.5960416667 будет означать 18.12.2018. Если же преобразовать его по стандартной Windows-схеме (от 1900 года), мы получим ошибочный 2014 год.
Выяснив причину, рассмотрим по шагам варианты решения этой задачи в 1С.
Если вы точно знаете, что загружаемые файлы приходят от пользователей macOS или в вашей организации стандартизирована система дат 1904, мы можем применить простую математическую корректировку — прибавить к полученному значению константу сдвига (1462 дня) или производить расчет от базовой даты 01.01.1904.
Рассмотрим пример функции, которая принимает число из ADO и преобразует его в полноценную дату 1С:
Функция ПреобразоватьЧислоВДату_ФиксированныйСдвиг(Знач ЧислоExcel, Использовать1904 = Ложь) Экспорт
// Определяем базовую дату в зависимости от используемой системы
Если Использовать1904 Тогда
БазоваяДата = '19040101000000'; // 1 января 1904 года
Иначе
БазоваяДата = '18991230000000'; // 30 декабря 1899 года (с учетом бага 1900 года)
КонецЕсли;
// Приводим строковое представление к числу
ЧислоДней = Число(СтрЗаменить(ЧислоExcel, ",", "."));
// Вычисляем общее количество секунд. В сутках 86400 секунд.
ВсегоСекунд = ЧислоДней * 86400;
// Округляем до целого числа, чтобы избежать микросекундных погрешностей типа Double
ВсегоСекундОкр = Окр(ВсегоСекунд, 0, РежимОкругления.Округление1515);
Возврат БазоваяДата + ВсегоСекундОкр;
КонецФункции
Перекладывать на плечи конечного пользователя выбор настройки «Файл создан на Mac» — не лучшее решение. Мы можем полностью автоматизировать этот процесс. Современные файлы Excel с расширением .xlsx представляют собой обычные ZIP-архивы. Системные настройки книги хранятся внутри этого архива в XML-формате.
Проанализируем структуру архива. Нам нужен всего один файл: xl/workbook.xml. Внутри него расположен узел <workbookPr>. Если в нем присутствует атрибут date1904="1" (или date1904="true"), значит книга использует систему дат 1904 года. Если этот атрибут равен 0 или вовсе отсутствует — используется стандартная система 1900 года.
Разберем по шагам алгоритм автоматического определения базовой даты:
ЧтениеZipФайла, откроем архив документа Excel.xl/workbook.xml во временный каталог.ЧтениеXML прочитаем параметры книги и определим значение атрибута date1904.Реализуем этот механизм в виде программного кода на платформе «1С:Предприятие»:
Функция ПолучитьБазовуюДатуExcel(ПутьКФайлу) Экспорт
БазоваяДата = '18991230000000'; // Значение по умолчанию (система 1900)
// Создаем уникальный временный каталог для распаковки служебного XML
ИмяВременнойПапки = ПолучитьИмяВременногоФайла("");
СоздатьКаталог(ИмяВременнойПапки);
Попытка
Архив = Новый ЧтениеZipФайла(ПутьКФайлу);
// Извлекаем только один нужный нам файл настроек книги
ЭлементАрхива = Архив.Элементы.Найти("xl/workbook.xml");
Если ЭлементАрхива <> Неопределено Тогда
Архив.Извлечь(ЭлементАрхива, ИмяВременнойПапки, РежимВосстановленияПутейФайловZIP.НеВосстанавливать);
КонецЕсли;
Архив.Закрыть();
Исключение
// Если файл заблокирован или не является корректным zip-архивом (например, старый формат .xls)
УдалитьФайлы(ИмяВременнойПапки);
Возврат БазоваяДата;
КонецПопытки;
ПутьКФайлуXML = ИмяВременнойПапки + "workbook.xml";
ФайлXML = Новый Файл(ПутьКФайлуXML);
Если ФайлXML.Существует() Тогда
ЧтениеXML = Новый ЧтениеXML;
ЧтениеXML.ОткрытьФайл(ПутьКФайлуXML);
Пока ЧтениеXML.Прочитать() Цикл
Если ЧтениеXML.ТипУзла = ТипУзлаXML.НачалоЭлемента И ЧтениеXML.Имя = "workbookPr" Тогда
// Ищем атрибут date1904
ЗначениеАтрибута = ЧтениеXML.ПолучитьАтрибут("date1904");
Если ЗначениеАтрибута = "1" Или ВРег(ЗначениеАтрибута) = "TRUE" Тогда
БазоваяДата = '19040101000000'; // Переключаемся на систему Mac
КонецЕсли;
Прервать; // Нужные параметры найдены, выходим из цикла
КонецЕсли;
КонецЦикла;
ЧтениеXML.Закрыть();
КонецЕсли;
// Обязательно производим очистку временных файлов
УдалитьФайлы(ИмяВременнойПапки);
Возврат БазоваяДата;
КонецФункции
Теперь объединим все наши наработки в единый готовый блок кода. Мы считаем файл Excel, автоматически определим его систему исчисления времени, подключимся через ADO и корректно заполним результирующую таблицу значений, восстановив дату и время с высокой точностью:
Процедура ЗагрузитьДанныеИзExcel(ИмяФайла)
// Шаг 1. Автоматически определяем базовую дату документа
БазоваяДата = ПолучитьБазовуюДатуExcel(ИмяФайла);
// Шаг 2. Настраиваем подключение ADO
ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + СокрЛП(ИмяФайла) + ";Extended Properties=""Excel 12.0;HDR=YES;IMEX=1;""";
ADODBConnection = Новый COMОбъект("ADODB.Connection");
ADODBConnection.ConnectionString = ConnectionString;
ADODBConnection.Open();
ADODBConnection.CursorLocation = 3;
// Получаем имя первого листа книги
Catalog = Новый COMОбъект("ADOX.Catalog");
Catalog.ActiveConnection = ConnectionString;
ИмяЛиста = СокрЛП(Catalog.Tables(0).Name);
ТекстЗапроса = "SELECT * FROM [" + ИмяЛиста + "]";
ADODBRecordset = Новый COMОбъект("ADODB.Recordset");
ADODBRecordset.Open(ТекстЗапроса, ADODBConnection);
// Создаем структуру Таблицы Значений для приема данных
ТаблицаРезультат = Новый ТаблицаЗначений;
КолвоКолонокExcel = ADODBRecordset.Fields.Count;
Для Ит = 1 ПО КолвоКолонокExcel Цикл
Поле = ADODBRecordset.Fields.Item(Ит - 1);
ИмяКолонки = "К_" + Ит;
ТаблицаРезультат.Колонки.Добавить(ИмяКолонки, , СокрЛП(СтрЗаменить(Поле.Name, "#", ".")));
КонецЦикла;
// Шаг 3. Чтение данных и конвертация числовых дат
Пока ADODBRecordset.EOF() = 0 Цикл
НоваяСтрока = ТаблицаРезультат.Добавить();
Для Ит = 1 ПО КолвоКолонокExcel Цикл
Поле = ADODBRecordset.Fields.Item(Ит - 1);
Если Поле.ActualSize = 0 Тогда
Продолжить;
КонецЕсли;
ЗначениеЯчейки = Строка(Поле.Value);
ИмяКолонки = "К_" + Ит;
// Проверяем, похоже ли считанное значение на числовую дату Excel.
// Шаблон проверяет регулярный формат вещественного числа (например: 41990.5960416667)
Если Найти(ЗначениеЯчейки, ".") > 0 И Найти(ЗначениеЯчейки, " ") = 0 Тогда
Попытка
ЧисловоеЗначение = Число(СтрЗаменить(ЗначениеЯчейки, ",", "."));
// Проверяем диапазон дат, чтобы случайно не преобразовать обычные числа (например, суммы)
Если ЧисловоеЗначение > 36525 И ЧисловоеЗначение < 73050 Тогда // Диапазон дат примерно с 2000 по 2100 гг.
ВсегоСекунд = ЧисловоеЗначение * 86400;
// Важно округлить секунды до целого во избежание сдвига на 1 секунду из-за погрешности double
ВсегоСекундОкр = Окр(ВсегоСекунд, 0, РежимОкругления.Округление1515);
НоваяСтрока[ИмяКолонки] = БазоваяДата + ВсегоСекундОкр;
Продолжить;
КонецЕсли;
Исключение
// Ошибка преобразования в число, оставляем как строку
КонецПопытки;
КонецЕсли;
// Если это обычный текст или число вне диапазона дат
НоваяСтрока[ИмяКолонки] = ЗначениеЯчейки;
КонецЦикла;
ADODBRecordset.MoveNext();
КонецЦикла;
ADODBRecordset.Close();
ADODBConnection.Close();
// Теперь ТаблицаРезультат заполнена корректными датами
КонецПроцедуры
Благодаря данному алгоритму, ваша обработка будет безошибочно распознавать любые типы исходных файлов Excel вне зависимости от операционной системы, в которой они были созданы, сохраняя точность даты и времени вплоть до секунды.