Чтение Excel в 1С 8.3 программно: ТабличныйДокумент и COM

Чтение файла Excel через ТабличныйДокумент.Прочитать

Чтение Excel в 1С через табличный документ — штатная возможность платформы: метод Прочитать() загружает файл xls, xlsx или ods прямо в ТабличныйДокумент. Ни COM, ни установленный Microsoft Excel не нужны:

ТабДок = Новый ТабличныйДокумент;
ТабДок.Прочитать("C:\Files\price.xlsx",
    СпособЧтенияЗначенийТабличногоДокумента.Значение);

Сообщить("Строк: " + ТабДок.ВысотаТаблицы);
Сообщить("Колонок: " + ТабДок.ШиринаТаблицы);

Важно про контекст: метод доступен на сервере, в толстом клиенте и внешнем соединении, но не в тонком клиенте. В управляемых формах файл сначала передают на сервер (через ПоместитьФайл / двоичные данные во временном хранилище), а читают уже в серверной процедуре.

Текст или Значение: как читать содержимое ячеек

Второй параметр Прочитать() определяет, что окажется в ячейках табличного документа:

• СпособЧтенияЗначенийТабличногоДокумента.Текст — всё содержимое попадает в свойство Текст ячейки как строка. Число 1250,50 придёт строкой в локальном формате, дата — так, как отображалась в Excel.
• СпособЧтенияЗначенийТабличногоДокумента.Значение — платформа распознаёт типы: числа приходят Число, даты — Дата, и лежат в свойстве Значение ячейки. Для загрузки данных это почти всегда правильный выбор.

Если параметр не указать, содержимое будет доступно только как текст — отсюда классическая ошибка «число превратилось в строку» (разбор ниже, в частых ошибках).

Обход ячеек: Область, ВысотаТаблицы, ШиринаТаблицы

После чтения файл обходят циклом по строкам. Ячейку или прямоугольный диапазон возвращает метод Область() — имя области задаётся в нотации R1C1 («строка-колонка»):

Для НомерСтроки = 1 По ТабДок.ВысотаТаблицы Цикл
    Ячейка = ТабДок.Область("R" + НомерСтроки + "C2");
    Если Ячейка.СодержитЗначение Тогда
        Цена = Ячейка.Значение;   // типизированное значение
    Иначе
        Цена = Ячейка.Текст;      // строка «как в файле»
    КонецЕсли;
КонецЦикла;

У области ячеек три ключевых свойства: Значение (типизированное значение), Текст (строковое представление) и СодержитЗначение (признак, что ячейка прочитана как значение, а не как текст). Метод ПолучитьОбласть() с тем же форматом имени возвращает фрагмент как отдельный ТабличныйДокумент — удобно, чтобы отрезать шапку или обрабатывать файл кусками.

Чтение данных из Excel в таблицу значений

Типовая задача — чтение данных из Excel в 1С с загрузкой в таблицу значений для дальнейшей обработки: поиска номенклатуры, установки цен, создания документов. Полный серверный пример для прайса с колонками «Артикул» и «Цена»:

Функция ПрочитатьПрайсИзExcel(ИмяФайла) Экспорт

    ТабДок = Новый ТабличныйДокумент;
    ТабДок.Прочитать(ИмяФайла, СпособЧтенияЗначенийТабличногоДокумента.Значение);

    Прайс = Новый ТаблицаЗначений;
    Прайс.Колонки.Добавить("Артикул", Новый ОписаниеТипов("Строка"));
    Прайс.Колонки.Добавить("Цена",    Новый ОписаниеТипов("Число"));

    Для НомерСтроки = 2 По ТабДок.ВысотаТаблицы Цикл  // строка 1 — шапка

        Артикул   = СокрЛП(ТабДок.Область("R" + НомерСтроки + "C1").Текст);
        ЯчейкаЦены = ТабДок.Область("R" + НомерСтроки + "C2");

        Если ПустаяСтрока(Артикул) Тогда
            Продолжить;  // пустые строки в хвосте файла
        КонецЕсли;

        СтрокаПрайса = Прайс.Добавить();
        СтрокаПрайса.Артикул = Артикул;
        СтрокаПрайса.Цена = ?(ЯчейкаЦены.СодержитЗначение, ЯчейкаЦены.Значение, 0);

    КонецЦикла;

    Возврат Прайс;

КонецФункции

Артикул здесь намеренно читается из Текст: артикулы вида 007 или 1E5 при чтении «как значение» Excel-логика превращает в числа, и ведущие нули теряются. Цена, наоборот, берётся из Значение — уже готовым числом.

Чтение листов Excel: главное ограничение без COM

ТабличныйДокумент.Прочитать() читает только первый лист книги — остальные листы xlsx-файла в табличный документ не попадают. Если данные разложены по листам, варианты такие:

• договориться о формате: один лист — один файл (самое надёжное для регулярных загрузок);
• пересохранить нужный лист первым или отдельным файлом на стороне отправителя;
• читать книгу через COM — там доступна коллекция листов.

// Обход листов книги — только через COM (Windows + установленный Excel)
Excel = Новый COMОбъект("Excel.Application");
Книга = Excel.Workbooks.Open(ИмяФайла);
Для НомерЛиста = 1 По Книга.Sheets.Count Цикл
    Лист = Книга.Sheets(НомерЛиста);
    Сообщить(Лист.Name);
КонецЦикла;
Книга.Close(Ложь);
Excel.Quit();

Чтение Excel через COMОбъект Excel.Application

COM-вариант допустим только на Windows (клиент или сервер) с установленным Microsoft Excel: платформа создаёт полноценный экземпляр приложения и работает с его объектной моделью. Зато доступно всё: листы, формулы с пересчётом, форматирование, старые и защищённые файлы:

Excel = Новый COMОбъект("Excel.Application");
Excel.DisplayAlerts = Ложь;
Книга = Excel.Workbooks.Open("C:\Files\price.xls");
Лист  = Книга.Sheets(1);

ВсегоСтрок = Лист.UsedRange.Rows.Count;
Для НомерСтроки = 2 По ВсегоСтрок Цикл
    Артикул = Лист.Cells(НомерСтроки, 1).Value;
    Цена    = Лист.Cells(НомерСтроки, 2).Value;
    // ... обработка ...
КонецЦикла;

Книга.Close(Ложь);
Excel.Quit();
Excel = Неопределено;  // освободить процесс excel.exe

Обязательно закрывайте книгу и вызывайте Quit() в блоке Попытка…Исключение: каждый незакрытый вызов оставляет в памяти висящий процесс excel.exe, и на сервере они накапливаются до исчерпания памяти.

Чтение Excel в 1С: ТабличныйДокумент или COM — что выбрать

• Сервер на Linux — только ТабличныйДокумент.Прочитать(). COM — технология Windows, на Linux-сервере Новый COMОбъект не выполнится в принципе.
• Windows-сервер без установленного Excel — тоже только ТабличныйДокумент: ставить Office на сервер ради загрузки файлов — плохая практика (лицензии, зависания интерактивных диалогов, процессы-зомби).
• Регулярные фоновые загрузки (прайсы, остатки, банковские выгрузки) — ТабличныйДокумент: быстрее, стабильнее, без внешних зависимостей.
• Нужны листы кроме первого, формулы, макросы, защищённые паролем файлы — COM на Windows-машине с Excel. Часто это выносят в отдельную обработку на клиенте.

Чтение больших файлов Excel

Файлы на десятки тысяч строк читаются через ТабличныйДокумент вполне сносно, но обход ячеек и последующая обработка могут стать узким местом. Практические приёмы:

• читайте файл на сервере, а не на клиенте: серверу доступнее память, и не гоняется трафик между контекстами;
• сразу задавайте СпособЧтенияЗначений = Значение — разбор строк в числа и даты своим кодом в цикле на порядок медленнее встроенного;
• обрабатывайте файл порциями через ПолучитьОбласть("R1C1:R5000C10"), если строите из данных документы с записью в транзакции;
• не используйте COM для больших файлов: каждый вызов Cells(r, c).Value — это межпроцессное обращение, на 100 000 ячеек это минуты вместо секунд;
• для по-настоящему больших регулярных обменов договаривайтесь о CSV — его можно читать потоково через ЧтениеТекста, не загружая весь файл в память.

Частые ошибки при чтении Excel в 1С

• Числа приходят строками. Файл прочитан без второго параметра (или со способом Текст) — всё содержимое лежит в Текст как строки в локальном формате. Читайте со способом Значение и берите Область(...).Значение, проверяя СодержитЗначение.
• Даты превращаются в числа или мусор. Excel хранит даты числом дней с 1900 года. При чтении «как текст» вы получите то, что было на экране; при чтении «как значение» платформа отдаст нормальную Дату. Если формат ячейки в файле кривой — надёжнее разобрать текст самостоятельно.
• Ведущие нули теряются. Артикулы и коды вида 00123 при чтении как значение становятся числом 123. Строковые идентификаторы читайте из Текст.
• Пустые строки в хвосте файла. ВысотаТаблицы учитывает и отформатированные, но пустые строки — цикл уходит далеко за реальные данные. Проверяйте ключевую колонку через ПустаяСтрока() и прерывайте или пропускайте строку.
• Объединённые ячейки. Значение лежит только в левой верхней ячейке объединения, остальные читаются пустыми. Учитывайте это в шапках-«шапках» с группировками или требуйте файл без объединений.
• xls против xlsx. Прочитать() понимает оба формата (Excel 97–2010), а вот xlsb и файлы с паролем — нет: их предварительно пересохраняют или читают через COM.
• «Недопустимая строка с указанием класса» на сервере. Код с Новый COMОбъект("Excel.Application") выполняется на сервере, где Excel не установлен (или сервер на Linux). Либо ставьте Excel на Windows-сервер (не рекомендуется), либо переходите на ТабличныйДокумент.Прочитать().
• Вызов Прочитать() в тонком клиенте. Метод недоступен в тонком клиенте — передайте файл на сервер и читайте в серверной процедуре.

Частые вопросы о чтении Excel в 1С