При разработке обработок загрузки данных из файлов Excel разработчики часто сталкиваются с фундаментальной разницей между значением ячейки (свойство Value в объектной модели COM) и ее отображаемым текстовым представлением (свойство Text).
Рассмотрим простую ситуацию: в ячейке Excel записано число 000125 или дата в формате 01.12.2023. При обычном программном чтении значений (через выгрузку области Value в массив) мы получим число 125 (ведущие нули пропадут) и стандартную дату-время. Чтобы сохранить исходный текстовый вид, маски валют, артикулы с ведущими нулями или сложные форматы, нам необходимо считывать именно свойство Text.
Однако если мы попытаемся обойти каждую ячейку в цикле через COM-соединение с помощью конструкции Sheet.Cells(Строка, Колонка).Text, производительность обработки упадет в сотни раз. Чтение таблицы из 10 000 строк может затянуться на долгие минуты, а то и часы, из-за постоянных накладных расходов на вызовы через OLE-границу.
В этой статье мы подробно проанализируем четыре лучших и максимально быстрых альтернативных способа решения этой задачи в 1С.
Начиная с современных версий платформы 1С (8.3.10 и выше), у табличного документа появился параметр, управляющий режимом интерпретации данных при чтении. Мы можем явно указать платформе читать данные файла именно как отображаемый текст.
Разберем по шагам, как загрузить данные файла Excel в ТабличныйДокумент и моментально преобразовать их в обычную ТаблицуЗначений с помощью ПостроителяЗапроса:
// Создаем новый табличный документ для загрузки внешнего файла
ТабДок = Новый ТабличныйДокумент;
// Важнейший шаг: читаем файл, передавая вторым параметром СпособЧтенияЗначенийТабличногоДокумента.Текст
// Это заставляет 1С парсить именно форматированный текст ячеек Excel
ТабДок.Прочитать(Объект.ПутьКФайлу, СпособЧтенияЗначенийТабличногоДокумента.Текст);
// Определяем область для построителя запроса (от первой ячейки до правого нижнего угла)
КоличествоСтрок = ТабДок.ВысотаТаблицы;
КоличествоКолонок = ТабДок.ШиринаТаблицы;
Если КоличествоСтрок > 0 И КоличествоКолонок > 0 Тогда
Построитель = Новый ПостроительЗапроса;
// Описываем источник данных на основании области табличного документа
Построитель.ИсточникДанных = Новый ОписаниеИсточникаДанных(ТабДок.Область(1, 1, КоличествоСтрок, КоличествоКолонок));
Построитель.Выполнить();
// Получаем готовую ТаблицуЗначений с текстовыми представлениями за доли секунды
ТаблицаДанных = Построитель.Результат.Выгрузить();
КонецЕсли;
Этот метод работает невероятно быстро. Таблица из 20 000 строк считывается и парсится в коллекцию 1С буквально за 1–2 секунды. Однако у этого подхода есть несколько критически важных особенностей, которые необходимо учитывать:
Текст могут считываться с микроскопической погрешностью (например, число 8733.00 может загрузиться в 1С как строка "8732.99"). Если в файле содержатся важные финансовые показатели, требующие точного совпадения копеек, будьте аккуратны.ПостроительЗапроса при автоматическом формировании таблицы значений может дублировать строки или смещать колонки. Для обхода этой проблемы рекомендуется отказаться от ПостроителяЗапроса и обойти строки самого ТабДок в обычном цикле. Это будет чуть медленнее, но все равно в десятки раз быстрее, чем прямое COM-соединение:
ТаблицаДанных = Новый ТаблицаЗначений;
Для НомерКолонки = 1 По КоличествоКолонок Цикл
ТаблицаДанных.Колонки.Добавить("Колонка_" + НомерКолонки);
КонецЦикла;
Для НомерСтроки = 1 По КоличествоСтрок Цикл
НоваяСтрока = ТаблицаДанных.Добавить();
Для НомерКолонки = 1 По КоличествоКолонок Цикл
// Извлекаем чистый текст ячейки напрямую из ячейки ТабДок
НоваяСтрока["Колонка_" + НомерКолонки] = ТабДок.Область(НомерСтроки, НомерКолонки).Текст;
КонецЦикла;
КонецЦикла;
Малоизвестный, но потрясающе быстрый метод, сочетающий гибкость COM-соединения и мгновенную передачу данных. Когда мы копируем диапазон ячеек в буфер обмена операционной системы, Excel самостоятельно преобразует все ячейки в плоский структурированный текст, разделенный знаками табуляции. При этом в буфер попадает именно отображаемый форматированный текст каждой ячейки.
Проанализируем программную схему реализации этого метода:
// Инициализируем COM-соединение с Excel
ExcelApp = Новый COMОбъект("Excel.Application");
ExcelApp.Visible = Ложь;
ExcelApp.DisplayAlerts = Ложь;
Book = ExcelApp.Workbooks.Open(Объект.ПутьКФайлу);
Sheet = Book.Worksheets(1);
// Выделяем всю заполненную область листа
ВсегоСтрок = Sheet.Cells(1,1).SpecialCells(11).Row;
ВсегоКолонок = Sheet.Cells(1,1).SpecialCells(11).Column;
ОбластьДанных = Sheet.Range(Sheet.Cells(1, 1), Sheet.Cells(ВсегоСтрок, ВсегоКолонок));
// Копируем всю область в буфер обмена одной быстрой командой
ОбластьДанных.Copy();
// Извлекаем данные из системного буфера обмена в 1С
ТекстИзБуфера = "";
#Если Клиент Тогда
// Для новых версий платформы 1С (начиная с 8.3.24) используем штатный механизм:
Попытка
ТекстИзБуфера = СредстваБуфераОбмена.ПолучитьТекст();
Исключение
// Резервный метод через легкий COM-объект htmlfile (для старых версий платформы)
Попытка
HTMLFile = Новый COMОбъект("htmlfile");
ТекстИзБуфера = HTMLFile.ParentWindow.ClipboardData.GetData("Text");
Исключение
Сообщить("Не удалось прочитать буфер обмена!");
КонецПопытки;
КонецПопытки;
#КонецЕсли
// Закрываем Excel, он нам больше не нужен
Book.Close(Ложь);
ExcelApp.Quit();
ExcelApp = Неопределено;
// Разбираем полученную строку
ТаблицаРезультат = Новый ТаблицаЗначений;
// (Код создания колонок в ТаблицеРезультат на основании первой строки данных)
МассивСтрок = РасщепитьСтрокуПоРазделителю(ТекстИзБуфера, Символы.ПС);
Для Каждого СтрокаТекста Из МассивСтрок Цикл
МассивЯчеек = РасщепитьСтрокуПоРазделителю(СтрокаТекста, Символы.Таб);
// Заполняем строки таблицы значений
КонецЦикла;
Этот способ позволяет "выплюнуть" миллионы ячеек в текстовую переменную 1С за долю секунды. Парсинг строки по разделителям Символы.ПС (новая строка) и Символы.Таб (новая колонка) происходит на стороне 1С мгновенно.
Многие разработчики предлагают использовать сохранение в формат CSV для последующего быстрого парсинга текстовым файлом. Однако формат CSV имеет серьезные недостатки: он часто искажает кодировки, теряет ведущие нули и ломает структуру данных при наличии запятых и кавычек внутри текста ячеек.
Гораздо более надежной альтернативой является программное сохранение листа Excel в формат HTML. В этом формате Excel жестко прописывает внутри тегов <td>...</td> именно то текстовое представление, которое отображалось на экране.
Рассмотрим код выгрузки страницы в HTML-файл с его последующим чтением:
ExcelApp = Новый COMОбъект("Excel.Application");
ExcelApp.Visible = Ложь;
ExcelApp.DisplayAlerts = Ложь;
Book = ExcelApp.Workbooks.Open(Объект.ПутьКФайлу);
// 44 — это внутренний код формата xlHtml в объектной модели Microsoft Office Excel
ВременныйHTMLФайл = ПолучитьИмяВременногоФайла("html");
Book.SaveAs(ВременныйHTMLФайл, 44);
Book.Close(Ложь);
ExcelApp.Quit();
ExcelApp = Неопределено;
// Читаем полученный файл в 1С с помощью объекта ЧтениеHTML или через регулярные выражения
ЧтениеHTML = Новый ЧтениеHTML;
ЧтениеHTML.ОткрытьФайл(ВременныйHTMLФайл, "UTF-8");
// Выполняем обход структуры документа и вытягиваем текстовое содержимое тегов ячеек
// Данный метод гарантирует получение точных строковых данных без искажения копеек и ведущих нулей
Если задача чтения файлов Excel стоит на стороне сервера 1С (под управлением Windows), где сам офисный пакет MS Excel не установлен, лучшим выбором будет использование свободной и бесплатной библиотеки ExcelDataReader через .NET-мост платформы 1С.
Данная библиотека работает напрямую с файлом как с ZIP-архивом, быстро парсит XML-структуру данных и содержит встроенный механизм автоматического применения масок форматирования к сырым значениям. Это означает, что библиотека сама подставит ведущие нули и отформатирует даты согласно правилам, записанным внутри книги Excel.
Схематичный пример подключения библиотеки и чтения данных в 1С выглядит следующим образом:
// Подключаем внешнюю сборку .NET
ПодключитьВнешнююКомпоненту("C:\Libs\ExcelDataReader.dll", "ExcelDataReader", ТипВнешнейКомпоненты.CLR);
// Инициализируем чтение файлового потока
FileStream = Новый ("System.IO.FileStream", Объект.ПутьКФайлу, ПолучитьРежимФайлаOpen(), ПолучитьДоступFileAccessRead());
ExcelReader = Новый ("ExcelDataReader.ExcelReaderFactory");
Reader = ExcelReader.CreateReader(FileStream);
// Читаем данные с автоприменением форматирования (AsDataSet)
// На выходе получаем готовый DataSet, где все типы приведены к текстовому виду, аналогично .Text в Excel
Использование .NET-библиотек позволяет добиться стабильной работы кода на сервере без зависаний OLE-процессов excel.exe в памяти сервера.
Выясним, какой метод лучше использовать в зависимости от архитектурных условий вашей задачи: